Can we add uniqueness check at endpoint dynamic attribute?

[rahul_p]

Hello,

We have service account request form, we have account name uniqueness rule enabled. But we want email to be entered from user and we need to check the uniqueness for that email, is it possible to do this?

Regards,

Rahul

[AmitM]

HI @rahul_p , Hope I have understood it right.

If you have another DA , that will pick value of DA / email that user entered , and check for uniqueness via querying tables behind the seen. Can have a case statement if count is greater than one NOT UNIQUE else UNIQUE? 

And if user still go ahead with NOT unique email , you can reject request and send email with reason.

BR,

Amit 

[rahul_p]

Hello @AmitM ,

Thank you for reply, is there any example or reference for this can be given?

Thanks,

Rahul

[AmitM]

Hi @rahul_p , 

Here is what we are using -select case when a.cnt >100 then 'Not Available and request will be rejected' else 'Available' end as ID from (select count(*) as cnt from accounts where customproperty4 = ${LicenceType} and endpointkey=32) a

But licenceType in our case is coming from dataset so that is an SQL ENUM, it seems your case is a string attribute.

See if it works else you can reject it in Workflow that is what we are doing if user still goes ahead and submit request.

Br,

Amit

[rahul_p]

Hello @AmitM ,

Thank you for your reply.

I used below query by referring to your query:

select case when a.cnt >0 then 'Email not Unique' else 'Email Unique' end as ID from (select count(*) as cnt from accounts where customproperty55 = ${EmailID} and endpointkey=3) a

But getting error like Caused by: groovy.lang.MissingPropertyException: No such property: Email_ID for class: SimpleTemplateScript

So I referred one more blog and tried below one:

select case when a.cnt >0 then 'Email not Unique' else 'Email Unique' end as ID from (select count(*) as cnt from accounts where customproperty55 = '${if(binding.variables.containsKey("EmailID")){ EmailID } }' and endpointkey=3) a

Using this its always giving email unique.

Can you please suggest something here?

Thanks,

Rahul

[AmitM]

HI @rahul_p , 

Can you use this - select case when a.cnt >0 then 'Email not Unique' else 'Email Unique' end as ID from (select count(*) as cnt from accounts where customproperty55 = ${EmailID} and endpointkey=3) a

And make the email address SQL ENUM - single select from query. Use query Select 'abc@xyz.com' as ID

Then see if it works. If it does then maybe it wont work with String attribute. Just to let you know I also just tested it and made my SQL ENUM attribute String and seeing same error as yours.

Best Regards,

Amit Malik

[rahul_p]

Hello @AmitM ,

Thanks for your reply.

I used SQL ENUM as field type and your query but getting below error:

WARNING [http-nio-8080-exec-98] groovy.sql.Sql$AbstractQueryCommand.execute Failed to execute: select case when a.cnt >0 then 'Email not Unique' else 'Email Unique' end as ID from (select count(*) as cnt from accounts where customproperty55 = ${EmailID} and endpointkey=9) a because: 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 '{EmailID} and endpointkey=3) a' at line 1"

Thanks,

Rahul

[AmitM]

Rahul , not sure what say but it works for us.

But it doesn't work if I make LicenceType as String. Only works when it is SQL ENUM. I guess your use case is user has to type the email id which has to be string type. 

It might end up as an enhancement request on Idea porta.

BR, Amit

[rushikeshvartak]

Share screenshot of dynamic attribute from endpoint page. It seems you have not create Dynamic attribute named EmailID

[rushikeshvartak]

• On runtime this is not possible however in workflow you can check if email already used then auto reject the reject
• On runtime you can validate email format using Regex Condition in dynamic attribute