Monday, 30 November 2015

Configuring Own Snapshots

Hi Everyone,

As you know Acumatica supports Snapshot functionality to copy, migrate, backup and restore data between one or multiple instance of Acumatica. Using snapshots you even can migrate data between different database types (Cloud, Microsoft Sql Serve, MySql).
You can get more about snapshots from this video - Acumatica Snapshot on YouTube

When you creating snapshot, system will go through all database tables, select appropriate data and put it into separate tenant (with negative tenant id, which is actually linked to original tenant by id number).
You can define what data will selected from the database, by defining Export mode:
Snapshots is database level technology that knows nothing about business logic and other stuff. System will just go through all specified tables and copy all data with no restrictions and no validation.
But you can define your own configuration for snapshots engine.

All snapshots configurations are stored on the file-system withing Acumatica site instance - "C:\Program Files (x86)\Acumatica ERP\<Your Site Name>\App_Data\SnapshotConfigs\"

If you open the simplest file - "Full except Attachments.esc", you will see this XML code:
<Tables fill ="True">
<Exclude  table="UploadFile" />
<Exclude  table="UploadFileInScreen" />
<Exclude  table="UploadFileRevision" />
<Exclude  table="SearchIndex" />

Some points here:

  • Tag "Tables" - is tag where you should specify all table that system needs to process. 
    • To include or exclude tables from this list you can use appropriate child tags (< Include/>, <Exclude/>) with specifying required table name. 
    • By default list of tables is empty and you need to include it one by one. But you can specify fill ="True" attribute and system will preload this list from the database, in this case you need to exclude unwanted tables. 
    • Acumatica uses "exclude" way in all configs to support your custom tables in snapshots by default.
  • "Exclude/Include" tags are used to specify tables that should be processed by Snapshots engine. 
    • You always have to specify the name of table like it is named in the database.
    • System will process this list of tables one by one and if you specify exclude for some table, but after some line will include it back, than in the result this table will be processed.
    • Some times, if you need apply restriction conditions on selected data, you can use "condition attribute". <Include  table="FixedAsset" condition ="where RecordType = 'C'" /> - in this example, before selecting data system will add additional "where" restriction to generated SQL. Here you can use "where" and "join" conditions that are written based on T-SQL language, but the platform will automatically convert it to MySql as well.
  • "ResetColumn" tags are used to modify data during export of the snapshot. You should specify a column name and the new value. During export operation system will automatically handle these rules and apply all changes. Values can:
    • Be a constants - value="NULL"
    • Refer to other field - value="[StartNbr]"
    • Can contains conditions - value="[StartNbr]@NumberingID!=TMCONTRACT"
    • Can contains some formula - value="TrimToOrig(00000000000000000000000000)" - in this case system will adjust new number to length of original one.

If I create new file and just put in the same folder, I will be able to use it in Acumatica.

Here file "Access Rights.esc" contains only information about access right tables that you can export from Acumatica and use for easier implementation next time.
<Include  table="Users"/>
<Include  table="Role" />
<Include  table="UsersInRoles" />
<Include  table="RolesInGraph" />
<Include  table="RolesInCache" />
<Include  table="RolesInMember" />

No comments: