5 Replies Latest reply on Dec 15, 2008 7:11 AM by trevorlawson

    Dynamically combining two or more tables into one

    trevorlawson

      Title

      Dynamically combining two or more tables into one

      Post

      Is it possible to dynamically put data from fields in different tables into a single field in another table?

       

      I have four "country" tables (for sites in England, Northern Ireland, Scotland and Wales). Each of the four country tables has a field containing the site names: "Name England", "Name Northern Ireland" etc.  I want my new table, say "UK sites", to contain a field called "Name UK" which holds all the data from the "Name" fields in the other tables.  How can I achieve this?  

       

      I had thought that in the Relationships graph I would be able to choose "Name UK" and then say "=Name England+Name Northern Ireland" etc, but there is no + symbol and it will not let me link several tables to one other.

       

      I'm doing this because I want the "Name UK" table to be a dynamic Value List for another field called "Staff location".  I need the Value List to automatically update when a new site is added to one of the four country tables.  

       

      Thanks a million for any advice you can offer.  

       

       

        • 1. Re: Dynamically combining two or more tables into one
          Kat4
            

          I'm still learning how to use FMP myself, so first thing I'll say is that you should wait for further advice before committing yourself to the path I suggest.

          I think you may have to create a "Key" rather than just using the Names in each of your tables in order to connect them in the relationships graph. For Example, you'd have the field "England ID" in the English table which would be set up to be an auto-entry serial number, and the same for each of the others. Then in your UK table, you have the UK ID also set up for auto-entry serial number, but you also list the fields England ID, Northern Ireland ID, Scotland ID and Wales ID. These Four can be regular fields, don't have to be validated. Then on your relationship chart, you connect each contry using the ID you created to the corresponding one on your UK Relationship box.

          Does that sound clear?

          You were on the right track but I think you just need to create those "Keys"

          Kathy 

           

          • 2. Re: Dynamically combining two or more tables into one
            Sorbsbuster
              

            The answer is certainly 'Yes', and it should be easy.  Unfortunately I'm finding it hard to think of a 'real-life' example set around the scenario you explained that would seem sensible.  I'm sure it's my mis-understanding, as in most of the interpretations of your scenario that I think of I would simply mark up a different field in the same Table, not record in another different Table.

             

            No matter; hopefully you can get what you want from my 'general' explanation:

             

            - Suppose you have 3 Tables, A, B, and C.

            - Each have two fields, A1 and A2, B1 and B2, etc

            - Set up two relationships, via Field1

             

            Table A --> RelationshipAB (FieldA1::FieldB1) --> Table B

            and

            Table A --> RelationshipAC FieldA1::FieldC1 --> TableC

             

             

            That relates Table A to Tables B and C (via fields A1, B1, C1, all with 'Country' in them, maybe?)

             

            If you want FieldA2 to have the concatonated contents of the fields B2 and C2 then just change FieldA2 to be a calculation:

             

            FieldA2 =  RelationshipAB::FieldB2 & " " & RelationshipAC::FieldC2

             

            This will create a field that has the contents of the matching record in Table B, with a space (you can make it a 'dash', or a 'return' if that is better presentation) and then the contents of the matching record in Table C.

             

            Note that you do not use the '+' symbol, but an ampersand.

             

            Because I do not fully understand why you are trying to do this, I have to add a caveat: in this calculation you will only get the contents of the first matching record in Table B joined to the first matching record in Table C.  In other words, I would guess it only makes sense if you are certain that there only ever will be one matching record in Tables B and C for each record in Table A.

             

            I hope this helps, anyway,

             

            Alan.

             

            Edit: Back to my first point:  why would you not have put all the records (for the 4 countries) in oneTable, and had a 'Country' field marked up as 'England', 'Scotland'... etc?  It would maybe help my understanding, to be able to help you better.

            • 3. Re: Dynamically combining two or more tables into one
              trevorlawson
                

              Hi Kat and Sorbs,

               

              Thanks for your suggestions.  I can rule out Sorbs' idea because I don't want to concatenate the locations from different countries.

               

              I'm not sure about Kat's advice.  If there is only one field in each table, containing unique data, I figure that must be a Primary Key.

              The problem for me is the whole vocabulary of Filemaker and the relationships between tables.  Every time I look at a solution or Help file, I see a dozen terms that I don't fully understand and I'm having trouble visualising the solution.  This problem is not confined to Filemaker alone of course!

               

              Maybe I also didn't explain myself too well.

               

              Say I have two tables:

               

              "Locations England" and "Locations Wales"

               

              Each table has one field:

               

              "Location England" and "Location Wales"

               

              In the England table there are three records: "EngSite1", "EngSite2" and "EngSote3" and in the Wales table there is one record: "WalSite1".

               

               I want a dynamic table that lists all these records in one field.  The table is "Locations UK" and the field is "Location UK".  There should be four records in the field:

               

                "EngSite1", "EngSite2", "EngSote3", "WalSite1"

               

              If I add another site in my Wales table: "WalSite2", it should automatically also appear in my UK table:

               

                 "EngSite1", "EngSite2", "EngSote3", "WalSite1", "WalSite2"

               

              I have set up two Relationships:

               

              "Location England = Location UK"

               

              and

               

              "Location Wales = Location UK"

               

              In both, the option to create records in this table is clicked only for the Location UK side of the relationship. 

               

              Lines come out of the England and Wales tables and go into the UK table.

               

              Then I create a layout using the field "Location UK" from the UK table and switch to Browse mode expecting to see all the records from England and Wales listed there.  But instead, all I see are the words "Unrelated table".

               

              I sink into a gloomy state.  I am lost.  Any advice warmly welcomed. 

              • 4. Re: Dynamically combining two or more tables into one
                Sorbsbuster
                  

                Sorbsbuster back for another go...

                 

                Yip, I think the vocabulary is confusing us.

                 

                Table:A collection of records. 

                Think:

                Table1 = 'Yellow Pages', Table 2 = 'Grey Pages'.

                 

                Record: A collection of fields (all relating to one item)

                Think:

                Table 1, Record 1 = 'AAA Taxis', Record 2 = 'AB Services'... Record (last) = 'ZZ Top Hairdressing'

                Table 2, Record 1 = 'Mr A Aardvaark', Record 2 = 'Mrs Ann Austin',... Record (last) = 'Mr Zebediah Zebra'

                 

                Field: A single characteristic of the record

                Think:

                Table 1 Fields in all records are  'Business name', 'Business Type', 'Street', 'Town', etc.

                Table 2 Fields in all records are  'Surname', 'Title', 'Street', 'Town', etc.

                 

                Data: contents of the field in the record in the table.

                Think:

                'Smith', 'Mr', Acacia Avenue', 'Manchester', etc.

                 

                Tables consist of a collection of records.

                Records consist of a collection of fields

                Fields are filled with data.

                 

                Hence, your sentence:  "Each table has one field: "Location England" and "Location Wales" " doesn't really make any sense.  I assume you mean:

                 

                Each Record has one field - 'Location'.

                One record in that Table has 'England' in that field.

                A second record in that Table has 'Wales' in that same field.

                 

                ------------------------------------------------------

                I think if you create two Tables you will see the effect you want:

                 

                Tables:

                1. Country

                2. Locations

                 

                In the 'Country' Table create two fields, 'Country', and 'Location'

                In the 'Locations Table' create  three fields, 'Country', 'Location', and 'Site'

                 

                In 'File -> Manage -> Database', go to the 'Relationships' tab.

                Create a new relationship (the button with a square and a '+', bottom left).

                On the LHS, choose the Table 'Country', and the field 'Country'.

                On the RHS, choose the Table 'Locations' and the field 'Country'

                Click 'Add'.

                Ok -> Ok, etc..

                 

                Change to the layout 'Country'.

                Select 'View' - > Layout Mode

                Choose the Portal tool from the Status bar

                Click and drag a rectangle on to the layout.

                In 'Show related records from...', choose 'Locations', and set the 'Portal Rows' to be 5

                Choose to set into the portal the fields 'Location', and 'Site'

                 

                Go back into Browse mode.

                In the 'Country' screen, create one record, and enter 'UK' into the 'Country' field.

                Change to the 'Location' screen.

                Create one record.  Enter 'UK' into the 'Country' field.  Enter 'Wales1' into the 'Location' field.

                Create another record. Enter 'UK' into the 'Country' field.  Enter 'Wales2' into the 'Location' field.

                Create another record. Enter 'UK' into the 'Country' field.  Enter 'Scotland1' into the 'Location' field.

                 

                Now go back to the 'Country' screen.  You will see that in the record with 'UK' in the 'Country' field it shows the 3 records listed, in the portal you created.

                 

                Change the 'Country' in that record to be 'USA', and they all vanish - there are no 'Locations' whose 'Country' has been tagged as 'USA'.

                 

                Go back into the 'Locations' screen.

                Add another record.  Type 'USA' into the 'Country', and  'Florida' into the 'Location'.

                Add another record.  Type 'USA' into the 'Country', and  'Texas' into the 'Location'.

                 

                Now go back to the 'Country' screen.  You will see that in the record with 'USA' in the 'Country' field it shows the 2 records listed, in the portal you created.

                Create another record in the 'Country' table.  (You will now have two records in the 'Country' Table, and 5 in the 'Locations' Table.)

                In one record have the 'Country' as 'UK', and  in the other have it as 'USA'.

                You will see as you flick between them they list all of the 'Locations' that have been tagged as 'belonging to them'.

                 

                If this works for you and makes some sort of sense, you will be able to make all the connections that you are talking about.

                 

                Sorry if this isn't clear enough,

                Alan.

                 

                • 5. Re: Dynamically combining two or more tables into one
                  trevorlawson
                    

                  Hi Alan,

                   

                  Thanks for this detailed answer.  I have not got around to testing it yet and will let you know when I do. Meanwhile, your advice is hugely appreciated - thanks so much.