4 Replies Latest reply on Mar 11, 2010 6:38 AM by jenh

# How to count records in a found set based on mulitple fields that have one of two values.

### Title

How to count records in a found set based on mulitple fields that have one of two values.

### Post

Hi.

I'd like to count records in a found set that have one of two values within 16 fields.

That is how many records that DONT have field1...field16 that hold values of "NO ERROR" or "----".

I'm currently trying to do the following calculation field

Let (
[
\$count = 0;
\$count = \$count  + If (_ERROR_      ≠ "\"NO ERROR\""; 1 ;0) and
\$count = \$count  + If (_ERROR_     ≠ "\"----\""; 1 ;0);
\$count = \$count  + If (_ERROR_2_ ≠ "\"NO ERROR\""; 1 ;0) and
\$count = \$count  + If (_ERROR_2_ ≠ "\"----\""; 1 ;0);
\$count = \$count  + If (_ERROR_3_ ≠ "\"NO ERROR\""; 1 ;0) and ........ etc

then count it in a summary field.   The above just counts every record.

Any suggestions? TY

• ###### 1. Re: How to count records in a found set based on mulitple fields that have one of two values.

Not sure what your question is. But I will make two suggestions:

1. Review your structure: having 16 fields of the same type usually indicates a design flaw; those fields should probably be records in a related table.

2. Do not use \$variables in a calculation field.

• ###### 2. Re: How to count records in a found set based on mulitple fields that have one of two values.

Thank you for the response.

These particular  16 fields in each record could have 14 or more separate values.

(The 16 fields need to be reported in sequence within each record)

I just want to count the number records that do NOT have "NO ERROR" or "----"  in all of the 16 fields within a found set.

Any reference or suggestion is appreciated.

• ###### 3. Re: How to count records in a found set based on mulitple fields that have one of two values.

Well, you could try something like =

Let (
all = List ( ERROR_1 ; ERROR_2 ; ERROR_3 ; ... ERROR_16 )

;

not ValueCount ( FilterValues ( "NO ERROR¶---" ; all ) )

)

My suggestion in point 1 still stands.

• ###### 4. Re: How to count records in a found set based on mulitple fields that have one of two values.

Thank you.

I appreciate your suggestions and will use them.