Jump to content
Sign in to follow this  
GC GYM

Find value in another table

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

MySkillsPortal.jpg

Share this post


Link to post
Share on other sites

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

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

as your conditional formatting formula.

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

That's even better, thanks.

Phil

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.