1 2 3 Previous Next 32 Replies Latest reply on Aug 17, 2015 1:44 PM by philmodjunk

    Inserting a foreign key based on a field match from parent table

    ThomasSharp

      Title

      Inserting a foreign key based on a field match from parent table

      Post

      Hello, 

       

      I am building a database and I've used access/SQL server before but I was strongly advised to use filemaker so here I am but struggling a bit with joining tables. I have related some small tables in FM14 manually and now I need to insert the main table and create the foreign key entries based on a match in the parent table. Here is a scrnshot of the parent table http://img42.com/4Mu0E+ and the company table is here http://img42.com/DZJUf+ 

      This is just a small practice run as the actual data is 10 000+ rows. ( I don't know if I did the right thing joining the other tables manually but couldn't see another way) In SQL server a Joined update would do this but I have no idea how to do this in Filemaker... Any advice would be really helpful for me

       

      Thanks

        • 1. Re: Inserting a foreign key based on a field match from parent table
          erawson

          create a relationship between the 2 tables using the matching field criteria (not the foreign key to primary key), lets call these 2 table occurrences Parent1 and Child1 for now. Then run a script like so:

          go to layout (any layout based on Child1 table occurrence)

          show all records

          replace field contents (target = the foreign key field in the child table, calculated value = Parent1::primarykeyfield)

           

          alternatively you can use executeSQL in your replace field contents calculation instead of creating the relationship

          • 2. Re: Inserting a foreign key based on a field match from parent table
            ThomasSharp

            Hi, I created a relationship between the tables on the Activity field and not the ID field. Then tried to replace contents with calculated value but displays 0 for all records.  How can I make it look for a match between the text column that both the tables share?

            Parent

            http://img42.com/4Mu0E+

            Child

            http://img42.com/DZJUf+

             

            Thanks

            • 3. Re: Inserting a foreign key based on a field match from parent table
              philmodjunk

              The relationship you describe should do it. Either the field's data types are not correct of the values in those fields of the two tables do not exactly match.

              • 4. Re: Inserting a foreign key based on a field match from parent table
                ThomasSharp

                Thanks, The 2 text columns are text and the others are numeric. If that is correct then this must be incorrect as the values definitely match

                Company::Activity = Activity::Activity = Activity::ActivityID

                • 5. Re: Inserting a foreign key based on a field match from parent table
                  philmodjunk

                  If they are text fields, all it takes is a difference of one character--even an invisible space in the value of one of these fields to create a situation where it looks like the values should match but in reality, they don't.

                  What you have posted for match fields seems to describe an impossible relationship. You can't have two table occurrence boxes with exactly the same name so

                  Activity::Activity = Activity::ActivityID

                  makes no sense.

                  Can you upload a screen shot of this portion of your relationship graph in order to clarify?

                  • 6. Re: Inserting a foreign key based on a field match from parent table
                    ThomasSharp

                    I've renamed the Activity field in the activity table to 'ActivityName' but it still produces 0

                    here are the relationships

                    http://img42.com/QFA55+

                    • 7. Re: Inserting a foreign key based on a field match from parent table
                      ThomasSharp

                      I've triple checked everything listed here and it just displays 0s - is there anyone else that can help?

                       

                      Thanks

                      • 8. Re: Inserting a foreign key based on a field match from parent table
                        philmodjunk

                        Why would you expect renaming a field to change how this works? If you were using a different database, would you expect renaming a field to change how a relationship works?

                        What I was noting was that your field names implied that you were not matching the same kind of data in your match fields.

                        You posted earlier:

                        Company::Activity = Activity::Activity = Activity::ActivityID

                        What I find in your screen shot (that could have been uploaded with the controls found below the Post A New Answer box):

                        Activity::ActivityName = Company::Activity

                        Which is more than a name change as Activity::Activity = Activity::ActivityID is not part of any relationship in your database at this time.

                        From your first post, I gather that you consider Activity to be your parent table and thus company is the child table? Is it possible that you have the need to link many companies to the same activity and a given activity to more than one company? That would require a different relationship than what you currently have in place. But let's leave that issue be for a moment.

                        I would suggest that you set up a portal to companies on your activities layout and see what records appear in that portal.

                        it just displays 0s

                        What is "it"? Is this a field? A field in what table? Or are you looking at information in the upper left corner of the status area tool bar?

                        is there anyone else that can help?

                        If you prefer, I can make this my last response to your questions. But I have been working with FileMaker since it was FileMaker Pro 2.5. I generally am able to provide good advice here so I really don't think stopping now is likely to be helpful to you.

                         

                        • 9. Re: Inserting a foreign key based on a field match from parent table
                          ThomasSharp

                          From your first post, I gather that you consider Activity to be your parent table and thus company is the child table? Is it possible that you have the need to link many companies to the same activity and a given activity to more than one company?

                          Yes that is what I need to achieve. My apologies for not being clearer and I do clearly need your advice. What I mean is the field ActivityID in the Company table after specifying the calculation displays 0s instead of what should be 2 (Dairy Products) and 4 (Special Foods & Wellbeing Products) This is when in browse mode.

                          When adding a portal in layout mode nothing seems to be displayed.. 

                          • 10. Re: Inserting a foreign key based on a field match from parent table
                            philmodjunk

                            And what calculation is that? Can you identify the field and paste it's calculation here so that I can see it?

                            Neither Company::Activity nor Activity::ActivityID should be calculation fields. They should either both be number fields where Activity::ActivityID auto-enters a serial number or both should be text fields where Activity::ActivityID auto-enters the calculation: Get ( UUID ). (Note that adding either feature to a table that already has records will not automatically assign values to existing records. They'd need to be updated using Replace Field Contents to given them either serial numbers or UUID text.)

                            the need to link many companies to the same activity and a given activity to more than one company

                            This describes a "many to many" relationship and is almost always implemented by setting up a third "join" table that links Company to Activities. But I don't think we want to go there until the above confusion is cleared up. The original set up describes a "one to many" relationship that is basic to any relational database setup and is "step one" to setting up a many to many relationship so we need to get that working first.

                            • 11. Re: Inserting a foreign key based on a field match from parent table
                              ThomasSharp

                              Hi, OK it is necessary for me to add these numbers to an existing table as I have split up a large excel spreadsheet and imported into different tables in filemaker. Therefore I have to match the activity in the company table with the corresponding ActivityID in the activity table, and by that I mean filemaker needs to match up the the activity from both tables (which are text fields) and generate the corresponding ActivityID number for that record.  

                              (Note that adding either feature to a table that already has records will not automatically assign values to existing records. They'd need to be updated using Replace Field Contents to given them either serial numbers or UUID text. 

                              In the Company Table, I have been selecting a record in the ActivityID,  going into replace field contents, replace with calculated result, and this is where I am stuck. From your answer it seems what I've been trying to do isn't possible maybe? The Get UUID doesn't seem like it would be right here as it generates a unique number for each row and I need the corresponding ActivityID for for that record to be present in the company table. 

                              • 12. Re: Inserting a foreign key based on a field match from parent table
                                philmodjunk

                                Sometimes trying to help someone via the forum is like peeking through a house's front door keyhole and trying to figure out what color paint was used in the bathroom. My memory may be defective here and I'm not taking the time to re-read previous posts, but I don't recall any mention that you are trying to link data that has been imported from an external source instead of creating new record in a related table.

                                Therefore I have to match the activity in the company table with the corresponding ActivityID in the activity table, and by that I mean filemaker needs to match up the the activity from both tables (which are text fields) and generate the corresponding ActivityID number for that record.

                                As I read that, you have TWO different pairs of values. You have text fields with a name or description of an activity and you have ID fields, typically defined as number fields that match records by ID. When importing data, it's often necessary to match records by a "name" field temporarily in order to assign ID numbers so that you can from that point forward link the records by IDs rather than names.

                                Is that what you are trying to do here?

                                If so, set up this relationship:

                                Companies::ActivityName = Activities::ActivityName

                                This assumes that your import from Excel put exactly the same text into these two ActivityName fields. If this part works, You should be able to put a portal to Companies on your Activities layout and see at least every record from company with a matching name. Make sure that works before moving forward.

                                The next step is to set up matching ID's in the two fields. Start with Activities. If you have a defined ID field with a number or auto-entered UUID and you can see that value in each field, great. If not, Add such a field and use Replace Field Contents to populate that new field with values. Then go to the companies layout, put the cursor into the Companies::ActivityID field and use replace field contents with this calculation:

                                Actiivities::ActivityID

                                This copies over the ID from the matching Activities record. With that value finally in place, you can change the original relationship to be:

                                Companies::ActivityID = Activities::ActivityID

                                • 13. Re: Inserting a foreign key based on a field match from parent table
                                  ThomasSharp

                                  yes It has worked and is what I have been trying to acheive, thank you for your help & patience. I'll follow these steps for all other tables, then presumably set up a join table for where necessary?

                                  • 14. Re: Inserting a foreign key based on a field match from parent table
                                    philmodjunk

                                    The join table may take some added work Here's how a join table would work between activities and companies:

                                    Activities-----<Activity_Company>-----Companies

                                    Activities::__pkActivityID = Activity_Company::_fkActivityID
                                    Companies::__pkCompanyID = Activity_Company::_fkCompanyID

                                    You can place a portal to Activity_Company on the Activities layout to list and select  Companies records for each given Activities record. Fields from Companies can be included in the Portal to show additional info about each selected Companies record and the _fkCompanyID field can be set up with a value list for selecting Companies records by their ID field.

                                    So the trick will be to populate the join table with the correct IDs. And note that the join table is often a good layout context for reporting as it can list data from both the other two tables as needed.

                                     

                                    1 2 3 Previous Next