Skip to main content

LOOKUPA

Lookup a value from Data Source A. This is functionally equivalent to LOOKUPB except that it uses Data Source A (Source) as the connection.

It enables you to lookup values within your source data source connection, you can connect to other entities/items/tables like when you are using the connection library.

Syntax​

object LOOKUPA(string column, FROM(from), keyValue)
object LOOKUPA(string columnName, LFROM from, params KVAL[] keyValues)

Examples​

Dynamics​

This function supports looking up OptionSet values from Entity OptionSets. In this example the source dataset is a simple id, name option set.

To lookup an id value for a given OptionSet name use the following syntax entity|optionset in the from part of the lookup function.

LOOKUPA("id","account|industrycode". WHEN("name", "Financial"))

As another example, you can lookup an account name in the account entity of Dynamics when you are connected to the contact entity on the source:

LOOKUPA("name", FROM("account"), WHEN("accountid",accountid))

Active Directory​

Different OU lookup​

If your source is connected to Active Directory you can do a lookup into another OU to further decorate your data.

info

DS- columns are Data Sync attribute columns that handle the addition of that field for each user. These usually have to be handled differently to add the values, which in previous versions would have needed you to write code. These columns take away the need to add code to project automation.

The DS-DN column is a Data Sync generated column that returns the distinguished name.

For example if you wanted to lookup the manager:

LOOKUPA("DS-DN", "LDAP://DC01/OU=Managers,DC=lab,DC=simego,DC=com", WHEN("DS-SAMAccountName", Manager))

The "DS-DN" is the column we are looking to return from Active Directory. This is the distinguished name of the manager.

We then specify the LDAP Path to the OU we want to look for the record in.

Then we specify a WHEN clause to define what the parameters are that need to be matched. This is the link between the source and the target. In this example is it the SAMAccount Name of the Manager in Active Directory to the Manager Field in the source columns. This example uses the Data Sync column DS-SAMAccountName to return the SAMAccount Name.

This could also be achieved using employee numbers if that is what is returned for your Source Manager column.

LOOKUPB("DS-DN", "LDAP://DC01/OU=Managers,DC=lab,DC=simego,DC=com", WHEN("EmployeeID", Manager)

Same OU Lookup​

If you wanted to lookup data within the same OU you can leave the LDAP path as an empty string:

LOOKUPA("DS-DN", "", WHEN("DS-SAMAccountName", Manager))

The "DS-DN" is the column we are looking to return from Active Directory. This is the distinguished name of the manager.

We then leave the LDAP Path empty so that it uses the path we are currently connected to.

Then we specify a WHEN clause to define what the parameters are that need to be matched. This is the link between the source and the target. In this example is it the SAMAccount Name of the Manager in Active Directory to the Manager Field in the source columns.

This could also be achieved using employee numbers if that is what is returned for your Source Manager column. The function would look something like this:

LOOKUPA("DS-DN", "", WHEN("EmployeeID", Manager)

SharePoint Lookup from a SubSite​

Sometimes you may need to specify a specific URL to the SharePoint list you want to perform a lookup on, if that list is not in the site specified in Data Source A. For example if Data Source A was running in a sub-site of SharePoint and the required list is located in the root site of SharePoint Data Sync's Lookup functions can handle extended formats that override the settings that are typically gathered from the data source properties. Using calculated columns we can use the LOOKUP function to pass the SharePoint site as part of the expression.

Syntax​

LOOKUPA(string column, FROM(KV(string parameter, string location/url), KV(string valueParameter, string valueName)), WHEN(string value, object))

Inputs​

ObjectData TypeDescription
columnStringThe column name to find.
parameterStringThe parameter to override.
location/urlStringThe location to find the parameter to override with, e.g. a URL.
valueParameterStringThe value you are looking for.
valueNameStringThe name of the valueParameter you are looking for.
valueStringThe value you are looking for.
objectStringThe object you are looking to match the value to.

Example​

In this example we can get the User ID from the User Information List in the root site whilst running in the sub-site.

LOOKUPA("ID", FROM(KV("SharePointUrl","https://simegoltd.sharepoint.com/"), KV("ListName","User Information List")), WHEN("Name",Title))