Create User Request with datediff validation

[glegault]

Hello,

In the Create User Request form, we would like to add validation on an EndDate field to force the user to enter a date between now and the next 365 days for specific employee types.

This validation is working fine but does not include date range validation. It forces the user to choose an enddate for specific types:

${((EndDate != null)||(employeeType != "Temporaire" && employeeType != "Consultant"))}

We would like to include a datediff validation if possible.

I tried like this but we get errors when using the form.

${((EndDate != null && DATEDIFF(Enddate, DATE_ADD(NOW(), INTERVAL 365 DAY)) > 0)||(employeeType != "Temporaire" && employeeType != "Consultant"))}

I also tried this with no luck:

${((EndDate != null && DATEDIFF(Enddate,NOW()) <= 365)||(employeeType != "Temporaire" && employeeType != "Consultant"))}

Is this because we are not using correct syntax or because we cannot use DATEDIFF in user form validations?

Thank you for the help.

[rushikeshvartak]

Can you try below

${((EndDate != null && (employeeType == "Temporaire" || employeeType == "Consultant") && DATEDIFF(Enddate, NOW()) <= 365 && DATEDIFF(Enddate, NOW()) >= 0) || (employeeType != "Temporaire" && employeeType != "Consultant"))}

[glegault]

Hi @rushikeshvartak thank you for the quick reply.

I just tried to replace our current validation with your suggestion and I still get these error messages if I choose either "Consultant" or "Temporaire" for employee type and choose a date in the near future. 

Example:

[glegault]

Hello again @rushikeshvartak ,

I just wanted to add that while troubleshooting this further I noticed in the Log Viewer a "no such property Enddate" message and realized the correct attribute name is EndDate with an uppercase D. I adjusted from your suggestion accordingly but sadly I still get the same error messages from the Create User Request form.

If it can help you help us here is what I see in the Logs at every attempt.

Thank you again.

[rushikeshvartak]

• Can you split condition and validate which part is breaking

[glegault]

As a test I configured only this as validation condition and I do get the same error.

${(DATEDIFF(EndDate, NOW()) <= 365)}

Our previous validation without DATEDIFF was working. It seems the error comes from the DATEDIFF part only.

Is it possible the date format from the  Create User Request form is not valid to use directly with the DATEDIFF function? I am asking because doing something very similar from Data Analyzer works fine. Example on a select from the users table.

select enddate from users where DATEDIFF(EndDate, NOW()) <= 365

Thank you again.

[rushikeshvartak]

Refer https://forums.saviynt.com/t5/third-party-access-governance/validation-of-user-form-date-fields-with...

[glegault]

Thank you. I also found this other article that seem to confirm DATEDIFF cannot be used in validation conditions of Create/Update user form.

Solved: Create/Update User Request form - How to get local... - Saviynt Forums - 81175

Would it be possible to provide help on how to go from this below to something equivalent using the parse method from the provided articles? Basically we want to force users to select an EndDate between now and 365 days.

${(DATEDIFF(Enddate, NOW()) >= 0 && (DATEDIFF(EndDate, NOW()) <= 365))}

[NM]

${(Date.parse("yyyy-MM-dd",Date currentDate = new Date(); ) >= enddate) && (enddate >=(startdate))}

Try this @glegault 

[glegault]

Hi @NM 

Thank you for the reply. I am not sure to understand correctly you suggestion as I do not see any reference to 365 days in it. I tried but I see this error in logs now.

[glegault]

Hi @NM and @rushikeshvartak ,

This may seem almost too simple after all the troubleshooting I did around DATEDIFF and parsing but from what I can see using this as a validation condition does exactly what I needed at least for the EndDate portion.

${(EndDate < (currentdate + 365) && EndDate > startdate)}

I will just combine this with our employee type conditions and test again but it is looking good.

At first I was not aware of the currentdate option and did not know DATEDIFF / NOW() could not be used in validation conditions for  Create/Update User Request.

Learning curve I guess... 

Thank you!

[NM]

Hi @glegault , is currentdate an attribute of user? I believe date parse should work in validation.

[glegault · Accepted solution]

Hi @NM 

I agree date parse should work in validation but I realize it is not required after all for our use case at least.

currentdate seem to be available for these kind of validations based on documentation. The documentation associate currentdate with start date, which can be misleading in my opinion because it would be the same as start date for a newly created user but not for update user. Just to confirm this, I updated an already created user which had a start date from more than a year ago and I was able to extend the enddate within 365 days from today (currentdate) but not longer. This is precisely what I was trying to achieve and was struggling with. Now we are good both for create or update user! Thank you.

Current validation used:

${((EndDate != null) && (EndDate < (currentdate.plus(365)) && EndDate > startdate)||(employeeType != "Temporaire" && employeeType != "Consultant"))}

Setting Up Simple Form Fields (saviyntcloud.com)

[rushikeshvartak]

so below configuration is working ?

${((EndDate != null) && (EndDate < (currentdate.plus(365)) && EndDate > startdate)||(employeeType != "Temporaire" && employeeType != "Consultant"))}

[glegault]

Yes it does. I just did a lot of testing around this and did not find any issue with it. It is also the method that seem to be the closest from what is shown in product documentation. I guess I should have started there... Thank you!

[rushikeshvartak]

✅👍Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.