I have a database (OS X FM 11 Advanced) I am working on for a personal business and have a complication that I need some assistance with.

Here are the tables:

Customers -> stores customer contact info

Prints -> predefined artwork prints with inventory for sale for any customer

Custom -> artwork that is custom based on customer input that is tied to one particular customer

Invoice -> invoice sent to a customer for artwork (both custom and prints) bought

Invoice_items -> each line item to be added to the invoice total

On my Invoice form, I have a portal tool that links to invoice items. In the portal tool I have a dropdown to select artwork to add to invoice. Here is where I am stuck.

What I want is the drop down list to populate with the following:

- Custom Artwork associated with the customer via cust_id

- Prints that are not sold out

I have been able to create a value list of Custom artwork based upon the customer_id of the invoice, but how do I merge the prints data to the same list when I have no relationships between prints and custom?

It seems like I need to create a third table that merges the data from custom based upon the invoice customer_id and prints that are not sold out and then use that table to generate my value list. Am I on the right track with this?

Any input on this would be appreciated. Thanks!

Why wouldn't you use a single table for both types of artwork to begin with?

Why wouldn't you use a single table for both types of artwork to begin with?

I am starting to reevaluate my database and am coming to that same conclusion. I added the print feature after the custom feature and was trying to fix it at the end rather than going back and fixing it "correctly" at the beginning.

Thanks.