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
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
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.
FileMaker Pro is rather forgiving about entering non-numeric characters
being typed into Number fields - it simply ignored them, which means
typing "15/65" would result in the amount 1,565 being stored rather
than 15.65.
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).
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").
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.
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.
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.
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.
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.
Charles H. Sampson <csampson@inetworld.net> wrote:
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.
And it works like a champ. I took a bit of time because I had to code it brute force; finite state machines are hard to implement in FMP's script language. I added several filips. For example, if invalid characters are entered, they are displayed one-by-one (in a single error message
dialog) with their positions in the string. That's the kind of nonsense
I do when I get carried away, when the fit is upon me, as I've said in
the past.
There's only one thing I wanted to do that I haven't figured out yet.
Based on the user's response to the error message, I wanted to either
simply put the cursor in the incorrect data, as entered, or I wanted to
clear the field and put the cursor there. In my extensive testing, this doesn't seem to be a big deal.
On 2018-02-25 02:16:59 +0000, Charles H. Sampson said:
Charles H. Sampson <csampson@inetworld.net> wrote:
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.
And it works like a champ. I took a bit of time because I had to code it brute force; finite state machines are hard to implement in FMP's script language. I added several filips. For example, if invalid characters are entered, they are displayed one-by-one (in a single error message
dialog) with their positions in the string. That's the kind of nonsense
I do when I get carried away, when the fit is upon me, as I've said in
the past.
There's only one thing I wanted to do that I haven't figured out yet.
Based on the user's response to the error message, I wanted to either simply put the cursor in the incorrect data, as entered, or I wanted to clear the field and put the cursor there. In my extensive testing, this doesn't seem to be a big deal.
You can't position the text cursor at a certain point inside a Field's
data (although there may be a plug-in that can). The only thing you can
do is use the "Go To Field [Fieldname]", with the option of either
selecting all the Field's data value or none of it.
There is the Set Selection command which could select the (first)
error, but that would only highlight one error and be a painful
approach if there are multiple errors.
You could use the formatting comamnds within a Set Field command to
highlight the errors in a different colour / style, but the colour /
style will probably stay after the user corrects it (at least until the
error check was performed again which could clear all extra error formatting).
Clearing the Field's data isn't a good idea. Not only is the user
unable to see what the problem was, they also have to re-enter the
whole thing.
If you validate a field by
calculation and the validation fails, the user is presented with a
message whose two responses are "Revert Record" and "OK". "OK" leaves
the contents alone and places the cursor at the end of that field;
"Revert Record" clears the whole record — after getting a confirmation — and places the cursor in the first field. Something like that is what I
hoped to immitate, maybe a little friendlier. I'm not sure clearing the
whole record is a good idea.
Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
On 2018-02-25 02:16:59 +0000, Charles H. Sampson said:
Charles H. Sampson <csampson@inetworld.net> wrote:
And it works like a champ. I took a bit of time because I had to code it >>> brute force; finite state machines are hard to implement in FMP's script >>> language. I added several filips. For example, if invalid characters are >>> entered, they are displayed one-by-one (in a single error message
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. >>>
dialog) with their positions in the string. That's the kind of nonsense
I do when I get carried away, when the fit is upon me, as I've said in
the past.
There's only one thing I wanted to do that I haven't figured out yet.
Based on the user's response to the error message, I wanted to either
simply put the cursor in the incorrect data, as entered, or I wanted to
clear the field and put the cursor there. In my extensive testing, this
doesn't seem to be a big deal.
You can't position the text cursor at a certain point inside a Field's
data (although there may be a plug-in that can). The only thing you can
do is use the "Go To Field [Fieldname]", with the option of either
selecting all the Field's data value or none of it.
There is the Set Selection command which could select the (first)
error, but that would only highlight one error and be a painful
approach if there are multiple errors.
You could use the formatting comamnds within a Set Field command to
highlight the errors in a different colour / style, but the colour /
style will probably stay after the user corrects it (at least until the
error check was performed again which could clear all extra error
formatting).
All good points and I essentially agree. If you validate a field by calculation and the validation fails, the user is presented with a
message whose two responses are "Revert Record" and "OK". "OK" leaves
the contents alone and places the cursor at the end of that field;
"Revert Record" clears the whole record — after getting a confirmation — and places the cursor in the first field. Something like that is what I
hoped to immitate, maybe a little friendlier. I'm not sure clearing the
whole record is a good idea.
Clearing the Field's data isn't a good idea. Not only is the user
unable to see what the problem was, they also have to re-enter the
whole thing.
Right. Yet that's what FileMaker does.
I'm stuck right now because I don't know how to get to the field that
has the error. I think I saw something about that when poking around in
the on-line documentation but I foolishly didn't write it down.
Charlie
Op 4-3-2018 om 08:34 schreef Charles H. Sampson:
Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
On 2018-02-25 02:16:59 +0000, Charles H. Sampson said:
Charles H. Sampson <csampson@inetworld.net> wrote:
And it works like a champ. I took a bit of time because I had to code it >>> brute force; finite state machines are hard to implement in FMP's script >>> language. I added several filips. For example, if invalid characters are >>> entered, they are displayed one-by-one (in a single error message
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. >>>
dialog) with their positions in the string. That's the kind of nonsense >>> I do when I get carried away, when the fit is upon me, as I've said in >>> the past.
There's only one thing I wanted to do that I haven't figured out yet.
Based on the user's response to the error message, I wanted to either
simply put the cursor in the incorrect data, as entered, or I wanted to >>> clear the field and put the cursor there. In my extensive testing, this >>> doesn't seem to be a big deal.
You can't position the text cursor at a certain point inside a Field's
data (although there may be a plug-in that can). The only thing you can
do is use the "Go To Field [Fieldname]", with the option of either
selecting all the Field's data value or none of it.
There is the Set Selection command which could select the (first)
error, but that would only highlight one error and be a painful
approach if there are multiple errors.
You could use the formatting comamnds within a Set Field command to
highlight the errors in a different colour / style, but the colour /
style will probably stay after the user corrects it (at least until the
error check was performed again which could clear all extra error
formatting).
All good points and I essentially agree. If you validate a field by calculation and the validation fails, the user is presented with a
message whose two responses are "Revert Record" and "OK". "OK" leaves
the contents alone and places the cursor at the end of that field;
"Revert Record" clears the whole record — after getting a confirmation —
and places the cursor in the first field. Something like that is what I hoped to immitate, maybe a little friendlier. I'm not sure clearing the whole record is a good idea.
Clearing the Field's data isn't a good idea. Not only is the user
unable to see what the problem was, they also have to re-enter the
whole thing.
Right. Yet that's what FileMaker does.
I'm stuck right now because I don't know how to get to the field that
has the error. I think I saw something about that when poking around in
the on-line documentation but I foolishly didn't write it down.
Charlie,
Did you ever read my contribution about validating numbers in filemaker
on comp.databases.filemaker? I'm sure at least part of it is usuable.
And it does result in a value where the user can see what he/she has
done different to the requirements. And fixes any errors that might have
been made while entering the value.
On 3/3/2018 11:34 PM, Charles H. Sampson wrote:
If you validate a field by
calculation and the validation fails, the user is presented with a
message whose two responses are "Revert Record" and "OK". "OK" leaves
the contents alone and places the cursor at the end of that field;
"Revert Record" clears the whole record — after getting a confirmation —
and places the cursor in the first field. Something like that is what I hoped to immitate, maybe a little friendlier. I'm not sure clearing the whole record is a good idea.
Charlie, I haven't followed the entire thread, but there are two things
I wonder about here:
1) Someone suggested setting the field validation using the checkbox for "allow numbers only". That would immediately notify the user and would require change without clearing the field or reverting the whole record.
2) Have you considered using the OnObjectValidate script trigger on that field? That way you can do all your validation as the user attempts to
exit the field, and the Exit Script[] steps within the script can be set
to either false (0) or true(1), which will either keep the user in that
field or allow the field's exit.
Charlie,
Did you ever read my contribution about validating numbers in filemaker
on comp.databases.filemaker? I'm sure at least part of it is usuable.
And it does result in a value where the user can see what he/she has
done different to the requirements. And fixes any errors that might have
been made while entering the value.
How can I find this contribution, Erik? I'd be most interested in
reading what you have to say on the subject.
However, I don't believe in fixing user errors. I don't want my programs
to do a lot of computing and spit out results that might be based on incorrect input, particularly if the inputter isn't aware that he made
an input mistake.
Charlie
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.
Op 7-3-2018 om 21:50 schreef Charles H. Sampson:
Charlie,
Did you ever read my contribution about validating numbers in filemaker
on comp.databases.filemaker? I'm sure at least part of it is usuable.
And it does result in a value where the user can see what he/she has
done different to the requirements. And fixes any errors that might have >> been made while entering the value.
How can I find this contribution, Erik? I'd be most interested in
reading what you have to say on the subject.
However, I don't believe in fixing user errors. I don't want my programs
to do a lot of computing and spit out results that might be based on incorrect input, particularly if the inputter isn't aware that he made
an input mistake.
Charlie
Here is what I wrote about a week ago. Also tried to mail you, but mail
got bounced.
Just to offer you an other way.
create a custom function 'Currency'
give it one parameter 'number'
as its calculation enter the following
NumToJText ( Div ( number ; 100 ) ; 1 ; 0 ) & SerialIncrement ( ".00" ;
Mod ( number ; 100 ) )
Close it
Make two fields: first one you already have, being the field where you
enter your amount 'InputValue'
and a calculation returning a number 'cMyValue'
Currency ( Filter (InputValue;"0123456789" ) )
Note the filter stripping out everything except numbers.
Enter whatever you want, don't think about decimals or thousands. The
last two numbers you added are the decimals
examples
1 returns 0.01
50/09ol0 returns 500.90
12345678 returns 123,456.78
Format as Currency
Fixed number of decimals = 2
Decimal = .
Use thousands separator = ,
There also are other ways to do this. If you are interested in Custom Functions I can advise you to have a look at Brian Dunnings excelent
website, where I also found the above example.
http://www.briandunning.com/filemaker-custom-functions/list.php
On Wed, 21 Feb 2018 19:05:56 -0800, Charles H. Sampson wrote:
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.
It's perfectly up to you to perform a validation and conversion of the entries - either by a simple validation, an automatic conversion or a
script trigger for far more sophisticated actions.
Personally, I do have a database to enter prices. What I do there is
- automatic conversion of number formats between decimal point and
decimal comma (because I forgot the current setup or do use
prices from different source via copy/paste)
- conversion of netto prices (without tax) by the suffix "-"
to add 19 % of tax
- conversion of multiple items to calculate a per-item price
e.g. 29.99/5 is a price for five items, giving a single item price
of 6.00
- conversion of currencies by detecting the currency symbols $, £ and
CHF to convert the prices to EUR
All of this could be done via multiple fields, checkboxes etc.
But since it is a singe user database, I do prefer to have all of it
within a single field.
What I do within another field, that is e.g. the conversion of different
date formats from d.m.y to yyyy-mm-dd
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 286 |
Nodes: | 16 (2 / 14) |
Uptime: | 87:12:19 |
Calls: | 6,496 |
Calls today: | 7 |
Files: | 12,099 |
Messages: | 5,277,144 |