'Syntax Error or Access Rule Violation detected by database' when trying to change field type

[Magda13]

Hello All,

I encountered an error when I tried to change the type of 'Order' field on service_offering table from string to integer.

It doesn't make sense that this is a string field OOTB and my client is using it to set the correct service offering for incidents. They sort the service offerings in descending order, but it's a string so it sorts lexicographically.

I have managed to change the type in dev and test instances without any issues, but encountered this error when attempted the change in prod:

The order field has been cleared of any values beforehand.

Any ideas? Thank you in advance!

[Musab Rasheed]

Screenshot is blurred can you copy paste error you are getting.? BTW it's not a best practice to change OOB setup

[shloke04 · Accepted solution]

Hi @Magda 

Couple of things to mention here:

1) This might happen because there are still records with values in this Order field in Production and will not allow to modify the field Type here. Can you double check on this point and share any screenshots to make sure.

2) Also, would like to suggest since your client want to store the order as an Integer, my suggestion would be not to update or modify the Field Type of an OOB field/attribute, instead you can follow the below steps for your scenario:

a) Create a new Integer type Field on Service Offering Table and name it as Order(custom_order) . Column Name should be different to OOB as it will not allow the same name to be inserted, how ever you can keep the same Name.

b) Now run the below Script in Background, which will copy the existing value from Order field if any you want to do to your new field created

Sample script shared:

var gr = new GlideRecord('service_offering');
gr.addEncodedQuery('orderISNOTEMPTY'); // You can modify this query as you need
gr.query();
while(gr.next()){
gr.New_FIELD_NAME = gr.order;
gr.setWorkflow(false);
gr.update();
}

c) Now if needed you can simply deactivate the OOB field Order to avoid any confusion.

Hope this helps. Please mark the answer as correct/helpful based on impact.

Regards,
Shloke

[shloke04]

@Magda 

Glad to see you marked my answer as helpful. If your query is resolved, please mark the response as correct and close the thread for others.

Regards,

Shloke