Combinig Table Data

[FM-Monster]

I am back for my second posting on FMForums--the last one went so well I can't stay away.

I have two tables that track data of inventory used on a job. I am using two tables because the two projects bill very differently, and I want to add dif projects in the future. I use the same value list for descriptions of all inventory. Both tables have a relationship back to the same customer. I also have a purchase orders table that adds inventory quantities.

I am using subsummaries in a layout to show the inventory totals being used for each table. But I can't get the two tables or layout information together. I want to take the combined information and to compare with the purchase orders to get my current inventory.

I am trying to combine the two tables info and have it update itself in realtime but can't seem to figure it out. Help!

I hope the above makes sense???

Edited October 7, 2005 by Guest

[Ender]

I have two tables that track data of inventory used on a job. I am using two tables because the two projects bill very differently, and I want to add dif projects in the future.

This does not quite make sense to me. Are the types of things in the two Inventory tables the same? If so, then they should be combined.

[FM-Monster]

Well maybe that is the avenue I should pursue. I use two tables because I wanted to use different portals on different tab layouts. I am still in the setting up phase and maybe I should just use one table.

[Ender]

You can relate to the same Inventory table in multiple ways by filtering each relationship by ProjectType (or whatever.) Something like this:

Project <=> Inventory by ProjectA =

Project: ProjectID = Inventory by ProjectA::ProjectID

AND Project::gProjectA = Inventory by ProjectA::ProjectType

and

Project <=> Inventory by ProjectB =

Project:: ProjectID = Inventory by ProjectB::ProjectID

AND Project::gProjectB = Inventory by ProjectB::ProjectType

Then when it comes time to do reports, you can either have a combined inventory report, or have a sub-summary by ProjectType to group them.