I'm in the process of creating a service module for our customer database. The products requiring service can be either hardware or software, and I have them as separate tables. In the attached sample, I have four tables, customer, hardware, software and service.
My idea was, that when creating a service sheet for a product in the service table, selection for the product could be done from a single popup lookup value list. The value list would show all products, both hardware and software, related to a customer.
Meaning that the list could look like:
Hardware - Luxel-8
Hardware - Screen 8800
Software - XMF
Software - Blackmagic
Problem is that I'm not sure how to set up the join between service, software, hardware and customer tables.
I'm totally open to good ideas and suggestions, as I'm stuck here :-)
I've attached a simple sample file which hopefully shows what I'm trying to achieve.