Editor’s Note: An often-ignored reality of data management is that entering data, correctly and cleanly, into all of these database apps we build can be a royal pain in the…
Assumptions can be dangerous, like assuming that your office manager-doubling-as-bookkeeper wants to manually calculate tax on every Invoice row. Or even has the time to.
Not many job titles (anymore) are simply “Data Entry Manager,” and yet entering data is a task that continually must be managed, by one or by many, in the role of “Inputter”. Just entering data can be tedious enough — so, let’s not make it harder. In fact, let’s strive to make it easier. With that in mind, Beezwax Senior Developer Christos Savva provides an example of performing math in a FileMaker field, using a custom function. This is designed to improve data entry user experience, for the sake of Inputters everywhere.
Why Make Data Input Harder?
Inputting purchase orders or invoices in accounting systems can be quite a hassle for bookkeepers, accountants, data administrators or anyone who simply has the role of “Inputter”. Each company may have its own invoicing theme and sometimes information is not always ready for easy input.
For example, an invoice line item may be shown with gross amount and discount as a percentage. The user however would want to input such a line item of the purchase invoice using the net amount. What happens at this point is the user would need to grab a calculator and manually do the calculation. Similarly, what if a discount percentage is applied at the end of the invoice and you need to apply it on individual lines instead?
Another example is a purchase invoice showing products in boxed quantities but the bookkeeper wants to enter individual quantities, i.e. 6 instead of 1 box of 6. Again the process is to grab a calculator and do the math. This of course causes frustration from reverting to manual data management, especially if we are talking about large volumes of data input.
When You Can Make Data Input Easier
A quick timesaver for all this hustle is to create automatically calculating fields. Below is a straightforward custom function called InputCalculation ( expr ) that you can use that utilizes FileMaker’s native Evaluate function.
InputCalculation( expr ) =
Let([ decimal = Left( 1/2 ; 1 ) ; filterText = "0123456789+-*/()^" & decimal ; filteredExpr = Filter ( expr ; filterText ) ]; Evaluate ( filteredExpr ) )
We could, of course, just use the Evaluate function on its own but that might be dangerous as you can evaluate just about anything like globals, ExecuteSQL and so on. Instead we are pre-filtering our inputted mathematical expression to just numbers, the system’s decimal character, the four main math operators and brackets for more complicated calculations. I threw in the power function ^ also just for fun…or if you are thinking of launching a missile from your office! You will note there is no % character for percentages since it’s not part of FileMaker’s native math operators. In such a case you would use 0.1 instead of 10%.
And that’s almost it really. All you need to do now is edit the fields that you want, e.g. Quantity, Amount, LineTotal, to use Auto-Enter → Calculatated Value → InputCalculation ( Self ).
Now, you are all set. Your data “Inputters” will thank you! Twice! Or perhaps just once…