AnsweredAssumed Answered

Multiple calculations from related table

Question asked by Michiel on Dec 9, 2015
Latest reply on Jun 2, 2016 by Michiel

Dear all,

 

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

Contact_ID               Document_ID

etc.                           fID_Contact_ID

                                 Document_Type

 

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.

Outcomes