5 Replies Latest reply on Dec 13, 2014 2:23 PM by philmodjunk

    Joining multiple tables/records to one layout

    ChristofferRexen_1

      Title

      Joining multiple tables/records to one layout

      Post

      Hello everyone

      I've searched through the forum for quite some time now, but haven't quite found the answer i'm looking for.

      How do I connect/join 3 tables, from 2 separate FM Databases, into one table or layout?

       

      My 2 FM Databases is called "Kundebase" - which contains 2 of the 3 tables I would like to connect/join - and "Privatforsikring" - contains 1 of the 3 tables I would like to connect/join. 

      I have been fooling around for a little, trying to connect the two databases with an _ID field, but my issue is the following.

      As told, "Kundebase" contains two tables - "Virksomhed" and "Medarbejder".

      "Virksomhed" table is primary table, and "Medarbejder" table is foreign table - in which I have 12.000 records with SSN etc.

      "Virksomhed" is also primary table for "Privatforsikring" -  in which I have 500 records with SNN etc. - but as External Data Source.

      1. How do I connect/join all of my 500 records in "Privatforsikring" with the "Medarbejder" table, without screwing up and making duplicates and such?

      2. I thought to make a layout, in which I gather all the primary information for both tables, and then let my colleagues advance via buttons further to the desired layouts - but that doesn't really solve my first problem.

      Any feedback would be appreciated

      Cheers

        • 1. Re: Joining multiple tables/records to one layout
          philmodjunk

          Assuming that you set up this relationship in the file where you want your layout:

          Privatforsikring>------Virksomhed-----<Medarbejder

          Virksomhed::__pkPrimaryKey = Medarbejder::_fkForeignKey
          Virksomhed::__pkPrimaryKey = Privatforsikring::_fkForeignKey

          Privatforsikring  would be a table occurrence based on the external data source reference to the file where this table is defined.

          Then you can create a layout based on Virksomhed and put portals to Medarbejder and Privatforsikring in order to show, create, edit, delete records from the two related tables.

          • 2. Re: Joining multiple tables/records to one layout
            ChristofferRexen_1

            Hi Phil

            Thanks for the quick reply.

            I forgot to mention the following.

            The Medarbejder Table was created first, which contains the 12.000 records.

            Privatforsikring Table was created secondly, which contains 500 records - some of these records have the same SSN, as some of the records in Medarbejder Table, but since they have no relationship, they have been created individually.

            As mentioned between the lines, I would like to pair the 500 records and data, with the existing 12.000 records, as I know there are some.

            Would it be the same way to do it, as you described Phil?

            Thanks

            • 3. Re: Joining multiple tables/records to one layout
              philmodjunk

              Then you would need a different relationship. One that links Privatforsikring to Medarbejder by the SSN fields. What you describe does not have enough detail for me to suggest the full relationship structure that you'd need for that as you also want to link this data in both these tables to a third table as well.

              • 4. Re: Joining multiple tables/records to one layout
                ChristofferRexen_1

                The idea was to gather all personal data like SSN etc. in one table, preferable the first created "Medarbejder", since many of the existing records in "Medarbejder" are records our employees are to contact again for private insurance matters some other time.

                I thought something like this, after sorting out, how to import the 500 records and associated data into "Medarbejder".

                Medarbejder (+ Privatforsikring)>--------Virksomhed

                Maybe deleting the "Privatforsikring" table, and then associate other related tables to the "Medarbejder" table? 

                • 5. Re: Joining multiple tables/records to one layout
                  philmodjunk

                  If you can map the fields of the two tables in a way to make that work, that makes sense. You can set up a unique values validation on a field such as SSN (Here in the states I'd read that as "social security number", but don't know if that's what you mean here as I'd assume each country has it's own identification system...). With "Validate always" enabled, you can import the records and all records with duplicate values in such a field will be omitted during the import leaving only unique values in your table after the import.