6 Replies Latest reply on Aug 11, 2014 9:19 AM by ShaneBess

    Multiple Format Validation Calculation

    ShaneBess

      Title

      Multiple Format Validation Calculation

      Post

           I am new to Filemaker and trying to write a calculation for field validation that covers two possible formats for the same field. I have tried various forms of case and if statements that all seem like they should work, but I'm apparently doing something wrong. Can anyone here help me figure this out?

           I need a field to be either 4 characters or 7 characters. If it is 4 characters, I need it to be filtered to just numerals. If it is 7 characters, I need the first three characters to be INV and the last 4 characters to be numerals. So the two possible formats are "####" and "INV####." Am I trying to do too much with a single field here?

        • 1. Re: Multiple Format Validation Calculation
          SteveMartino

               How do you differentiate between the two during data entry?  It seems like there are 4 numbers either way, what parameters would make the decision to add the INV?

          • 2. Re: Multiple Format Validation Calculation
            ShaneBess

                 I was basing it on Legth. It's a validation for a manual entry. It should only accept entries at a length of 4 or 7. If it's 4, it should apply the one filter, and if it's 7 it should apply the other.

            • 3. Re: Multiple Format Validation Calculation
              SteveMartino

                   It may be helpful to give a better description of why this is necessary, so different options can be considered.  The problem i see is what if someone enters 3,5,6, or 8 digits-or letters? Which validation should kick in? 

                   If the field represents 2 different types of information, say type 1 or type 2, maybe you should have the data entry user first pick the type in a drop down, then use that choice to validate.  

              • 4. Re: Multiple Format Validation Calculation
                ShaneBess

                     I'm not sure how to be more clear for you. In case 1, the input contains 4 characters that are numbers only. In case 2, the field contains 7 characters and the first three must be INV while the last four must be numbers. Why doesn't matter. I don't need the script to know why I want to do it, just how to do it. I'm not sure on the exact syntax in the scripting language, being new to this, but it should look something like this:

                     Case (
                     Length (FieldName)=4 AND Filter (FieldName; "0123456789"); 1;

                     Length (FieldName)=7 AND Filter (Left (FieldName; 3); "INV") AND Filter (Right (FieldName; 4); "0123456789"; 1;

                     0

                     ) 

                     Obviously I need something better than Filter for the prefix on the second case, otherwise I would have to set up a filter for each individual character position. I can't get that far, though, as I can't get a script like this to work in the first place beyond the first case.

                • 5. Re: Multiple Format Validation Calculation
                  philmodjunk

                        ( Length ( self ) = 4 and ( self = Filter ( FieldName ; 9876543210 ) ) OR

                        ( Length ( self ) = 7 and Left ( self ;3 ) = "INV" and ( self = "INV" & Filter ( self ; 9876543210 ) ) )

                  • 6. Re: Multiple Format Validation Calculation
                    ShaneBess

                         That works! Thank you Steve and Phil for your time and help. I see now how I was messing up the syntax.