AnsweredAssumed Answered

NEWBIE QUESTION: Combining records from diff. tables

Question asked by ChristianFog on Jun 8, 2011
Latest reply on Jun 9, 2011 by mgores

Like • Show 0 Likes0
Comment • 5

Title

Post

Hi Forum

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

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

No one else has this question

Outcomes

mgores Jun 8, 2011 7:53 AM

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.

Actions

philmodjunk Jun 8, 2011 8:19 AM

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

Actions

ChristianFog Jun 9, 2011 1:36 AM

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.

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

Actions

philmodjunk Jun 9, 2011 8:30 AM

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.

Actions

mgores Jun 9, 2011 9:43 AM

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.

Actions

5 Replies

mgores Jun 8, 2011 7:53 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
philmodjunk Jun 8, 2011 8:19 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
ChristianFog Jun 9, 2011 1:36 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
philmodjunk Jun 9, 2011 8:30 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
mgores Jun 9, 2011 9:43 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions

NEWBIE QUESTION: Combining records from diff. tables

Title

Post

Outcomes

Related Content

Recommended Content