5 Replies Latest reply on Nov 4, 2010 3:28 PM by philmodjunk

# Calculation based on Value List Checkboxes

### Title

Calculation based on Value List Checkboxes

### Post

I don't know why, but I have the hardest time with calculations.  Can someone help?

Basic Set Up:

Everthing is based out of my main table.  Let's call it Table 1.  Each Record is one person (like a medical record).

On there I have a portal to another table, Data Table.

There is a field on there called Behaviors.  Behaviors has a Value List (Called Behavior) with Smile, Frown, Laugh and Cry, which I have set up as a Check Box.  It's next to a Date Field so that each day has its own set of check boxes.

My hope is to set up a calcuation to let me know if the person who's record is open has a history of Smile or Laugh (which are in the Value List- Check boxes).  (Whether just one or a billion doesn't matter)

So far I've gotten it to say "NO" using an IF function... except it said no even if Smile or Laugh WERE clicked.

If it only works for just One Value from the Value List, that's ok.  I don't mind doing two calcuations... I just have no idea what I'm doing!

Thank you for your help!  I really appreciate it!!

~Kim~

• ###### 1. Re: Calculation based on Value List Checkboxes

It helps to understand how FileMaker stores the data when the user clicks some check boxes. It stores the actual values from your list separated by carriage returns in the order they were selected by the user.

Thus, in one record, you might find the values in the field are:

Smile
Frown
Laugh

and in another record:

Frown
Laugh
Smile

They will look the same as they have the same values checked, but because the user clicked the check boxes in a different order, they aren't stored in the same order in the actual field. You can see this for yourself, if you place a copy of your check box field next to the first, then change its format back to edit box instead of checkbox set.

So to test and see if a value was selected in a checkbox formatted field requires extracting that value out from any other values that might have been selected. Here's the best way I know to check and see if "smile" was selected in the field:

IF ( Not IsEmpty ( FilterValues ( YourcheckBoxField ; "Smile" ) ) ; "smile selected" ; "Smile not selected" )

• ###### 2. Re: Calculation based on Value List Checkboxes

So now if I select any of the checkboxes, it tells me that "Behavior" is designed to only show specific values and makes me clear all the checkboxes.

Would it make my life easier to just put the two behaviors I care about (Smile and Laugh) in a different field with their own value list?

This would mean I just need a formula that shows if anything in that one field has been selected at all.

Potentially in the future I would like to have a running tally of number of laughs, smiles, etc. Or the number of people who have laughed, smiled etc.

• ###### 3. Re: Calculation based on Value List Checkboxes

The calculation example I gave should not be used as the field that you've set up as a checkbox. This would be a calculation field separate from the check box field. It should not have any effect on the behavior of the check box when you click it.

If you wrote it as:

IF ( Not IsEmpty ( FilterValues ( YourcheckBoxField ; "Smile" ) ) ; 1 ; 0 )

The sum of this calculation field would give you the total count where "smile" was selected.

• ###### 4. Re: Calculation based on Value List Checkboxes

Using your example as I originally had everything set up resulted in that strange error.  I created a separate field with the calculation you provided. Afterwards I received the 'error' message any time I tried to click anything inside Behavior Field/Value List.

I changed the field calculation (which I've called CALC) to the second example you've given me.  Now it shows a 1 on the first 'record' (which is correct), gives me the error on a second, and a zero for the next three (which is wrong on the first one).

I am debating changing to format (to more fields) if it would make it easier on me in the long run.  I haven't done it yet out of stubbornness.

Just as soon as I think I have it solved, a new thing pops up.  :)  Thanks again for helping me.  You're a life saver!!

~Kim~

• ###### 5. Re: Calculation based on Value List Checkboxes

Seperate fields may have an advantage. For one thing, they could all enter the value 1 when clicked. You'd just resize the fields so that only the check box is visible and then place layout text next to each box as a label. This would make totaling up specific behaviours pretty straight forward.

If you want to take another shot at a single field for your check boxes, compare what you've done to this demo file:  http://www.4shared.com/file/MirDCm3a/CheckBoxEnumeration2.html