Searching between variable (really stupid question

[Nicholas]

Hi there,

I am very very new to FMP7 (about 2 days) so this is going to seem like a silly question but please help me.

I am trying to create a database for a casting agency, and part of the actors profile is to input an age range that they can play (ie 20-30). So there is a minimum and a maximum.

How do i create a find command that will look for people say 'suitable to play a 25 year old'?

And also (prepare for the really stupid question), how would I make a search command that will find people who are higher or lover than a standard (ie search for people who are over 30 years of age)

If anyone can help me it would be much appreciated as i am really struggling through this right now.

Cheers

Nicholas

[QuinTech]

Hi, Nicholas. If Age is a number field, then the find request

20...30

will find everyone with an age between 20 and 30 (inclusive). Enter it just like that in the find request. And the requests

>30

>=30

<30

<=30

find records where the age is greater than 30, greater than or equal to 30, less than 30, and less than or equal to 30, respectively.

If you don't have an Age field, but do have a Birthday field, search this forum for "+birthdate +age" (without the quotes). There was recently a thorough discussion of how to calculate age from a birthday.

HTH,

Jerry

[Nicholas]

Thanks for your answer Jerry, they have helped me out with creating the basic finds. My major drama though is that if the profile has two ages, (not their actual age but a high age and a low age that they would be capable of playing) how can I create a search between these.

For example, lets say Joe is 32 years old but he would be able to be cast for a character that is between the ages of 22 and 35 (people dont always look their age). And Ben is actually 21, but would be able to play any roles between 20 and 26. Now i need to search for people that would be suitable to play a 25 year old. Both Ben and Joe are suitable! Disregarding their actual age field, I need to search between the filds 'low age' and 'high age', which are entered manually by the casting director.

I feel confused just writing this.

Regards

Nicholas

[stanley]

Nicholas:

You would have two fields to enter the age range in, such as Low_Age and High_Age, so in your example, for Ben you'd put in 20 in Low_Age and 26 in High_Age, indicating the range of ages he'd like to play. For the casting director, have another field called Age_Search, where they'd put in the age they're looking for. Have a button (called "Search for this age" or whatever) which triggers the following script:

Enter Find Mode []

Set Field [db::Low_Age; "<" & db::Age_Search]

Set Field [db::High_Age; ">" & db::Age_Search]

Perform Find []

This will find any entries where the Low Age is below your number, AND the High Age is above it. In other words, it finds who has a range which encompasses the number you're looking for.

-Stanley

[stanley]

Nicholas:

One further thing. In the example I gave you, Low_Age and High_Age are number fields, and Age_Search is a number field set as a global (in the define fields dialog, click on "options" and then, under "storage" set it to global storage.)

-Stanley

[QuinTech]

Ack! That is confusing. There's an easy solution, though, i think. Take a look at the attached example for an idea of where to start.

J

ages.zip

[stanley]

J:

There's a problem with how your example calculates the possible ages - it is skewed up by one (so the person who's age range is 2-4 comes up when you search for 3-5).

Anyway, I don't think my example is confusing; it's a pretty straightforward find method.

-Stanley

[-Queue-]

Wouldn't you want to make that "<=" and ">=" so that it doesn't omit those whose max or min ages equal the requested age? This should be easy enough to set up a range relationship in FM7 also.

[stanley]

Queue:

You're right. It should be "<=" and ">=" rather than "<" and ">".

-Stanley

[Nicholas]

Stanley,

Everytime i try to create this script FM7 tells me that i need to have a 'calculation' included (ie + - etc).

Eeeekkk

What do i do

(Told you I'm new to it)

Nick

[stanley]

Nicholas:

I'll assume that this occurs when you're doing the Set Field[] step. What you're missing is the ampersand ("&"), so FMP doesn't understand your entry. Type in exactly what's on the next line:

"<=" &

Or, obviously the same thing with the greater-than symbol instead. Then select the field Low_Age or High_Age, depending on where you are.

I believe you were doing all of this, except the ampersand. Let me know if this works.

-Stanley

[Nicholas]

Stanley,

No, Its still not working. I typed in exactly what you had on the first post (substituting with my fields), but it would not let me save the calculation because i was missing an operational "+ - * ...".

Also, would you be able to tell me (and I'm sure this should be an easy one), how can you create a searchable field that searches across many different fields that can carry the same value. Part of the database includes the clients skill (Musical Instruments) for example. There is a pop-up menu which has a list of instuments on it (piano, guitar, flute etc etc). Then i have another field under that with the same pop up menu, and then another one under that again and so forth. The person may be able to play 5 seperate instruments. Each of these menues are a different field with the same set of values (so that when you pick a value in one it doesnt change the others to the same)

The problem here is that if you want to search for someone that can play piano, how can you create a single field that will search all the related fields, because if you want to search for Piano using the standard find command, it has to be entered in exactly the same field and not in another one that has the same values (Does this make sense).

Ideally i want to have a search page with just one of each of the skill flyout menues, which will search all the related skill fields in the original database.

Please help me - i am so out of my depth

Nicholas

[stanley]

Nicholas:

Post your file here (attach it to your post) and I'll look at it & see what the problem is, and I'll put in an example of the search function too & repost it for you. I'm at a client today, so that won't happen until the evening.

-Stanley

[Barbecue]

It sounds like you have a typo somewhere in your definition. Instead of typing in the values, try using the field list and buttons to create the calculation and see if that helps.

With regard to searching multiple fields, you could do that by creating multiple find requests, BUT... there's a much better way to handle this. Instead of trying to keep track of instruments in multiple fields, just use a single field to hold all the instruments. Create a single field called Instruments, and instead of formatting it as a pop-up menu, use the checkboxes option. Then just check the box for each instrument the person can play. When you need to find all of your Krumhorn players, go to find mode, check the Krumhorn box, and click FIND. Presto, there's a list of Krumhorn players. If you want everyone who plays both Krumhorn and Glockenspiel, check both boxes, your find will return everyone who plays both.

[QuinTech]

Stanley: my "confusing" comment wasn't directed at you, it was at the post prior to your first. I just took a long time typing it out, so it appeared i was responding to you. Sorry for the confusion.

As for the error, i accidentally posted a version with the wrong calc. If you go into the field Actors::possibleAges and change the line "lowAge * 3 + 1" to "lowAge * 3 - 2", that should work better.

J

[-Queue-]

My guess is that Nicholas is putting both the target field and calculation into the calculation portion of the Set Field. The portion before the semicolon is the target field to select; the portion after the semicolon is the calculation to enter.

[Nicholas]

Hi Stanley,

Attatched is a work in progress. Things will seem as if they are all over the place (and they are, this is all new to me).

On the attributes tab up the top next to the find command, I have posted another temporary batton called 'Advanced Search'. Ideally this will replace the find command. It loads another page on which i wish to have a set of specific search criteria. The casting director will fill out all aplicable fields and then click one major search button which will bring up the results in the list mode.

I know the database is very messy, and i still have a lot of work to go, and I've probably done a few things the wrong way, but its all one big learning curve.

Thanks to Queues advice, I managed to create the script for 'agesearch', but i cant enter a value into it in find mode. I also want to have just one search button which searches that field along with all the others on the page, not a seperate button for each search. I only want the find page to be accesible in find mode, so it doesnt display any data (so its not accesible at all in browse mode)

Finally, you will see what i mean by having a number of fileds that carry the same values, and how i want to search these through the advanced page.

If you can help me out at all it would be much appreciated, all i'm feeling right now is frustration at my incompetence.

Thanks

Nicholas

[email protected]

castingagency.zip

[stanley]

Nicholas:

The search function works fine - it is completely button driven (so you don't enter find mode first; just enter an age, then push the button.) I have added error correction to your script, and made it clear your global after you're done, so the field doesn't always show a number.

I've added a new script and a new field and button for your full search. This is probably the better way to go, and you can trash what I told you earlier. Now the search field is a normal field (not global) so you can enter it in find mode. The script transfers the specified symbology into the correct fields, then clears the search field itself. Hope that makes sense.

So, how you use it is to click the "click first" button, then you can put a number into the box on its right as the age you're searching for, and you can also fill in stuff in the other fields at the same time. Hit enter when you're done, and off you go.

-Stanley

[Nicholas]

Stanley,

Its not letting me download your attatchment and is loading an error page. Can you please attatch it again or alternatively email it to me.

[email protected]

Thanks

[stanley]

Nicholas:

I'll email it to you directly, then I'm going to bed.

-Stanley