I have created a database to manage the distribution of a large personal library to family members who have expressed an interest in various items. I have one field titled "status" in which I have a value list formatted as checkboxes. This value list is in the form of the initials of people being contacted.

What I want to quickly do is find just the items in which two or more people have expressed an interest in the same item; i.e., where I have checked two or more checkboxes in the status field.

Any ideas? I am somewhat rusty on my FM skills but thought this would be simpler than it is turning out to be. Thanks for any help out there.

Bruce Williamson

Simply create this calc field:

WordCount ( check )

It helps to indicate your versions, Bruce, in your profile or on each post. If you are using prior to vs. 7, you can use:

PatternCount(check , "¶" ) + 1 to count how many are checked.

[color:red]NOTE: Many of the calcs listed in the following posts have inadvertantly had their ¶ replaced with ?. I corrected them all.

Edited December 20, 200520 yr by Guest
Added NOTE

LaRetta'calc is better than mine if some item of the list contains more than one word...

otherwise my calc is good also for FM6 (but you have to subtract 1)! So it becames:

WordCount ( check )-1

HAH! When I first read Bruce's post, I 'thought' ValueCount() and so that's what I 'saw' when I read your calc, Daniele!!

When I just read your response that it worked in 6, I had to look again (because ValueCount won't work in 6)! If vs. 7, ValueCount() protects from multiple words also (ie, only counts the number of multiline entries).

Very good, LaRetta...

I learned something

So, for 7 ValueCount(check)

for 5 : PatternCount(check , "¶" ) + 1

Edited December 20, 200520 yr by Guest
Revised ? to ¶ (LaRetta)

due to an error when no check is cheched, the calc for FM5 is:

Case(

Length( check);PatternCount( check; "¶" ) + 1;

0

)

Edited December 20, 200520 yr by Guest
Revised ? to ¶ (LaRetta)

This is an intriguing change between versions, Daniele.

Because in vs. 7, PatternCount(check; ¶) + 1 produces BLANK when the field is empty if you have selected 'Do not evaluate if all Referenced Fields are Empty' which is the default. I suppose I should have specifically mentioned it should be checked! BUT ...

Upon testing in vs. 6, same calc - PatternCount(text, "¶") + 1 produces 1 even with 'Do Not Evaluate' checked!! Both are number. Both have 'Do Not Evaluate if all Referenced Fields are Empty' checked! Both are unstored calcs. WOW.

Well 7 works the ways it's supposed to. If 'Do not Evaluate' is checked, it doesn't evaluate. But if unchecked it still evaluates. Why vs. 6 evaluates regardless is beyond me.

I believe this would be due to the calc being unstored, which forces it to evaluate even when the referenced field is empty. There is no need for it to be unstored in this case IMO, but if there were, I would use Case( not IsEmpty(field), PatternCount( field, "¶" ) + 1 ).

Edited December 20, 200520 yr by Guest
Revised ? to ¶ (LaRetta)

Hi JT

My purpose for unstored was to test. What I observed is that vs. 6 and vs. 7 handle this identical calculation differently. I thought that was significant as I recall nothing about this type of change between versions. And are you suggesting that an unstored calculation, even if "Do Not Evaluate" is checked, will STILL evaluate if the field referenced is empty? I'd like to understand this distinction. I've been caught on this before ... I want to be clear on when (and when not) a calculation will use this checkbox.

I've been using it and ignoring the IsEmpty() test because it shouldn't be necessary if this box is checked. Do I have it wrong still?

LaRetta

I am not positive, but that just seems to be the way PC functions in 6.

If you do some testing with Position, you will find something even more interesting. When testing with a numeric parameter, it will always return a result; when testing with a text parameter, it will only return a result when the field is not null, even if it is unstored (at least this is how it works in 5.5). Try Position( textfield, 2, 0, 1 ) versus Position( textfield, "2", 0, 1 ).

WOW.

I considered the fact that in 6, we must enclose the CR in "" and that might make a difference but it didn't. They certainly do evaluate differently AGAIN ... yes!

vs 6 first calc produces: 0

vs 6 second calc produces null

vs 7 both calcs produce null

This is with checked 'Do Not Evaluate.' Unchecked all these calcs behave the same. Oh. At least 7 appears to be more logical ... not evaluating anything when any field referenced is empty. But I must study this further! I wonder if people have had conversion problems with this, ummm, change. I wish I could figure out FM's 'thinking' on this one. Maybe it would be something like, "Yeah. We realized it didn't work right in 6 so we fixed it."

Why else would they change an evaluation pattern when so many people have calculations depending upon it? Or maybe the short-circuiting type 'behavior' looks at the inside of the () first - calcs do that, right? And it stops the moment it hits the first empty field. That's what a true 'Do Not Evaluate' should do. I'm not suggesting it short-circuits ... only that it's smarter in it's approach, maybe. Now I'm really speculating and will instead get to work on it.

Thanks JT!!

LaRetta