• Validating Currency Entries

    From Charles H. Sampson@21:1/5 to All on Wed Feb 21 19:05:56 2018
    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
    --
    Nobody in this country got rich on his own. You built a factory--good.
    But you moved your goods on roads we all paid for. You hired workers we
    all paid to educate. So keep a big hunk of the money from your factory.
    But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Helpful Harry@21:1/5 to Charles H. Sampson on Thu Feb 22 19:43:20 2018
    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 :o)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erik Appeldoorn@21:1/5 to All on Thu Feb 22 09:18:32 2018
    Op 22-2-2018 om 04:05 schreef Charles H. Sampson:
    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

    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

    Keep well, Erik

    --
    met vriendelijke groeten,
    Erik Appeldoorn

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Charles H. Sampson@21:1/5 to Helpful Harry on Thu Feb 22 16:26:16 2018
    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.

    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.

    Right, and that's what happens to me in my current fumble-fingers mode.

    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.

    Charlie
    --
    Nobody in this country got rich on his own. You built a factory--good.
    But you moved your goods on roads we all paid for. You hired workers we
    all paid to educate. So keep a big hunk of the money from your factory.
    But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Helpful Harry@21:1/5 to Charles H. Sampson on Fri Feb 23 15:19:56 2018
    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)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Charles H. Sampson@21:1/5 to Charles H. Sampson on Sat Feb 24 18:16:59 2018
    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.

    Charlie
    --
    Nobody in this country got rich on his own. You built a factory--good.
    But you moved your goods on roads we all paid for. You hired workers we
    all paid to educate. So keep a big hunk of the money from your factory.
    But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Charles H. Sampson@21:1/5 to Helpful Harry on Sat Feb 24 18:16:59 2018
    Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:

    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.

    I knew all this. I'm afraid I haven't made myself clear. It's not how
    the data are displayed, it's about how they are entered. I want to allow
    the user to clump using commas if that's what he wants. I want to allow
    him to use a dollar sign if that's what he wants. However, I insist that
    he do this right. Entering a value like 12,34,567 says to me that the
    user made a mistake. He wanted to use the triples-of-digits notation for
    some reason and he got it wrong. In the many years I worked with user
    input, I was strict that it had to make sense (which is different from
    saying that a meaning could be supplied) to avoid GIGO.

    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

    I used

    Truncate (Value, 2) = Value

    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.

    Right. I don't require two cents digits or even a decimal point.
    Entering a single cents digit is pretty common, so I can't call it an
    error that must be fixed.

    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.

    Thanks, as usual, Harry.

    Charlie
    --
    Nobody in this country got rich on his own. You built a factory--good.
    But you moved your goods on roads we all paid for. You hired workers we
    all paid to educate. So keep a big hunk of the money from your factory.
    But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Helpful Harry@21:1/5 to Charles H. Sampson on Sun Feb 25 18:09:57 2018
    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.

    Helpful Harry :o)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Charles H. Sampson@21:1/5 to Helpful Harry on Sat Mar 3 23:34:29 2018
    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:

    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).

    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
    --
    Nobody in this country got rich on his own. You built a factory--good.
    But you moved your goods on roads we all paid for. You hired workers we
    all paid to educate. So keep a big hunk of the money from your factory.
    But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Howard Schlossberg@21:1/5 to Charles H. Sampson on Sun Mar 4 10:24:28 2018
    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.

    Howard

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From ursus.kirk@gmail.com@21:1/5 to All on Sun Mar 4 13:15:20 2018
    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:

    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).

    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

    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.

    --
    Vriendelijke groet, Erik Appeldoorn

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Charles H. Sampson@21:1/5 to ursus.kirk@gmail.com on Wed Mar 7 12:50:41 2018
    <ursus.kirk@gmail.com> wrote:

    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:

    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).

    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.

    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
    --
    Nobody in this country got rich on his own. You built a factory--good.
    But you moved your goods on roads we all paid for. You hired workers we
    all paid to educate. So keep a big hunk of the money from your factory.
    But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Charles H. Sampson@21:1/5 to Howard Schlossberg on Wed Mar 7 12:50:41 2018
    Howard Schlossberg <howard@nospam.fmprosolutions.com> wrote:

    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.

    I didn't use this because I wanted to allow properly-used commas and I
    assumed that commas would be disallowed. I was wrong, but freely used
    commas are permitted and then ignored. As I've said, I consider a
    misused comma to be an indication of a data input error (also known as
    "typo").

    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.

    This is something new to me and I'll have to check it out. Right off
    hand, I wonder about the sequence between the specified validation and
    my script; who takes precedence. I suspect that concern can be avoided
    by specifying a validation that is always True (e.g., 1 = 1).

    It's good to hear from you, Howard.

    Charlie
    --
    Nobody in this country got rich on his own. You built a factory--good.
    But you moved your goods on roads we all paid for. You hired workers we
    all paid to educate. So keep a big hunk of the money from your factory.
    But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From ursus.kirk@gmail.com@21:1/5 to All on Thu Mar 8 09:54:08 2018
    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

    Keep well, Erik


    --
    Vriendelijke groet, Erik Appeldoorn

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Martin =?UTF-8?Q?=CE=A4rautmann?=@21:1/5 to Charles H. Sampson on Thu Mar 8 13:19:22 2018
    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

    Martin

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Charles H. Sampson@21:1/5 to ursus.kirk@gmail.com on Sun Mar 11 18:15:53 2018
    <ursus.kirk@gmail.com> wrote:

    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.

    Sorry about that. I don't post my email address in public places. The
    so-called address you see is a fake.

    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.

    I understand Filter. I use it to detect that the input value has invalid characters:

    Filter (InputValue;"$.,0123456789") = InputValue

    I also use Trim to keep from getting confused by harmless leading or
    trailing blanks.

    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

    We're deep into the area of personal preferences. My preference is to
    not ignore obvious errors, such as 50/09ol0 being interpreted as 500.90.

    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

    Thanks, Erik. I've locked this post so it doesn't accidentally get
    deleted before I have a chance to check this out.

    Charlie
    --
    Nobody in this country got rich on his own. You built a factory--good.
    But you moved your goods on roads we all paid for. You hired workers we
    all paid to educate. So keep a big hunk of the money from your factory.
    But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Charles H. Sampson@21:1/5 to t-usenet@gmx.net on Sun Mar 11 18:15:54 2018
    Martin ?rautmann <t-usenet@gmx.net> wrote:

    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

    Thanks for all of this, Martin. You seem to have the same general
    attitude about this kind of thing as I do; we differ in details.

    Charlie
    --
    Nobody in this country got rich on his own. You built a factory--good.
    But you moved your goods on roads we all paid for. You hired workers we
    all paid to educate. So keep a big hunk of the money from your factory.
    But take a hunk and pay it forward. Elizabeth Warren (paraphrased)

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)