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

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

Aurelien

Hi @JLC

What about:

FILTER("USER", 
  CONTAINS([_THISROW].[Status].[Role],[Role])
)

For reference:

FILTER() - AppSheet Help

CONTAINS() - AppSheet Help

JLC

That returns a list datatype but this is not a virtual column. I could change my schema to remove the assigned-to column and make it virtual. However, I need the assign-to as a field in my db for other processes. Can I wrap that in a string formula to return text value?

Aurelien

@JLC wrote:

That returns a list datatype but this is not a virtual column.

I'm not sure of your need.

If you wish to have it set as a real column, it does not work when you edit your contact ? Normally, real column are re-calculated when editing - while virtual column are calculated on the fly.

JLC

Ok, If I understand correctly this will filter Users for the matching role in the Status table. However, this would return ALL users for that role. I only want to return Manager or Reviewer based on the status. Do I need to do a filter of a filter? In regards to the list datatype. Should this formula go in Appformula OR initial value? I want this to dynamically set the assigned to. The user only sets the contact status. They cannot set the assign-to manually. I don't want to return a list of option to select the user, I want the app to return the value on who should be assigned to the manager or review based on the role in status.

Aurelien