Reallocation of Child Data: Moving child records to link to a new master record
An Orixa App contains links between "master" and "child" records. For example a Staff record might be linked to multiple Projects, or a Farmer record might be linked to multiple Deliveries and Inspections. In some situations it may be useful to reallocate child records. For example a staff member might leave, and their projects might need to be managed by a new person, or a new Farmer might take over the work of an old Farmer, and the Deliveries and Inspections might need to be "switched" to the new Farmer. Manually changing the link between multiple records is time-consuming and slow.
Your administrator can run short SQL Scripts to automate this process.
If this is something that needs to happen regularly, a Procedure can be created and added to your App to fully automate the process.
The steps required for these processes are explained in this article.
Reallocating data explained
Master - Child Data example: Farmer, Deliveries, Inspections
- An example Farmer "Akosua Boatemaa" has been located.
- Large numbers of Deliveries records are linked to this farmer, showing produce the farmer has delivered.
- The Farmer also has multiple "Inspections" records linked to them, detailing times that staff have visited the farmer to check their farming operations.
The aim of the process is simple: To re-link all the Child "Deliveries" and "Inspections" records to a new Farmer record, all that is needed is to swap out the "Old-ID" and replace it with a new one.
Technical details of linkages
In Orixa all data is linked on simple "integer" IDs. In the Database different data-tables use foreign key fields to allow data from one table to be linked to sets of data in other tables.
Orixa implements a very simple standard for this process. If a "Deliveries" table lists deliveries from Farmers, then the Deliveries table should include a "FarmersID" field, which will link to the ID of the farmer. In a few cases the "Link-ID field-name" will differ, but usually the "master" table-name plus "ID" is used.
|
Checking the existence of the foreign key in Child data |
How to determine the names of the foreign-key fields To reallocate data from a child-table to a new master record we will update the value of the foreign-key field. Before doing this we must carefully determine the field we want to update.
|
|
Checking which tables contain Child data for a master record |
How to determine which data-tables are children of a master record.
|
|
The Master ID Value |
How to find the Master-ID you need to update. In Orixa the master ID is displayed at the top of every edit window.
|
SQL Script to reallocate records
The fundamental form of the SQL is extremely simple:
UPDATE [Child-Table-Name]
SET [ID-Field-Name] = [New-ID-Value]
WHERE [ID-Field-Name] = [Old-ID-Value]
To undertake the process the admin must know:
- The list of Child tables to be updated.
- The list of ID-field-names
- The Old and New IDs.
Then a SQL Script can be written to fulfil the needs of the update process.
The administrator may choose to just write this script and save it to run from time-to-time to occasionally reallocate records, or they may want to convert it into a Stored Procedure they can access, or write the Stored Procedure and surface it into the App so other users can access it.
Example Script
|
Master - Child data example |
Steps:
Example SQL:
UPDATE Deliveries SET |
Example Stored Procedure
If a process needs to happen regularly, it should be automated with a Stored Procedure. a DESCRIPTION can be added to any stored procedure to surface it in the Orixa App where users can access it.
The following SQL shows a stored procedure which takes 2 parameters ("Old-ID" and "New-ID") and which reallocates records for farmer-deliveries, loans and inspections from the old to the new farmer. The description surfaces the procedure into the Orixa App. Note that staff will still need to be trained to use the procedure, and shown how to find the "Old-ID" and "New-ID" values. The Description can also be used to set a "SecurityLevel" so that only staff with a certain level of clearance can access the procedure.
|
SQL to create a procedure to reallocate the Deliveries, Inspections and Loans data |
Details of the stored procedure
For details of how to write DESCRIPTIONS to surface procedures in different parts of an Orixa App read the following link: System Actions: Stored Procedures surfaced in the App
|
Example SQL
CREATE PROCEDURE "ReallocateFarmers" (IN "aOldID" INTEGER, IN "aNewID" INTEGER)
BEGIN
DECLARE Crsr CURSOR FOR Stmt;
DECLARE TableName VARCHAR(100);
SET TableName = 'Inspections';
PREPARE Stmt FROM
' UPDATE "' + TableName + '"
SET PersonID = ?
WHERE PersonID = ? ';
OPEN Crsr USING aNewID, aOldID;
CLOSE Crsr;
SET TableName = 'Deliveries';
PREPARE Stmt FROM
' UPDATE "' + TableName + '"
SET FarmersID = ?
WHERE FarmersID = ? ';
OPEN Crsr USING aNewID, aOldID;
CLOSE Crsr;
SET TableName = 'Loans';
PREPARE Stmt FROM
' UPDATE "' + TableName + '"
SET PersonID = ?
WHERE PersonID = ? ';
OPEN Crsr USING aNewID, aOldID;
CLOSE Crsr;
END
DESCRIPTION
'[Properties]
Type=Record
Title=Reallocate Farmer Deliveries, Inspections and Loans
LinkTable=Farmers
UserMessage=Before running, ensure you have the "Old-ID" and "New-ID" for the farmers you want to change.
CheckFirst=true
SecurityLevel=40
ImageIndex=265'