Bug Issue when default value is missing

With the new designer it is not possible to change the default value when the value is not anymore in the list of values of the lookup.

 

Create a lookup field, then add a value in the list of value and setup a default value in the field. Delete the lookup value in the list then when saving the record you get an integrity error : The INSERT statement conflicted with the FOREIGN KEY constraint ...

 

In earlier version you were able to change the default value in the designer but now it is not possible anymore when changing the value you got the load turning and you cannot change the default value.

 

It would be a great idea to apply a change and not to have to go in the db for changing the Foreign key.

12 comments

Dear Franck,

 

Thank you for your inquiry!

 

Please be advised that the "The INSERT statement conflicted with the FOREIGN KEY constraint ..." error message appears due to a deleted lookup value that was previously used in the system.

 

Please note that it is highly not recommended to delete the lookup value to reduce the risk of such errors.

As a workaround, we may suggest you renaming the lookup rather than deleting it.

 

If the issue with the error persists we would recommend using the SQL script below to find out to which entity the FK is attached and where it is located.

After executing the script, you would need to find the connection between the section where the record is added with the FK that prevents it from being added.

Most likely the section has a field with a removed DEFAULT VALUE from the system or, that same field was removed which may cause this error.

 

Please see the SQL script:

 

SELECT
OBJECT_NAME(constraint_object_id) AS FKName,
OBJECT_NAME(parent_object_id) AS TableName,
COL_NAME(parent_object_id, parent_column_id) AS ColumnName,
OBJECT_NAME(referenced_object_id) AS RefTableName,
COL_NAME(referenced_object_id, referenced_column_id) AS RefColumnName
FROM
sys.foreign_key_columns
where OBJECT_NAME(constraint_object_id) = 'FK0ptyMLPUPcuuJ4DXFRngaggXnqcL'
ORDER BY
FKName,
constraint_column_id

 

 

Hope this helps!

 

Thank you!

 

Regards,

Danyil

Yes but before the release 7.17 you were able to correct this in the designer. With 7.17 and upwards, it is not possible to change the value in the interface. My request is to restablish the feature in the designer.

Best regards,

Franck

Dear Franck,

 

Thank you for your patience!

 

Please be advised that we have requested a demo instance of 7.16.2 version of Creatio for testing purposes.

 

Could you please login and provide us with the descriptive information you are referring to in your last message about changing the value in the designer in prior Creatio versions please?

 

You may use this link, login and password below:

https://0967526-service-enterprise-demo.bpmonline.com/
Supervisor
g42TKnvzRNabsXendtCvUp8Tf5uv

 

Thank you for your cooperation!

Looking forward to hearing from you!

 

Regards,

Danyil

Danyil Onoprienko,

Created a new section UsrTest with a field Status populated some values and set as default value : "New".

Then I deleted the New value in the lookup.

Then I was able to change or delete the default value for the field Status in UsrTest.

I took the printscreen for more information if needed.

Feel free to come back to me.

Franck

Danyil Onoprienko,

I have noticed this behavior as well in 7.17 (which is different than previous versions). In 7.17 you cannot edit the default value so it no longer references the deleted item (it will just spin and not allow you to remove the now invalid default item)

Ryan

Franck Lehmann,

Please be advised that the Default value can be set up in 7.17 in the same way as in 7.16. Please refer to the screenshots below:

 

Could you please check this in your end and confirm?

 

Thank you!

 

Regards,

 

Danyil

Ryan Farley,

Thank you for providing additional information on this matter!

Please be advised that upon our testing on a local website we were able to add a Default value to a lookup. Please refer to the screenshot below:

 

Please be advised that if the issue persists on your end you may contact us at support@creatio.com for further assistance.

 

Thank you!

 

Regards,

Danyil

Danyil Onoprienko,

The issue I experienced (this was in 7.17.1, I've not yet retested this in 7.17.2) wasn't with setting the default value. The issue was when a default value for a lookup column had been set, then later that lookup item was deleted (so the default value referenced an Id from the lookup that no longer exists). In this case, I was unable to edit the default value to change or remove the default - I assume because it was trying to load the Name part of the default value which no longer existed.

The only way I could get around this and edit the default again was to insert a row into the lookup using the same Id as the deleted item (the Id for the default value of the lookup column)

Ryan

@Danyil,

Please execute the scenario I explained on 7.17 or upwards and you will be able to reproduce the issue.

Franck

Dear Franck Lehmann, Dear Ryan Farley,

 

Thank you for your patience and your dedication!

 

Please be advised that upon our testing we have confirmed the issue with an infinite loading after changing the Default Value in the Lookup in 7.17.0 version.

 

We may also confirm that this issue has been fixed in 7.17.2 - the system allows you to change the Default Value of the lookup after this exact value was deleted within the lookup list.

 

We appreciate your cooperation!

 

Please do not hesitate to contact the Support Team in case you have any additional questions!

 

Regards,

 

Danyil 

Danyil Onoprienko,

Thanks Danyil. Glad to hear it's fixed!

Ryan

Thanks Danyil, this is a good thing to fix it.

In the meantime you can remove the constraint of integrity in the settings for having the form working without issue.

 

You may get performance issue when trying to change the value several times it happened to us on Cloud.

Glad I helped.

Show all comments