5 Replies Latest reply on Jun 9, 2011 9:43 AM by mgores

    NEWBIE QUESTION: Combining records from diff. tables

    ChristianFog
    ChristianFog Jun 8, 2011 7:28 AM

      Title

      NEWBIE QUESTION: Combining records from diff. tables

      Post

      Hi Forum Laughing

      First visit, first post. Don´t kill me for dumb questions, please Embarassed

      I am setting up my first FM db of hundreds of PCs at my school and which student or employee uses which PC. Made a Computer table with serial number as key and a Person table also with a number as key. A third table contains only those two key fields, trying to link the users with their PCs. I think this is correct to make my base relational.

      I have 100 new computers imported into the Computer table and about as many names in the Person table. But I can´t get the third table filled with data from the two others.

      The question is, how do I combine the two, assigning a PC to a person? Is it a table thing or do I need a script? I need a hint where to start looking. Have browsed through the Users Guide, FM Help and Google.

      Of course, I can assign a PC to each person outside FM, in an Excel sheet, then reimport the data to FM. But it looks like I can only import to target fields in one table at a time. My question concerns new PCs not yet assigned to a user. But later I need to import lists from Excel of PCs already linked to a user. So importing across tables sounds like a solution to me. But how?

      Thanx a whole bunch for listening,

      and even more for a hint to a solution

      Christian

      • 146 Views
      • Tags:
        • 1. Re: NEWBIE QUESTION: Combining records from diff. tables
          mgores
          mgores Jun 8, 2011 7:53 AM (in response to ChristianFog)

          Are you trying to keep track of which person has which computer?  If so, I don't think you need a third table.  You should be able to have a computerID field in the Person table or put a personID field in the computer table, depending on whether 1 person can have more than one computer of 1 computer can have more than 1 person.

          • 2. Re: NEWBIE QUESTION: Combining records from diff. tables
            philmodjunk
            philmodjunk Jun 8, 2011 8:19 AM (in response to ChristianFog)

            If you do need to assign many people to many computers, here's a demo file that assigns companies to contracts in this fashion you can take a look at: 

            http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

            • 3. Re: NEWBIE QUESTION: Combining records from diff. tables
              ChristianFog
              ChristianFog Jun 9, 2011 1:36 AM (in response to ChristianFog)

              Thanks Mark and Phil :-)

              First Mark, I do need the third table to keep the base strictly relational.

              Second Phil, my three tables has exactly the same table structure as in your example. So you confirm that I´ve got the structure right.

              But my question was how do I

              ETHER

              if I am restricted to import records into one table at a time, how will the  data show up in the third table, (your Contract_Company table). I have imported computers and persons into table one and two (corresponding to your Companies and Contracts tables) and naively expect my third table to join the people with the PCs (again corresponding to your Contract_Company table). The third table contains only the two key fields from table one and two, trying to link the  users with their PCs.

              OR

              is it possible to import records from an external Excel sheet into fields in different  tables in one go (I know how to import into fields in ONE FM table)

              http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

              • 4. Re: NEWBIE QUESTION: Combining records from diff. tables
                philmodjunk
                philmodjunk Jun 9, 2011 8:30 AM (in response to ChristianFog)

                I don't confirm that you have the structure right. I suspect that you do not need a join table here. (And your database will still be relational.)

                If you try out the demo file, you'll find that you can add records directly to the join table via a portal.

                But it still seems that you really need this relational database structure:

                Computers----<People

                People::PeopleID = Computers::PeopleID

                You only need the join table if you will have a list of multiple people assigned to the same computer AND also a list of multiple computers assigned to the same person.

                • 5. Re: NEWBIE QUESTION: Combining records from diff. tables
                  mgores
                  mgores Jun 9, 2011 9:43 AM (in response to ChristianFog)

                  As Phil stated above the only reason for a third table is if you have more than one person per computer and more than one computer per person.

                  As for importing data into 3 tables from one (or multple excel files) it would take a separate import for each table or file.  You could probably write a script to do it in one step, but it would take longer to write the script than to do the imports.  That would be worthwhile if it was an import that you would have to do over and over, but if it is a one time thing.........

                  If your excel file has the computerID, computername, personID, personName on each line.  You could do the import for the computer table to get the computerID, computername and personID fields.  Then for the person table import the personID, personname and computerID fields.  Then you can set your relationships to be either by computerID or by personID depending on what you need.