Best practice for deploying projects subsequent to making table changes

  • 12 May 2023
  • 8 replies
  • 54 views
Joe Schmucker
Rank
Userlevel 6
Badge +3

I have a project that has evolved over the years.  This project is only for use by one client. 

For example, when I first deployed the project, table SSGNCR had 10 columns.  Over time, I’ve added fields and even changed field sizes/types.

I now have this project with the original Custom Table Schema and a bunch of DB scripts that update the original schema.

I had a case this week where the client went to publish an update to the project and they accidentally published a previous project.  Somehow, this deleted data from some of the fields in the table.  I am assuming one or more of the sql scripts was run even though it had already been applied.  To change a field size, there are scripts that create a temp column, drop the original, recreate the column with the new size, copy the data back into the revised column, and delete the temp column.  This may have been the culprit.  We are now trying to recover the lost data from those fields.  I am somewhat surprised that any of the previous scripts would have been run as I think they would be seen as already applied, but I’ve not published old projects overtop of new ones.  

I would like to simply delete all the Custom Table Schemas and DB Scripts and re-add Custom Table Schemas with what is currently in the DB.  “Nice and clean”.  

What do others do in cases like this?  Do you do “clean up” on tables and scripts?  

  

Joe Schmucker, CPA

8 replies

D
Rank
Userlevel 7
Badge +5

I would be tempted to clean up the project as you suggested. The other thing that I would likely do would be to keep my original project with the custom table up to date and put the SQL scripts into a separate project so I can run it once and then delete it.

 

Joe Schmucker
Rank
Userlevel 6
Badge +3

@Django FANTASTIC idea.  Thank you!

Joe Schmucker, CPA
aaghaei
Rank
Userlevel 7
Badge +8

@Joe Schmucker If I were to manage this kind projects that requires DB Objects manipulations. I would add a table with only one int column to keep the package version. At the beginning of running my DB Scripts I would always get the value from DB for this field and execute my block of code if the criteria would match other wise I wouldn’t and at the end of my customization package I would always update this hidden table single column with my latest version number. This way because that table always keeps a single record that represents the latest published version, and you check that value in scripts, then older scripts won’t be executed. You can have everything in one package as well.

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*.
Joe Schmucker
Rank
Userlevel 6
Badge +3

@aaghaei That’s sounds like a great idea.  Would all of this be done within the SQL script itself?

Joe Schmucker, CPA
aaghaei
Rank
Userlevel 7
Badge +8

@Joe Schmucker yes

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*.
aaghaei
Rank
Userlevel 7
Badge +8

@Joe Schmucker if you need help with that we can have a quick Teams call

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*.
Joe Schmucker
Rank
Userlevel 6
Badge +3

@aaghaei I’m fairly savvy with SQL.  I think I can do what you suggested.  I didn’t know if there was some kind of functionality in the publish process such as a “pre publish” function.  When the project is being published, there are all kinds of “actions” occurring and I thought maybe there was some way to add my own custom “action” into that process.  If it is just being done in SQL, I think I got that part ok.

Thanks so much for the suggestion.  

Joe Schmucker, CPA
aaghaei
Rank
Userlevel 7
Badge +8

@Joe Schmucker Great,

I am sure you know how to add SQL Scripts to the Customization Projects. So the SQL script in question will live inside your Customization Package.

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*.

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