The purpose of this post is to provide a thorough walk-through of Global Variables and how to use them when building a SmartConnect process.
Table of Contents
- What are Global Variables?
- Default Values vs. Map Values
- System vs. User Defined Variables
- Global Variables in Calculation Column
- Global Variables in Restrictions
- Global Variables in Data Sources
- Summary
What are Global Variables?
Global Variables are a powerful scripting tool that allows you to store values from any SmartConnect scripting window
to be used later in the SmartConnect process, and even pass values to child integration processes. New Global Variables can be created in SmartConnect from the Maintenance tab.
Global Variables can be used in five stages of the SmartConnect process:
- Calculations
- Restrictions
- Pre and Post Map Scripting Tasks
- Post Map Email Tasks
- Data Source Queries
Default Values vs. Map Values
A Global Variable can be assigned values at different levels. Understanding what value is being used is crucial to
ensuring the Global Variable functions correctly.
Default Values
These are set on the Maintenance > Global Variables window and are used for the following actions:
- Applying filters when previewing a data source from the Data Sources tab.
- Setting the value for a variable when adding a new variable to the ‘Variables’ tab on a Process.
- Updating the variable on an existing integration process via the ‘Variables’ tab when clicking ‘Reload Value’.
*Note changing this value in the Maintenance > Global Variables screen will NOT update the Map Variable until you
click ‘Reload Value’ on the ‘Variables’ tab in each process.
Map Values
These are the variables you add to the ‘Variables’ tab in an integration process and are used for the following
actions:
- Applying filters when previewing a data source within the SmartConnect Process window
- Setting the initial value to be used by variables when the process runs.
Map Value Tab in Process Setup
Persist Run Value:
If the ‘Persist Run Value’ box is checked, the last value in the variable after the process completes running will be
saved and used next time. This functionality can be useful for incremental filtering on the data source by allowing
you to pass a filter to the source query to make sure you don’t run the integration for all records, but instead run
it for only records that are newly created.
*Note the ‘Persist Run Value’ option will only update the Map Value not the Default Value.
Persist Error Run Value:
If the ‘Persist Error Run Value’ box is checked, the same functionality as Persist Run Value, but applies to integrations ran from the “Error Processing” screen. This functionality can be useful if the global variable is being used as a custom rolling column or other value that should increment even when ran through error processing.
*Note leave this box unchecked if the global variable is being used as an incremental data source filter such as “last run date” filters.
Select at Runtime:
If the ‘Select at Runtime’ box is checked, the user will be prompted to provide a value for a variable prior to running the process. This functionality can be useful for allowing the user to manually provide filters prior to running an integration. Another use could be requiring the user to provide a value to map to the destination such as batch or company name.
*Note ‘Select at Runtime’ will overwrite the value saved in ‘Persist Run Value’.
**Note Runtime Settings will require pop-ups to be allowed in your browser.
System vs. User Defined Variables
A list of Global Variables in the SmartConnect database can be found in SmartConnect > Maintenance > Global
Variables. Variables must exist here before they can be used in a process. There are two types of variables.
System Defined Variables: These variables are defined automatically by SmartConnect. These global variables
may be included in scripts, however, they are only updated through SmartConnect processing. Scripts that update these
variables will be ignored at runtime. Think of these as read-only variables.
User Defined Variables: These variables are created by the user and can be read from and written to using
scripts. They can be manipulated whenever needed, think of these as read-write variables.
System Defined Variables Example
The most common use for the System Defined Variables is to send process result information in an email. You can find
documentation on each variable that is defined by the system here.
In the below screenshot I have added system defined variables to my Integration Post Failure Task. These
variables are automatically populated with values when the map run to provide additional information about the map
run and error details.
User Defined Variables Example
In this scenario I want to track the last invoice that I export from my SQL table. I only want to export the new invoices created since the last time the process ran.
Before a User Defined Variable can be used in a process it must be created in SmartConnect > Maintenance >
Global Variables > Create Global Variable.
When creating the variable, set a default value that will allow SmartConnect to validate successfully if used in a
data source. In my example, the variable will be used to filter on an integer field, so I picked an integer value
that I know exists in my data set.
Once the Global Variable exists, I can use it in a data source. I want to filter for all records with an Id greater than 4001 so my query will look like:
Select dbo.Invoice_Headers.Id, dbo.Invoice_Headers.InvoiceNumber, dbo.Invoice_Headers.status, dbo.Invoice_Headers.Total, dbo.Invoice_Headers.Type From dbo.Invoice_Headers Where dbo.Invoice_Headers.Id > GBL_INVOICEID
Once my data source previews correctly, I can create my integration process. I created the mapping of the target like normal. Once the target is mapped, I switched to the ‘Integration > Restriction’ tab. I don’t want to restrict data so I will leave the return true, but I will add a line to set the GBL_INVOICEID equal to the current Invoice Id.
This script will update my Global Variable for each record it processes. I don’t care about any value that is set on my Global Variable, except the last one, because that is the value I need to use on my next process run to ensure I only retrieve unprocessed invoices.
Why did I use a Restriction? SmartConnect.com does not have document level tasks, and I need my script to run for each record, not just once at the beginning or end of the process run. A Restriction runs for each source record so I can add process based on each record.
The final step is to navigate to the ‘Variables’ tab in my process, and set the Global Variable to ‘Persist Run Value’. Now after each run the last exported Invoice Id will be saved in my process to be used on the next run.
Global Variables in Calculations
A Global Variable can be both set to a value and read from using a Calculation. If you need to map a Global Variable to your destination, you will need to read the value using a Calculation, and map the Calculation column. The below example shows how to set the value of a Global Variable and read the current value in a calculation.
Global Variable Restrictions
A Global Variable can be both set to a value and read from using a Restriction. If a restriction returns true the node will be processed and sent to the destination. If the restriction returns false, the node will be skipped, and SmartConnect will move on to process the next record. Below is a sample restriction using a Global Variable.
Global Variables in Data Sources
Most SmartConnect query data sources support Global Variables. Below are some examples for our popular connectors.
D365 Business Central Odata
The Business Central Odata connector supports all Documented Filter
Expressions. If the field you are filtering on is a string in Business Central, make sure you use single
quotes around the variable as shown below.
If the field you are filtering on is a Date or Number, do not use single quotes. If you need to filter on a Date
field, note that both SmartConnect and Business Central use UTC dates so no conversion is required. A script to set a global variable date would look like:
D365 CRM/Customer Engagement
A Global Variable can be added in the query builder in SmartConnect, as shown below.
CRM date filtering is more advanced as discussed in this
article. We need to first adjust the time from UTC to whatever time zone the user sees in CRM. The Offset
will need to be changed to whatever your difference is from UTC. If you want to make the syntax simpler, you can set
the Integration account to use UTC so it matches the SmartConnect server.
Microsoft SQL Server
The SQL Connector supports full SQL Syntax so you can get advanced with these data sources. You can add Global Variables in the query builder. If the field you are filtering on is a string in SQL, make sure you add single quotes around the variable.
If doing a date filter based on the current time you can use GETDATE in SQL, but if you want to filter on a date
calculated elsewhere in the SmartConnect process you may need to add the filter with a global variable and
JavaScript which would look like:
NetSuite
Global Variables will also work in the NetSuite Connector. When adding a new source enter a variable as you normally would enter a value. Below is a screen shot of how this would look.
NetSuite date filtering is more advanced, because the SmartConnect server runs in UTC, therefore we need to first adjust the time from UTC to whatever time zone the user sees in NetSuite. The timezone is set in user preferences within NetSuite. The Offset
will need to be changed to whatever your difference is from UTC. If you want to make the syntax simpler, you can set
the Integration account to use UTC by adjusting the user preferences.
Salesforce
The Salesforce connector uses the standard SOQL syntax. You can insert global variables into your script
like what is shown below. Make sure you use single quotes around string variables.
If the field you are filtering on is a Date or Number, do not use single quotes. If you need to filter on a Date
field, note that both SmartConnect and Salesforce use UTC dates so no conversion is required. A script to set a global variable date would look like:
Rest Services
The Rest Service Connector also supports using Global Variables. You can add the Global Variables as a parameter or part of the method body.
Most REST Services support either ISO 8601 or an EPOCH timestamp. The below script shows how to retrieve the current date, format it in either of these formats, and assign it to a Global Variable.
Summary
Global variables are a powerful tool to have in your SmartConnect toolbox. They can be used in data source queries and all scripting windows in the integration process.
We continue to expand the functionality of these variables, so if you have any suggestions feel free to submit them here.
If you have any further questions, you can email us at support@eonesolutions.com.