User Documentation for File-Based Integrations

Introduction

If your accounting system does not offer an API, Martus supports a File-Based Integration that allows you to:

• Define the accounts and dimensions to be used in Martus.
• Set up and maintain an Account Mapping.
  1. For systems that do not have a natural chart of accounts, and for systems with segmented account numbers that assign specific accounts to specific dimensions, this mapping translates your accounting system’s chart of accounts to Martus accounts and dimensions. Additionally, this supports reverse mapping (automatically assigning the accounting system account number to each budgeted item in Martus.)
• Periodically import GL transactions to sync Martus to your accounting system. As part of implementation, Martus will define a mapping for the GL transaction file so that you can use a standard output from your accounting system for these updates.
• If using the Fund Balances Report feature of Martus, periodically import a trial balance for restricted and designated fund balances. As part of implementation, Martus will define a mapping for the trial balance report file so that you can use a standard output from your accounting system for these updates.
• Take advantage of all Martus features for creating and maintaining budgets, and all Martus features for reporting budget vs actuals.
• Build your budget in Martus and use the standard Martus capability to export it. For some accounting systems (Shelby Next, Sage 100, and Sage 300), Martus also provides a custom export in a format suitable for import directly into your accounting system.
• Martus has an API-based integration with FE NXT. However, because FE NXT’s account number is segmented, Martus’ integration with FE NXT makes use of the Account Mapping structure. Although users in an FE NXT instance will never need to update the Account Mapping, Martus super users can see the Account Mapping menu selection and view the account mapping data.
  
  

Uploading and Syncing GL Transactions into Martus

• To keep Martus’ accounting data current, you’ll periodically need to extract transactions from your accounting system and import them into Martus.
  There must be a separate transaction file for each month, and that file must contain all the transactions for that month. 
• If importing activity for the current month, be sure to include all transactions to date in that month. 
• The upload process will use the data in the file to replace 100% of the data in Martus for each month synced. 
• For example, if you were to upload a transaction file for a prior month with just three corrected transactions in it, that’s all that would be shown for that month in Martus. 
• Similarly, if you were to upload a file for the current month with just the most recent week’s data, that’s all that you’d see in Martus.
  
  

Requirements for the Format of the Transaction File  

• File must be saved as an 'xlsx' excel file 
• File must be saved with the name of either
  • Transactions_yyy_mm.xlsx
  • Transactions_yyyy_mm company_name.xlsx, (company name is optional)
  • For example, the file for Community Church for September 2022 could be named
    •  Transactions_2022_09.xlsx
    •  Transactions_2022_09 Community Church.xlsx
• The headers and data within this file must be in the format agreed upon at the time of implementation of your integration. Generally, this file must contain a single sheet with columns for GL account, date, amount (either signed, or in separate columns for debit and credit amounts), and a memo/description. This file can also include vendor information. 
• For clients with a natural chart of accounts, and for clients who use a segmented account number but also use additional dimensions such as project, this transaction file must also include columns with the appropriate dimension values for each transaction.  
• There must be a header line that identifies the data in each column; at the time of implementation, the column headers are mapped to the appropriate data within Martus.  
• There may be additional headings, report titles, blank lines, total lines, and subtotal lines.

The transaction upload and import process will not work properly if you change the headers within this file or format the amounts differently than agreed during the implementation. 

If at some point in the future you need to change anything about your transaction file format, please review the changes in advance with Martus Support.  There may be a charge for implementing those changes within Martus.

How to Import Transaction Files into Martus 

Follow these instructions to upload and sync properly-formatted GL transaction files into Martus:

1. Select Dashboard > Updater.
2. Use the Upload Files section, browse for your transaction detail file(s), then click Upload.  You can select one or more files to be uploaded at the same time. The upload processes those files and starts the sync for those files.

You’ll see the process running in the Updater Task Queue section.

After you’ve uploaded files, they are listed in the Archived Files section:

Uploading and Syncing Designated Fund Information into Martus

If you are using the Fund Balances Report feature of Martus, you will periodically need to extract a trial balance from your accounting system and import this information into Martus. The file must be saved as an excel file and named: 

• Designated_yyy_mm.xlsx or
• Designated_yyy_mm Company name.xlsx

The headers and data within this file must be in the format agreed upon at the time of implementation of your integration.  The file can contain an additional header. Examples below illustrate trial balance files from ShelbyNext and ACS Realm.

Sample from ShelbyNext Financials

Sample from ACS Realm 

Notes about the Upload and Sync Process

You can upload and sync transaction files and designated funds files as often as you wish.

If you make changes to transaction data in your accounting system (reclass an expense, change a transaction’s description, or add activity in a prior month), you will need to generate a new transaction file from your accounting system for that month and upload it into Martus to ensure the accuracy of the data that your end users are relying upon.

Errors in the Sync Process

If you try to upload a file that contains an account number or any other dimension value that is not defined or properly mapped in Martus, an error message will be displayed on the Updater screen.

Add that appropriate data to Martus. Then, use the Actuals section of the Updater screen to identify the month and click the Sync Monthly Actuals button to re-process the transaction file.

Account and Dimension Maintenance

As part of implementation, Martus works with you to map your Chart of Accounts to identify natural accounts and determine the dimension structure that will support your reporting and budgeting needs. If your accounting system has a segmented account, the account mapping within Martus will determine how those segments are mapped to the Martus account and dimension values.

When the implementation is completed, Martus mirrors your COA and dimension structure. After implementation is complete, you’ll sometimes need to add new dimension values or new GL accounts, and new account mappings. You can do this by using selections on the Setup menu to maintain natural accounts, dimension values, and the account mapping for your accounting system’s account numbers.

If you add a new GL account number to your accounting system, you must also add that account number to Martus. (If you do not do this, the import of any transaction file that includes activity for that account will fail with an error message that indicates that the account does not exist.)

• Use Setup > Accounts and add the natural account (if it does not exist already in Martus).
• Use Setup > Dimensions and add any dimension values that do not yet exist in Martus (for example, a new department).
• If your accounting system has a segmented account, use Setup > Account Mapping and add the mapping for this account.

If you use dimensions that are not part of the account number (project, for example), and you add a new project to your accounting system, you’ll need to add it to Martus as well. (If you do not do this, the import of any transaction file that includes activity associated with that project will fail with an error message that indicates that the project does not exist.)

• Use Setup > Dimensions and add any dimension values that do not yet exist in Martus.

You can also import an Account Mapping file into Martus to add new account mappings (for new account numbers in your accounting system), natural accounts, and dimension values.   To do this, use Setup > Account Mapping, export the current mapping file, update the appropriate pages in that file, and import it back into Martus.

Defining Parent-Child Hierarchy within Dimensions, Adding Dimension Values that Do Not Exist in Your Accounting System

Martus allows you to define parent-child hierarchies within dimension values – even though those are not defined within your accounting system. For example, you will use this if you have several departments that you’d like to roll up into a parent department for reporting purposes.  

You can even create dimension values that don’t exist in your account system so that you can use them as parent dimension values within Martus.

How Account Mapping Works

Account Mapping is the Martus tool that relates the account numbers in your accounting system to Martus’ account numbers and dimensions. 

The menu selection Setup > Account Mapping is used to maintain account mappings. 

Account Mapping is used in all File-Based Integrations with accounting systems that have a segmented account number. It is not usually used if your accounting system has a natural chart of accounts.  

The Account Mapping screen is used to add new mappings and change existing mappings.  

• You can export existing mappings, and browse for, then import a mapping file
• Use the Search box to find all mappings that contain specific text
• Use the filters to limit the list by natural account or any of your dimensions
• One of the key features of account mapping is the Account dimension.  All of your accounting system account numbers must be defined as values for that dimension. Each accounting system account number can only be assigned to a single account mapping; in other words, it can only be mapped one way.  
• Use the Add Mapping button to add new mappings. If you want to add a mapping manually, you must first add any dimension values to be applied to that dimension.
• When you add mappings via the file export/import method, be sure to add all necessary dimension and account values for the new mappings before importing the updated mapping file.

Explanation of Account Mapping Page

• External Id – The account number from your accounting system. The maximum length of this field is 25 characters. If your accounting system’s account number exceeds this, it is truncated to 25 characters.  
• External Type – The account type.
• [xxxx] Account Num and [xxxx] Account Name  – This information is from the account number value and name in your Account dimension. If the account number is longer than 25 characters, the full entire account number is shown here.
• Natural Account – The Martus account number.  
• Dimensions – The dimensions to which the account number is mapped.
  
  

How to Add or Edit an Account Mapping

Use the Setup > Account Mapping to add or edit an account mapping.  The example below is for illustration only; your system’s configuration will determine the specific fields and field names that you see!   Note that before you can add an account mapping, the GL account must exist on the Setup > Accounts screen in Martus, and any dimension values must already exist in Martus. You must add the accounting system account number to the Account dimension before you can define the mapping for it.

• External ID  – The External ID is usually the account number from your accounting system. This field has a maximum of 25 characters, so if the accounting system’s number is longer than 25 characters, you’ll need to truncate it to fit in this field.
• External Type – The account type. When adding a mapping, select this from the dropdown.
• Natural Account - The Martus account number.  When adding a mapping, select this from the dropdown.
• Dimensions – The dimensions represented by the accounting system’s account number. When adding a mapping, select this from the dropdown.
• [xxxx] Account – The accounting system account number for this mapping (from the Account dimension). When adding a mapping, select this from the dropdown.

Budgeting Considerations for File-Based Integrations

As part of the implementation, Martus will provide you with instructions to import your current budget into Martus. Essentially, you will map your current budget into the new account number/dimensional format in the standard Martus budget import file, then import it into Martus.

If your account system has a segmented account number, your Martus instance is configured to limit the accounts that can be used on a budget worksheet to those valid for the dimensions of the budget worksheet.  Additionally, your Martus instance will typically be configured with what we call “reverse mapping”. With this, you will have a line item dimension that contains your accounting system account number, and the budgeting functions of Martus (the budget worksheet and SPWs) will automatically assign the appropriate accounting system account number to each budget item.

Exporting Your Budget from Martus

When you create a new budget within Martus, you can export it from Planner Setup, then reformat it as necessary for import into your accounting system. Martus also provides custom budget exports for ShelbyNext, Sage 100, and Sage 300.  Use this link to a Knowledge Base article for more information about the Sage 100 and 300 exports.

Budget Export from Martus for ShelbyNext

1. Export a budget template file from Shelby.
2. Select Shelby Next from the Planner menu within Martus.
3. Select the budget year, then pick the Martus budget that you’d like to use.
4. Browse for and select the Shelby Next budget template.
5. Click Export, and that budget template file will be updated with Martus budget information.

Notes:

• The updated budget file has the same name as the selected template file.
• The file is updated according to the Shelby fund and department on each Shelby account within the template file, even if that Shelby account is assigned to a different department in Martus.
• If there are any budget values already in the template for accounts that are not budgeted in Martus, they are not updated by this process.
• If there are any accounts budgeted in Martus that do not exist in the Shelby template, they are not added to the budget template file.

Transaction Uploads - Video Tutorial

With a file-based integration, GL transaction detail must be manually uploaded into Martus to support financial and budget-to-actuals reporting.  

You must title your transaction file Transactions_yyyy_mm and must be saved as an '.xlsx' file type in order to upload them into Martus successfully.

This tutorial also provides a special Excel tip for those Martus customers whose accounting system cannot provide a transaction file that has a date on each line.  With just a few clicks, you can ensure that each line is formatted appropriately for import!

Budget Export from Martus for ShelbyNext

Martus supports the export of a budget file in the appropriate format for the ShelbyNext accounting system. 

Steps:

1. Start in ShelbyNext
2. Export any budget from ShelbyNext (this file will be used in future steps for Martus to understand the ShelbyNext format).
3. Go to Martus
4. Go to Planner > ShelbyNext
5. Select the appropriate Martus budget to export
6. Import the file exported in step 2 above
7. Click Export