5 Replies Latest reply on Nov 3, 2010 3:29 PM by philmodjunk

    validation script or calculation???

    bate01

      Title

      validation script or calculation???

      Post

      Hello - I have an issue where I need to have my parent database compare fields to see if the building and room listed for an employee matches the building and room data in a related facility db. If they don't match then it needs to create a flag (an x or something like that in a field) so that the user can double check the data. 

      What is the best way to go about this? What I tried failed so I am stumped.

      I am new to calc fields - using FileMaker 10 that is hosted on FileMaker server. Also, I use a Mac.

      Any help is appreciated as I am not sure how to proceed.

        • 1. Re: validation script or calculation???
          philmodjunk

          In a calculation field you could use:

          If ( ParentField1 ≠ RelatedTable::Field1 Or parentField2 ≠ RelatedTable::Field2 ; "Error" ; "" )

          There are even ways to have the field display a graphic image instead of text with this method or you can use the first parameter of the above If function in a conditional format expression to highlight the field or fields in question. (If I used that approach, I'd use separate conditional formats for each pair of fields so that only the questionable fields would highlight.)

          • 2. Re: validation script or calculation???
            bate01

            Thank you for the advice. I must have entered something wrong as I  cannot get it to calc correctly if I have it set up just like it is  below

            If ( ParentBuilding1 ≠ RelatedTable::Building1 Or  parentRoom2 ≠ RelatedTable::Room2 ; "Error" ; "" ) - it will put error  in the field of data validation but if I only have it checking one field  [example: If ( ParentField1 ≠ RelatedTable::Field1; "Error" ; "" ) ] it  works where it only puts the error in if the fields don't match. Once I  add the second portion it puts error in all records. 

            I am not sure what I did wrong.

            • 3. Re: validation script or calculation???
              philmodjunk

              Yes, if you just compare two fields, then the error appears only when that pair doesn't match. That could flag the specific pair of fields where the data is inconsistent.

              You could do it this way:

              Enter Layout Mode
              Select ParentTable::Field1 and also RelatedTable::Field1 if both are present on the layout.
              Righ click then and choose conditional format.
              Add a conditional format formula as: ParentTable::Field1≠ RelatedTable::Field1
              Select a Red Fill color and click OK.

              Repeat for each pair of fields involved.

              When an error occurs, only those fields where there is inconsistent data will highlight in red.

              • 4. Re: validation script or calculation???
                bate01

                I was able to get your second answer to work but I would still like to understand your first reply regarding the calculation. 

                I noticed that in my reply back to you that I had left out an important part. What I meant to say is:

                When I use the calc If (ParentBuilding1 ≠ RelatedTable::Building1 Or parentRoom2 ≠ RelatedTable::Room2 ; "Error" ; "" ) - it will put error in the data validation field FOR ALL RECORDS but if I only have it checking one field [example: If ( ParentField1 ≠ RelatedTable::Field1; "Error" ; "" ) ] it works where it only puts the error in if the fields don't match. Once I add the second portion (comparing room too) it puts error in all records. 

                What did I do wrong?

                • 5. Re: validation script or calculation???
                  philmodjunk

                  Can't tell for sure from here. any chance ParentBuilding1 or parentRoom2 are global fields?

                  It shouldn't be necessary as far as I can tell from my tests, but you could try adding some parenthesis:

                  If ( ( ParentBuilding1 ≠ RelatedTable::Building1 ) Or ( parentRoom2 ≠ RelatedTable::Room2 ) ; "Error" ; "" )