Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #3044

Re: Validating Currency Entries

Show all headers | View raw

On 2018-02-23 00:26:16 +0000, Charles H. Sampson said:

> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
> 
>> On 2018-02-22 03:05:56 +0000, Charles H. Sampson said:
>>> 
>>> Is there an easy way to validate a numeric entry that is supposed to be
>>> currency (dollars)? At a minimum, I mean that the entry should have only
>>> decimal digits, at most one decimal point, and at most two fractional
>>> digits. I know how to do this using Filter, Position, and Length, but
>>> that's a lot of typing for each currency entry and my databases have a
>>> lot of them.
>>> 
>>> A more elaborate test would allow commas, but only in "correct"
>>> positions. A misplaced comma in a number entry usually indicates a
>>> typing error. Letting it go unchecked, by just ignoring the commas,
>>> could be disastrous.
>>> 
>>> All my FMP databases are for personal use. I'm having a lot of trouble
>>> at the moment with the magic keyboard on my iMac, often typing "/"
>>> instead of ".". Manual verification of results usually uncovers the
>>> errors -•‰ I hope -•‰ but I would like to have them caught at 
>>> the time of
>>> the mistake.
>>> 
>>> Charlie
>> 
>> The best way is to use a Number field and store it as only a number
>> (including the decimal point and negative symbol) - there's no need to
>> enter any commas at all and simply makes more data entry work than is
>> needed. The correct commas and currency symbol can be added by
>> formatting the field.
> 
> There's no need to enter commas, but I want to allow the user (me) to
> use commas to make long numbers easier to understand. That's the concept
> of clumping.

Changing the formatting options for the Field on the Layout(s) will 
mean FileMaker Pro will display the commas (and the currency symbol) 
itself without you having to specifically enter them every time. The 
same can be done in Excel and other spreadsheet programs around these 
days.

The only time the commas won't be displayed is when you actually click 
into the Field to edit the data. You could get around that if necessary 
by using an Auto-enter Calculation to add in the commas for you.




>> The Validation really only needs to make sure nothing except numbers, a
>> decimal point, and a negative symbol are entered ... which is VERY easy
>> since there is a Validation option of "Strict Data Type: Numeric Only".
>> Any data entered failing that validation can display an error message
>> (making sure to not let the user override the error message).
> 
> I can do most of that pretty easily, but my validation test requires
> three function references. That's a lot of typing and testing for the
> large number of currency fields that I have. Even then, it still doesn't
> catch the error in entering 12.340. From the data entry view, that's an
> indication that something is wrong with the entry - the user did
> something wrong and just accepting it because one interpretation of the
> entry works is an error on the implementor's part, IMO.
> 
> I can catch the latter error by making my validation test even more
> complicated.
> 
> By the way, I think I have a fix that satisfies me, but I haven't tested
> it yet. See below.
> 
>> Another method for your particular "/" typo could be to set the
>> Auto-enter by Calculation option for the Field to be:
>> 
>> Substitute ( {insert number fieldname} ; "/" ; "." )
>> 
>> remembering to turn off the "Do not replace existing value" option.
>> That way FileMaker Pro will automatically replace your "/" typo with a
>> ".". The only problem here is if you enter something unlikely such as
>> "35/37.52", which would result in "35.37.52" begin stored (FileMaker
>> Pro will ignore the second "." and any calculations will use the
>> nonsense currency amount "35.3752").
> 
> I don't often disagree with your help, Harry, but this time I do. I
> consider that disastrous, that such an incorrect entry as 35/37.52 would
> be silently accepted and processing would continue without warning the
> poor user.

I didn't say it couldn't be caught, only what FileMaker Pro would do 
with data entred in that unlikely way.



> Here's what I think will work for me. I can write a pretty simple script
> that will make sure that only a correct value is entered. I then attach
> that script to the "exit field" event. The beauty part is that I can
> easly import that script into any data base and use it to validate all
> the currency fields of that data base. I'll let you know how that works.

The simplest way is to use the "Strict Data Type: Numeric Only" 
validation option. You can also add a By Calculation validation option 
to check for extra digits after the decimal place or multiple decimal 
places. The easiest being:

    Int ( NumberField * 100 ) / 100 = NumberField

That won't catch a trailing zero (e.g. 15.230) or missing end zero(s) 
(e.g. 15 or 15.7) in data entry, but the extra / missing zeroes are 
irrelevant since they don't affect any other calcualtions and can 
easily be removed / added via the Field's formatting options being set 
to display two decimal places.

The only real issue is that FileMaker Pro only allows one error 
message, so there's now way to specify exactly which validation option 
was failed.


Helpful Harry   :o)

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Validating Currency Entries csampson@inetworld.net (Charles H. Sampson) - 2018-02-21 19:05 -0800
  Re: Validating Currency Entries Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-02-22 19:43 +1300
    Re: Validating Currency Entries csampson@inetworld.net (Charles H. Sampson) - 2018-02-22 16:26 -0800
      Re: Validating Currency Entries Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-02-23 15:19 +1300
        Re: Validating Currency Entries csampson@inetworld.net (Charles H. Sampson) - 2018-02-24 18:16 -0800
      Re: Validating Currency Entries csampson@inetworld.net (Charles H. Sampson) - 2018-02-24 18:16 -0800
        Re: Validating Currency Entries Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-02-25 18:09 +1300
          Re: Validating Currency Entries csampson@inetworld.net (Charles H. Sampson) - 2018-03-03 23:34 -0800
            Re: Validating Currency Entries ursus.kirk@gmail.com - 2018-03-04 13:15 +0100
              Re: Validating Currency Entries csampson@inetworld.net (Charles H. Sampson) - 2018-03-07 12:50 -0800
                Re: Validating Currency Entries ursus.kirk@gmail.com - 2018-03-08 09:54 +0100
                Re: Validating Currency Entries csampson@inetworld.net (Charles H. Sampson) - 2018-03-11 18:15 -0700
            Re: Validating Currency Entries Howard Schlossberg <howard@nospam.fmprosolutions.com> - 2018-03-04 10:24 -0800
              Re: Validating Currency Entries csampson@inetworld.net (Charles H. Sampson) - 2018-03-07 12:50 -0800
  Re: Validating Currency Entries Erik Appeldoorn <ursus.kirk@gmail.com> - 2018-02-22 09:18 +0100
  Re: Validating Currency Entries Martin Τrautmann <t-usenet@gmx.net> - 2018-03-08 13:19 +0000
    Re: Validating Currency Entries csampson@inetworld.net (Charles H. Sampson) - 2018-03-11 18:15 -0700

csiph-web