Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners Click HERE | EMEA/APJ Click HERE |
12/14/2022 04:19 AM
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.
Solved! Go to Solution.
12/14/2022 04:22 AM - edited 12/14/2022 04:23 AM
Does both are entitlement values ?
or A200 is dyanmic attribute
please add screenshot
12/14/2022 04:29 AM
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-->
12/19/2022 03:58 AM
If the DA on the request form is a single select - you can try to use
ev.custompropertyXX like '%${DynamicAttr}%'
12/20/2022 03:22 AM
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.
12/20/2022 04:22 AM - edited 12/20/2022 04:23 AM
Custom Query is deprecated from v2022 . Please use saviynt for saviynt connector to achieve above case
use group_concat for custom query
12/20/2022 05:25 AM
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.
12/20/2022 05:41 AM
Using find in set in entitlement type query
12/19/2022 09:40 AM
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)
12/20/2022 03:24 AM
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
12/20/2022 04:19 AM
Simplify the use case
based on entitlement privileges you want to display entitlement type (table/ drop down ) ?
01/24/2023 10:16 PM
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)
01/25/2023 04:52 AM
There is no mapping between f & e table?
share select query
01/25/2023 05:15 AM - edited 01/25/2023 05:28 AM
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)
01/27/2023 01:57 AM
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