Auto set check-box field based on related records

[innodat]

BASIC SET-UP

Table 1: Contacts

Table 2: Grant Rounds

CHALLENGE

In my contacts table I have a field to specify the contact type, such as Applicant, Grantee, Nominee, etc. It's one field with check-boxes that originate from a value list.

I'm looking to populate this field automatically based on related records - and hopefully without intense scripting.

EXAMPLE

There's a contact record for "Thomas Meyer", and there's two Grant Round records which are linked through Record ID to his contact record. In a field named "progress" (name doesn't matter) one of the records contains the string "Applicant", on the other record it's "Nominee".

This should somehow translate to checking the appropriate boxes (Applicant and Nominee) on the CONTACT record of Thomas Meyer.

Likewise, if these records are deleted, the check-boxes on the contact record should be un-checked.

Any ideas anyone?

[comment]

You can make the Type field a calculation =

List ( Grant Round::Progress )

However, keep in mind that the calculation will be unstored.

[innodat]

So simple!

Unstored is ok, I have very few at this point (speed) and don't need it in a relationship.

[innodat]

One more small challenge.... if there's more than one related record for a certain contact type, the List() function lists it multiple times.

Applicant

Grantee

Grantee

Is there a way to reduce the listing to only showing one of a kind?

[comment]

See this recent thread on the same topic:

http://fmforums.com/forum/showpost.php?post/353884/

---

BTW, if the field is formatted as checkboxes, then the duplication doesn't matter.

Edited April 11, 2010 by Guest

[innodat]

Works like a charm!

I've even added a substitute to get the items listed on one line:

Substitute(

FilterValues ( ValueListItems ( Get (FileName) ; "Contact Type Static" ) ; List (T1A • Ind. Progress::Progress ));

"¶"; ", "

)

Of course I now have a "," at the very end, but that's a detail...

Thank you again for your help - you are truly expanding my FM horizon.