Counting Values Across Several Fields

[Greg G]

I have 8 text fields in each record and need to count how many times each value shows up in any of these fields across all records. The same value can occur in any of the 8 fields in any record.

Any ideas?

Thanks,

Greg

[Ender]

You could do:

ValuesInRecord (calculation, number result) =

patterncount(field1; "value") +

patterncount(field2; "value") +

patterncount(field3; "value") +

patterncount(field4; "value") +

patterncount(field5; "value") +

patterncount(field6; "value") +

patterncount(field7; "value") +

patterncount(field8; "value")

Then use a summary Sum of ValuesInRecord to get a total across the found set.

But you should recognize that the implied structure (that allows a value to be placed in any of n fields) could probably be done more efficiently using a relational setup, with a portal of values. You can then check for the presence of a specific value by using a second relationship filtered by the value, or get an aggregate Sum() of how many such values exist.

[Greg G]

Will this work eventhough I do not know in advance what the "value" is?

I see your point about the structure and I am open to suggestions. The reason I started going in this direction is due to how that data is created. The data that is in each of the 8 fields is extraced via a calc (MiddleWords) from one field that the user accesses. The user enters up to 8 values in this one field seperated by something such as the plus sign. This is done for two reasons. First, this use of one field is an industry accepted method of notation. Users are expecting to see one field with all of the data when they enter it and when it prints on reports. Finally, there is frankly no room on the layouts to have 8 fields showing for this data.

Basically I need to find all of the unique values in this one field across all of the records and then count how many times each unique value occurs across all records.

Knowing this, if you have any suggestions please let me know.

Thanks again,

Greg

Edited April 5, 2007 by Guest

[Ender]

Sorry, you lost me. Maybe you can provide example data and then explain what you want to do with it.

[Raybaudi]

Hi Greg

but how many different value can you have ?

8, more, indefinited ?

[Greg G]

I have a field called Code. The user can enter up to 8 codes in this field. A single code looks like: "R2B4". The field data looks something like "R2R5 + R11A + L1E5" etc. I need to be able to create a report that counts how many times a code is used in this field across all of the records. The actual code values are infinite so there is no way to have a list of possible codes in advance.

Hope this helps. Thanks

Greg

[Greg G]

I started to try and solve my problem by parsing out the data in this Code field. I created 8 calc fields (Code 1, Code 2, etc.) using the MiddleWords function to pull out the 8 possible text strings and get them each into a seperate field. This works great up to this point. I know can not figure out how to count the number of times each code value shows up in any of these 8 fields across all of the records.

Greg

[Ender]

If you parse out the code values into separate related records, rather than separate fields, you would then be able to use a simple sub-summary report (in a layout based in the related table) to show all the codes with a count of how many times each code appeared across all records.

[Greg G]

I had a feeling you were going to say that. How do you suggest I automatically create the right number of linked records in this new table. I am not quite sure how to make this a background function that just happens without the user being involved.

[Ender]

I would make it a function that happens when the report is run. You could loop through the records to create the related code records.

Another option that could be more efficient is to tightly control the data-entry of that text field on the record so that the related code set can be rebuilt when the user makes changes. There are different ways to do this; it just kind of depends on how you like the processes to work.

[Raybaudi]

This is a way ...

CountingValues.zip

[comment]

I would make it a function that happens when the report is run.

That seems most sensible. But instead of looping...

Parse_Report.fp7.zip

[Ender]

Hey, great use of a repeating field! I'm glad we keep you around. :(

[Raybaudi]

Beautiful :(

[Søren Dyhr]

Hey, great use of a repeating field! I'm glad we keep you around

Indeed, this is the propper way... use repeaters to normalize, instead of what infortunately is stumbling near denormalization!

I have 8 text fields in each record and need to count how many times each value shows up in any of these fields across all records.

This is an obvious violation of 1 NF's "one fact per field". I wondered if this purpose here are those bagage tags for flight travels??

http://en.wikipedia.org/wiki/First_normal_form

--sd