Haaron Gonzalez Rotating Header Image

Data

How to display additional lookup data from another list in a Nintex Form

Let’s say that you have a lookup site column that you are using all over the place in multiple lists and sites under your site collection and you need to be able to display additional data from this important list in a Nintex form, there is a way fortunately.

By adding this site column into your content type or list the Nintex Form will identify field as a lookup control automatically and by using the lookup function you can query another list in the site collection and return data from it.

In this example, I have a Stores list, every store has a district number associated, so when I pick and choose a particular store number by using the lookup function I am able to return the district for the selected store.

lookup1

To understand how the lookup function determines what data to bring back, consider the following lookup function:

[code language=”css”]
lookup(“listA”, “ID”, 1, “Title”)
[/code]

This lookup will send a query to SharePoint, asking for list items within a list titled ‘listA’ and return the Title column value from any list items whose ID column value is 1.
Note: The match on the value is case-insensitive.

The lookup function does not support complex query construction; it determines what list items are included, as data to bring back, based on the filter column being an exact match to the value you specify. You can compose complex formulas with the formula builder if you need to build up a complex query, however be mindful that each lookup function will send an individual request to SharePoint for data.

So, in my example I just added a Calculated Value control in the Nintex form and configure the lookup runtime function like this:

[code language=”css”]
lookup("/sites/mysitecollection|mylist","ID",Store,"District")
[/code]

Notice that there is a “|” to point to a list in another site, in this case, the top level site using a relative URL Path, then delimit the list title with a pipe ‘|’ symbol. If your list exists under your current site just use the list title without the URL Path.

lookup2

 

Connect your data with Layer 2 Cloud Connector

layer2 There are always scenarios where products fit and represent a viable solution for a specific technical challenges. Today I want to talk a little bit about Layer2 Cloud Connector, this is handy tool for dealing with data synchronization between any data source and our favorite product, SharePoint. This tools allow you to define a source connection to one data source and a target connection to a second data source, then you define the mapping between those data sources and just let it run, will sync the data between data source 1 to data source 2 or you can configure bi directional sync so data will get updated in both data sources.

Maybe you have the scenario to sync on premise list to Office 365 SharePoint Online list, or maybe your are not ready for a Hybrid scenario between on premise and Office 365 and you need a solution now, or, maybe you want to sync data from an CSV or excel file in the network on daily basis, this tool can make the difference.

Some general comments:

  • The process of setting the source and target data connections is easy and very straight forward
  • Support key columns identifier in the source database
  • Support key column identifier in the target SharePoint list
  • It use .NET Data Providers including .NET OracleClient Data Provider
  • It use Layer 2 Data Providers to SharePoint, it’s based on CSOM so will connect to any SharePoint List

Some sync comments:

  • Cloud Connector reads the source data and insert in to target list
  • I created a mapping between database columns and SharePoint Fields using the tool
  • The tool automatically identify inserts, updates and deletes that need to be performed on target list
  • Tools has a log that will show how the sync process outcome so you can tell how many records were updated
  • Tool can run as service so we can do a sync schedule

The licensing model it’s based on the number of source and target connections you need to do, so be careful with that.

Her is an example of the resulting log, I was able to open a local excel file in a share drive, run a SQL query to filters the data from excel and sync the data to a SharePoint list, that was awesome, here is the result:

  • -> Current product edition is ‘Shareware’
  • -> Loading items from the data entity ‘SQL Customers’… 91 items retrieved.
  • -> Loading items from the data entity ‘SharePoint Customers’… 25 items retrieved.
  • -> Loading metabase…
  • -> Executing uni-directional synchronization…
  • -> Instructing data entity ‘SharePoint Customers’ to perform 0 inserts, 6 updates and 0 deletes…
  • -> Performing post synchronization tasks…
  • -> Synchronization of connection ‘Customers to O365’ finished:
  • -> 19 records were already up-to-date, 6 records have been synchronized and 0 records have been skipped. 1 warning occurred. (0.43 minutes)