Clobbered by a Comma

Last year, I added credit card processing to a existing FileMaker Point of Sale (POS) system.  The system, CCQFM, was the only one offering credit card processing without the help of a plugin, and, as such, would work properly on an iPad.  Here’s the explanation from their website on how it works:

CCQ-FM consists of 2 files.

ccqfm.php is an encrypted PHP file that performs the processing. The second file CIDProcessCard.php* is created for your specific merchant account and is also encrypted.

The programming from FileMaker Desktop and FM GO is the same. Programming from the web is basically the same as well.

Transaction are processed using a URL . It must be sent as https://

For FileMaker, Pro or Go, you can use a Web Viewer to send the URL and the response is returned in the web viewer. The other option is Insert From URL (FM 12 Only) ** The response is returned to the field specified.

So, FileMaker sends an https url to the two files located on a secure web server, which in turn communicates with the CC processing center.  The processing center returns a data packet with the approval or decline info, along with codes, error messages (if any), and such. FileMaker grabs and parses out the data packet, and updates the invoice, payment table, and an audit table I threw in to keep track of what happens, by whom, and when.  One thing the data packet does not include is the amount that was approved.

This system was a bit of work to install and test, but worked pretty well for over a year after the bugs were worked out.

A couple of weeks ago, this store sold an item for $17oo.  The next morning, when the bookkeeper reconciled with the cc processing center, she discovered that the transaction was approved for $1.  The owner was not happy.

I checked the code, poked around some, and checked the audit log.  The audit records showed all was in order.  Of course, since the data packet does not send back an amount, I couldn’t find the error in the audit log.  Next, I called the processing center.  They confirmed that the amount sent to their system was $1.  That pretty much stymied me, as this had not happened before in other large sales.

As this was a one off problem, and all was working well since, we decided to wait and see if it happened again.

The next weekend, a sale for $4300 came back authorized for $4.

A little more investigation followed, and the bookkeeper told me the same person rang up both sales.  And she thinks that person typed in the comma:  $4,300.00.   Now, I had formatted the field to show a comma, but hadn’t considered what would happen if a person typed the comma in the number.

As it turns out, the php truncates the comma, and everything to the right of it.  So, we were clobbered by a comma. Knowing that, the solution was easy: substitute out the comma when assigning the payment amount to a variable:

Set Variable [$$PaymentAmount; Value:Substitute ( newRecord::gPaymentAmount; “,” ; “” )]

I’m happy to say that little bit of code saved the day.  And really, the problem was with me not knowing this would happen, not with CCQFM or the processing company.  In the future, I will investigate what constitutes a proper URL and strip out any illegal characters.  But I’ll have to be careful not to strip out too much.

Oh, and by the way, the owner did get all of his money.  He knew both customers and they made everything right.  So, a happy ending for all!