Question about Records from Multiple Tables
I'm trying to create a simple solution for invoicing; I used the supplied 'starter solution' for 'invoicing' as (of course) a starting point for this.
However, since this solution only has a table 'products' which feed the line items for the final invoice, I created another table, 'Services' which is similar to products but does not need all the inventory-related stuff.
When I create a new record for the invoices table, the layout has a portal to 'line items' (another table). This 'Line Items' table is related to 'Related Products' through the Product ID (see the provided starter solution in a standard FM Pro 11 install).
In the portal on the 'Invoices' layout, a user can select a product ID from a value list which is defined as 'Related Products::Product ID', so if someone had added a record to the 'Products' table (i.e., a new product), it will show in the drop-down list on the portal and the rest of the invoice line will auto-fill (using the lookups from the 'Line Items' table).
Now here comes the tricky part. I would like the drop-down list to show me values not only from Products (i.e. the 'Product ID'), but also from 'Services' (i.e. 'Service ID'). Then, I would like the line item to perform the lookups in their respective tables, so if someone selects the service ID, it will fill the invoice line with the relevant descriptions, etc from the Services table (and perhaps 'Quantity' is left auto-empty).
I've understood that somehow, I think I will need an aggregated/meta-table that collects both the Product ID and the Service ID in one table, but thus far I have failed in creating this. This table could then feed the line items table.
Any ideas how I can create the invoice, for either a product or a service? Or another approach? Perhaps make a 'merged table' somehow? Perhaps there's some complex relationship that I need?
Thanks for all the help and feedback. Highly appreciated.