How to count accross >100 fields

[IanJ]

I want to count a specified number of fields that are non-blank on a record by record basis but there are upwards of 100 fields I want to count and I dont want to enter them all in a count calculation (they have arcane names and periodically new names are being added). In this case they are the names of photosynthetic pigments. Because these and only these fields are prefaced with "Z-" I thought I might be able to use something like the following where "CCLM-Pigments is the File name, "Major pigments" is the layout name, "Z_" is the prefix and "*" is wildcard for rest of pigment name.

Count ( Case ( FieldNames ( "CCLM_Pigments"; "Major pigments") ) = "Z_"* )

or variations incl

Count ( Case ( FieldNames ( "CCLM_Pigments"; "Major pigments")="Z_*")))

I mostly get a "too few parameters error" but while the syntax may be messy it may also be an inappropriate use of Case or Fieldnames functions but I'm not sure where else to head. I may be trying to overcomplicate something that is blindingly obvious to others. >>>I hope so then I might get a quick reply.

Thanks

[Vaughan]

This indicates a problem with the data structure. Instead of multiple fields, a related table and multiple records should have been used.

Rather than trying to work around the problem with hundreds of hard-coded calculations I recommend rebuilding the database with the correct structure.

[Matthew F]

In a calculation, the * character stands for multiplication. It is not a wildcard character, like it is in Find mode. If you put "*" inside the quotes then it will be interpreted as part of the text string, but still not as a wildcard character. If this evaluation were being done in a script you could use a loop to evaluate all of the field names and use expression 'left(nextField, 2) = "Z_' to test whether the first two characters of each field started with 'Z_'.

As an aside, when you state that you have well over a hundred fields on your layout and that you are adding new ones on a regular basis, I can't help but think that you may not have the best database design. Ordinarily I would try to have users add new records, not new fields. This could then be sorted or filtered through relationships and portals. I guess the new Table format in FM Pro makes it a snap for users to add new fields, but there are better facilities for counting records than there are counting fields.

[David Jondreau]

Put the fields you want to count on their own layout.

ValueCount(

Evaluate(

"List(" &

Substitute(

FieldNames( Get(FileName);Get(LayoutName)); ¶; ";")

& ")"

)

)

Edited October 4, 2010 by Guest