BCS Action


Badge +9

Hello

I have created an external content type and successfully was able to pull data from our sql table. However how do I pull data using the BCS action in Nintex?

I have a list where I have added the BCS action to it

190647_pastedImage_1.png

This is how I configured the action:

190652_pastedImage_2.png

When I click on the Run Now button I get the following to display:

190653_pastedImage_3.png

My question is how do I populate this data in my list? I got it to run successfully in the action but I want to be able to retrieve the data from the sql table into my list.

Thank you!!!


10 replies

Badge +3

While I am fairly new to Nintex as well, I believe you will want to use a For Each loop to look through the collections (assuming you made the storage variables collections).  Then, depending on how often you want to write back to the list, you either write back within the For Each loop so you write everytime, or you have some conditions within the loop to only write back to the list based on those conditions. 

So what I would do is store each value you're pulling in a collection (so make txtDate, txtCallDate, and txtCompany collections), create a For Each loop to loop through one of them assigning it to an index, then within the For Each loop make Run Parallel action with 3 collection operations, one for each Collection, which looks in the proper collection, at the index you assigned in the For Each loop, and assigns it to a variable.  So now each time it loops through you have all 3 items assigned to a variable and can write them to a list.

This gives some good information:

http://nintexdownload.com/Nsupport/tutorials/How%20to%20use%20the%20Collection%20variable.pdf

Badge +9

Hi Steven

I think I am almost there! I appreciate your help. This is what I have so far. Just so you know, my external content type has 19 records.

After I made changes to my workflow based off of what you have mentioned the last record is what populated in my list. I need all 19 items to populate in my list.

So my workflow settings i have set it to start when a new item is created.

190659_pastedImage_1.png

ForEach looks like:

190660_pastedImage_2.png

The first collection on the left is :

190661_pastedImage_3.png

Second collection in the middle is :

190662_pastedImage_4.png

The third colection :

190663_pastedImage_5.png

my variables txtID, txtDate and txtCollectorCode all are being set on single line columns. I manually ran the workflow on an item that I had in my list. What ended up happening is it populated the 19th record in my list instead of populating all of them.

Wasn't it supposed to retrieve all of the items? I don't have a condition set in the workfow yet, as you have mentioned to do in your post, but what I need is the workflow to run on new items being entered into the table.

You are the best for helping me ! 

Badge +3

I think instead of "Set a field value", you should be using "Create Item" and just add each variable to that one item.  That way each time it loops, it creates a new item and adds each value. 

At least I hope it's that simple happy.png

Badge +9

Thank you it worked!!!

Badge +9

Yes please go ahead. I am surprised that it was a discussion rather than a question. I remember clicking on Ask A Question. Sorry about that Frank.

Thanks

Rency

Badge +11

Hi

Please go ahead and mark the appropriate response as correct answer.

Badge +9

Steven I had a question. Doesn't the BCS action run on real time data? Meaning when a new item is added to the external content type wouldn't this trigger the workflow that has BCS connection to run? when I manually run the workflow it pulls all the data in. However when a new item is added to the external content type it doesn't retrieve that item in unless I manually run the workflow again.

I am sure I can create a schedule for the list that I have created that will run every 15 minutes, which is fine however I am ending up with a lot of duplicate items being copied back into my list. Suggestions?

Thank you!!

Badge +3

I have never used BCS so I cannot comment on how it runs or if it can kickoff the workflow.  It seems to me your workflow would need to be running 24/7 to pickup new data added to it. 

As for duplicates.  The collection operation has an option to remove duplicates, so you could do that right after your GET collection so that all duplicates would be removed before ever writing.  That is if they are being pulled into the collection as duplicate.

Another option if the list has duplicates in it is to use the Query List action with a filter (this can be set in the Query Action's configuration screen) set to check against the title (or whatever filled/variables you want to check - may want to use your collection variables) of the item you are trying to add.  This way if your query returns a result, you know that an item with that title/variable already exists then you skip writing to the list, which could be done in a Run If statement.  I would probably put the Query List inside the loop so you're basically checking 1 item at a time if you are using your collection variables as query filters.  If the variable you store the query results in is NULL then you know it doesn't exist and you can then write it to the list.  There are a lot of different ways you could do it.

Badge +9

Hi Steven

Thank you for your reply, so you are saying to do the following ?

190709_pastedImage_1.png

Then right after add another collection

190710_pastedImage_2.png

And then create the item?

190712_pastedImage_4.png

My title column will be blank so that column will not be used I will be removing that.

So basically I will end up with additional "remove duplicate" collection operations after each collection variable correct?

190713_pastedImage_5.png

Badge +3

If you are getting duplicates from the BCS table then yes I think that would work.  Although, you may have to remove duplicates BEFORE your get statement and maybe outside of the loop since you already have your collections before getting into the loop.  You may have to play around with that.

If you already have the items on your list and you want to check the list first to see if that item already exist before writing to it again, then I would query the list using the collection variables as filters and if the query returned no results then you know the item doesn't exist on the list and you could write to it.

Reply