Check field value against value list

[Courtney]

I have a value list that includes the "Personnel::UID" of all managers in my personnel table. (Determined through a self-related table where "Personnel::Manager" = "PersonnelSelf_Managers::UID". I included a screenshot of the relationship.)

I want to compare a single value ("Personnel::UID") against the contents of that value list to take action (show or hide some data on the layout) based on whether the person in question is a manager or not.

Can this be accomplished with a calculation using ValueListItems? I am having a hard time figuring it out.

Is there a simpler way to do it? I am trying to minimize the creation of lots of random fields that could be handled via calculations instead.

[comment]

I don't understand your description. It looks like a manager is someone whose Personnel::UID value appears in the Manager field of another record of the Personnel table. If that's correct, then a value list of managers' IDs would be based directly on the Personnel::Manager field, and the relationship would not be required (for the purposes of defining this value list).

Now, while you certainly can check if an item appears in a value list by =

not IsEmpty ( FilterValues ( YourItem ; ValueListItems ( Get ( FileName ) ; "YourValueList" ) ) )

it seems to me it would be much simpler to determine if the current person is a manager or not by testing for the existence of subordinates - e.g. by =

not IsEmpty ( PersonnelSelf_Managers::UID )

 

[Courtney]

11 minutes ago, comment said:

I don't understand your description. It looks like a manager is someone whose Personnel::UID value appears in the Manager field of another record of the Personnel table. If that's correct, then a value list of managers' IDs would be based directly on the Personnel::Manager field, and the relationship would not be required (for the purposes of defining this value list).

Hmm, you are correct. I described it poorly. My value list is based on Personnel::Manager, and I use the relationship to display their name, rather than an ID # in pop-up lists.

 

11 minutes ago, comment said:

Now, while you certainly can check if an item appears in a value list by =

not IsEmpty ( FilterValues ( YourItem ; ValueListItems ( Get ( FileName ) ; "YourValueList" ) ) )

it seems to me it would be much simpler to determine if the current person is a manager or not by testing for the existence of subordinates - e.g. by =

not IsEmpty ( PersonnelSelf_Managers::UID )

 

Using

 not IsEmpty ( PersonnelSelf_Managers::UID )

leaves me with only the people who do not have a manager specified. (I am using the layout feature "hide object when", in case that was unclear.)

I'm going to try using FilterValues, which I completely overlooked. I was using Position and looking for items with no match, so to speak.

[Courtney]

32 minutes ago, Courtney said:

not IsEmpty ( FilterValues ( YourItem ; ValueListItems ( Get ( FileName ) ; "YourValueList" ) ) )

FilterValues did it. Thank you so much.

[comment]

56 minutes ago, Courtney said:

Using

 not IsEmpty ( PersonnelSelf_Managers::UID )

leaves me with only the people who do not have a manager specified

Ah, I now see that your relationship goes in the opposite direction from what I thought (i.e. it shows who the manager is, not who the subordinates are).

Unfortunately, for conditional formatting you cannot choose the context for evaluation. Are you sure you don't have (and don't need) a second self-join relationship of:

PERSONNEL::UID = PersonnelSelf_Subordinates::Manager

so you can show a person's subordinates in a portal?

[Courtney]

40 minutes ago, comment said:

Ah, I now see that your relationship goes in the opposite direction from what I thought (i.e. it shows who the manager is, not who the subordinates are).

Unfortunately, for conditional formatting you cannot choose the context for evaluation. Are you sure you don't have (and don't need) a second self-join relationship of:

PERSONNEL::UID = PersonnelSelf_Subordinates::Manager

so you can show a person's subordinates in a portal?

I do, actually! I've been staring at all of this so long, I'm losing my mind.

 

But since all managers still also have a manager of their own, I'm not clear on how IsEmpty could help with this application. (?)

[comment]

9 minutes ago, Courtney said:

But since all managers still also have a manager of their own, I'm not clear on how IsEmpty could help with this application.

Have a look at the attached, super-simplified, demo. It highlights the name of any person who is a manager (i.e. has subordinates), regardless of whether that person has a higher-ranking manager over them or not.

 

Managers.fp7

[Courtney]

10 minutes ago, comment said:

Have a look at the attached, super-simplified, demo. It highlights the name of any person who is a manager (i.e. has subordinates), regardless of whether that person has a higher-ranking manager over them or not.

 

Managers.fp7

Works beautifully in my file. Thank you again!