7 Replies Latest reply on Apr 9, 2015 11:56 AM by philmodjunk

    Lookup Data from same table, but a different record

    chf

      Title

      Lookup Data from same table, but a different record

      Post

      I have a database, for animal husbandry, and each record has a unique ID.  Within each record in the "inventory" table, there are fields that show that animal's unique ID, it's name, and the parent information.  This includes the unique ID for both the father and the mother (both of which are also in this database), and then the father and mother's name.  Is there any way to make it so that when I enter the father's unique ID into the "father ID number" field, it automatically populates the "father name" field by finding the father's record, and populating it with the "Name" field that is listed for that record?  I know you can set the field as a lookup field, but you can only look up items from other tables.  This is information that would be within the same table.

       

      Thank you for your help,

      Caroline

        • 1. Re: Lookup Data from same table, but a different record
          philmodjunk

          You don't actually look up data from other tables even though that's what it looks like in the dialogs. You actually look up data from Tutorial: What are Table Occurrences? and such a table occurrence can be another occurrence of the very same table. But I wouldn't add fields for parent names to your record. If you later discover that you have mis-entered a name, you'd have to track down and update the parent name fields for all progeny of that animal. Instead, you can just link to that record and include that animal name field from that related record. If such a data update is later needed, this name then automatically updates on all the records of that animal's progeny.

          Here's how:

          Make a new Table Occurrence of your existing table by selecting it in Manage | Database | Relationships and clicking the duplicate button (two green plus signs). You can then double click this new table occurrence box to open a dialog where you can rename the table. You now have what looks like a new table and you can set up a relationship linking it to the original table like you would any other table but this is really just another reference to the very same table. Here's were you could set up that look up, but what I would do is go to my layout where I want to see the father's name and use the field tool to add the animal name field, but first select this new table occurrence from the list of names before selecting the animal name field so that you are specifying that this field be referenced from this newly added table occurrence.

          • 2. Re: Lookup Data from same table, but a different record
            FilmUser

            Phil - what would be the match field in this self relationship?

            • 3. Re: Lookup Data from same table, but a different record
              philmodjunk

              It seems that you've answered that question in your original post:

              Is there any way to make it so that when I enter the father's unique ID into the "father ID number" field,

              InventoryTable::FatherIDNumber = InventoryTable 2::animalID

              Note that you can format that field with a value list where field 1 is the AnimalID number and Field 2 is the corresponding name. You can then select the father by name, but actually enter the Father's ID number.

              • 4. Re: Lookup Data from same table, but a different record
                chf

                Thank you for your response, that works great.  The problem now, is that I have some records in which the parent animal number is not in the database.  These records were imported, and have the animal name in the "parent name" field.  When I change that field to the field from my new table (even though it is really the same table) those names do not show up, because there is no corresponding animal number in the database... is there any way to fix this? it is nearly 1000 records that will have this problem.

                • 5. Re: Lookup Data from same table, but a different record
                  philmodjunk

                  Perform a find for the records that do not have a value in this field. (Go to a layout based on the table and that has the field located on it. Enter find mode and put a lone = character in the field. Perform the find.)

                  Then use Replace Field Contents with the serial number option to assign them an ID.

                  If you import records in the future, be sure to either enable auto-enter options during import or use Replace Field Contents to assign value immediately after the import.

                  • 6. Re: Lookup Data from same table, but a different record
                    chf

                    I may not be understanding correctly.  Lets say my database starts with animal # 5000.  The first 1000 animals (animals # 5000-6000) have parents who have animal #s that are lower than 5000, and therefore are not present in the system.  When the information for these animals was imported, the parent #, as well as the parent name, were populated into fields in my original inventory table called "parent #" and "parent name".  Now, in my "inventory" layout, when I change the "parent name" field from my "inventory" table, to the "name" field from my "parent info" table (which is just the duplicate table of the inventory table set up by the relationship described earlier where Inventory::parent number = parent info::name), this field will remain empty for all animals who's parent # is not in the database.  I don't want to assign a new serial number to them, I would like to, say, make a calculation that says that if the value for the "name" field in the new "parent info table" is empty, or cannot be located based on the parent number entered, populate the field with "parent name" from the original "inventory" table.  Does that make more sense?

                    Sorry for the confusion haha

                    • 7. Re: Lookup Data from same table, but a different record
                      philmodjunk

                      That was not what I understood from your previous post. I thought that you had imported records from another source and that those records did not have ID numbers.

                      What you describe is a very simple calculation field:

                      If ( IsEmpty ( RelatedTable::ID ) ; OriginalNameField ; RelatedTable::Name )