Solved: Case management system and queues of cases\teams

joe_brackenboro · 11-22-2021 09:18 AM

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

Marc_Dillon

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

View solution in original post

Marc_Dillon

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

joe_brackenboro

thank you for replying, so Data is the following

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

joe_brackenboro

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.

Marc_Dillon

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

joe_brackenboro

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

Marc_Dillon

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: