Please help me solve my FM problem

[ann_bas]

Hi,

I am new to filemaker. I have a problem that i need to solve immediately. I have two tables, contact table and dre taable. I have contact ID as a primary key in contact table and contact ID as a foreign key in dre table. Where in dre table I have dre ID as a primary key and I have set this a foreign key in contact table. Both of these tables have same fields like first name, second name, street address, city and zip. My target here is to merge all the data that I have inside first name, second name, street address, city and zip from dre table with that in contact table. Any help from you would be a life saver!

[Ted S]

Are you sure you even need to copy the data from one table to the other? My guess is you don't. You should already be able to reference one from the other via the primary key in the first table.

Can you explain more what you are ultimately trying to do rather than asking how to accomplish a specific task in what may be an unconventional method.

I'm not trying to be offensive but when I was new to FM I built some stuff in a structurally deficient manner because I didn't know any better and that was the only way I knew. What I'm saying is that there may be an easier and more conventional way of accomplishing your goal.

[ann_bas]

Thank you for your quick response Ted.

I already have 2000 contact info in contact table. I need to add 70,000 plus contact info from dre table onto contact table. Both these tables have same field names like first name, last name, street address, city and zip. I have Contact ID as primary key in contact table & foreign key in dre table and dre ID as primary key in dre table & foreign key in contact table.

I have a layout which shows contact info from contact table. What I have to do now is add the contact info from dre table onto the same layout.

I hope this makes sense. Please suggest what would be best solution. Thanks

[mr_vodka]

Perhaps I not following correctly here but you just want to combine the data from the two tables into 1 correct? If so, just import the one table into the other. You can map the fields when you do as well as only importing a specific found set.

[Ted S]

I agree with Mr. Vodka. Sounds like you really only need to import the data from one table into the other.

Check the help section of Filemaker, it should explain the basic process of importing.

If you have any trouble let us know.

[ann_bas]

Importing the info from one table into another makes a lot sense and i can do that very easily. But in my case i want both these tables on a single fm database. My question is how can I make a layout that now already shows 2000 contact info(first name, second name & address) from contact table also show 70,000 contact info from dre table?

[mr_vodka]

My question is how can I make a layout that now already shows 2000 contact info(first name, second name & address) from contact table also show 70,000 contact info from dre table?

You can't. Well let me clarify...

FileMaker does not support UNIONs. There are a few options but nothing native.

Option 1: Import both sets of data into one table.

Option 2: Use two portals to display each set of data separately but on one layout. ( I dont think you want it to look like this )

Option 3: If you have some kind of other external DB such as mySQL, you can either completely move the two tables to it, or if you have access to FileMaker ODBC ( supported locally by FM Pro, and FMS Advanced if served ) you can perform a query on the external data source to create a view using a UNION SQL statement.

[ann_bas]

Hi John,

I would like to try option2. Contact info from one table is already there on a layout but i can't make the contact info from the second table appear on a portal on the same layout. My layout is accepting only the first table. What should I do to solve this?

[mr_vodka]

Well here is an example of what I meant.

Again, as I said earlier, I dont think its the result that you are looking for. There is no way to create a UNION between tables without some sort of trickery as fore-mentioned.

2portals.zip

[ann_bas]

Hi John,

Thank you so much for sending me this file. I greatly appreciate your help.

I have one more question. Is there a way of showing the data from both the tables on the same layout, meaning can I have column 1 with fields displaying 2000 data from table 1 and separate column 2 with separate fields displaying 3000 data from table 2, on the same layout showing the total records of 5000 (table 1 + table 2)?

Thanks

Ann

[mr_vodka]

Ann. This is what I have been trying to explain to you from the beginning. Merging the data from two tables is called a UNION. There is no such thing in FileMaker. You will have to go with the options I explained earlier.