2 Replies Latest reply on Jul 12, 2013 10:29 AM by hrcap

Auto Enter Calculation

Title

Auto Enter Calculation

Post

Hi Guys

I need some help if possible.

I have a field called dob_day, this field is used to enter the day of the month that someone was born and so must contain a value 01 - 31.

I have most of my auto enter calculation sorted that ensures that the number is entered in the correct format, e.g if "1" is entered it will turn the number into "01" or if "111" is entered it will turn the number red to show an error.

I wish to modify this auto enter calculation slightly so that it will only allow the entry of the numbers 01 - 31 and if a different number is entered it will turn red.

Below is the calculation that i have so far, i think the part that needs modifying is the if statement with the *** next to it, in particular the part: @Numbers  ≠  ("1" or  "2" or "3") NB: this should go all the way to 31 once the caluclation is correct.

Let(

@Numbers = TextFormatRemove (Filter(Self; "0123456789"));

If ( @Numbers  ≠  ("1" or  "2" or "3") ; TextColor ( Self ; RGB(255; 0; 0)) ; (    ********************

Case(

Length(@Numbers) = 0 ;

(@Numbers) ;

Length(@Numbers) = 1 ;

"0" & @Numbers ;

Length(@Numbers) = 2 ;

(@Numbers) ;

TextColor ( Self ; RGB(255; 0; 0))

)
)
)
)

Hopefully i am on the right track.

Thanks in advance for any help.

Cheers

• 1. Re: Auto Enter Calculation

There are at least two different problems in that part of your calculation. The correct syntax for what you intended is:

@Numbers  ≠  1 or  @Numbers  ≠ 2 or @Numbers  ≠  3

But this won't totally fix things here as the valid value of 12 will be flagged as an error becase 12 ≠ 1 is a true statement.

I suggest that you use

Let(

@Numbers = GetAsNumber ( TextFormatRemove (Filter(Self; "0123456789") ) );

Case( @Numbers > 31 ; TextColor ( Self ; RGB(255; 0; 0)) ;
Right ( "0" & @Numbers ; 2 )
) // case
) // let

PS: seems like using a simple date field for entering the data of birth would be much easier. Then you calculation can simply be:
Right ( "0" & Day ( DOBfield ) ; 2 )

Then FIleMaker's built in error checking will catch invalid dates including entering a 31 for  month that does not have 31 days.
• 2. Re: Auto Enter Calculation

Hi Phil

Thank you very much, i used your 'GetAsNumber' solution and it works fine.

The reason i did not use a date field is because my solution must work via web publishing which causes problems with validation dialog boxes and also the drop down calendars dont fit exactly into my field layout.

The caluclation i used is below for reference:

Let(

@Numbers = GetAsNumber( TextFormatRemove (Filter(Self; "0123456789")));

Case(

@Numbers  ≤ 0 or
@Numbers  > 31 ;

TextColor ( Self ; RGB(255; 0; 0)) ;

Right( "0" & @Numbers ; 2)

)

)

Cheers