If you have a relationship such as:
Customer::__pkCustomerID = Invoices::_fkCustomerID
with _fkCustomerID set up on the invoices layout as a "use values from field" value list with __pkCustomerID as the "first field" in the value list....
You can add any fields from Customer that you need to the Invoices layout and they will display data from the selected customer once you select a value in the value list. You can also enter find mode and specify find criteria in this field. But keep in mind that such a find, finds all invoices by that customer. You won't be able to use this method to find a customer that does not yet have an invoice in your database.
Always nice to get help from you Phil, much appreciated!
It seems like a cleaver way to solve it as you suggested. I think my problem then maybe lies within my Contacts -table. This table contains all company contacts, suppliers, customers and other contacts. I do not have a separate Customers-table. It is only the Relation-field that states the status of the contact. With your suggestion the setup would rather look like
Contacts::__pkCompanyID = Invoices::_fkCompanyID
If I create a value list based on this it would show all the contacts and not only my customers. That is why I based the relation on the Relation field instead and could pick out only the ones marked as "Customer".
Would it be a better design to have different tables for different types of contacts (customers, suppliers, others) and gather them all together with a portal in some way to display all contacts ?
Ok, that's the next step of your project--limiting your value list to just those contacts that represent customers. I would use a "hardwired conditional value list" for that.
See the "hardwired" CVL examples in "Adventures in FileMaking #1 - Conditional Value Lists" for two different ways that you can use to get such a value list.
Wow! What a resource!
Thank you very much. This will hopefully help me through!