10 Replies Latest reply on Sep 28, 2011 1:18 PM by HaroldBernstein

# Help with Unique Value script

### Title

Help with Unique Value script

### Post

I would like to calculate the number of unique integer values in Field1, but only for those records in which Field2 ≠ "Yes."

I'm familiar with the unique value calculating script:

sGroupCount=Count(Field1)

cFraction=Get Summary(sGroupCount;Field1)

sUniqueCount=Sum(Field1)

I need some advice, however, for modifying so that I'm only counting unique fields in records which satisfy Field2≠"Yes."

• ###### 1. Re: Help with Unique Value script

The simplest approach is to perform a find for only records where Field2 does not have "yes" in it. Will that work for you?

sGroupCount= Summary field, "Count of" : Field1

cFraction= 1 / Get Summary(sGroupCount;Field1)

sUniqueCount=Summary field, "Total of" : cFraction

You caould also subsitute a calculation field in place of Field 1:

If ( Field2≠"Yes." ; 1 )

• ###### 2. Re: Help with Unique Value script

If I go the Find route, will the reciprocal method then only perform the calculation on found records?

If I go the If calculation route, how do I replace Field1 in sGroupCount? It doesn't appear that Summary fields allow you to input a calculation.

Sorry for all the questions--I'm new to this.

• ###### 3. Re: Help with Unique Value script

Yes, and since this omits records where field2 = "yes.", you will then get the correct count.

Using the calculation field approach, you define a new calculation field, let's call it cNotYes, then you refer to cNotYes instead of Field1 in the definition for sGroupCount.

• ###### 4. Re: Help with Unique Value script

Thank you. Before adding that on, I seem to be having a problem implementing the Sum the Reciprocal. In a test run, I have 5 distinct values in Field1, yet cUniqueCount returns the value 2.833333. Field1 is an integer, so I'm looking for how many 33s and how many 34s, etc. I have.

• ###### 5. Re: Help with Unique Value script

Did you sort your records by field1? They have to be grouped by the value in this field before the calculations return the correct values.

• ###### 6. Re: Help with Unique Value script

I did. I'm wondering if there's another setting burried in there among the calculation or summary fields that I need to take care of. I noticed that the summary fields have options for Running Count, Restart Summary for Each Sorted Group when Sorted By:

• ###### 7. Re: Help with Unique Value script

Do not use the running count option here. If sorted correctly, this should work.

What do you see in cFraction for each of these records?

• ###### 8. Re: Help with Unique Value script

It's now working sometimes. For the Summary fields, I tried setting Summarize Repetitions to "Individually" instead of "All Together," and that seemed to help. There seems to be a long delay, though, between entering values into Field1 and re-sorting, then the correct value appearing in sUniqueCounts. It almost seems as if I have to toggle between Layout and Browse modes to update the sUniqueCounts field.

• ###### 9. Re: Help with Unique Value script

Are you using repeating fields? That's what this setting refers to and it shouldn't make any difference to the results produced unless this is the case and you haven't mentioned yet that you are using repeating fields.

If you are using a mac system, TsGal reported in a previous thread that she needed to switch back and forth between modes to get the summary values to update properly in Filemaker. This is something that you should be able to build into a script if you need to do it.

• ###### 10. Re: Help with Unique Value script

I don't think I'm using repeated fields. Thank you again for all your help.