Extract values from multiple select from SQL query dynamic attribute

[tanvi_gaikwad]

Hello Saviynt Team, 

We have a dynamic attribute of type SQL MULTISELECT. The values are stored in a customproperty as comma separated values. 

Is there any way we can separate out each of these values and store them in remaining custom properties?

We had tried to use the split function in Data Analyzer, but it gives Access Denied error. Another approach we were looking into was creating a user update rule using custom action. 

Looking forward for solutions. 

[rushikeshvartak]

Its not supported. You can use only 1 account column at a time.

[tanvi_gaikwad]

Is there any way we can use the User Update Rule. This rule will be triggered when the user is created from UI, and will check if the attribute value has comma-separated values. If yes, then a custom action will be called. According to the documentation, we need a class name and a method name. From where do we create this custom jar?

[rushikeshvartak]

You need to create custom jar and upload under - settings - External Jar section

[tanvi_gaikwad]

HI Rushikesh, we are not able to locate the External JARS section under Settings. Can you provode exact location?

[rushikeshvartak · Accepted solution]

[tanvi_gaikwad]

Many thanks for the response. Also, can you provide a sample JAR file for us to refer?

[rushikeshvartak]

Validation Jars

• Organization Validation Jars-20210224T101132Z-001.zip

• Organization Custom Validation Source Code-20210224T101130Z-001.zip

• User Custom Validation Source Code-20210301T182031Z-001.zip

• User Custom Validation Source Code-20210301T182047Z-001.zip

[tanvi_gaikwad]

Hello Rushikesh, 

One DA is having multiple comma separated vales. How can we split those values using SQL query.

We are looking at something like this ->

select * AS ID  from users STRING_SPLIT(customproperty5 , ',') where username='adminsmita'

customproperty5(can have any number of comma separated values) = Garry and Warren Smith Honda,GWS Peninsula Honda,Pakenham Honda

Expected output->

• Garry and Warren Smith Honda
• GWS Peninsula Honda
• Pakenham Honda

Thanks.

[rushikeshvartak]

SELECT value AS ID
FROM STRING_SPLIT((SELECT customproperty5 FROM users WHERE username = 'adminsmita'), ',')

[tanvi_gaikwad]

Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '((SELECT customproperty5 FROM users WHERE username = 'adminsmita'), ',')) as sav' at line 1

[rushikeshvartak · Accepted solution]

SELECT Substring_index(Substring_index(customproperty5, ',', 1), ',', -1) as id
FROM   users where username = 'adminsmita'
UNION
SELECT substring_index(substring_index(customproperty5, ',', 2), ',', -1) as id
FROM   users where username = 'adminsmita'
UNION
SELECT substring_index(substring_index(customproperty5, ',', 3), ',', -1) as id
FROM   users where username = 'adminsmita'
UNION
SELECT substring_index(substring_index(customproperty5, ',', 4), ',', -1) as id
FROM   users where username = 'adminsmita'
UNION
SELECT substring_index(substring_index(customproperty5, ',', 5), ',', -1) as id
FROM   users where username = 'adminsmita'
UNION
SELECT substring_index(substring_index(customproperty5, ',', 6), ',', -1) as id
FROM   users where username = 'adminsmita'
UNION
SELECT substring_index(substring_index(customproperty5, ',', 7), ',', -1) as id
FROM   users where username = 'adminsmita'
UNION
SELECT substring_index(substring_index(customproperty5, ',', 8), ',', -1) as id
FROM   users where username = 'adminsmita'

[tanvi_gaikwad]

Thank you Rushikesh for the response. This is working.

One query, this will work only of the max we have are 8. It will not work for selections more than 8?

[rushikeshvartak · Accepted solution]

Yes keep adding union 

[tanvi_gaikwad]

Hello Rushikesh, 

We have requirement of splitting string having more than 35 comma separated values. We already tried it with 5 select/union statements and it's affecting server performance. it's very slow and taking time to load user creation form. So is it right to go ahead with same approach? or do you have any other alternative?

There should be a way to handle the options selected in the 'Multiselect from query DA'

[rushikeshvartak]

There is no alternative. Store in multiple customproperties