IF Field = Matches then this Field = This

[Dubaxter]

Not another Newbie question....

 

I have two tables I am trying to work across, Organisation Table and Contact Table, what I need is for a field called "Institutional Subscription Exists" to automatically enter YES or NO based on the following.

 

I need a calculation that looks at what is selected in a drop down menu from a defined list called "Publications" in a field called "Associated Publications 1" in the Contact Table and compare it with what is selected on my Organisation Table in a field called "Media and Culture" that is a defined list called "Subscriptions" and it is displayed as a check box.

 

For example I have "JMP" selected in "Associated Publications 1" on my Contact Table and I have "JMP" checked on my Organisation table in the "Media and Culture" check box field and I want it to automatically say YES in the "Institutional Subscription Exists" field on my Contact Table. Ofcourse if it was not checked I would want it to say NO.

 

Any help would be more than greatly appreciated and would result in a reward of a pint for anyone coming to Bristol.So Cool!

[SlimTech]

Maybe a more experienced FMP user wouldn't need to ask this but, is there a relationship between your two tables, Organisation Table and Contact Table? If so, what is it / what are the match field(s)?

 

Mike Franks

[Dubaxter]

Apologies if I had not made my message clear.

 

To simplify matters.

 

I have two tables that have a relationship.

 

I need to compare two fields both containing defined lists (one a check box, one a menu list) to see if they match, if they do I need another field to say YES/NO

[SlimTech]

What is/are the match field(s) between your Organisation Table and Contact Table?

[Dubaxter]

The only match field I have is Organisation ID.

[SlimTech]

Hi Dubaxter,

 

I hope this late reply is still of use to you or others.

 

I've assumed that your "Media and Culture" check box field options are dynamic and constructed from all available publications. If the options come from the "Publications" value list, you're forced to add a new Contact record together with the new publication each time (provided that in the Pop-up list field formatting, "Other" is selected).

 

To be able to enter new publications such as JMP or "The Economist" without having to add new Contact records, it's necessary to have a separate table for them or to create a new value list. I made a working solution with a new table called, Publications containing just the one field, Publication. The Publications table is not related to either of the other two tables.

 

Base your Publications value list upon "Publication" in the new Publications table. Format the "Associated Publications 1" field as a pop-up list based upon the Publications value list. Format "Media and Culture" as a checkbox also based upon the Publications value list. The "Subscriptions" value list is no longer required.

 

In the Calculation field definition for "Institutional Subscription Exists" enter the following:-

Case ( IsEmpty ( Associated Publications 1 ) ; "" ; PatternCount ( Organisation Table::Media andor Culture ; Associated Publications 1) > 0 ; "Yes" ; "No" ).

 

I have to thank AHunter3 for the PatternCount wizardry as if instead,

Case ( IsEmpty ( Associated Publications 1 ) ; "" ; Associated Publications 1 = Organisation Table::Media andor Culture ; "Yes" ; "No" ) is used, "Yes" is returned if only one of the checkboxes is selected (including a matching publication, of course).

 

Format "Institutional Subscription Exists" as a simple "Yes" / "No" checkbox value list containing these two values.

 

Hope it helps,

Michael Franks