I have been struggling with this one for too long, and I believe that it should be simple, so I'm here to ask for help. 

 

I have an Employees table. The user can mark an employee "Inactive" when they are no longer with the company. It's a number field with 0 if they are still employed and 1 when they are no longer employed. 

 

I am trying to show only active employees on my drop downs. I can only get it to show ALL employees and I'm not sure what I am doing wrong.

 

Here's what my TO relationship looks like:

 

InactiveKey = 0

 

Here is what my Value List looks like: 

 

What am I doing wrong?

 

Thank you,

Ryan

 

 

What am I doing wrong?

 

Hard to tell without seeing your fields and, possibly more interesting, their types and contents.

 

Here is a working sample: 

ActiveEmployees_eos.fmp12.zip

Hi eos,

I was just getting ready to say the same thing. I’ll add, be sure that the related fields, are not a result of a calculation.

 

Hi Ryan,

 

It speeds up the process sometimes if you attach a copy of your files or a mockup.

 

Lee

First, your mistake is that your value list is set to show values from Employees::UserID, instead of from Employees_Active_TO::UserID. The 'starting from' TO is also wrong: it needs to be Employees, not Employees_Active_TO.

 

 

Note that you could make this much simpler by defining a calculation field cActiveUserID =

Case ( not Inactive ; UserID )

and base your value list on that, without requiring any additional TOs/relationships.

comment, your solution was incredibly simple. I am switching to that and thank you.

 

Thank you, Lee and eos for your information as well. I wasn't aware that you could not use a calculated field in a relationship as I thought I had seen that on an informational blog.

I wasn't aware that you could not use a calculated field in a relationship

 

That's an incorrect statement. The only limitation is that the matchfield/s on the "other" side of the relationship (the one that supplies the related records) must be indexable. This would rule out unstored calculation fields - not calculation fields per se.

Ah, OK. I will keep that in mind. But still, I like your solution better than the direction I was going.