Jump to content

Scripted find with checkbox set


Adam Abrams
 Share

This topic is 4153 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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"...

  • Like 1
Link to comment
Share on other sites

  • 2 months later...

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

Link to comment
Share on other sites

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....!

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

  • 2 months later...

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:

Link to comment
Share on other sites

This topic is 4153 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.