Hello,
I was wondering if it is possible to have a secondary key to associate 3 tables, in addition to the primary foreign key?
I have 3 tables (AR, WIP, and Inventory).
Each table has a Plant field and Customer Field.
The foreign key I am using is the Plant Field.
I have a tables set-up to show AR aging, WIP aging, and Inventory aging and a sum Dollar amount for each.
For AR, each record has a plant, customer, invoice #, date invoice was sent, and invoice amount. I then sum the amounts to get the total AR, etc. I do the same for WIP (instead of invoice # we have work order #) and Inventory (part # and date last used).
When I filter by the Plant Field, everything works as expected.
When I filter by customer, it does not work as expected, and that is because I have 3 customer dimensions (AR_customer, WIP_customer, and Inventory_customer). If I filter by customer on the AR table, the Plants associated to that customer are filtered. These filters are carried over the WIP tables and values shown are all WIP for the plants that were filtered by selecting the customer in AR.
In other words...I would like to have the Plants associated and customers associated so that the customer dimension carries throughout the application as well as the plant dimension.
What would be the best way to handle this?
