How to preform a find by comparing 2 fields

[Cabinetman]

I should know this but.....

In Scripting...Preform find and the Edit Find request... I want to find all records where the field 'Packages::value' is less than the field 'Prefs::dc_value

I have 'Packages::value' in the field section and '< Prefs::dc_value' in the criteria section

The file has 2 tables - Packages and Prefs

'Packages::value' is unique to each record and is a number

'Prefs::dc_value' is a number also and I modify regularly

Basically I want to find all items under $.99.... or maybe $.75 so this amount needs to be easily changed without going into scripting every time.

I have no problem finding them if I put '< .99' in the criteria section but that makes it to much trouble... I want it to look at the field.

[mr_vodka]

Use a global field to enter in the criteria that you want. Then you can refer to that global field in your script.

[Cabinetman]

First, Prefs::dc_value is Global.

I am still far from being a master of FileMaker but have been using it since V.3 and have done lots and of scripts/searches for myself but am drawing a blank here. In the 'Criteria' section can I not make it a sort of calculation.. Packages::value < Prefs::dc_value ? Is something wrong in the syntax?

I could create a calc field..da.da.da.da..da but I think there's a simplier way.

....Going on 5 hrs sleep since 6 AM Wednesday .

Thanks for the reply though

[AudioFreak]

How about in your find you use

Enter Find Mode

Set Field[Packages::value "<" & Prefs::dc_value]

Perform Find

[Reed]

you don't specify find criteria in your scriptmaker script step. You just enter find mode and use set field to move the values from your global fields (passed to the script as script parameters) into the search fields. Then you perform the find.

[Cabinetman]

UGH Thanks AudioFreak and Reed! Not enough sleep... I knew that but couldn't see the forest for the idiot in the mirror.....lol

[cbum]

If I may add to the discussion,

the above suggestions does not seem to apply if the fields to be compared are not global, but instead both regular fields on a layout.

Say I want to find all records where the value or the date of one field is higher or lower than another field in the same record?

Somehow it feels like I'm missing the obvious...

[comment]

There are never "fields to be compared". It is always the field to be searched and some constant. The global field is only a way to enter the constant into the field to be searched in Find mode. This is because the search is done in the index (at least whenever possible). No calculations as such are performed during search.

To enable your find, you will need to add a calculation field =

one field > another field

then search for 1 or 0 in this field.

[cbum]

I was afraid of that. There are a number of these comparisons I need, so I will need a "utility" layout just for them...

Thanks anyway.