3 Replies Latest reply on May 21, 2013 8:59 AM by philmodjunk

    Check if field value for a record exist

    JCPython

      Title

      Check if field value for a record exist

      Post

           I have a couple tables that are related, each have a animal_ID field (these fields are seperate from the table unique auto serial ID)

           In one table its imported data parsed from a xml file and what i need to do is have a field of some sort next to the animal ID where it says either - not found (the animal ID in this record for this table doesnt exist in the animal_ID field in the other table) or says nothing if there is a match.

           Can someone tell me the best practices to achieve these results?

            

           Thanks so much! :)

        • 1. Re: Check if field value for a record exist
          philmodjunk

               Define a relationship between occurrences of the two tables that match by Animal ID. Then this calculation will return the value not found if there is no record with a matching Animal ID in the related table:

               If ( IsEmpty ( Table2::Animal ID ) ; "Not Found" )

               This can also be set up on layout text with a conditional format and then you do not need the calculation field to display "not found" when there is no matching record.

          • 2. Re: Check if field value for a record exist
            JCPython

                 Im not sue if i performed this right, im getting not found for all my records and only one should say not found. i should clarify that table 1 is my main animal table, table 2 is a table that has a assortment of fields used for parsing xml data from a online source, the data is then filled into records in a listview layout, and then finally the data is imported into the proper areas of the main animal table sections. after the import then all xml parsed datta records are deleted from the table 2.

                 i related table one and two as such:

                 Table1:AnimalID---=---Table2:AnimalID

                 Table1:RecordID---=---Table2:Table1 Foreign Key

                  

                 Hope this makes sense....

            • 3. Re: Check if field value for a record exist
              philmodjunk

                   I don't think that relationship will work. I am interpreting what you posted to be:

                   Table1:AnimalID---=---Table2:AnimalID AND

                   Table1:RecordID---=---Table2:Table1 Foreign Key

                   If I understand what you need correctly, you need just:

                   Table1:RecordID---=---Table2:Table1 Foreign Key

                   You may need to create a duplciate Tutorial: What are Table Occurrences? for one of these two table occurrences so that you can set up a relationship that matches only by AnimalID and still keep your existing relationship that matches by RecordID to Foreign Key.