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

    Auto Enter Calculation

    hrcap

      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
            
            
           Hadleigh

        • 1. Re: Auto Enter Calculation
          philmodjunk

               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
            hrcap

                 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
                  
                 Hadleigh