Simplest solution is to create a single Products/Services table that lists hard- and software, and if necessary, employs a flag field to distinguish the categories.
This will probably make a lot of other things easier down the line.
Thanks, but how do you create one 'Product' field in the Service table which lists both Software and Hardware products for one customer?
Let me think loudly here: a service pertains directly to a CustomerProduct, and only indirectly to a Customer; so the recommended structure would be:
Customer --< CustomerProduct --< Service
Now rather than creating a new blank Service record and using a value list to set a foreign key, display a portal of the customer's products on the Customer layout, and use a script to create a service for the product that was clicked/selected in the portal.
PeterC, I believe a 'Virtual List' may be able to provide the solution you are looking for.
Check out Virtual list creation as the answer may be in there. Hope this helps as we are using Virtual List ever since HOnza Koudelka introduced me to them over lunch. And 'yes' I am dropping names since I was lucky enough to sit down to lunch with last year's FileMaker DevCon Developer Cup Champion! ;-)
Your question lead to a fundamental discussion regarding relational data structure, but I will keep it simple:
What is your entity?
Instead of having a table for software and another for hardware you could consider them as one entity: The unit you are going to service.
What you try to accomplish is something like this (I have added service to your RD):
But what about simplifying:By seeing the unit you are going to service as the entity and then add an attribute to, a field with a value list [hardware|software] or a code like [0|1] for that.You will probably find that you need different information for hardware and software, or maybe not. That depend on your solution. This can be handled by having two tabs/sliders on you layout showing one if the attribute is set to hw and the other if it is set to sw.I would also set up the names of the keys different, but that is a matter of taste, we can take that later if you want. But as you can see I have removed your foreign key id_customer from the service table. It is not needed since service is connected to customer through the relationship with unit.And what are you going to use the lookup fields fore, not sure about them.Please let me hear what you think, and I am sure we can move forward.
I've gone back and changed the structure so that I have a Product entity which contains fields for both Software and Hardware:
Customer --< Product --< Service
I've simply set a Product Category as either 'Software' or 'Hardware' to differentiate between the two.
Appreciated your help, thanks