AcumatiCares
INTRODUCTION
Some time ago, I was given the task to analyze the requirement to delete a warehouse that had been created during an implementation. The customer did not want to use the defined warehouse anymore. But unfortunately, because it had been used for a few test transactions, it could not be deleted (due to obvious referential integrity) even though it was “empty” as far as the user could see.
Right off the bat, you can see that this request is much more complex than what the customer had asked for. In this case, not only do we have to delete this warehouse, but in order to do so, we must also do the following:
- Delete some of the children of the warehouse (for example, warehouse locations) that are not needed anymore;
- Update some of the tables using the warehouse as a foreign key (some transactional rows) to replace the warehouse ID with another warehouse ID to simulate the fact that the warehouse ID was never “transacted”;
- Aggregate some statistical tables using the warehouse as part of the primary key to simulate that the warehouse ID was never used in such statistical calculations (such as Total Qty On Hand by item/warehouse). Now, this might need a different solution for different tables, but at least it would allow for the warehouse ID to disappear without causing harm (for example, a left-over Qty On Hand must be added to another warehouse).
Also, you may notice that this process requires us to select another warehouse that will become the target or beneficiary of our updates when needed. In this case, the warehouse SiteID to delete was 36 and its target was 4.
Now, even if the database structure is familiar to you, it is not necessarily an easy job to find all the references to the warehouse value – even if you know (or you think you know) all of the tables. Therefore, in order to save time searching for the rows, I decided to create a dynamic SQL query that would help me find any field with a certain name pattern (SiteID is the warehouse internal field value) and with a given value (36). Now, this script is not purely an Acumatica script, per se. This is because of Acumatica’s natural and standardized table/field nomenclature; it becomes possible to use the kind of script.
First, we need to find all tables having a SiteID. Obviously, in some cases, the field could be named OriginSiteID or DestSiteID, but you get the idea.
Find the Field in All the Tables
GIST: https://gist.github.com/ste-bel/d37daa37915b6ec35f6bbb84e9bb5965
Here’s a screenshot of the results (partial) after running the above script:
Next, I wanted to build a dynamic query that would search all the rows of all the tables I have found in the previous result. Using this query, I wanted to generate update and delete scripts for each table and field.
Declare a virtual table and some variables
GIST: https://gist.github.com/ste-bel/b2e13aab2e6a5fe3fd0169e2dfb2c70d
Build the dynamic query and generate scripts
GIST: https://gist.github.com/ste-bel/5c6609b2a6dee84b491b2b86d471e4c3
Finally, look at the result of the found values and update/delete scripts generated:
GIST: https://gist.github.com/ste-bel/2d4b5f3f6cf434d7fe1ad172d408c233
SUMMARY
This is obviously not merely a quick fix for what appears to be a simple problem. However, I think that this is a great tool for consultants, support engineers, and developers to search for problematic/leftover data. We have also successfully used it with ScreenID, BOMID, InventoryID, CustomerID to fix all kinds of problems. Additionally, it can be used to recode primary keys that are used by foreign keys which were strictly strings without an underlying integer key (such as ShipVia, TermsID).
It saved my life, if you will, quite a few times over the years. I hope you find it useful as well, saving you and your colleagues a lot of time and effort in the future.
Happy Coding!
