1 2 3 Previous Next 30 Replies Latest reply on May 26, 2010 10:18 AM by jenh

    Validation for field values inconsistency on import.

    jenh

      Title

      Validation for field values inconsistency on import.

      Post

      Using FileMaker pro Advanced 10. 

       

      We import data intermittently throughout the week.   200 - 500 records typically with each import. 

       

      Many (not all) of the fields imported should have the same value for each record for that import.  We would like to see a message if all field values for the records in an import are not the same.  (We do not want to change the value)

       

      For example, if location is a field name and the value should be Disneyworld for every record in this import but one or two are Disneyland, we need a message to alert us that not all location field values are the same for all records.   (Value of these fields will be different for each import, we just want to be alerted for each import if there are field values that are NOT all the same.)

       

      In "Manage Database" I set the Validation tab for the field options to "always", "existing value" and  "Display custom message if validation fails"  (with a message).  This does not show any results.    

       

      Any suggestions or references are appreciated.  Thank you. 

        • 1. Re: Validation for field values inconsistency on import.
          comment_1

          This cannot be done by validation: validating by "Existing value" considers all existing records in the table - not the ones being imported. If you don't already  have a record for "Disneyworld" prior to the import, then no record that contains "Disneyworld" will be imported.

           

          You could run a looping script immediately after the import to check if all imported records have the same location.

          • 2. Re: Validation for field values inconsistency on import.
            jenh

            Thank you for the response.  

             

            I'm just getting accustomed to FileMaker scripting.  

            I will not know the value of these fields. 

             

            Do you have a suggested reference for scripting generic loops for checking the field values?  

             

             

            • 3. Re: Validation for field values inconsistency on import.
              comment_1

              I am not sure what exactly do you want to happen when the test fails. Here's an example that will bring up a dialog at the first problematic record (with respect to the first imported record):

              Go to Record/Request/Page [ First ]
              Set Variable [ $location; Value:YourTable::Location ]
              Loop
              Go to Record/Request/Page [ Next; Exit after last ]
              If [ $location ≠ YourTable::Location ]
              Show Custom Dialog [ Message: "Not all records..."; Buttons: “OK” ]
              Exit Script [  ]
              End If
              End Loop





              • 4. Re: Validation for field values inconsistency on import.
                jenh

                Thank you.  I will test this script.  I don't want anyting to happen just a message that says.. a particular field in an import (that should have the same value for all records in the import) has different values.    

                • 5. Re: Validation for field values inconsistency on import.
                  jenh

                  The given script works great when I run the script. 

                   

                  I'd like to add multiple field validations in one script and have it

                  run automatically upon import.   

                   

                  One message after import:  for example... 

                   

                    "Not all records share the same location"

                    "Not all records share the same machine unit #"

                   

                  How would I have the script run automatically upon an import?

                  • 6. Re: Validation for field values inconsistency on import.
                    comment_1

                    Please elaborate: suppose you have imported 50 records. Record #10 has a different location, and record #20 has a different unit number - what exactly should happen?

                     

                     


                    jenh wrote:

                    How would I have the script run automatically upon an import?


                     

                    The best way, I think, would be to have a script run the import AND the subsequent check.

                     


                    • 7. Re: Validation for field values inconsistency on import.
                      jenh

                      TY.  

                       

                      I'll have to research further about scripting the import.  That may not be an option at this time.  When we import it is from various folders and at various times with various numbers of records.

                       

                      No action should take place upon the message, we just need to make note in analysis that

                      the data received is not consistent. 

                       

                      Ideally upon each import, an alert of anamolies in any of the fields imported is needed.  

                      (Many of the fields would require validation/scripting unique to that field)

                       

                      A printable report is the ultimate goal. 

                       

                      Thank for your input.      

                      • 8. Re: Validation for field values inconsistency on import.
                        comment_1

                         


                        jenh wrote:

                        No action should take place upon the message, we just need to make note in analysis that

                        the data received is not consistent.


                         

                        I am afraid that still doesn't answer my question. Should the script stop at the first inconstistency (record #10), or should it continue until record #20 - or perhaps it should check ALL records every time?

                         

                         

                         


                        jenh wrote:

                        When we import it is from various folders and at various times with various numbers of records.     


                        I don't see why that would be an issue - you can click a button to import, then select the file to import (IOW, do not specify the data source in the Import Records[] script step).

                         

                         



                        • 9. Re: Validation for field values inconsistency on import.
                          jenh

                          It would check all records everytime.   

                          • 10. Re: Validation for field values inconsistency on import.
                            comment_1

                            OK, then how about:

                            Go to layout [ TargetTable ]
                            Import Records[]
                            # CHECK LOCATION
                            Sort Records [ Specified Sort Order: TargetTable::Location; ascending ]
                            Go to Record/Request/Page [ First ]
                            Set Variable [ $location; Value:TargetTable::Location ]
                            Go to Record/Request/Page [ Last ]
                            If [ $location ≠ TargetTable::Location ]
                            Set Variable [ $error; Value:1 ]
                            End If
                            # CHECK UNIT NUMBER
                            Sort Records [ Specified Sort Order: TargetTable::UnitNumber; ascending ]
                            Go to Record/Request/Page [ First ]
                            Set Variable [ $unit; Value:TargetTable::UnitNumber ]
                            Go to Record/Request/Page [ Last ]
                            If [ $unit ≠ TargetTable::UnitNumber ]
                            Set Variable [ $error; Value:
                            $error + 2 ]
                            End If
                            # REPORT RESULT
                            If [ $error ]
                            Show Custom Dialog [ Message: "Not all records..."; Buttons: “OK” ]
                            End If










                            • 11. Re: Validation for field values inconsistency on import.
                              jenh

                              That looks like the answer I need. 

                               

                              I'll test it out.   I'd like the message to list the variables that aren't the same. 

                               

                              ie;  Not all records in "location" are the same.

                                    Not all records in "unit" are the same.

                               

                                I would think I could put variable $location or $unit in the message.  (Eventually print it out)

                               

                              Thank you again for your time and suggestions. 

                              • 12. Re: Validation for field values inconsistency on import.
                                comment_1

                                Well, the $error variable in the above script has 4 possible values:

                                 

                                0 - no error;

                                1 - not all records have the same location;

                                2 - not all records have the same unit;

                                3 - not all records have the same location AND not all records have the same unit.

                                 

                                You can use Choose() or GetValue() to calculate the appropriate message text based on $error value.

                                • 13. Re: Validation for field values inconsistency on import.
                                  jenh

                                  Hello,

                                   

                                  Thank you for assistance in developing the scripts in previous replies for field value validation on import. 

                                   

                                  The field validation project is being expanded to all fields on an import for a database. 

                                   

                                  (Using Filemaker Pro 10 for windows)   

                                   

                                  There are about 80 fields per record for this DB. 

                                   

                                   Out of the 80 I have nearly 16 different conditions to validate the fields.  I do not want to change the fields;  just get a message noting when a field such as "location" are not the same or all fields such as "inv date are not sequential". 

                                   

                                  One condition for validation is for nearly 34 fields that will be validated if all records in a import have the same value for each field.  

                                   

                                  As of now only a few fields are being validated ie:

                                   

                                  Go to Record/Request/Page [First]

                                  Set Variable [ $location; Value; Table::Location ]

                                  Set Vairable [$location; Value; Table::Date ]

                                  Loop

                                  Go to Record/Request/Page [Next; Exit after last]

                                  If [$location /= Table::Location ]

                                  Show Custom Dialog [ Table::LocationMessage: "Not all records have same location"; Buttons: "ok"]

                                  If [$Date /= Table::Date ]

                                  Show Custom Dialog [ Table::DateMessage: "Not all records have same date"; Buttons: "ok"]

                                  Exit Script []

                                  End If

                                  End If

                                  End Loop

                                   

                                   

                                  (There could be a list of several messages "Not all records have same date",  "Not all records have same location".)

                                   

                                  Is there a more effiencient way to write this script for 34 different fields as opposed to 34 If statements?  

                                   

                                  Thank you.

                                   

                                  • 14. Re: Validation for field values inconsistency on import.
                                    comment_1

                                    I'd probably do something like:

                                    Go to Record [First]
                                    Loop
                                    Set Variable [ $location; Table::Location ]
                                    Set Variable [ $date; Table::Date ]
                                    # set more variables here
                                    ...
                                    Set Variable [ $error; <calculation> ]
                                    If [not IsEmpty ( $error ) ]
                                    Show Custom Dialog [ $error ]
                                    Exit Script []
                                    End If
                                    End Loop






                                    1 2 3 Previous Next