Case management system and queues of cases\teams

Hi,

I cant seem to be able to work out how to pull members of a queue.
I am building a case management system, I have a table of officers, queues and cases. The cases are linked to a queue and an officer.

The officer links to the queues in the the column Queuelookup linked in officer

I have made the following so in the UX of cases it only shows cases in queues that are a member of i.e Contains(
LOOKUP(USEREMAIL(), “Officer”, “IDEmailOfficer”, “QueuesLookup”), [Queue])

My data says joeb@email.com [IDemailofficer] and a field that has queue 1, Q2, Q3 [QueuesLookup].

My problem is the other way round, how can I on the queues link all the officers that are members of them.

I would like to be able to see all members of the queues so that when I create a new case I can select the Queue and then see just members of that queue

I guess something like this

select(Officer[IDemailOfficer],contains(
[QueueName], officer[QueuesLookup]))"

Equally, I would like the ref_rows functionality so I can edit this inside the queue rather than going to the officer and editing the members per person.

Thank you in advance

joe

Can you please show screenshots of example data, as well as an example of what your intended result would be?

2 Likes

thank you for replying, so Data is the following

Table 1
[Email] | [Queuelookup] |
Joe.B@gmail.com | BlueQueue,GreenQueue|
Joe.A@gmail.com | YellowQueue,GreenQueue|
Joe.C@gmail.com | OrangeQueue,RedQueue,GreenQueue|
Joe.D@gmail.com | RedQueue,GreenQueue|

Queue lookup is one column and each Cell has multiple Queues separated by commas

Table 2
[QueueName] |
BlueQueue
YellowQueue
OrangeQueue
RedQueue

Query
Look at the person pull out each queue they are a member of and then list them under the Queues as related people in that queue. Linked like a ref_row so that the person is linked to the Queue table one linked with table 2.

Result

BlueQueue
Joe.B@gmail.com

GreenQueue
Joe.A@gmail.com
Joe.B@gmail.com
Joe.C@gmail.com
Joe.D@gmail.com

YellowQueue
Joe.A@gmail.com

OrangeQueue
Joe.C@gmail.com

RedQueue
Joe.C@gmail.com
Joe.D@gmail.com

Hopefully this makes sense

cheers

Joe

Happy to restructure my data, I am not sure how to link something Many to Many. It’s easy when it’s one to many.

Is the column type EnumList?

If so, and if I understand what you’re asking, you can put this expression in on a Virtual Column in the Queue Table.

FILTER(
  Table 1 ,
  IN( [_THISROW] , [Queuelookup] )
)

https://community.appsheet.com/search?q=many%20to%20many

2 Likes

thank you very much, yes that worked perfectly. I think I need to brush up on functions as my formula was very long and didn’t work this is quite simple. thank you very much

1 Like

Going from:

…to:

… I would offer the following resources:

FILTER(Table) is equivalent to SELECT(Table[key-column]). I always use it for easy expression readability in cases where the return value must be key values.

…versus:

And finally:

1 Like