3 Replies Latest reply on Dec 21, 2013 10:10 PM by philmodjunk

    Using a Calculation to fill in foreign key field



      Using a Calculation to fill in foreign key field


           An apologetic preface: this is my first attempt working with FMP and I haven't done any kind of coding in about a decade so I'm sorry if this comes across as stupid but here goes...

           I have a "child" table which lists different transactions.  I need to use an identifying string of numbers (14 characters) from this table to be able to automatically link it to the corresponding record in the "parent" table (I want the calculation to spit out the proper value into the foreign key field). The field that I want to compare to in the "parent" is 10 characters. I already figured out how to truncate the last 4 characters from the child record that I don't need so both values are now 10 characters.

           Is there a way to compare the "child" value to every record in the "parent" and come back with key value to link them?

           If that's a little confusing maybe this is easier to see:

           If ChildRecord = ParentRecord Than ForeignKeyField = x

           ...I just thought of this... Can I just set up a new relationship between the two tables using the fields I'm trying to compare? That would probably remove the need for this calculation at all eh??

           Thank you to anyone in advance for taking the time to help!

        • 1. Re: Using a Calculation to fill in foreign key field

               You seem to have things reversed and are needlessly complicating the process of linking a parent record to a group of child records.

               First, the linking value should be an auto-entered value in the parent record, not the child.

               Second, this should be a simple value, devoid of any special "meaning" other than that it uniquely identifies each record in the parent table. In FileMaker, you have two simple options that you can specify: An auto-entered serial number--still the simplest most trouble free option to use or you can set up a text field with an auto enter calculation: Get ( UUID ).

               With either approach, the value is automatically generated each time you create a new parent record. When you create and link a child record to that parent, you use one method or another to copy the value of this primary key field in the parent record into the foreign key field of the child record. The simplest way to do that (not the only way), is to set up a portal to the Child Record on the Parent Record's layout and enable "allow creation of records via this relationship". With that setup, you can simply enter data into the portal rows and the system will create new records as needed in the child table and copy over the needed primary key value into the designated foreign key field to link it to the correct parent record for you.

               That's not to say that special use identifier "codes" aren't sometimes a necessity. Sometimes they are needed to support legacy systems that have been in long use. Sometimes special labels have to be produced that include this code which often densely encodes additional data that knowledgeable personnel can "decode" and sometimes you get a client that just insists on such a code. But in none of these cases, should you actually use that code as a primary key for linking records to other tables. Put such a code in a field in the parent record and you can use it for searches, label printing, etc but not be stuck with a less than optimum primary key to use in your database.

          • 2. Re: Using a Calculation to fill in foreign key field

                 Thank you for the response!  Maybe my explanation was not great but I do have an auto-entered primary key field set up in my parent table. My problem is with getting that key copied into the foreign key field in the correct records in the child table.  The data in the child is imported from an outside source.

                 To be more specific (and maybe easier to grasp), my parent table is a list of Housing Subdivisions.  The child table is a list of Home Sale Closings.  These closings have an identifying number (the County Assessor's Parcel Number of the lot where the house is built ex. 123-45-678-001).  I also have a field in the parent table that shows which parcel numbers are located within that subdivision. (ex. All parcels starting with 123-45-678 belong with Subdivision X).

                 So like I said at the end of my original post, does it make more sense to link these tables using that Assessor's Parcel Number instead of the normal primary key? OR would it be better to try and compile a complete list of every parcel number in each subdivision and create a new table to put between the 2 original tables?

            • 3. Re: Using a Calculation to fill in foreign key field

                   Data from an external data source should not be used as a permanent primary/foreign key though it may be necessary to use immediately after import to match data in order to get the data linked to an internally generated ID. You would set up two relationships, one that uses the external key and one the internal. The external key based relationship would be used to copy the needed value in order to set up the internal key based relationship.

                   Since this appears to be a one to many relationship, one subdivision to may parcels, I see no reason for an additional table to use in order to link them in relationships.