Finding Out What's in a Check Box Field

[Don Sloane]

I have a field named zz_Groups in the table Contacts.  

 

zz_Groups uses values from the List MyGroups.  Let's say the Value List contains friend, family, professional, work, personal, etc.  

 

Using a script, how can I determine which of the options have been checked on the form in the Check Box field based on zz_Groups?

 

Thanks!

[KaosMaker]

The contents of the field are, in fact, your checked items.

 

Let's say you have friend, professional, and personal checked off. The actual contents of your field would be:

 

Friend

Professional

Personal

 

All separated by carriage returns. If you want to check for specific values, you can use a Patterncount for each value. Patterncount(Contacts::zz_Groups; "friend") will return a 1 if they checked off friend, and a 0 if not. You'll have to do this for each value you want to check for.

[Don Sloane]

Fantastic, thank you.  I'm still a bit stuck.  Ultimately, what I'm trying to accomplish is to create distinct drop down lists based on which groups have been selected for each contact. For example if Fred Smith has been checked as a friend and professional I want to include him in the list for each of those two groups.  Do I accomplish this by creating a calculated field for each of the groups within the Contacts table and then using that field as the source for the value list or do I accomplish this by creating a relationship table.  Either way I think I've got the basic concept but can't figure out how to create the function I'm looking for.

 

Again, thanks in advance!

[bruceR]

"include him in the list for each of those two groups"

 

What does that mean?

Include him in what list?

[LaRetta]

Hi Don,

 

You want, when you select 'personal' in Group value list, it filters the Contacts down to only those Contacts will appear in the Contacts drop down that have checked 'personal' in their Groups checkbox, right?

 

What you are looking for is called a Conditional Value List.  If you do a search in the value lists section, you will find many to choose from.  I am currently on different laptop or I would provide a link to Michael's great conditional value lists.  Otherwise I would be happy to provide a sample when I get home.

[Don Sloane]

I have a field that I want to limit to contacts that are "friends" is what I was referring to.  E.G.  If the contact Fred Black's check box options for 'friend' and 'professional' were checked I want to create a Value List that just includes contacts that are "friends" and another distinct Value List for "Professional".  Hopefully this clarifies...

 

The first image shows how I have the Check Box laid out.  In the second image, for the field 'Primary Counselor' I just want the user to be able to select from the contacts that have Counselor selected 

[LaRetta]

Yes, then that is exactly what you need to search for.  Maybe Bruce or KaosMaker can put one together otherwise I will address it when I get home in a few hours. :-)

[Don Sloane]

Thanks, LaRetta

[LaRetta]

Hi Don,

 

You haven't explained the data-entry being performed so I made it up and used a global field to hold the Groups in your data-entry so you may have to adjust it a bit but see if this is close.  :-)

 

Groups.fmp12.zip

[LaRetta]

I also wanted to mention that, depending upon the functionality you require, you can set that global with "Primary Counselor" via Set Field [] script trigger when you switch to that layout.  There are many ways of implementing this but that would be pretty common method.

A single value list will adjust according to the group you specify.

You can also use ExecuteSQL() but I prefer value lists particularly since they have been optimised further in 13.

[Don Sloane]

Really Stuck - 

 

Please see

• the Entry Screen shot - this is where I'm capturing the data
• Field Definition where I'm trying to get what's in zz_Groups
• Output - zzInTheList is not showing the content of zz_Groups

 

What am I missing?

 

 

 

 

 

 

 

 

[LaRetta]

Does my file give you want you want? Does it filter the Contact's value list down to the appropriate Contacts depending upon their Group?   If yes, then it would help to see your file (zipped first) or if you cannot post it, I can provide step by step instructions you can walk through and check against your file.

 

If the answer is no, then tell me what I am missing in your need that my file does not provide.  Your second screen shot shows the calculation I provided ONLY for displaying the list in the ALL GROUPS portal.  That has nothing to do with the functionality here.  

 

Not to worry ... we'll work it through.  :-)

[LaRetta]

BTW, the calc can display the selections in a line and it can be used in your solution.  It breaks because you have not wrapping zz_Groups in quotes.  Also if you do not match the value list name EXACTLY, it will not tell you it is incorrect; it simply won't work.  So wrap it in quotes and be sure it matches your value list and it should produce a result.

 

But still, that does not address whether the pop-up filters down properly.  

[Don Sloane]

Thanks so much, LaRetta.  I've attached the file...

[KaosMaker]

In that field definition, you are calling a Value List. Did you actually create a Value List called zz_Groups in File->Manage->Value Lists?

 

If you did, be sure to put it in quotes ("zz_Groups"), since otherwise you are not passing the name of the Value List, but instead the contents of zz_Groups, which is not what it is looking for.  ValueListItems requires the *name* of an already created Value List, created through the menu I mentioned above.

 

The easiest way to do this is to:

 

1) Create a global field in Contacts, say, gGroup, and make a relationship between Contacts::gGroup and Contacts2::zz_Groups (or whatever you want to name your second table occurrence).

 

2) Create a Value List based on Contacts2::NAME (or whatever you've called it). We'll call it ContactsInGroup

 

3) Place Contacts::gGroup on your layout, with your already existing Value List (the one you used to make your checklist) populating a drop down menu.

 

4) Lastly, place whatever field you want to drop your NAME field into (I'm not sure what you're using it for) on your layout, with the ContactsInGroup value list populating a drop down menu.

 

Now, to view any complete list of contacts in a group, simply use the gGroup field to select which group you want to see, and the value list in the second field will update accordingly!

 

(oops, LaRetta responded before me, but now you get the same answer two different ways!)

 

edit: man, I am really slow...hehe

[Don Sloane]

Let's try this...

Development.fmp12.zip

[LaRetta]

You need to make your file as I show and you have nothing in it except the calculation which is not the functionality at all.  Or start over and step through KaosMaker's list.

 

If still stuck after exploring my file further, post again.  This requires three pieces:  A relationship as defined in my file, a value list created based off that new relationship.  Then attach that new value list to your Company popup.

 

Enter a Group in the global group and the Company will pop filtered.  Does my file give you what you want?  Explore it.  :-)

I ask again - Is my file giving you what you need?  It does no good to continue to try to solve something if it is not what you need. If so, then please continue your exploration of it but if not, let's regroup now.  

[bruceR]

"Output - zzInTheList is not showing the content of zz_Groups"

 

 

Well; not true.

As shown in list view portion of your screenshot, zz_Groups already shows its own contest.

 

Delete zzInTheList

[Don Sloane]

You guys are ROCK STARS! Thank you so very much for the timely and useful support. I got it working!