8 Replies Latest reply on Oct 28, 2009 5:52 AM by panchristo

    field validation by calculation



      field validation by calculation


      I want to validate the format of data entered into a field, in this case patient name, using the validate by calculation option.  I want to strip unnecessary spaces, numbers, and punctuation other than comma, apostrophe, hyphen, space, and parentheses.  I then want to ensure that what is left has at least two letters of the last name and one for the first by creating local variables for the first and last names.  If all is OK, I would like to recombine the last and first names with an intervening comma and space and place the result either in the entry field or in a different field.


      Using the Let function, I can do all that, except that I can't seem to get the "good" name (ie, the concatenated last and first names) set in the same record as the entry name.  It seems to get set in the next record.  I set the concatenated name into a global variable, then set a field in the database equal to the global variable.


      I suspect that the "good" name value not showing up until the next record has something to do with committing records, but I don't know how to handle that in a Let function.  Perhaps I have to break down and use a completely separate script to do this.


      Any suggestions?  



      Nan at Baylor College of Medicine

        • 1. Re: field validation by calculation



          Thank you for your post.


          The Filter() function allows you to filter out unwanted characters.  Since you want to "strip unnecessary spaces, numbers, and punctuation other than comma, apostrophe, hyphen, space (huh?), and parentheses", the calculation would be:


          Filter (<field name>; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz,'-()" )


          Any other characters not in this text string are ignored.  For example, if in your field you had:


          Dr. Jones %56


          The result would be:




          .... because the period is ignored, the spaces ignored, the percent symbol ignored, as well as any number.


          Since you are removing spaces, I'm not sure how you would retrieve the first and last names.  If the first letter of the first and last names are capitalized, you could use the Filter function to filter only uppercase characters so you could retrieve the first letter of the last name.  Somehow, you would need to determine what works for you.


          If you have a calculation that returns the first name and last name, the calculation to combine the two fields would be:


          Last Name & ", " & First Name


          If you are doing this all within a script, then you can use "Set Field" script step to assign a value to a field.


          If you have a specific example, perhaps that would help more.



          FileMaker, Inc. 

          • 2. Re: field validation by calculation

            Thanks for responding, TSGal.  I tried to reply to this before, but I don't know that it posted, so here goes, again.


            I am not having a problem with the filtering.  I want to keep interior spaces, but trim them at the ends.  The Trim() function does fine for that.  The filter function is also working as I expected it to.  The problem is that once I have the name trimmed, filtered, and checked, and separated into first and last names, I want to create a name that is the concatenation of last name & ", " & first name.  I can do that too.  But I cannot seem to store it in the same record that the name was entered into in the first place.  It doesn't show up there, but if I do ctl N for a new record, then it shows up.


            The following is the code I have in the calculation part of the validate by calculation option for field PNtest.  I would like the validated name to be in field PN.  I use the global variable $$PN to pass that value back, since the result of the calculation must be boolean to satisfy the validation.  OK is that value.


            Let ( [X=Trim (PNtest ); $FN=""; $LN=""; Y=Filter ( X; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz '-()," ); PC=Position (Y; ","; 1; 1); $LN=Case ( PC>2; Left ( Y; PC-1 ); "" ); $FN = Case ( Length ( Y ) > (PC+1); Right ( Y; Length ( Y ) - PC ); ""); OK=( Length ( $LN ) > 0 and Length ($FN ) > 0); $$PN = Case ( OK; $LN & ", "  & $FN; "" )]; OK)


            Thanks again for your help.


            • 3. Re: field validation by calculation

              Well, I have written a script which is virtually the same as the calculation field.  The difference is that in the script I set the field PN equal to the Let function which returns the "good" name.  In the validation calculation, the value returned had to be boolean, so I couldn't set the PN field directly.


              I think this solves the problem.


              Thanks for your time and effort.


              • 4. Re: field validation by calculation

                Looking at this problem, I don't think you need to validate the data so much as to replace it with a calculated result. (This also means that you don't need a script) This can be done in the auto-enter options for the PNTest field. Open the Options dialog for the field and on the Auto-Enter tab choose Calculated value. Enter the fololowing:



                Let ( [

                X= PNTest; 

                Y= Filter ( X; "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz '-()," ); 

                PC= Position (Y; ","; 1; 1); 

                LN = Trim ( Left ( Y; PC - 1 ) ) ;

                FN = If ( PC > 0 ; Trim ( Right ( Y; Length ( Y ) - PC ) ) ; "" ); 

                OK = ( Length ( LN ) > 1 and Length ( FN ) > 0 ); 

                PN = If ( OK; LN & ", "  & FN; "Invalid name" )







                Click OK. Then in the options, UNCHECK the 'Do not replace existing value of field (if any)" checkbox.


                When you enter a name into PNTest, it will be corrected as per the calculation when the user tabs out of the field. If the name does not match the criteria (OK = 0), then it will be replaced with the string "Invalid name".

                • 5. Re: field validation by calculation

                  Thanks for your help, David.  This is what I needed.  The only thing is that autoenter doesn't set the PN field (local variable, yes, field, no), but it does reset the PNtest field.  Since PN is the field I'm interested in anyway, I just set the autoenter for that field to the Let function as you described.


                  Thanks so much for the info.  I think this solves the issue, but I don't know how to set the solved flag here in the Forum.  Maybe you do...



                  • 6. Re: field validation by calculation

                    Can somebody please answer my question related to the topic:


                    Is it possible to conditionally format a field refering to whether it validates as a member of a value list?


                    e.g. If I deliberately choose a non-existent value from a value list,I need to format the field in red colour in order to spot it easily.

                    • 7. Re: field validation by calculation

                      Yes and you can also set up a validation rule and message to inform the user of such if you want.


                      Conditional Format expression:


                      IsEmpty ( FilterValues ( ValueListItems ( Get ( Filename ) ;“YourValueListName”) ; YourTable::YourField ) )

                      • 8. Re: field validation by calculation
                           Great! Thanks a lot...