Find value in another table

[GC GYM]

I tried to post before, but can't see it anywhere, sorry if I've double posted.

I'm trying to conditionally format a field according to whether or not a record with the same id exists in another table.

I'm using this...

PatternCount(

List(OtherTable::ID );

ThisTable::ID)

>0

but if ID number 10 exists in the other table, the item in this table with ID 1 also seems to meet the condition.

Gotta be an easier way right?

Phil

[comment]

What is the relationship between the tables (hint: if you match ThisTable::ID to OtherTable::ID, you won't need a calculation)?

[GC GYM]

I'll try to be more specific.

I'm on a layout showing a record of a student.

I also have a table of skills that students need to learn called SKILLS, each skill has a field defining the class it applies to.

A Portal on the student's record shows the skills that this student needs to learn, match field is the student's class.

When a student passes a skill, a new record is created in another table SkillsPassed with the skill ID and the Student ID (really it's a join table I suppose).

When a related record (by student ID) in the SkillsPassed table has same SkillID as theskill in this portal row, the condition is met and formatting makes the skill in that row turn green. Otherwise it's red.

It allows the operator to mark off some skills on the student's list as passed, while only generating a record in the join table when a pass has occurred. (see pic attached)

So from the student there is a relationship (MyCurrentSkills) to the SKILLS Table, and a relationship (Skills I Passed) to the SkillsPassed table.

From the hint I think you are suggesting I should make a relationship between the two tables based on skillID, but I'm not really sure how to then include the student ID or what to do after that?

Thanks

Phil

[comment]

Ah no, you don't need another relationship. Try:

not IsEmpty ( FilterValues ( MyCurrentSkills::SkillID ; List ( Skills I Passed::SkillID ) ) )

as your conditional formatting formula.

[GC GYM]

That worked, many thanks Comment.

Was the problem that PatternCount() is a text function? or just that the list should first be filtered for the required ID value?

Edited April 25, 2009 by Guest

[comment]

The problem is that PatternCount() does exactly that: it looks for a pattern, and the pattern "1" can be found in "10" as well as in "11", "105", and so on. This can be solved by looking for:

PatternCount ( ¶ & list & ¶ ; ¶ & item & ¶ )

but I believe FilterValues() is much neater.

---

BTW, if your items are numeric, you can reduce the test to just:

FilterValues ( item ; list )

This will evaluate to true if the item is found (since it is a non-zero number), false otherwise (since an empty string evaluates to false).

Edited April 25, 2009 by Guest

[GC GYM]

That's even better, thanks.

Phil