Sage Intacct Collectors

Overview

The Sage Intacct collector allows you to collect data directly from your Sage Intacct instance. The Sage Intacct credentials used in your DataBlend integration must have the proper permissions to access the data you want to collect.

DataBlend can collect the following object types from Sage Intacct:

Configuration

Bookmarks

Bookmarks can be used on certain collectors to set a dynamic value each time the collector runs. A field from the collector source will need to be selected to be associated with the Bookmark function. For example, ‘WHENMODIFIED’ is a field in the Sage Intacct GL Detail object that can be used as a Bookmark:

Once the Bookmark Field is set, every collection will save the value based on the latest run time. In the example above, the Bookmark is stored as a 'WHENMODIFIED' date of '05/17/2022 04:22:10'. The next time the Collector runs, it will only pull values with a different value, and then will update the Current Value field dynamically.

Other Notes about Bookmarks:

• Most commonly set as date fields, the Bookmark Field can also be a non-date field.

• Filters on the collector can still be applied, so even though a Bookmark may be set, if a filter is present it will still only collect data with both the Bookmark value and filters in mind.

• Data gets collected into a new stream even when Bookmarks are present, the data does not automatically consolidate into one stream. Consolidation would need to be done in a query.

  • To accomplish this consolidation in the query, your FROM section will need to leverage ‘All Streams’. This will also carry a risk that if the bookmark’s ‘Current Value’ is cleared or the bookmark is removed, the same data can be included into a new stream and therefore overstate the dataset that is being used in the query. It is important to be mindful of which collectors and queries are relying on the bookmark functionality and avoid modifying the bookmark while in use. To reset the bookmarked data, make sure to clear the ‘Current Value’ and also purge all streams or create a new Schema so that you’re starting from an empty dataset.

Check For Duplicate Records

Sometimes Intacct returns duplicate records through the Query API instead of the missing ones. To help prevent this, we’ve added an extra validation layer that filters out bad data from being collected. 

When bad data is received, DataBlend will automatically retry the collection. If the same issue occurs on the second attempt, the collector will return an error. This error does not impact your existing data, but it helps ensure that incomplete or incorrect records don’t make it into the integration. 

When selected, an input to use in the query to check for duplicate is shown. If no value is set the query uses “RECORDNO.”

After the data is collected and the stream is closed, the job will run a query to check for duplicates. Additionally, the check will include if there was a mismatch between the initial number of records reported by the collector and the total collected. If duplicates or a mismatch is found, the job will collect the data again and check for duplicates again. If duplicates are found a second time the job will error.

Both sets of data are kept as collection items/streams for users to further investigate.

Duplicated or Mismatched will be shown in the Status message.

History 

Under the History tab you can view all runs the collector had. You can filter the History by State or Created. You can also change the page or the number of results that are displayed on each page. 

You can see the data that was collected in the schema used by the collector by clicking the ‘Eye’ icon. You can also download either the CSV or Excel file of the collection depending on the size of the file. The Excel icon will not be available if the file size is too large. 

You can click ‘Complete’ or the State of the run to view the Logs, get a Data Preview, see what Items were collected, and what Parameters were used for a specific run. 

Parameters 

Under the Parameters tab you can create parameters that are used for your collector run. The most commonly used are a start date and end date parameter for a specific time frame to collect the data. 

Advanced 

The Advanced tab allows you to choose how each run updates the schema, the Run Mode, the Minimum Log Level, how long the history of runs is kept, the amount of time the collector runs before timing out, and the schedule to run it on. 

The Schema Update Type has 4 choices: 

• Recreate Columns: Existing columns are removed from the schema and new columns are created based on the collected data. 

• Add New Columns: Existing columns are preserved and any new columns from the data are added to the schema. 

• Preserve Columns: Existing columns are preserved in the schema and new columns are ignored. 

• Infer: This feature is in beta and is intended to make the unnesting of Json arrays easier. 

The Run Mode is used to determine is you would like concurrent jobs for the collector to be ignored or queued for a run. 

• Run Job Exclusively: This will reject any jobs that are trying to run concurrently for the collector. 

• Run Jobs in Sequence: Jobs will run only one at a time and the rest will be queued. You will see queued jobs with a ‘Pending’ state. 

The Minimum Log Level determines the detail of the logs collected with each run. The primary use is for troubleshooting errors. 

The History Retention field is used is how many days DataBlend will keep record of the collector runs. The Default is 180 days.  

Timeout is used to determine the length of time the collector will run for before stopping with a timeout error. This is useful when something may happen on the Sage Intacct side and it does not complete the collection. 

Schedule 

You can set your collector to run on a schedule using a Cron Expression. 

Clicking on the Presets allows you to use a user-friendly UI to set the schedule for the collector. 

This will bring up a pop-up box with the available options for a preset Cron Expression. Select which one you need and then click OK. Be sure to save the collector after making any changes. 

Usage 

You can see which workflows your collector is used in by checking the Usage tab. From there, you can click the name of any workflow to go directly to its page. 

Filters

You can select filters for your collector to use during data collection. These filters generally fall into two categories, Comparison and Predicate. You can also combine multiple filters by using AND or OR junctions to refine your results. 

This gives you flexibility to customize how your data is collected and ensure you’re getting exactly what you need. 

Comparison filters are useful when filtering using a number or date range. 

Here’s a list of Comparison filters and when to use each one: 

• Less than – Use when you need a number smaller than the value or a date earlier than the value. 

• Less than or equal to – Use when you need a number smaller than or equal to the value, or a date earlier than or the same as the value. 

• Equal to – Use when you need to match the exact value. 

• Not equal to – Use when you want to exclude a specific value from your results. 

• Greater than – Use when you need a number larger than the value or a date later than the value. 
• Greater than or equal to – Use when you need a number larger than or equal to the value, or a date later than or the same as the value. 

Predicate filters are useful for special cases. 

Between is useful when you have a minimum and maximum value you need to filter for such as a start and end date. 

In/ Not In – This is useful when you have multiple values that you need to filter by in the field. You can separate the values using a comma “,” with no spaces between values. The Split Values toggle should be set to on. 

Is Null / Is Not Null - useful for checking if the field is blank or not. 

Like /Not Like – This filter is useful when you only need to match part of the value. 

You can clear a filter that you don’t need by click the ‘x’ in the Type field. 

Filter values can be parameterized. To make parameters dynamic, you can generate a string containing the parameter value(s) as part of a query result and then pass that string to the parameter.

 

Special Notes

Append Date to Schema is for Custom Reports and adds a date column to the end of the data that was collected. You can use this with a parameter to set a dynamic date when the collector runs.