Searching for students by belt rank color

[Jim65]

Hi! Thanks for taking the time to try and help me. I have a script that uses a field named EmailFind. If I enter a karate belt color such as Black it finds all of the students that have a black belt and I can then send them an email. That works fine. However, if I wanted to find, say, all of the black belts and yellow belts I type in black...yellow and it finds every belt starting with a b through a y. I understand this is how Filemaker searches. Is there a way I can pick 1, 2 or more colors (the names for the colors) and only find those?

Any help is greatly appreciated.

Jim

[comment]

To find students that have a black belt OR a yellow belt, you should do:

Enter Find Mode []
Set Field [ YourTable::SomeField ; "black" ]
New Record/Request 
Set Field [ YourTable::SomeField ; "yellow" ]
Perform Find []

Similarly, if you have a list of belt colors to find, make your script loop over the list and create a separate find request for each color - see an example here:
https://fmforums.com/topic/99419-find-from-a-list/

--
P.S. Not sure why you keep the student's belt color (or anything, for that matter) in a field named "EmailFind".

 

 

Edited April 16, 2021 by comment

[Jim65]

comment,

I don't have the belt colors in EmailFind. That is a search field I use to find students to send emails to. I need to find everyone that has a, say black belt and the ones that have maybe an orange belt not either but both. Or yellow and brown or black and purple and yellow and so on. The script I have is:

If isempty (students:EmailFind)

Beep

Show Custom Dialog - Warning goes here

Go to field Students::EmailFind

Halt script

End If

Got to layout - students layout

set error capture on

Enter find mode

set field students::RankColor ; students:EmailFind

Perform Find

Send Mail Send via email client 

Yada, Yada.

This is a representation of the script. There are many typing errors but I think it covers the basics. It finds any individual color that I enter into EmailFind field and a range if I enter Black...Yellow or * for all but the range of Black...Yellow includes all of the belts due to the Filemaker's Find when I may only want black and yellow.

I hope this is a bit clearer. I tried the New Record Request but can't seem to make it work.

Thanks.

[comment]

2 minutes ago, Jim65 said:

I need to find everyone that has a, say black belt and the ones that have maybe an orange belt not either but both.

I am not sure I understand this fully. Do you have students that have both a black belt AND an orange belt?  If yes, what is the exact content of the RankColor field for such student?

Searching for a range makes no sense in this context, where the values have no meaningful order - neither numerical not alphabetical.

 

[Jim65]

No. I want to find students that have a black belt AND other students that have an orange belt or all the brown belts, all the purple belts and all the green belts. Then send this found set an email.

The RankColor is:

Black

Brown

Purple

Green

Orange

Yellow

White

Occasionally I have to send out emails to only one group of them or several.

Everyone except a white belt will have earned the other belts up to black. But each student only has one current belt color.

[comment]

18 minutes ago, Jim65 said:

I want to find students that have a black belt AND other students that have an orange belt

That is what I assumed when I posted my first reply. This may seem a bit counter-intuitive at first, but in order to get a found set that contains all students that have a black belt AND all students that have an orange belt, you need to search for students that have either a black belt OR an orange belt. And in order to perform an OR find, you need to have a separate find request for each option.

There is an example script as well as a demo file in the thread I linked to earlier.

Alternatively, you could perform a find for the first color and then extend the found set for each additional color. But that comes down to doing the same thing, only in a more complicated way.

 

Edited April 16, 2021 by comment

[Jim65]

I look at that thread but couldn't download the file?

[comment]

I had no problem downloading it just now. FWIW, here's a copy of the link:
http://fmforums.com/applications/core/interface/file/attachment.php?id=24674

[Jim65]

Thanks, I'll check it out. When I click the link in the article it doesn't do anything. But yours worked. Much appreciated.

[Jim65]

The example certainly does find whatever I check but I'm lost as to how to implement it into my file. Instead of entering the list of states I need to find my already existing ranks? I truly have no clue?

I also wondered why I could download the file from you but not on my own. The links are different:

https://fmforums.com/applications/core/interface/file/attachment.php?id=24674 - the link you sent me.

http://fmforums.com/applications/core/interface/file/attachment.php?id=24674 - the link in the article. It's missing the s on the end of http

Thanks for your help but I'm going to need  more guidance if you don't mind.

My ranks are stored in a table named Ranks

Students are in Students

My search field is EmailFind and it is global

Using a checkbox is ok since I only have 7 different color belts.

Thank you.

[comment]

1 hour ago, Jim65 said:

My ranks are stored in a table named Ranks

Students are in Students

How - if at all - are these table related? 

The other question is how do you want to enter the list of colors to search for. If you can use a global checkbox field, then you are in exactly the same situation as the one in the demo file.

 

[Jim65]

Students::StudentsIdPk to Ranks::StudentIdFk

I currently have the search field (EmailFind) on my Main Menu layout. It is a txt field set to global storage.

I'm thinking the checkbox would work but I'll be darned if I can figure it out. I don't like to make excuses but I am getting old!

I have to go teach class right now and won't be back until after 8 EDT tonight. I apologize.

Thanks,

Jim

Edited April 16, 2021 by Jim65

[comment]

14 minutes ago, Jim65 said:

Students::StudentsIdPk to Ranks::StudentIdFk

I am afraid that makes no sense to me. I would think that the relationship is 'one rank has many students', not  'one student has many ranks'.

Also, you said that that there is a RankColor field in the Students table. I am not sure why you need such field - unless this field is also the match field to the Ranks table.

 

[Jim65]

The Id connects the student table to a rank table. It's true that one rank has many students so maybe I just stated it incorrectly. In the Rank table is what's called kyu and dan ranks. These correspond to colors. Many of the colors have sub ranks but only one color that's why I was searching by color. 

My script goes to the students table, finds all of the Active students then looks for a color that I entered into the 'EmailFind' field. It then 'Finds' those students. It works fine if I only search for one color or all but not for just 2 or 3 colors.

Sorry, I'm gonna be late!

Thanks,

Jim

[Jim65]

comment,

Thank you very much for your help. I looked at the file and tried to understand it from the point of view of my file. I got it to work exactly like I had hoped!

Thanks again,

Jim