Solved: Need help with complex SELECT / DEFERENCE /IN? set... - Page 2

JLC · 06-29-2023 07:29 AM

I have an app that needs to have a dynamic "assigned to" user.

I think I need to have a Select expression but I am not sure how to set this up. I have a status table with a role column that could be one or more roles. Users are assgined to one role. I have a contacts table where the contacts can be assigned a status, one or more managers and a reviewer. I want to be able to assign the contact to either the manager(s) or the reviewer based on the status. How do I get the correct User in the Assigned To field?

Status	Role
New	A
Pending	A
Step1	A,B
Step2	C
Complete	C


User	Role
User1	A
User2	A
User3	C

Contacts	Status (REF)	Managers (ENUMLIST	Reviewer (EMUM)	AssignedTo
Some Contact1	New	User1,User2	User3	User1,User2
Some Contact2	Step2	User1	User3	User3

Aurelien

OK, thank you for further explanation.

@JLC wrote:

I only want to return Manager or Reviewer based on the status.

Can you try this instead:

FILTER("USER", 
  AND(
    CONTAINS([_THISROW].[Status].[Role],[Role]),
    CONTAINS(CONCATENATE([_THISROW].[Reviewer],[_THISROW].[Manager]),[idColumnUser])
  )
)

@JLC wrote:

Should this formula go in Appformula OR initial value?

App Formula, definitely.

@JLC wrote:

I want this to dynamically set the assigned to.

Then, you would prefer using a virtual column instead of a real column.

Because you said you need it to be written, then you may want to build a mechanism with a bot for example, to refresh data value with an action everytime a user has been updated.

View solution in original post

Need help with complex SELECT / DEFERENCE /IN? setup.