Layout/relationship troubles

[Erick P]

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.

[mr_vodka]

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.

[Erick P]

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?

[Fitch]

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 .)

[Erick P]

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.

[mr_vodka]

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.

[mr_vodka]

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.

[Erick P]

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.