Automate your C/Side to AL Data transfer (Business central v14)

If you plan to read this post you’ve probably already worked or planned to on C/AL to extension convertion. This post does not treat about the base update or AL convertion but only about the data transfert from c/al to extension.

AL extension data are stored differently than C/AL : adding fields on customer table through a “table extension” will produce something like: [dbo.Cronus$Customer$your-extension-guid]

The problem is that Microsoft does not provide any tool to transfer your C/AL table data to extension, and you also can’t have both your c/al and extension customizaiton installed at the same time to do the trick (or rename each object and.

I’m bouncing out from the following very nice post of hougaar, he made an SQL script generator allowing us to do this : Uplift Data from C/Side objects to AL extension script generator

I’ve personnally used it but with bunch of fixes and small enhancments so it’s why i’m writing this post :


Modified version of hougaar Uplift script generator

 – Extension convert to C/AL to avoid the symbol regenration needed for using it
 – SQL script added USE [database name]
 – SQL script handle error when new fields were not include in tableextension target
 – SQL script fix bug on table name, lowercase+ remove { } of extension GUID
 – SQL script using CRLF instead of LF for better visibility
 – Extension & fieldfilter parameters are no more cleared when selection is changed
 – Add some verifications for script generation
 – Subpage showing standard table new fields to migrate

Prerequisite

  • Business Central V14 database with your C/AL solution
  • Admin. right on the database
  • Your C/Side solution migrate to Extension ready to publish
  • Your extension must match the fields and tables names of the original C/AL. The transfer is based on table/field names, not id.
  • Uplift Data Cside to AL script generator, text source :
    .zip Uplift-SQL-Data-CSide-to-AL-1.2

Methodology

  1. Install the uplift script generator page and run it (run the 99990 page from development environment)
  2. Set your target extension GUID (available in your app.json)
    Then select the new table created by your C/AL solution you want to migrate
    Select the fields no. range added on standard table. The found result will shown in sub page
  3. Use “Generate SQL Script”, the page will generate 3 different SQL script to be executed
  4. Execute the first SQL script, this will move all C/AL solution specific data to temporary tables. The syntax checker will not recognize the renamed table select it’s totally normal.
  5. Remove all your C/AL new tables previously selected
    Remove your C/AL new field on std. table :
    import cronus Fob to erase them or remove them manually
  6. Regenerate your base symbols references with finsql command HERE or import the standard symbol from the BC installation DVD\ModernDev\[…]\System.app with
    Publish-NavApp cmdlet and option -PackageType SymbolsOnly
    Check “EnableSymbolLoadingAtServerStartup” is well enabled on your service
    Restart your service
  7. Execute powershell cmdlet Sync-NAVTenant -ServerInstance <BC14> -Mode forceSync
  8. You have now a clean standard v14 base with temp table containing your solution data
    Publish and install your extension solution (please restart your service again if any error occur)
  9. Execute the second SQL Script to import the temp table data into extension table
    If your extension have less fields than original C/Side solution, some SQL update will have to be modified manually in order to remove them (didn’t find easy SQL command to avoid this problem)
    Check if your data is accessible from BC user interface
  10. If all is done as expected, you can remove temp table with the third SQL script and remove the uplift pages 99990 and 99991.

Feel free to use or contriubute

11 Comments

    • Ok thank you. Basicaly they are new subpage, some SQL script modifications and sécurity check before script generation and extension converted to c/Al to avoiding symbol reloading

  1. Thank you for your enhancement.
    I’ve got one dude:
    I need to export data from BC14 to BC15. In order to get it, i have to install in BC15 the extension created in BC14 ( with data), haven’t I?

  2. Hi Maxime,
    I upgrade from NAV2018 to BC15, with the BC14 upgrade step in between. I already have upgraded my solutions to BC15 Extensions. Can I use the SQL scripts to move the data to temporary tables, upgrade the solution to BC15, then publish my extensions and only then use the SQL script to move the data back? That way I would avoid downgrading my BC15 extensions to BC14.

    • Czesc Jacob

      So you have a v14 base with C/AL + data and a v15 with extension no data ?
      In this case as you said you can
      1 Execute the transfert script on the v14
      2 Remove your C/AL personalizations on the v14
      3 Invoke database conversion powershell to v15
      4 Install your extension
      5 execute the transfer script and the clean-up script

      I havn’t tried this but it should work as well for you.
      Please, remember to remove you personnalizations on the v14 before invoking the database conversion to v15, or you should be hit by conflicts when installing your extension.
      Powodzenia

  3. I have used the tool, but I always get the error “Incorrect use of the where parameter”.
    Have you any ideas for this problem?

    • Is this SQL or Nav error ? If SQL please double check the statement before executing it, it can be caused by escape character from field name or table name.

  4. The “Incorrect use of the where parameter” is raised in the C/Al code at the SQLTable function at the DELCHR.

    The two last parameters of the DELCHR is reversed.
    WRONG: DELCHR(LOWERCASE(FORMAT(ExtensionGUID)), ‘{}’, ‘=’)
    CORRECT:DELCHR(LOWERCASE(FORMAT(ExtensionGUID)), ‘=’, ‘{}’)

    The ‘=’ should be second. I have reversed those and code completed succesfuly.
    I haven’t tried the sql scripts on actual scenario yet tho.

Leave a Reply to Piedi Blanco Cancel reply

Your email address will not be published. Required fields are marked *