#045 BC EXTENSION RECORDS MANIPULATION ON INSTALLING APPS

 

For example:

Customer table in SQL from the Base App	Customer extension table in SQL from the other app
No.	No.
Name	QQQ Field 1
Address	QQQ Field 2
Address 2
Post Code
…

When installing an app with table extensions, a delete and insert command is executed for each table extension of the app being installed.

 

Why does this happen?

This information is valid for BC21 and BC22.

Other versions may exhibit different behavior, but with similar effects.

When installing an app with table extensions, SQL commands like the following will be executed. This syntax has been simplified for better readability.

 

— DELETE FROM Extension Table if it doesn’t exist in the Base App.

DELETE FROM < company >$< table >$QQQAppGUID

WHERE NOT EXISTS

(

SELECT 1

FROM < company >$< table >$BaseAppGUID

WHERE

< company >$< table >$QQQAppGUID.< PK > = < company >$< table >$BaseAppGUID.< PK >

)

 

— INSERT INTO Extension Table to match the same rows of the base app.

INSERT INTO < company >$< table >$QQQAppGUID (< PK >, QQQAppColumns)

SELECT < PK >,QQQAPPDefaultValues

FROM < company >$< table >$BaseAppGUID (SERIALIZABLE)

WHERE NOT EXISTS

(

SELECT 1

FROM < company >$< table >$QQQAppGUID

WHERE

< company >$< table >$QQQAppGUID.< PK > = < company >$< table >$BaseAppGUID.< PK >

)

 

Where:

• < company > = Company Name. The process repeats for every company in the database.
• < table > = Base table that has been extended.
• < PK > = Primary key of the table. This could be more than one column, but for readability, the example shows only one column.
• BaseAppGUID = GUID of the Microsoft base app or the app being extended.
• QQQAppGUID = GUID of the app being installed.

In summary, these SQL commands ensure that the data in the extension table aligns with the corresponding data in the base app, and unnecessary records are deleted from the extension table.

Records in the extension table are inserted or deleted based on their existence in the base app, as seen above.

Please consider the following example of usage in the Sales Header table, to understand the need of the synchronization:

Assumption	SQL Sales Header Table Base App	SQL Sales Header Table Extension	SQL behavior
1. New clean database where only Base App is installed.	No rows	No rows
2. Insert Record 1 and Record 2.	Record 1 Record 2	No rows
3. Publish and install QQQApp that extends Sales Header table.	Record 1 Record 2	Record 1 Record 2	The system inserts into extension tables to match same rows of the Base App.
4. Insert Record 3.	Record 1 Record 2 Record 3	Record 1 Record 2 Record 3	The system inserts into extension tables to match same rows of the Base App.
5. Uninstall QQQApp that extend Sales Header.	Record 1 Record 2 Record 3	Record 1 Record 2 Record 3	The system keeps all the records in the tables (base and extension).
6. Delete Record 2.	Record 1 Record 3	Record 1 Record 2 Record 3	Since QQQApp isn’t installed. Record 2 isn’t deleted from extension table.
7. Re-install QQQApp that extend Sales Header.	Record 1 Record 3	Record 1 Record 3	Record 2 is deleted from extension table if doesn’t exist in Base App.

Take into account that the more records there are in the base table, the longer the process of synchronizing records between the extension tables and the base table will take.