On-premise customer, trying to
Question:
On the web service action, what is the replacement for getting external data into a site for use on a form? Let’s say we have a customer list in oracle or SQL on prem. How do we get that list to a site to be used in a drop down on a form? Especially when it might be used across multiple sites and multiple site collections and must be up to date?
Answer from Brad Orluk
The absolute best approach here would be to use BCS in SharePoint to surface that Oracle data in an external data column. This way we show it in the form using our BCS control. Otherwise, they would need to use the Web Request / Execute SQL action in workflow to pull the data from the database and put it into a list and then use a list lookup for the dropdown. This would need to be done as a scheduled workflow so that the data in the list stays relatively up to date with what’s in the database.
Answer from Dan Burke
From what I looked into, what looks like the only way to do this online will be to create a list on O365 and run a workflow that uses a combination of a Loop, Execute SQL, and Update Item actions to populate the list within SharePoint. From there, you could have a separate list where the form lives, that utilizes list lookups and cascading drop-downs to input the data in the form. This wouldn’t necessarily get things auto-populated, but it should get all the relevant information available in the form.
Schedule Workflow Workflow Hack: Scheduled Workflows in Office 365
From a Partner:
This is our take:
Currently
The customer has a BDC List connected to SQL Server and a workflow to move the BDC Content to a “normal” SharePoint list. From that list they are using drop down fields with some lookup capability. Looks like they are still experimenting in their environment.
Their request is
To verify that this function can be done in an O365 Nintex Workflow (connecting to an internal SQL Server).
Our understanding is that it can be done with the Execute SQL action in O365.
My interpretation of his request is this: Fill your SQL boots - New Execute SQL action for O365
*This works if the SQL db is internet accessible. If it is not, the BCS connection itself will be a little tricky. This technet article walks through the convoluted voodoo required. BCS in SP Online: https://social.technet.microsoft.com/Forums/en-US/6e35ec7a-ce00-4207-bb57-ae4e8698e2a9/bcs-in-sharepoint-online-office-365?forum=sharepointgeneral
If it is internet accessible, once the BCS connection is set up, then it should work just fine.
