You can use Adaptive Integration to import data from SAGE Intacct.
Set up a Web Services User in SAGE Intacct with Permissions for Full Read Access to General Ledger and the Company Module
Set up a Web Services User within SAGE Intacct with the following settings and permissions. This user will only exist to pull data from SAGE Intacct into Adaptive Insights.
- Username: xml_gateway_adaptive
- Email: Use your email address
- Account User Type: business user
- Account does not need admin rights
- Account needs full read access to the General Ledger Module and the Company Module
Once you create the account, you will receive a Welcome To SAGE Intacct email that contains the username, company code, and temporary password. Copy and paste these into your Intacct Data Source Settings.
Allow SAGE Intacct Web Services Authorization Access
SAGE Intacct added a new level of security for all instances that require Web Services connections. Customers must now grant permission for a Web Services Sender ID to connect to their Intacct instance and request data.
Grant Adaptive Integration Permission to Connect to a SAGE Intacct Instance
- Within SAGE Intacct, navigate to the Company > Company Info > Security tab. Click Edit.
- In the Web Services Authorization Section, click the + icon above the top-right corner of the table.
- In the Web Services Sender Information pop-up, enter adaptiveplanning. The Sender ID is case sensitive, only use lowercase.
- Click Save.
Enter SAGE Intacct Data Source Settings
In Adaptive Integration, navigate to Integration > Data Designer.
In the Data Source folder in the Component Library on the right side of the screen, click Create New Data Source.
The Create New dialog box appears.
Select Intacct as your data source type and enter the name for the data source.
The center area of the screen displays your new data source’s settings, as shown below.
Enter the data source’s information:
- Log level: Select a log level from the drop-down list to specify the detail for the logging for this data source. Your options are:
- Error: Only logs errors.
- Info: Logs all basic information, such as when the data source was updated.
- Verbose: Provides very detailed information about all phases and actions. (This level is used primarily for debugging or auditing, as it may produce more log information than is practical for typical use.)
- User name: Enter the user name for the Intacct Web Services account.
- Password: Enter the password associated with the user name.
- Company ID: Enter the company ID associated with the user name.
- Client ID: Enter the client ID associated with the user name. (Optional.)
- Location ID: Enter the location ID associated with the user name. (Optional.)
6.Click Save on the Actions menu.
You can test the connection information in the new agent by clicking Test connection in the Actions menu. The system tries to connect to SAGE Intacct using the information in the data source fields. If the system can connect to your SAGE Intacct account, click OK. If there are connection problems, contact Adaptive support.
When you can connect to SAGE Intacct, you’re ready to import the data structure. This shows you the tables and columns in the database.
To import the SAGE Intacct Structure, do the following:
Click Import structure in the Actions menu. A dialog box appears with informationabout what is imported.
Click OK. Adaptive Integration imports the structure. A dialog box appears showing the progress.
Click Close when the import is complete. The tables and columns appear in the Data Components menu.
Click Close to close the Import Intacct dialog box.
Click Save on the Actions menu.
Adding Tables to an Intacct Data Source
After you have imported the Intacct structure, you are ready to specify the information you want to import. The Tables to Import section of the screen lets you configure the data imported from Intacct. To specify the tables to import from Intacct, follow these steps:
Expand the entry for the data source in the Data Components menu, as shown below. (Data components with a slash through their icon are not currently included for import.)
Drag and drop the tables you want to import to the Tables to Import section in the center of the screen, as shown below. As you drag each table to the Tables to Import section, a tab appears for the table and information from the Spreadsheet account appears in the Tables to Import section letting you see the type of information you’ve added. (Several sample tables appear below.) Each table’s tab also has a drop-down menu, which you can access by clicking the arrow next to its name.
If a table you are working with has a large number of columns, you can use the Manage Columns to do quick edits for which columns to import. The Manage Columns dialog box displays the columns in a table so you can then check or uncheck columns for importing.
To manage columns for importing, follow these steps:
Click the arrow on the tab to the right of the table name.
A floating menu with several options appears.
Click Manage Columns on the floating menu.
The Manage Columns dialog box appears.
Check the box next to a column to import it. When you check a column, the column also appears within the preview window after the popup contents are saved. Unchecking a column removes the column from the preview window.
You cannot change the import status on system-generated fields such as isDeleted. You can also change the import status on individual columns by selecting column properties and setting the property or by dragging and dropping the columns onto the designer.
Customizing the Tables
For each table, you can customize the way in which data is imported.
To customize the table settings, follow these steps:
Click the arrow on the tab to the right of the table name.
A floating menu with several options appears.
Click Table Settings on the floating menu.
The Table Settings dialog box appears, as shown below.
Enter the table settings:
- Data Import Filter: Use this to enter a SQL expression that filters or formats information being imported. The SQL filter is applied before data is extracted from the database to reduce the amount of data that needs to be imported, giving you an additional level of control for which data is brought into the staging area. Click the field to display the Edit SQL Expression dialog box, shown earlier. (The filter you enter here is applied to the actual data in the database rather than the preview data in the staging area.) Not all data sources support the same level of SQL filtering capability.
- Click Apply to apply the settings.
- Repeat steps 1-3 for each table.
- Click Save to save the settings in the data source.
Setting Column Options
While using the Tables to Import section, you can see a preview of the data as it is in the data source or as it is in the staging area after it has been imported. Select Intacct or Staging table from the drop-down to preview data.
You can change the options for each column by mousing over its heading and then clicking the drop-down menu next to its name. These options are:
- Sort Ascending: Sort the entire table based on this column in ascending order.
- Sort Descending: Sort the entire table based on this column in descending order.
- Column Settings: This dialog is used to change the properties of the column within the staging table.
- Name: The name of the column, displayed in the header.
- Column Type: The type of data used in the column.
- Convert Column Type: Select the new type from this drop-down menu.
- Exclude column from import: Select this to remove this column from the data being imported.
- Delete custom column: Select this to delete the column. (Available for custom columns only.)
Downloading Data from a Staging Table
You can download data from a single staging table.
To download data from a single staging table, follow these steps:
Click the down arrow in a table heading in the Tables to Import section.
Select Download Data.
At the prompt “This operation will send an email with a url to download the data,” click Send. Adaptive Integration sends an email containing a URL to the email account associated with the data designer.
Open the email and click the URL. The data is downloaded in csv format.
Using the Advanced Filter
Below the Source drop-down menu, you can click Advanced Filter to reveal options for filtering the data in the Tables to Import preview area. The Advanced Filter provides tools to view and browse a subset of the data within a staging table. By changing filter properties, you can change the view of the data shown within the preview window without changing the data in the staging area.
The preview area is specifically for previewing a small portion of the data in the staging area. You can explore the data that is available from the Spreadsheet account and verify that you are getting the data you expect by examining the staging area. (Filters set in the preview function do not affect the actual data in the staging area.) The Advanced Filter lets you experiment with the data in the preview to set up a data import filter.
The options for the Advanced Filter are:
- Distinct Rows: Check this box to hide duplicate rows.
- Max Rows: Limit the number of rows displayed in the preview area by entering a number in the text box.
- Columns: Check or uncheck columns in this drop-down menu to show or hide the columns displayed.
Removing a column from this list does not remove the column from the import list.
SQL Filter: Click the large text box to display the Edit SQL Filter dialog box, shown below.
Enter a SQL expression in the field. This is a filter to restrict the rows being previewed. Click Apply to automatically check the SQL syntax of the filter. (If there are any errors, the error is indicated in the expression.) For detailed SQL syntax help, you can click online help in the Notes section. The expression you entered appears in the Advanced Filter section of the preview window.
Click Remove Filter (next to the Source drop-down menu) to clear all advanced filter settings.
As part of the table customization process, you can also create join tables and custom columns using SQL expressions.
Clearing the Staging Area
Information remains in the staging area until you clear it.
To clear data from the staging area, do the following:
Click Clear staging tables in the Actions menu.
The staging area for all the tables in the data source is cleared.