Multiple tables as data sources for list for single field in another table

Discussion created by on Jan 22, 2017
Latest reply on Jan 23, 2017 by philmodjunk

How do you make a list include items from multiple varying tables?   It's not a true Many-to-many.  Perhaps it's more like consolidating several Many-to-One relationships.


In our project, we’re tracking three types of billable items: Equipment, Labor, and Miscellaneous. We want to track these items in a single table of "Line Items", as there should really be only one ledger of items clients are buying, regardless of type of item.  But each of the types of items have many exclusively relevant fields.  Underlying Labor units are people.  Underlying Equipment units are machines or products.


The main table of line items should include the fields: Client, Item Type, Item, Rate, Quantity, Discount, Extended Subtotal.

But we’re also interested in tracking specific costs and inventory, specifically, against each billing, so we’d also want to include, within each line item, Assigned Resource (the specific equipment unit / person tasked to the job), and Internal Cost (cost of that specific resource).


It's not clear how I can build table and form structure so that the drop downs "Item" and for "Assigned Resource" could each include all three types of items. I don't want to have to denormalize so much that every billableitem record has 3 different fields for items, depending on type, and 2 will always be empty - that seems to make no sense.  Likewise, having three tables (e.g., billableEquipment, billableLabor, and billableMisc) seems to make no sense.


I don't want to overcomplicate, but I also don't want to create unwieldy tables.


Thanks for sharing your expertise.