How to add list collector of location in the approver table for if approver is the Contact

[Samiksha2]

Hi All,

I have a catalog item in which a variable is a list collector (cmn_location). 
In the location table I have added the contact for each location.
The flow is approver will go based on selected location's contact. (This is working).
The requirement is in the approver table that location should be populate for which contact is approving.

For example,
In the location table
Location 1 has User 1 contact

Location 2 has User 2 contact

Location 3 has User 1 contact

Location 4 has User 1 contact

And in the catalog item end user has selected Location 2, Location 3,Location4

In the Approver table, in the string value for User 1 Location 3 and Location 4 should populate and for User 2 Location 2 should populate.

The reason behind doing this is they want a report where stakeholder can see who approves which location.
I have created a database view for requested item and approver table.
But I am unbale to get the list collector values separately. 

If any other way to to do this, please let me know.

Thanks,
Sam
 

[Ankur Bawiskar]

@Samiksha2 

when you join using those 2 extra tables which I mentioned, you can specify the exact variable name

what did you start with? something like this

check this link as well

Database view with list field 

Table	Order	Prefix	Where
sc_item_option_mtom	400	mtm	mtm.request_item=r.sys_id & mtm.sc_item_option = 'listCollectorVariableSysId'
sc_item_option	500	imp	instr(imp.value, a.approver)>0

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Samiksha2]

Hi @Ankur Bawiskar ,
I tried but it is not working.

[Ankur Bawiskar]

@Samiksha2 

Did you give the correct variable sysId in that clause?

I am having doubt if you can use indexOf or contains in string field using database view where clause. the last one is complex/challenging

did you check that link?

Database view with list field 

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Samiksha2]

I am checking it. 
Yes variable sys_id is correct.

[Samiksha2]

In the sc_item_option table, list collector value is coming as this.

How can we add where clause in database view?