HELP ARTICLE

Global Variables in SmartConnect.com


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

  1. What are Global Variables?
  2. Default Values vs. Map Values
    1. Default Values
    2. Map Values
  3. System vs. User Defined Variables
    1. System Defined Variables Example
    2. User Defined Variables Example
  4. Global Variables in Calculation Column
  5. Global Variables in Restrictions
  6. Global Variables in Data Sources
    1. D365 Business Central Odata
    2. D365 CRM/Customer Engagement
    3. Microsoft SQL Server
    4. NetSuite
    5. SalesForce
    6. Rest Services
  7. 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:

  1. Calculations
  2. Restrictions
  3. Pre and Post Map Scripting Tasks
  4. Post Map Email Tasks
  5. 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:

  1. Applying filters when previewing a data source from the Data Sources tab.
  2. Setting the value for a variable when adding a new variable to the ‘Variables’ tab on a Process.
  3. Updating the variable on an existing integration process via the ‘Variables’ tab when clicking ‘Reload Value’.
Global Variable Setup Window

Global Variable Setup Window


*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:

  1. Applying filters when previewing a data source within the SmartConnect Process window
  2. 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.

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.

Assign value at Runtime

Assign value at Runtime

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.

Email Failure Task Configuration

Email Failure Task Configuration

Email Received on Process Failure

Email Received on Process Failure

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.

Defined Global with default value

Defined Global with default value

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
Source filter with a Global Variable

Source filter with a Global Variable

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.

A Restriction using a Global Variable

A Restriction using a Global Variable


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.

Map Value Tab in Process Setup

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.

Business Central Query using a Global Variable

Business Central Query using a Global Variable

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.

Netsuite Query using a Global Variable

CRM Query using a Global Variable

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.

Source filter with a Global Variable

Source filter with a Global 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 Query using a Global Variable

Netsuite Query using a Global Variable

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.

SOQL Query using a Global Variable

SOQL Query using a Global Variable

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.

Global Variable in a REST Data Source Parameter

Global Variable in a REST Data Source Parameter

Global Variable in a REST Data Source Body

Global Variable in a REST Data Source 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.

RECENT POSTS


SmartConnect Setup for Office 365 E-Mail with TLS
Excel data sources work intermittently or give blank error message.
How to set up automatic payments on eOne's website portal
"Info 1625. This installation is forbidden by system policy", when attempting to install the SmartConnect Excel Add-In
Salesforce Objects or fields are not available in SmartConnect

POPULAR POSTS


SSL Security error using OLEDB Connection
Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
The server process could not be started because the configured identity is incorrect.
Using WINSCP and a task to upload or download files from an SFTP site
Shortcuts Template for Extender

CATEGORIES

ARCHIVES

open all | close all

TAGS

Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP GPUG integration Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Office Hours SmartList SmartList Builder SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.