Solved

Invalid Column Name when I try to open screens

  • 14 September 2022
  • 5 replies
  • 560 views
aaghaei
Rank
Userlevel 7
Badge +8

Hello everyone,

I had some fields that I needed to rename to comply with Acumatica’s Prefix Validation Standard.

  • I Unpublished the customization
  • The fields are renamed in SQL Database and now have the new names.
  • Custom Scheme fields are updated accordingly in the customization project
  • The DAC is updated accordingly in my C# project and any dependent events as well.
  • ASPX DataFields are updated accordingly in the customization project
  • The Project is compiled and the project is updated with the modified dll and published without any error.

When I look into the CstPublished folder and open the customized screen, I see the new names are in the file, and NONE of the names which are raising errors exist. But when I try to open the Project screen and I get the invalid column error. For testing when I add any of the columns to the SQL DB, the error gets resolved. Long story short I do not have these fields that the system is picking on them neither in my C# project nor pages aspx but somehow the system is picking on them. Oh, I cleared the cache, restarted the website, and even restarted my computer but no luck. What should I do to resolve this issue?

 

See below screenshot for the error. all UsrCF are renamed to UsrHCL as mentioned above:

 

 

icon

Best answer by aaghaei 14 September 2022, 22:51

View original
Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

5 replies

Brian Stevens
Rank
Userlevel 6
Badge +4

This error message occurs when your code refers to fields that it cannot find in the database, so it seems your field names make have been reverted back to the original names when you published your project.  Check your database to be sure the column names are still correct.  Basically, that message indicates they are not in the database that way anymore.  If you did not update the database scripts to use the new field names, then publishing the project would have reverted your database field name changes back to the original names.  I have made that mistake a few times myself, so now I just change my field names directly in the project.xml via the customization project screen if I’m designing the data/screen.  If I need to rename an existing field with data already in it, I either use a database script in the project to rename the field or make the change in SQL directly and then remember to use “Reload from Database” before publishing.

 

 

Changing the world, one byte at a time - https://bstevens.net/ (formerly acumatica.dev)
aaghaei
Rank
Userlevel 7
Badge +8

Thank you @Brian Stevens I have done the same way you explained. Here is the search result snip in my project that shows zero UsrCF in my project xml file. All custom fields on the page are UsrHCL… as well. Something else happening because even when I unpublish all my custom projects, still I get the same error. 

 


 

 

 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Brian Stevens
Rank
Userlevel 6
Badge +4

When your aspx page asks for a specific fieldname, you get the error because it cannot find that field.  If you open the Project.XMLdirectly in the customization project, you can rename the fields… assuming there isn’t a reason they should have begun UsrHCL in the first place.

 

 

For existing tables that you have added fields, you can find them by table name like this and edit the column name…

For new custom tables, you can find them like this and edit the column name...

 

Assuming you have data in them already, you might search for UsrHCL in the Project.XML file to see if there was another database script somewhere that is adding those fields.  If you want to retain the data in those fields, you NEED to rename them with a SQL script in the project or directly in the SQL server.  Otherwise, the old fields will be dropped and the new (empty) fields will be added.

Just be careful that you don’t lose the data in the old fields.  There are several ways to rename the fields without data loss, but if the instance is cloud based then you can’t do direct SQL and have to add custom scripts in the project.

In my case, I would rename the existing fields directly in SQL and then make sure Project.XML is updated with the new field names so the new field names are retained.  Ultimately, the aspx files must point to the name of the field in the DAC which must have a matching field in the database if they are bound fields.

Changing the world, one byte at a time - https://bstevens.net/ (formerly acumatica.dev)
aaghaei
Rank
Userlevel 7
Badge +8

Thanks, @Brian Stevens  All your points are valid but not the reason in my case as I had considered them all when I did the rename. As I mentioned even when I didn’t have any customization published, still I would receive the same error that doesn’t make any sense. As I thought something else was messed up but I really couldn’t figure out what was it. I just uninstalled and reinstalled the application and everything is fixed now. Thank you for your time and explanations. 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Tony Lanzer
Rank
Userlevel 4
Badge +2

I ran across this post when trying to resolve this issue for an instance myself.  The only way I was able to resolve it was to rebuild the instance unfortunately.  So apparently this is still an issue in 23R2.  I’m guessing there’s some embedded code in the db somewhere that won’t clean up itself when all of the fields and all referenced code has been updated with the renamed fields. 

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved