Handling multiple values in the same customproperty of an entitlement for Dynamic attribute Filter

[shubhangsinha_]

Hello, 

I have the following use case-->

For instance, If an entitlement(ENT_A) that has comp code(entitlement attribute) as A100, A200,A150,A050 then during the filtering process in ARS the corresponding entitlement should be visible to select on any of the comp code values i.e if we only select A200 as comp code then also that entitlement(ENT_A) should be visible or if we only select A150 as comp code then also that entitlement(ENT_A) should be visible for selection and so on.

How do we handle this case, any suggestions would be appreciated. Thank you.

[rushikeshvartak]

Does both are entitlement values ? 

or A200 is dyanmic attribute

please add screenshot 

[shubhangsinha_]

A200 is the value of the dynamic attribute(Comp code) as you see in the below screenshot that the same entitlement can have different Comp Codes, so we need to display the entitlement if an end user selects any one of the multiple comp code values-->

[KirtiAjrot]

If the DA on the request form is a single select - you can try to use 

ev.custompropertyXX like '%${DynamicAttr}%'

[shubhangsinha_]

Thank you for the response, i tried it in data analyzer, and it partially solves my question but another issue here is that how do I update these multiple values(as comma separated values) in the same customproperty. Currently I am updating singular values on using this custom query job--> 

update
fields f,
entitlement_objects o,
entitlement_values e
set
e.customproperty10 = o.minvalue
where
f.fieldkey = o.field_key
and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY
and e.ENTITLEMENT_VALUE like 'zd%'
AND f.fieldkey = 13
and e.ENTITLEMENTTYPEKEY = 3

But as per the requirement Customproperty needs to be something (A100,A200,..) and so on.

[rushikeshvartak]

Custom Query is deprecated from v2022 . Please use saviynt for saviynt connector to achieve above case

use group_concat for custom query

[shubhangsinha_]

My version is v2021, in that case custom query is working and I will try to use group_concat as suggested by you. 

Also the requirement is that for an Entitlement(Ent_A) we need to store (A100, A200, A300) and so on in the custompropertyxx and then display the entitlement (ENT_A) if the user selects anyone of the values from A100,A200 or A300. So we can write ev.custompropertyXX like '%${DynamicAttr}%' in enttype as suggested by contributors here, but how do I update multiple values(using query) coming from the same column. In conclusion- A200,A100 , A300 are all coming from columnA and needs to be stored in Custompropertyxx. 

[rushikeshvartak]

Using find in set in entitlement type query

[sk]

If I understood your requirement correctly, Your expectation is that on ARS page if user selects any one values from list (A100, A200,A150,A050) on Dynamic Attribute(Comp Code) then you only want to display entitlements whose CPxx has the value which is selected for the list. Also I assume DA is single select and entitlement CPXX has comma separated values of Comp Code. Is that correct?

If above statement is the right understanding of your requirement then below is the solution

On entitlement type configuration of respective endpoint try to set below logic on Config for Requestable Entitlement in ARS

ev.custompropertyxx like '%${Dynamic Attribute Name}%', Example: ev.custompropertyxx like '%${Comp Code}%'

Where Comp Code is the Attribute Name of Dynamic Attribute(Not Attribute Label)

[shubhangsinha_]

Thank you for the response, I posted a sub problem in the above reply as well, if you have a workaround that , do let me know

[rushikeshvartak]

Simplify the use case 

based on entitlement privileges you want to display entitlement type (table/ drop down ) ?

[shubhangsinha_]

I tried the below update query using group_concat but it is running infinitely with no ending time hence nothing could be seen in logs, and the select statement with the same query is giving me the result in the desired format, so kindly suggest any changes to the query, thanks -->

update fields f, entitlement_objects o, entitlement_values e set e.customproperty12 = (select group_concat(distinct o.minvalue) where f.fieldkey = o.field_key and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY and e.ENTITLEMENT_VALUE = 'ZD_A_HEAD_O_1_WA151' AND f.fieldkey = 13 and e.ENTITLEMENTTYPEKEY = 13 group by e.entitlement_value)

[rushikeshvartak]

There is no mapping between f & e table?

share select query 

[shubhangsinha_]

select group_concat(distinct minvalue), ENTITLEMENT_VALUE from fields , entitlement_objects, entitlement_values where fields.fieldkey = field_key and entitlement_objects.ENTITLEMENT_VALUEKEY = entitlement_values.ENTITLEMENT_VALUEKEY and entitlement_values.ENTITLEMENT_VALUE like 'ZD%' AND fieldkey = 13 group by ENTITLEMENT_VALUE

Also I tried doing update using the below queries but it did not work -->

UPDATE entitlement_values e SET customproperty12 = k.minval from (SELECT GROUP_CONCAT(DISTINCT o.minvalue) AS minval, e.entitlement_value from fields f,entitlement_objects o, entitlement_values ee WHERE f.fieldkey = o.field_key AND o.ENTITLEMENT_VALUEKEY = ee.ENTITLEMENT_VALUEKEY AND ee.ENTITLEMENT_VALUE = 'ZD%' AND f.fieldkey = 13 AND ee.ENTITLEMENTTYPEKEY = 13 GROUP BY ee.entitlement_value)k where k.entitlement_value = e.entitlement_value

UPDATE entitlement_values e SET e.customproperty12 = (SELECT GROUP_CONCAT(DISTINCT o.minvalue) FROM fields f INNER JOIN entitlement_objects o ON f.fieldkey = o.field_key INNER JOIN entitlement_values e1 ON o.entitlement_valuekey = e1.entitlement_valuekey WHERE e1.entitlement_value like 'ZD%' AND f.fieldkey = 13 AND e1.ENTITLEMENTTYPEKEY = 13 AND e1.entitlement_value = e.entitlement_value)

[shubhangsinha_ · Accepted solution]

Tried the below query and it worked , thank you.

UPDATE entitlement_values INNER JOIN(select group_concat(distinct minvalue) as minval, ENTITLEMENT_VALUE from fields left join entitlement_objects
ON fields.fieldkey = entitlement_objects.field_key left join entitlement_values ON entitlement_objects.ENTITLEMENT_VALUEKEY = entitlement_values.ENTITLEMENT_VALUEKEY where ENTITLEMENT_VALUE like 'ZD%' AND fieldkey = 13 and entitlement_values.entitlementtypekey=13 group by ENTITLEMENT_VALUE)cte ON entitlement_values.entitlement_value= cte.entitlement_value set customproperty12 = cte.minval