Counting # of times checkbox items appear in table

[-Queue-]

Here is a technique to summarize all occurrences of checkbox items throughout a table. It uses a global calculation with the Evaluate function so that it updates when any items are selected or deselected. A simple refresh script is necessary, as is often unfortunately the case when using value list calculations. But it is quite easy, compared to how nearly impossible it was in previous versions.

SummaryCount.zip

[transpower]

Neat, -Queue-. For my implementation, I made the options field an auto-entry calculation, with this:

options & S4HU_EventScript("SummaryCount.fp7" ; "Refresh" ; "" )

and added "Halt Script" to the end of the Refresh script. This way, the Refresh button isn't necessary!

[-Queue-]

Yes, well, if you are using a plugin, then of course it isn't necessary.

[Samdj]

thank you so much!! I was looking for a way to do that!!

[Nicnacs]

Cool script. Haven't quite worked out how it works yet, but have managed to implement it in my system. Thing is, I want to do the same several times to different fields. I've managed to do it to count up all the different ways people have got in touch with us, now I want to do it to count up all the different topics of question they've asked us.

I duplicated everything, changed the relevant fields but it isn't working. Isn't coming up with the list of options - TotalofX. And probably not doing other stuff either!

I've changed the R and O in the repOption field to make them unique, but that hasn't helped.

Any thoughts?

Thanks

[Nicnacs]

See, I duplicated the Serial field too so there's a serial field for the Source and a serial field for the Method (of getting in touch). But it's not working. The calculation Get(RecordID) - how does that work? Where is it getting the record ID from? I don't have a field defined as record ID - is that built into filemaker? I've always used the serial auto-enter way of doing things.

hmmm.....

[-Queue-]

The Serial field is based on FM's internal record id.

Could you post your file and specify what you want it to do?

[Nicnacs]

At the moment, the file counts the number of times a certain result appears as the Source for someone's enquiry. So maybe they heard about us through our website, or they're already members or whatever. In the monitoring area, you can specify the dates you want to monitor and then it tallys everything up. And the Source bit works.

Then I tried to extend it to tally up the Topic of each enquiry - volunteering, library info, etc. That's the bit that doesn't work. It's probably something obvious somewhere that I haven't changed, but I can't see it.

I've attached the file - Enquiries and also the Value lists it uses.

Enquiries.zip

[-Queue-]

Well, first of all, there is no value list called Topic, which you reference in Topic_list and TopicCount. Secondly, there is no need to create an additional ID, especially since you are using the same calculation as the original one--so the fields are the same. Just use EnquiryID again.

[Nicnacs]

See that's what happens if you don't quite understand how something works! Thanks for your help. I've managed to get things working now - although not 100% as it's not counting everything up, or maybe not refreshing properly. Not to worry, I'll work it out.

Thanks again

[Nicnacs]

Hello

Got it all working - just a question of comparing and contrasting one set of things that works with the ones that don't.

Now I'd like to export the info into excel and I'm getting some strange results. Using tab and comma separated text and no joy. Get funny square characters between each entry in the lists and count fields so excel won't sort things into the right columns.

Any thoughts or should I post in the forums?

Thanks

[-Queue-]

That would be the repetition delimiter. I think you would have to create new fields to concatenate the values into one field, using

repfield1[1] & ", " & repfield[2] & ", " & repfield[3] & ", " & ... & ", " & repfield[X]

replacing the comma with whatever delimiter you prefer to use.

[Nicnacs]

Hello again!

I'm using this method a lot for a particular database and have come across another problem. It seems that if you have a value list with similar words, they get counted twice. So for example, if you ask people for feedback at an event and their options are:

Very useful, Quite Useful, Useful, Not very Useful, Completely Useless

Then if you choose Very Useful, Useful also gets a score when you refresh. Now this one is quite easy to overcome cos you can just remove the word Useful and it still makes sense. But one of my other evaluation questions involves the answers:

Strongly Agree

Agree

Neither agree nor disagree

Disagree

Strongly disagree

and I just can't think of a way round that one! Any ideas?

Thanks

p.s. I've found that the refresh script strips the last record of any information so I've added a create record and delete record either side of the actual refreshing. THat seems to work!

[Nicnacs]

Hello again!

I'm using this method a lot for a particular database and have come across another problem. It seems that if you have a value list with similar words, they get counted twice. So for example, if you ask people for feedback at an event and their options are:

Very useful, Quite Useful, Useful, Not very Useful, Completely Useless

Then if you choose Very Useful, Useful also gets a score when you refresh. Now this one is quite easy to overcome cos you can just remove the word Useful and it still makes sense. But one of my other evaluation questions involves the answers:

Strongly Agree

Agree

Neither agree nor disagree

Disagree

Strongly disagree

and I just can't think of a way round that one! Any ideas?

Thanks

p.s. I've found that the refresh script strips the last record of any information so I've added a create record and delete record either side of the actual refreshing. THat seems to work!

[Nicnacs]

Hello again!

I'm using this method a lot for a particular database and have come across another problem. It seems that if you have a value list with similar words, they get counted twice. So for example, if you ask people for feedback at an event and their options are:

Very useful, Quite Useful, Useful, Not very Useful, Completely Useless

Then if you choose Very Useful, Useful also gets a score when you refresh. Now this one is quite easy to overcome cos you can just remove the word Useful and it still makes sense. But one of my other evaluation questions involves the answers:

Strongly Agree

Agree

Neither agree nor disagree

Disagree

Strongly disagree

and I just can't think of a way round that one! Any ideas?

Thanks

p.s. I've found that the refresh script strips the last record of any information so I've added a create record and delete record either side of the actual refreshing. THat seems to work!

[-Queue-]

In the case where value list items may be similar, you'll need to make SumCount equal to

Evaluate(

"PatternCount(

[-Queue-]

In the case where value list items may be similar, you'll need to make SumCount equal to

Evaluate(

"PatternCount(

[-Queue-]

In the case where value list items may be similar, you'll need to make SumCount equal to

Evaluate(

"PatternCount(

SummaryCount.zip

[Nicnacs]

Thanks I'll try changing the sum count as you suggest. I just wish I understood more how it worked so I could trouble-shoot myself!

[Nicnacs]

Thanks I'll try changing the sum count as you suggest. I just wish I understood more how it worked so I could trouble-shoot myself!

[Nicnacs]

Thanks I'll try changing the sum count as you suggest. I just wish I understood more how it worked so I could trouble-shoot myself!

[Nicnacs]

Pants it's not working at all now! Did you change anything else or just the Count bit? Hmmm....can't think what it might be. As I understand it, you define a value list (for example Gender). Then repGender is used to create AllGender - another value list.

Gender_list creates a list of possibilities (Female, Male and Transgendered) and GenderCount counts how many times each of the possibilities is chosen.

The script refreshes the window each time.

I get that much I think, but I've no idea how each individual calculation works. Sorry, can't figure it out.

Thanks

[Nicnacs]

Pants it's not working at all now! Did you change anything else or just the Count bit? Hmmm....can't think what it might be. As I understand it, you define a value list (for example Gender). Then repGender is used to create AllGender - another value list.

Gender_list creates a list of possibilities (Female, Male and Transgendered) and GenderCount counts how many times each of the possibilities is chosen.

The script refreshes the window each time.

I get that much I think, but I've no idea how each individual calculation works. Sorry, can't figure it out.

Thanks

[Nicnacs]

Pants it's not working at all now! Did you change anything else or just the Count bit? Hmmm....can't think what it might be. As I understand it, you define a value list (for example Gender). Then repGender is used to create AllGender - another value list.

Gender_list creates a list of possibilities (Female, Male and Transgendered) and GenderCount counts how many times each of the possibilities is chosen.

The script refreshes the window each time.

I get that much I think, but I've no idea how each individual calculation works. Sorry, can't figure it out.

Thanks

[-Queue-]

Do you mean the attachment or your implementation is not working? I only changed the SumCount field to prevent partial matches. It may help to put the repOptions and SumCount fields on a layout and format them to display all repetitions. You might see then if there is a problem with your syntax or structure.

The individual calcs work as follows:

repOptions retrieves each selected value in the Options field in its own repetition and concatenates the value with an underscore and the serial number. In the last attachment, the first record's repOptions field equals

B_1

G_1

D_1

E_1

C_1

where each line is a single repetition. (The values of the repetitions are in the order in which the options were selected.) This field is then used for the AllOptions value list. The serial number suffix ensures that each value will not appear only once in the value list, but as many times as it appears in the table.

The SumCount field then determines how many times each item from Options appears in AllOptions. To ensure exact matches, the value is delimited by a carriage return and an underscore. So, if the value list contains AA_10, for example, another item of A will not return a match because PatternCount( "

[-Queue-]

Do you mean the attachment or your implementation is not working? I only changed the SumCount field to prevent partial matches. It may help to put the repOptions and SumCount fields on a layout and format them to display all repetitions. You might see then if there is a problem with your syntax or structure.

The individual calcs work as follows:

repOptions retrieves each selected value in the Options field in its own repetition and concatenates the value with an underscore and the serial number. In the last attachment, the first record's repOptions field equals

B_1

G_1

D_1

E_1

C_1

where each line is a single repetition. (The values of the repetitions are in the order in which the options were selected.) This field is then used for the AllOptions value list. The serial number suffix ensures that each value will not appear only once in the value list, but as many times as it appears in the table.

The SumCount field then determines how many times each item from Options appears in AllOptions. To ensure exact matches, the value is delimited by a carriage return and an underscore. So, if the value list contains AA_10, for example, another item of A will not return a match because PatternCount( "

[-Queue-]

Do you mean the attachment or your implementation is not working? I only changed the SumCount field to prevent partial matches. It may help to put the repOptions and SumCount fields on a layout and format them to display all repetitions. You might see then if there is a problem with your syntax or structure.

The individual calcs work as follows:

repOptions retrieves each selected value in the Options field in its own repetition and concatenates the value with an underscore and the serial number. In the last attachment, the first record's repOptions field equals

B_1

G_1

D_1

E_1

C_1

where each line is a single repetition. (The values of the repetitions are in the order in which the options were selected.) This field is then used for the AllOptions value list. The serial number suffix ensures that each value will not appear only once in the value list, but as many times as it appears in the table.

The SumCount field then determines how many times each item from Options appears in AllOptions. To ensure exact matches, the value is delimited by a carriage return and an underscore. So, if the value list contains AA_10, for example, another item of A will not return a match because PatternCount( "

[Nicnacs]

Thanks for the explanation. I think I need to sit down with a cup of tea and a clear hour or so to work it out completely - it's interesting though.

I must have done something slightly wrong I think - I meant my implementation wasn't working. Thanks for offering to take a look. I've attached it.

[Nicnacs]

Thanks for the explanation. I think I need to sit down with a cup of tea and a clear hour or so to work it out completely - it's interesting though.

I must have done something slightly wrong I think - I meant my implementation wasn't working. Thanks for offering to take a look. I've attached it.

[Nicnacs]

Thanks for the explanation. I think I need to sit down with a cup of tea and a clear hour or so to work it out completely - it's interesting though.

I must have done something slightly wrong I think - I meant my implementation wasn't working. Thanks for offering to take a look. I've attached it.

Monitoring.zip

[Nicnacs]

Cool - I worked it out. Made a mistake when changing Options and AllOptions to the names of my fields.

Now I'm having problems making it count just a limited number of records. So for example, I'm recording the enquiries we receive. If I want to monitor how many have been taken by email in January, I perform a find to find just those questions taken in January. Unfortunately it seems to count all the questions, not just those from the found set.

I shall have to ponder some more....

[Nicnacs]

Nope still can't figure it out. Can't work out how to just make it count a sub-set of your results. Anyone got any ideas?

Thanks

Nicnacs