Jump to content
Salesforce and other SMB Solutions are coming soon. ×

IF Field = Matches then this Field = This


Recommended Posts

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Terms of Use