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

AppSheet AppSheet Q&A
1

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
2

Hi @JLC

What about:

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

For reference:

FILTER() - AppSheet Help

CONTAINS() - AppSheet Help

1 Like
3

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?

4

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.

5

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.

6

OK, thank you for further explanation.

Can you try this instead:

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

App Formula, definitely.

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.

7

Ok I got this to work with setting the IDColumnUser to the same value in the Manager / Reviewer columns.

I think I am good to go. Thanks for your help!