Englisch isn't my native language, so please forgive me. I hope you can help me. This is my first FM solution, so I'm really a beginner.
My database has (among others) the following tables:
Contacts and Documents
Every document belongs to only one contact. A contact have more documents. Clearly a one-to-many relation (Contacts:Contact_ID=Documents:fID_Contact_ID).
In my "contacts-layout" I have made a portal that shows me all the documents related to that contact. Works fine and so far so good.
Let's say one of the document type is "Type x". I want to create a calculation field in de table 'Contacts' that will calculate there is a 'Type x' document for that contact. I created the following calculation:
If ( Documents::Document_Type="Type x" ; "Yes" ; "No" )
This works, but only for the first related record; Only if the first document of this contact is 'Type x', the calculation will show 'Yes'.
After reading several discussions I changed 'sorting records' in the relation between the two tables, by sorting on 'Document_Type'. This solves the problem, but only for 'Type x'. I also want to make a similar calculation for 'Type y'. The result is 'No' when the first related record is 'Type x' and the second related record is 'Type y'. That's incorrect, because there is a document 'Type y' for that contact.
So my question; how do I solve this?
Extra info; why these calculations?
I want to create a list of all contacts, where I can see quickly if the contact has an document 'Type x' and/or 'Type y'. Any suggestions are very welcome!
Thank you in advance.