1 2 Previous Next 15 Replies Latest reply on Apr 1, 2014 8:52 AM by dutchpete

# calculation to find a number in an range

Hello,

i have 2 tables client and arrangements.

on the client layout there is a dropdown field with age 18,19, 20 etc.

In the arrangements table there are 2 fields "agefrom" and "ageto" , an arrangement is based on a age range 18 to 27, 27 to 35, 35 to 50, 50 to 65

the dropdown age will trigger a script and puts the age in a variable \$\$age.

now i want to perform a find what finds all records where \$\$age is a part of the above range or not.

i tried all possibilities with find mode in script , but its not working, can some please give me a hint where to start.

thanks

very much

• ###### 1. Re: calculation to find a number in an range

thanks,

i tried that , but its not working

it must be something like :

Set Variable [ \$age ;clients::age]

Go to Layout [ arrangements ( arrangements ) ]

Enter Find Mode

Set Field [ \$age ; Arrangements::gAgeFrom & ".." & Arrangements::gAgeTo ] === formula is not right !!!

Set Error Capture [ On ]

Perform Find

Peter

• ###### 2. Re: calculation to find a number in an range

Set Variable [ \$age ;clients::age]

Go to Layout [ arrangements ( arrangements ) ]

Enter Find Mode

//Set Field [ \$age ; Arrangements::gAgeFrom & ".." & Arrangements::gAgeTo ] === formula is not right !!!

Set Field [ Arrangements::agefrom"=>\$age" ]

Set Field[ Arrangements::ageto "=<\$age" ]

Set Error Capture [ On ]

Perform Find

Assuming you are in the arrangements table.  I think what you are wanting it to do a search on both fields.  Find all that are equalto or greater than agefrom but also equal to or less than age to.

Alternatly and possibly better you just have a relationship based on this age field

client::age>=arrangements::agefrom

clinet::age<=arrangements::ageto

Then use the go to related records script step.

• ###### 3. Re: calculation to find a number in an range

Jared Hague wrote:

Assuming you are in the arrangements table.  I think what you are wanting it to do a search on both fields.  Find all that are equalto or greater than agefrom but also equal to or less than age to.

this is exactly what i need, the age from the client layout must fall between agefrom and ageto and find the records with arrangements for people who are in this agegroup.

but the formula gives no results , i need the script and not the relation option because the script is part of a multiple search with all kind of fields.

Is it possible that i need to put something in the perform find.

also i can not put qoutes before the => sign only after.

thanks

Peter

• ###### 4. Re: calculation to find a number in an range

sorry I had the equal sign on the wrong side it should be <= or >=. I made a quick sample for your reference.

https://www.dropbox.com/s/nz98po9phis6y9c/search%20number%20range.fmp12

• ###### 5. Re: calculation to find a number in an range

is there a difference between the your signs and ≥ or ≤

peter

• ###### 6. Re: calculation to find a number in an range

another question are the fields number or text fields ?

• ###### 7. Re: calculation to find a number in an range

nope they are same

and

yes number fields

• ###### 8. Re: calculation to find a number in an range

thanks a lot , but its still not working, in your example you use numbers, when i use a variable it can not find records, it puts the variable text \$age itself in the search field

• ###### 9. Re: calculation to find a number in an range

Set Field [client::ageto; "" & \$age]

calculation should look like this

"" & \$age

you need to do it like this.  I updated the file

• ###### 10. Re: calculation to find a number in an range

thanks for all your help, i understand now, in your example its working like it should, but in my own file its not, i will wreck my brain further tomorrow, its kinda late over here.

thanks again

peter

• ###### 11. Re: calculation to find a number in an range

I tried iT. In your sample the Seach is in the Same table , could iT be because i,m searching in a related table That its not working, Shouf i use gtrr ?

--

Peter

Verstuurd met Sparrow (http://www.sparrowmailapp.com/?sig)

Op dinsdag 1 april 2014, om 01:23 heeft Jared Hague het volgende geschreven:

created by Jared Hague (https://fmdev.filemaker.com/people/JaredHague) in Performing Finds - View the full discussion (https://fmdev.filemaker.com/message/143521#143521

Set Field [client::ageto; "≥" & \$age]

calculation should look like this

"≥" & \$age

you need to do it like this.  I updated the file

Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network (https://fmdev.filemaker.com/message/143521#143521)

Start a new discussion in Performing Finds by email (mailto:discussions-community-discuss-finds@filemaker-inc.hosted.jivesoftware.com) or at FileMaker Technical Network (https://fmdev.filemaker.com/choose-container!input.jspa?contentType=1&containerType=14&container=2031)

FileMaker Developer Conference 2014 • San Antonio, Texas • July 28-31 • www.filemaker.com/devcon

• ###### 12. Re: calculation to find a number in an range

dutchpete wrote:

I tried iT. In your sample the Seach is in the Same table , could iT be because i,m searching in a related table That its not working, Shouf i use gtrr ?

'iT' is really not that difficult …

also, if you already have an appropriate relationship in place, you can do just a GTRR without additional searching.

• ###### 13. Re: calculation to find a number in an range

erolst,

thanks very much for your example, it explains a lot.

Its working with gtrr, and finds the records i need , just one more question , when i want to make more searches in arrangements with more criteria , can i use the same method , because the arrangements must also be filtered on a lot of other fields like , hours a week, kind of contract, disabled or not etc.

Again thanks a lot for all your help.

peter

• ###### 14. Re: calculation to find a number in an range

dutchpete wrote:

Its working with gtrr

Actually, it doesn't – it uses a search. But you could of course also use a GTRR to the record showing in the portal.

As to your other question: sure, you can use Client record data and/or ad hoc search criteria from global fields to perform a search in Arrangements on multiple criteria – or you could use them as relationship predicates, as long as the data in Arrangements is stored.

But it's difficult to recommend an optimal approach if we don't know your table structure, or why you are searching – for display purposes, or to edit the existing data? Then a portal may very well do. To create a report? Then you need a search, because the report would be generated from an Arrangements layout.

1 2 Previous Next