Scripted find with checkbox set

[Adam Abrams]

Hi all! Well, here I am, at the end of my rope, again... but with the help of the experts here I'm sure I'll be shimmying back up.

I have a bunch of contacts, some of whom participated in a walkathon. One of the Contact table fields ("WalkerByYear") is a checkbox set, with the last five years listed. I want a script to find all the people who walked in the previous two years; thus, they will have either 2009 or 2010, or possibly both, checked.

This has turned out to be, shall we say, less simple than I expected.

I've realized that checkbox sets are indeed just regular text fields that get the various options strung together with carriage returns (when you view them as a regular text field and not a checkbox set).

So my challenge, I guess, is to say, "find me all contacts where the "WalkerByYear" field contains "2009" or "2010" ANYWHERE in it. But it's simply not working. I'm getting all kinds of wrong results.

I'm wondering if it can be stated in a single find, or if I have to do an Extend Found Set. Seems that shouldn't be necessary, but what do I know... not much as I continue to discover!

My current calculation for searching on the field is as follows:

Set Field [Contacts::WalkerByYear; GetAsText ( Year (Get(CurrentDate)) - 1 )]

Perform Find []

Set Field [Contacts::WalkerByYear; GetAsText ( Year (Get(CurrentDate)) - 2 )]

Extend Found Set []

But I think this is not the correct approach...

If someone can point me in the right direction that would be most very welcome...

Thanks!

adam

[Adam Abrams]

I solved it... turns out all I had to do was a find on my first value for the field in question, then a "New record/request" to add the second value.

Pretty basic stuff! Sorry to post unnecessarily like that...

Although I've managed to accomplish some fairly advanced stuff in FMP, I clearly am not fully dialled in on some of the basics! Time to do a little refresher on "Find 101"...

[Adam Abrams]

Hi again! Well, after a hiatus I am back on this FMP project again. Maybe it's my time away, but I seem to be having a problem with this same checkbox set and basing finds upon it. I also guess I am having difficulty understanding some of the behaviour of checkbox sets.

Trying to maintain "best practices", I am trying to simply do a find that gets me the result I want, before trying to duplicate that in my script. But I'm having trouble getting that to work.

What i now want to do is bring up only the people who participated in the 2011 walk - they have the "2011" checkbox checked.

However, when I do a find by checking that "2011" box and hitting "find", a whole whack of contacts who do NOT have the 2011 checkbox checked come up.

I was utterly baffled until I changed the checkbox set to instead display as a simple edit box. Then I saw "droppings" of data in the text box - like "200920102011" - even though when I switch it back to displaying as a checkbox set, all my checkboxes for the various years are not checked!

Is this old data from when a checkbox was perhaps checked, then unchecked, and which for some reason remains in there?

Am I perhaps approaching this particular field in a less than optimal way? Should I even be using a checkbox set? I don't know how else I would deal with this...

Anyway, any help is much appreciated.

Thanks!

Adam

[comment]

Is this old data from when a checkbox was perhaps checked, then unchecked, and which for some reason remains in there?

Not likely. Do you have any auto-enter options turned on for the field?

[Adam Abrams]

Do you have any auto-enter options turned on for the field?

Nope. Strictly a manual check-off... so I'm wondering under what circumstances a checkbox-set field could show ANY data when displayed as an edit box yet not show any of its checkboxes checked when displayed as checkboxes... utterly baffling... and really messing me up....!

[comment]

under what circumstances a checkbox-set field could show ANY data when displayed as an edit box yet not show any of its checkboxes checked when displayed as checkboxes.

Whenever the field contains a value that is not a member of the value list used for the checkboxes. For example, if your value list is: "A¶B¶C" and the field contains "D", nothing will be shown as checked. If the field contains "A¶D", the "A" checkbox will be checked - but that doesn't mean the field contains only "A".

[Adam Abrams]

Whenever the field contains a value that is not a member of the value list used for the checkboxes. For example, if you value list is: "A¶B¶C" and the field contains "D", nothing will be shown as checked. If the field contains "A¶D", the "A" checkbox will be checked - but that doesn't mean the field contains only "A".

Aha. The data in the field (at least the example I referred to) is formatted incorrectly as data which - individually - are checkbox options, but in the case in question, are all strung together as a single unit. Thus not "2009¶2010¶2011", but "200920102011". Why that happened is a mystery in itself, but it certainly does explain why it's not showing up in "checkbox view".

I guess I may have to do a search and replace to strip out this useless data...

[comment]

Why that happened is a mystery in itself

Did you at some point import data into the field? Perhaps there is an invisible character where a carriage return should be.

[FMPierce]

Adam, for your first question, you can do this,

Set Field [Contacts::WalkerByYear; GetAsText ( Year ( Get ( CurrentDate ) ) - 1 ) & "…" & GetAsText ( Year ( Get ( CurrentDate ) ) - 2 )]

Perform Find []

"…" is ellipsis which in find mode means "in range" so it would be "2009…2010" which is, "in range 2009 to 2010" which would result in 2009 and 2010.

Hope it helps

FMPierce

:ninja:

[bcooney]

Automatic message

This topic has been moved from "Database Schema & Business Logic → Custom Functions" to "Database Schema & Business Logic → Finding & Searching".