How to use dynamic attributes under custom query for the user creation workflow

[Padmavathi]

Hi Team,

We have the requirement of user creation / updation needs approval of rank1 owners. If rank1 owner only raised the request it should be auto approval, if any other rank3 owner raised the request it should go for rank1 approval.

1. We have the mapping of companyname with custoprorperty32

2. while creating the user, requestor can select companyname. But for updating the user companyname name should get from the user property. 

3. Using the below query under custom assignment -> custom query

SELECT oo.userkey AS userkey
FROM organization_owners oo
INNER JOIN customer c ON oo.customerkey = c.customerkey
WHERE oo.rank IN (1, 2)
AND c.customername =
CASE
WHEN '${dynamicAttributes.childorganization}' IS NULL OR '${dynamicAttributes.childorganization}' = '' THEN '${user.customproperty32}'
ELSE '${dynamicAttributes.childorganization}'
END

4. Here, the request is going to admin always instead of auto approve / rank1 approval.

Can anyone please help us to get the required functionality.

Thans in advance!

Padmavathi 

[Raghu]

can you try instead of userkey, map username in select value try it  

select username from users where userkey  in (SELECT oo.userkey AS userkey FROM

[Padmavathi]

Hi @Raghu 

Thanks for the response!

I have used as per your suggestion. But again, request is going to the admin.

select username from users where userkey in (SELECT oo.userkey AS userkey FROM
FROM organization_owners oo
INNER JOIN customer c ON oo.customerkey = c.customerkey
WHERE oo.rank IN (1, 2)
AND c.customername =
CASE
WHEN '${dynamicAttributes.childorganization}' IS NULL OR '${dynamicAttributes.childorganization}' = '' THEN '${user.customproperty32}'
ELSE '${dynamicAttributes.childorganization}'
END)

Also, can you please confirm us the above usage dynamic attributes in custom query are fine or any changes required?

Thanks

[Raghu]

childorganization - this object not valid we dont hve right. issue causing der only

[Padmavathi]

Hi @Raghu 

childorganization is the dynamic attribute which we created for user form in user registration form under Global configuration > Identity Lifecycle

Thanks

Padmavathi

[Raghu]

@Padmavathi  can you try like below syntax attributes

try your first query again issue, try after select username query also

'${dynamicAttributes.get('childorganization')}'

reference article :

https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter12-Workflows/Workflow-Compon...

[PremMahadikar]

Hi @Padmavathi ,

Can you try hardcoding the dynamic attribute values in your query and run it in the data analyzer? Is it returning one userkey?

If this answers your questions, please consider selecting Accept As Solution and hit Kudos

[Padmavathi]

Hi @PremMahadikar 

Thanks for the response!

I have hardcoded the values like below and tested in data analyzer and I am able to see the username of rank1 and rank2 owners.  

select username from users where userkey in (SELECT oo.userkey AS userkey
FROM organization_owners oo
INNER JOIN customer c ON oo.customerkey = 9
WHERE oo.rank IN (1, 2)
AND c.customername =
CASE
WHEN customer IS NULL OR customer = '' THEN 'ABC'
ELSE 'DEF'
END)

Please let me know, if any changes to place in workflow

Thanks!

Padmavathi

[PremMahadikar · Accepted solution]

Hi @Padmavathi ,

For create/update user(register user), using dynamic attribute in workflow is a different setup which is not like request account/access.

The below is a solved solution for your usecase:

Configure your workflow like below (ifelse block will differentiate createuserrequest(request type=18))

Custom Assignment Block 1: (For create user request)

select o.userkey FROM organization_owners o, customer c where o.customerkey = c.customerkey and o.rank IN (1,2) and c.customername in (select raa1.attribute_value from request_access_attrs raa1, ars_requests ar1, request_access ra1 where ar1.requestkey = ra1.requestkey and ra1.REQUEST_ACCESSKEY = raa1.REQUEST_ACCESS_KEY and ar1.REQUESTKEY=${ARSREQUEST.id} and raa1.ATTRIBUTE_NAME = 'customproperty23')

Custom Assignment Block 2: (For update user request)

SELECT o.userkey FROM organization_owners o INNER JOIN customer c ON o.customerkey = c.customerkey WHERE o.rank IN (1, 2) AND c.customername='${user.customproperty23}'

My dynamic attribute is simple: (Attribute Name: companyname and User Column=customproperty23)

select customername as ID from customer

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos

[Padmavathi]

Hi @PremMahadikar  @Raghu 

Sure,I will check and get back.

Regards,

Padmavathi

[Padmavathi]

Hi @PremMahadikar 

Thanks for the Solution!

Regards,

Padmavathi