Passer au contenu principal


Adaptive Insights French

How to use Integration to update Account codes and/or names

Adaptive Integration can be used to keep your chart of accounts in sync with a source system or database (getting that setup will require paid professional services support).  It can also be used if you need to do a mass update to your accounts in Adaptive Insights using Excel as the datasource. To use this article, you will need to know how to do a couple things in Excel that we will not detail here (trim spaces off a string and vlookups).  A basic knowledge of Adaptive Integration is also required. This only works for General Ledger accounts, custom or metric accounts would need to be updated manually. If you have less than 100 accounts to update, you will find that it is much faster to just manually update them.

  1. Login to your model (must be an administrator), navigate to Model Management > General Ledger Accounts.

  2. Click the “Generate printable view” icon to export your current set of accounts to Excel.

  3. Make the following edits to the file, then save the file.

    1. Delete the top 3 rows.

    2. Scroll to the bottom of your list of accounts and delete the two rows containing the time stamp information.

    3. Delete all columns except for:  Name, Code, Rolls up to (optional, you could keep Short Name if you wish to update that information). 

    4. The names in the Name column by default have blank spaces padded to the front of them that need to be removed.  The basic steps are:

      1. Add a new column.

      2. Add for formula to the new column to trim the spaces from Name column (=trim(a2) for example).  

      3. Copy the formula to all rows.

      4. Copy and Paste Special Values from the calculation results over the values in the Name column.

      5. Remove the extra column.

    5. Add 2 new columns:  SourceID, ParentID. It will be easier if you add them after the Name column.

    6. SourceID:  This column will be used for a unique ID number for each account.  Starting with the first account, populate the SourceID column by numbering the accounts starting with 1.

    7. ParentID:  Specifies the new unique ID for the account that the current account rolls up to.  For these values we used a vlookup (=vlookup(E1, Acct_namedrange, 2, false) for example) checking the value from the Rolls up to column getting the SourceID based on the Name (created a table out of the Name and SourceID columns).

      1. For the Root accounts that result in a #N/A, delete the values leaving them blank.

      2. Copy and paste special values the results of the vlookup to the ParentID column.

  4. Sample file:

    NOTE:  This example is setup to update all GL Accounts at once as such all the Root accounts are included.  If you focus on one type of accounts, for example Expenses, there is one tweak to the file that is needed.  You would need to remove the row with the Root account from file.

  5. Need to use the existing account setup to create a relationship between your new file and your model, DON’T CHANGE ANYTHING IN THE EXISTING NAME OR CODE COLUMNS yet.  

    1. Navigate to Integration > Data Designer.

    2. Create a new Spreadsheet data source, name it.

      1. Click “Import spreadsheet” and browse for the file you created in Step 3. Click “Import”.

    3. Create a new Planning Account Loader, name it.

    4. Data Source Settings:

      1. Source Table:  Set to the tab in your Excel file.

      2. Planning Account:  Select GL Accounts as we are updating all accounts.

      3. Adjust the other settings as needed.

    5.  Column Mapping:

      1. Parent Id = ParentID column

      2. Account Name = Name column

      3. Account ID and Account Code = SourceID and Code columns respectively

      4. Map any other columns you kept for updating.

    6. Click Save, then click “Preview loader output”.  If there are no errors then your loader is ready to be run, either manually run it or create a task and run it to create the relationship between the file and your model.

  6. Now you can mass update the data in your Excel file to the updated values, you can update Codes, Names, and any other info you kept.  Once the file is updated just re run the Task/Loader created in Step 5. Once complete verify that your model reflects the updated information.


  • Cet article vous a été utile ?