Searching Related Data with AND

[Andrew Robinson]

Ok we have a table of names of people which relates to a table of skills that person has and what level of skill they have.

 

Table 1

• Full Name

Table 2

• Skill
• Skill Level

We want to be able to do a find with multiple requests. For example, I want to see all the people in Table 1 which has First Aid as a skill at level 2. Easy! 

Now I want to see all the people who have First Aid at level 2 AND also can drive a car at level 3. The important thing here is I want request 1 AND request 2.

I have tried to do this and cannot figure out a way.

Any help?

[rwoods]

You have to add a second find request. So if you are in table 1...

1) Enter find mode

2) Select First Aid Level 2

3) Add a 'New Find Request' (Cmd-N or Ctrl-N depending on platform)

4) Choose Car at level 3

5) Hit 'Return'

One thing you need to be aware of, is that FileMaker does funny things when searching in portals. If you select First Aid in one field in the portal, and Level 2 in another field in the same portal on the same find request, FileMaker may think you are trying to find one or the other (OR) rather than both (AND). You may have to create a single field in Table 2, which concatenates the two pieces of information, and then search on that new field in the two find requests, to get what you are looking for.

Edited October 23, 2019 by rwoods

[Andrew Robinson]

Thanks @rwoods

Table 2 only has 2 fields, Skill and Skill level.

Eg, 

Name	Skill	Skill Level
John Smith	First Aid	2
John Smith	Car	3
Roger Brown	First Aid	1
Steve Smith	First Aid	2

 

So cannot concat anything.

Doing multiple requests as you suggest would just give all the people first aid level 2 as well as people who have the Car Level 3

I need a list of people that have both.

[rwoods]

OK, in that case...

 

1) Enter find mode

2) Select First Aid Level 2

3) Hit return (you will now have those that have First Aid Level 2)

4) Enter find mode

4) Choose Car at level 3

5) Choose 'Constrain Found Set' (this performs the second find only on the results from the first find)

This may be awkward for inexperienced users, so you could create a script that steps users through this.

I don't agree that you cannot concatenate two fields

Table2::Skill & Table2::Skill Level

would give FirstAid2, Car3 etc and you may need to do this. But I may be missing something...

[comment]

1 hour ago, Andrew Robinson said:

We want to be able to do a find with multiple requests.

You cannot accomplish this using a single find with multiple requests. Multiple requests perform an OR find (as you have already discovered). An AND find is usually accomplished using a single request with multiple criteria. However, you cannot have multiple criteria for the same field in the same request.

What you need to do is find the records that meet the first criteria, then constrain the found set using the second criteria.

 

Edited October 23, 2019 by comment

[Andrew Robinson]

@rwoods Thanks will give that a go.

You are correct it will be awkward for end users but I will try and script it.