Trying to set value of form attribute using another form but facing issue.

[manish97sh]

I am trying to set value of one user form attribute using another one but facing issue.
There is 1 StartDate attribute in user forms which is a mandatory attribute and user has to fill it while submitting user creation request.

now i want to set value of statuskey of user based on this StartDate attribute at the time of user creation only. I created one new user attribute UserStatus and configured it in below format:

Query is as below:

select case when DATEDIFF(CURDATE(), ${StartDate}) <=5 THEN 1 ELSE 0 END as ID

after saving this data when i creating user request, UserStatus attribute is not showing up any values on form and also not storing anything in STATUSKEY.

[Dhruv_S]

Hi @manish97sh 

1. Have you used mapping in the "What action to perform when the parent attribute changes" in attribute details. Please use mapping and see if works.  Also please check with the Refresh once in the same field.

2. Please check logs and share what query is being printed.

3. Please share if any error post executing the query in logs.

4. Please validate the query in data analyzer for an existing user before executing.

Regards,

Dhruv Sharma

[manish97sh]

Hi @Dhruv_S ,

Yes i have used MAPPING in atttribute details.

I have also validated query in data analyzer and it is giving expected output there.

Also after submitting form, i have checked logs and there is no clue about UserStatus form attribute and query

[rushikeshvartak]

Make user column as lower case 

statuskey

[manish97sh]

@rushikeshvartak , I have tried it in lowercase also but not getting results.

[rushikeshvartak]

Can avoid keeping users table column name as dynamic attributes name for startdate maybe change to dynstartdate

[manish97sh]

Hi @rushikeshvartak ,

I tried with changing name of form attribute to DyStartdate but still same issue and not getting any results.

[rushikeshvartak]

Make status dynamic attribute as editable 

[Dhruv_S]

Hi @manish97sh 

I tried to replicate your issue but the Userstatus is always updating to 0 whatever date I put in the dynstartdate which might be happening if the condition is always evaluating to false. 

Can you share screenshot from data analyzer how you have validated the query. In what format the curdate and startdate is there.

Regards,

Dhruv Sharma

[manish97sh]

Hi @Dhruv_S @rushikeshvartak 

I tried with Editable option and in that case system is taking 0 as default value for statuskey. Without Editable option it is taking 1 as default value which is Active.

Below is the query i am using in form attribute:
select case when DATEDIFF(${DyStartDate}, CURDATE()) <=5 THEN 1 ELSE 0 END AS ID

Below is the query i am using in Data Analyzer and getting below results:

I created a user with startdate >=5 days from current date

[Dhruv_S]

Hi @manish97sh 

Please try putting this value in the attribute SQL query and see if the datediff value is getting populated. It will help in further troubleshooting.

select datediff(${dynstartdate}, curdate()) as ID from users where dynstartdate=${dynstartdate}

Regards,

Dhruv Sharma

[manish97sh]

@Dhruv_S 

I tried with your query but still there is no output in UserStatus attribute.

[rushikeshvartak]

What’s error in logs

[manish97sh]

@Dhruv_S 

There is no such instance of that dynamic attribute in logs.

Please find logs attached for your reference:

[Dhruv_S]

Hi @manish97sh @rushikeshvartak 

I tried to replicate the issue with below query. It seems that the dynamic value of ${dynstartdate} is not getting evaluated which is the reason it is not working.

Query used to test

select datediff(${dynstartdate}, curdate()) as ID from users where startdate =${dynstartdate}

"2024-01-18T14:12:54.302+00:00","ecm","workflow.JbpmworkflowmanagementController","http-nio-8080-exec-22-bzwt6","DEBUG","finalQuery--select ID as ID from (select datediff('', curdate()) as ID from users where startdate ='') as T order by ID"
"2024-01-18T14:12:54.476+00:00","ecm","","null-bzwt6","","18-Jan-2024 14:12:54.303 WARNING [http-nio-8080-exec-22] groovy.sql.Sql$AbstractQueryCommand.execute Failed to execute: select ID as ID from (select datediff('', curdate()) as ID from users where startdate ='') as T order by ID because: Incorrect DATETIME value: ''"
"2024-01-18T14:12:54.304+00:00","ecm","workflow.JbpmworkflowmanagementController","http-nio-8080-exec-22-bzwt6","ERROR","Exception"
"2024-01-18T14:12:54.476+00:00","ecm","","null-bzwt6","","java.sql.SQLException: Incorrect DATETIME value: '' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) at com.saviynt.ecm.workflow.JbpmworkflowmanagementController$_closure145.doCall(JbpmworkflowmanagementController.groovy:9899) at

Regards,

Dhruv Sharma

[rushikeshvartak]

SELECT DATEDIFF(CURDATE(), COALESCE( ${dynstartdate}, CURDATE())) AS ID FROM users

[manish97sh]

Hi @rushikeshvartak,

I tried with above query but still there is no output.

Thanks 

[rushikeshvartak]

Mapping does not work as mentioned by dhruv and i have also tested please raise enhancement request on ideas portal

[manish97sh]

Hi @rushikeshvartak ,

I understand that part and i will raise enhancement request for same.

But for now can u suggest me an alternative to achieve my goal ?

Best regards,

Manish

[rushikeshvartak]

use preprocessor