Hi all. This is my first post here, so please, be gentle.

I'm developing a view into our online ordering system, but I can't figure out how to get the layout to show me all my records.

I have a customers table setup. From that table I have a one-to-many relationship to 2 other tables:

1. custom_orders

2. designed_orders

I setup a layout based on the customers table. I then created a tab control with 2 tabs to show the custom & designed orders for a customer. So far so good.

Now I want to have a layout that shows me All order from both the custom_orders table AND the designed_orders table.

I've been searching the forums and done a lot of trial and error, but I'm stumped. How can I get a view to show me all the orders for all the cusotmers?

Any advice much appreciated.

Erick.

Hi Eric,

Welcome to the fourms.

A portal can only show you the related records from one table occurence so you will not be able to see all the records unless you merge the tables.

I believe that you should merge the tables anyway considering that all of them are orders. You can create a new field called ordertype wehre you can designate it to be a custom order or designed order.

You can then use a global field to filter the portal to display Custom, Designed, or All records via an interface of a radio button or something.

John,

Thanks for the quick reply. I agree with you on combining the tables. The problem is I'm syncing my filemaker db with a MySQL db solution that is already setup this way. I've spoken to the developer and it is a major change for him to combine the tables.

So, I'll keep trying to get creative to solve this. I thought about creating a new table called All_Orders. In it I would have 3 fields:

All_Orders ID

CustomOrderID - foreign key back to customerOrder table

DesignedORderID - foreign key back to DesignedOrder table

The idea being that I could setup a script to populate that table with all records from both order tables.

Then I should be able to build a layout based on the new All_Orders table that has a portal with calculated fields.

In the calc fields, I would see which field in the All_Orders table is populated (CustomOrderID or DesignedOrderID), then pull the needed fields from those tables.

Ok, so that is all theoretical. I'm totally new to filemaker and I have no idea if/how to do most of what I just described. I'm nose deep in a manual and the forum archives.

Is there anything here that sounds like it won't work?

I agree, it sounds like those two tables should be merged into one. However, it is possible to show all records from two tables on one layout, by using a portal for each one. The portals would be based on relationships using the cartesian join, which is the "X" option in the relationship graph. (Typically .)

Tom,

I've played with the cartesian join but got stumped on the layout.

If I understand you correctly, I make a cartesian join between the 2 orders tables? Or between the 2 orders table and the customers table?

Once the join is made, what table do I base the layout on? I tried this before but couldn't figure out how to make the relationships work to get the portal setup.

Hiya Tom,

But this would give you all the records from each table using two seperate portals. The OP wants one portal displaying all of the customers related orders.

John,

Thanks for the quick reply. I agree with you on combining the tables. The problem is I'm syncing my filemaker db with a MySQL db solution that is already setup this way. I've spoken to the developer and it is a major change for him to combine the tables.

So, I'll keep trying to get creative to solve this. I thought about creating a new table called All_Orders. In it I would have 3 fields:

All_Orders ID

CustomOrderID - foreign key back to customerOrder table

DesignedORderID - foreign key back to DesignedOrder table

The idea being that I could setup a script to populate that table with all records from both order tables.

Then I should be able to build a layout based on the new All_Orders table that has a portal with calculated fields.

In the calc fields, I would see which field in the All_Orders table is populated (CustomOrderID or DesignedOrderID), then pull the needed fields from those tables.

Ok, so that is all theoretical. I'm totally new to filemaker and I have no idea if/how to do most of what I just described. I'm nose deep in a manual and the forum archives.

Is there anything here that sounds like it won't work?

Are you using ODBC to import the data? If so, then as a part of that script process, you can import it into one FM table and in that script assign the value to the type field.

Are you using ODBC to import the data? If so, then as a part of that script process, you can import it into one FM table and in that script assign the value to the type field.

Great idea! That is exactly what I need to do. I can't believe I never saw that option before now. Thanks! I'll report back.