I posted this before, but it seems like the thread has died out. I will try to explain a little better here.
I have a document that contains around 30 tables. It was originally built for users to see information regarding orders. So The flow of the document begins with the Order head table and flows down to order details, jobs, shipments, and invoices. Other selection fields are also connected to the Order head table, like the Customer table for users to select customers and see their orders. Order details also has selection tables for part classes and for product groups.
We are also Multi company so part of the link from table to table is the company field. For user selection I am using the Company field from the Order head table.
With this set up everything was working correctly. Users select a company and see everything related to order regarding that company. The same is true for products, classes, and customers.
Now I have been requested to add Quote information to this document as well.
This is where I am having problems. There can be quotes that have no Orders, and Orders that are not related to quotes. They are linked from the quote detail table to the order detail table.
Users want the ability to select a company,customer,product,class (fields that quotes details and order details both have) and see ALL orders and ALL quotes from the selection.
Currently due to my selection tables/Company field linking into the Order head and order detail tables, when the user selects something they expect to see everything, but this is creating an left join to the quote and only pulling quotes that are linked to Orders.
In the other thread someone suggested concatenating the order tables with the quote tables to allow this to work, however this removes the link between quotes and orders. (They want to see a chart also that shows total quotes compared to what was ordered)
How could I go about doing this where they only need to select one field and see both sides of the data? Linking the customer to both quote head and to order head would create a circular reference, as would doing the same with the other selection tables.
Any help would be highly appreciated.
Attached is the table view. Circled in red are the tables/fields that I am having selection issues with.
Untitled.png 126.3 K