{"id":65315,"date":"2024-01-18T15:52:35","date_gmt":"2024-01-18T15:52:35","guid":{"rendered":"https:\/\/arquiconsult.com\/?p=65315"},"modified":"2024-10-23T10:35:11","modified_gmt":"2024-10-23T09:35:11","slug":"bc-extension-records-manipulation-on-installing-apps","status":"publish","type":"post","link":"https:\/\/arquiconsult.com\/en\/bc-extension-records-manipulation-on-installing-apps\/","title":{"rendered":"#045 BC EXTENSION RECORDS MANIPULATION ON INSTALLING APPS"},"content":{"rendered":"
\n

Every table extension in Business Central corresponds to a different table in SQL database.<\/p>\n<\/div>\n

 <\/p>\n

For example:<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n
Customer table in SQL from the Base App<\/strong><\/td>\nCustomer extension table in SQL from the other app<\/strong><\/td>\n<\/tr>\n
No.<\/td>\nNo.<\/td>\n<\/tr>\n
Name<\/td>\nQQQ Field 1<\/td>\n<\/tr>\n
Address<\/td>\nQQQ Field 2<\/td>\n<\/tr>\n
Address 2<\/td>\n<\/td>\n<\/tr>\n
Post Code<\/td>\n<\/td>\n<\/tr>\n
\u2026<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

When installing an app with table extensions, a delete and insert command is executed for each table extension of the app being installed.<\/p>\n

 <\/p>\n

Why does this happen?<\/strong><\/p>\n

This information is valid for BC21 and BC22.<\/p>\n

Other versions may exhibit different behavior, but with similar effects.<\/p>\n

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

 <\/p>\n

— DELETE FROM Extension Table if it doesn\u2019t exist in the Base App.<\/strong><\/p>\n

DELETE FROM < company >$< table >$QQQAppGUID<\/p>\n

WHERE NOT EXISTS<\/p>\n

(<\/p>\n

SELECT 1<\/p>\n

FROM < company >$< table >$BaseAppGUID<\/p>\n

WHERE<\/p>\n

< company >$< table >$QQQAppGUID.< PK > = < company >$< table >$BaseAppGUID.< PK ><\/p>\n

)<\/p>\n

 <\/p>\n

— INSERT INTO Extension Table to match the same rows of the base app.<\/strong><\/p>\n

INSERT INTO < company >$< table >$QQQAppGUID (< PK >, QQQAppColumns)<\/p>\n

SELECT < PK >,QQQAPPDefaultValues<\/p>\n

FROM < company >$< table >$BaseAppGUID (SERIALIZABLE)<\/p>\n

WHERE NOT EXISTS<\/p>\n

(<\/p>\n

SELECT 1<\/p>\n

FROM < company >$< table >$QQQAppGUID<\/p>\n

WHERE<\/p>\n

< company >$< table >$QQQAppGUID.< PK > = < company >$< table >$BaseAppGUID.< PK ><\/p>\n

)<\/p>\n

 <\/p>\n

Where:<\/p>\n

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

    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.<\/p>\n

    Records in the extension table are inserted or deleted based on their existence in the base app, as seen above.<\/p>\n

    Please consider the following example of usage in the Sales Header table, to understand the need of the synchronization:<\/p>\n\n\n\n\n\n\n\n\n\n\n
    Assumption<\/strong><\/td>\nSQL Sales Header Table Base App<\/strong><\/td>\nSQL Sales Header Table Extension<\/strong><\/td>\nSQL behavior<\/strong><\/td>\n<\/tr>\n
    1. New clean database where only Base App is installed.<\/td>\nNo rows<\/td>\nNo rows<\/td>\n<\/td>\n<\/tr>\n
    2. Insert Record 1 and Record 2.<\/td>\nRecord 1<\/strong><\/p>\n

    Record 2<\/strong><\/td>\n

    		No rows<\/td>\n<\/td>\n<\/tr>\n
    3. Publish and install QQQApp that extends Sales Header table.<\/td>\nRecord 1<\/p>\n

    Record 2<\/td>\n

    		Record 1<\/strong><\/p>\n

    Record 2<\/strong><\/td>\n

    		The system inserts into extension tables to match same rows of the Base App.<\/td>\n<\/tr>\n
    4. Insert Record 3.<\/td>\nRecord 1<\/p>\n

    Record 2<\/p>\n

    Record 3<\/strong><\/td>\n

    		Record 1<\/p>\n

    Record 2<\/p>\n

    Record 3<\/strong><\/td>\n

    		The system inserts into extension tables to match same rows of the Base App.<\/td>\n<\/tr>\n
    5. Uninstall QQQApp that extend Sales Header.<\/td>\nRecord 1<\/p>\n

    Record 2<\/p>\n

    Record 3<\/td>\n

    		Record 1<\/p>\n

    Record 2<\/p>\n

    Record 3<\/td>\n

    		The system keeps all the records in the tables (base and extension).<\/td>\n<\/tr>\n
    6. Delete Record 2.<\/td>\nRecord 1<\/p>\n

    Record 3<\/td>\n

    		Record 1<\/p>\n

    Record 2<\/strong><\/p>\n

    Record 3<\/td>\n

    		Since QQQApp isn\u2019t installed. Record 2 isn\u2019t deleted from extension table.<\/td>\n<\/tr>\n
    7. Re-install QQQApp that extend Sales Header.<\/td>\nRecord 1<\/p>\n

    Record 3<\/td>\n

    		Record 1<\/p>\n

    Record 3<\/td>\n

    		Record 2 is deleted from extension table if doesn\u2019t exist in Base App.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    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.<\/p>\n

     <\/p>\n\n

    \n \t\n\t
    \n\t \t\t
    \n\t\t\t
    \n\t\t\t \t
    \n\t\t \n
    \n
    \n