Using a script to find

[bb84604]

I am trying to write a script that will find records based on a field. Right now I have a loop setup that checks each record and omits or passes it.

Can't this be done by putting a "find" in the script?

I just want to say find records where Field x = Field y but when I go to script the find, it looks like I can only make it find where Field x = something I manually type in.

Is there a way to do this?

[spb]

Leaving aside the idea of finding with a script for a bit, first you need to easily find records where Field x = Field y manually.

If this is something you need to do frequently, I'd suggest making a calculation field that does the comparing, call it FieldMatch. The calculation would be something like:

Case (Field x = Field y, 1, 0)

FieldMatch will contain a one if the two fields match, a zero if they don't. Your find is now a find on FieldMatch. Search for all records with a one in FieldMatch, and you have a set where Field x = Field y.

You could script this several ways. The easiest is to run the find manually first, then make the script. When you include the "Perform Find" script step, make sure the "restore" option is checked. It will memorize your manual find & run it every time the script is invoked.

Conversely, you could set it up manually in the script by including these steps;

Enter Find mode (**Restore UNchecked**)

Set Field (FieldMatch, 1)

Perform Find (**Restore UNchecked**)

By unchecking the two restore options, your scripot steps will find what the Set Field tells them to, not what was last done.

You could set error capture & conditionals (what do I want to happen if there are no matching x & y fields?), but this is a basic beginning.

Note: This is FM 6 stuff. No doubt there is a slicker way to do the comparative find in 7 that does not involve the calculated FieldMatch field. If you are searching a lot of records, tens of thousands or more, you might think about this. You could get a speed hit finding on a calc field.

Steve Brown

[-Queue-]

You can use Field x = Field y also. The Case is redundant here.

Note that if you perform a manual find for 1 and use the restore option with Perform Find, then the Enter Find Mode step is unnecessary in the script; a Perform Find [Restore] step will suffice.

[spb]

Queue:

I'm feeling thickheaded this afternoon. If we drop the Case and make the calc just Field x = Field y, then how do we arrive at a result of 1 or 0? The compare is the test, and the 1 or 0 are the result of the test, which is why I suggested a Case. I think I'm missing something obvious.

Regarding the memorized find, I said that. I gave the poster both options. I prefer a fully scripted find myself, as it is easier to edit or modify.

Steve Brown

[-Queue-]

The test itself returns either 1 or zero, which is why the Case works. All such tests are boolean and don't require If, Case, or Choose, unless you require a non-boolean result.

I realized you said it, but I wasn't sure if it was clear enough that only the Perform Find was necessary in that case.

[spb]

Thanks for that insight. I'd never really thought about it before. So if I set up a calc field with any test (Field A = 32 or Field A < 32), without using Case, then the field will populate itself with a one or zero? I presume the field has to be result = number. What happens if I do result = text?

As an unschooled self-taught FMer, I have many odd and obvious gaps in my learning. I never knew this and will henceforth apply it.

Steve Brown

[-Queue-]

It could be text or number. There isn't really a reason to make it text though, if it should only be populated with 1 or zero.

You can use this same idea to "de-Case" certain calcs. For example,

somedate + Case( DayOfWeek(somedate) = 2, 5 )

is equivalent to

somedate + 5 * (DayOfWeek(somedate) = 2)

Enclosing the test within parentheses forces FileMaker to treat it as a boolean. So if the test is true, a 1 is returned and multiplied by 5, thus 5 is added to somedate. If the test is false, a zero is returned and multiplied by 5, thus nothing is added to somedate.