Delete records SQL SO

  • 2 February 2015
  • 5 replies
  • 1 view
N
Badge +5

Hi,

I have created one SharePoint list Colum’s are

Approver Name & sequence.

I want used this list as destination group so I had created SO for this list but I found that records are fetch randomly. since there is no way to arrange the approvers in sequence using sequence column of the list. I had write SharePoint Event wizard and trigger workflow which save data in SQL table. I had created the store procedure which checked if the List ID is present it update the data else add new records in sql table.

Now my problem is that suppose any one deleted the columns from SharePoint list. How I can find out that and deleted the records in SQL table. I had checked the ItemDeleted option in my SharePoint event wizard.. Is there any way so I can find out that Item Deleted method is called and I can delete that records from SQL table.

 

Thanks in advance.

5 replies

M
Badge +10

Hi Niks,

 

I'm not 100% clear on the solution you're building, but perhaps a workflow that fires when "a list item is changed" might be able to help.  My understanding is that you need to deal with a condition where an item is deleted from a sharepoint list, and then somehow remove the relavent entries from SQL.  I've reconstructed something similar to your list, based on your description:

 

11443i0DBDC830C0EA6561.png

 

Now, at this point, I have a workflow that specifically deals with what to do when an item is deleted from the list.  The steps in this workflow will deal with what to do when an item is deleted from the list, and happen automatically.  As far as the relationship between your SQL data and your SharePoint data, that I'll need some more details to work out.  I assume that the approver, or some other ID value, exists in your SQL table, and therefore you'll have some way to use the information in the sharepoint list to selectivley delete/update your SQL table.

 

Hopefully that helps,


Mike

 

 

N
Badge +5

Hi Mike,

 

Thanks for your reply.

 

What i tired to achive is.

1. I have sequential approval workflow. and Apporvers name stores in Sharepoint List. Suppose if i create SO base on SP list and assign as destination group . The task assigment was done randomly.It will not consider the sequance column in SP list to assign the worklist item sequentailly.

Is there any way i can used SP list as destination but it will consider the sequance column while assign the worklist items?

 

2. Since this approch not worked hence i had create the SQL table which has same column as sharepoint list, and  create procedure which check if SP list ID exists in table it  will update the record else it will add the record.

 

SQL TABLE COLUMNS - ID,Name,Sequance,ListID

 

IF EXISTS(SELECT ID FROM K2_ApprovalList WHERE ListID= @ListID)
BEGIN

UPDATE K2_ApprovalList
SET Name=@ApprovalName,
Sequance=@ApprovalSequence
WHERE ListID=@ListID

END

ELSE

BEGIN
INSERT INTO K2_ApprovalList (Name,Sequance,ListID)
VALUES(@ApprovalName,@ApprovalSequence,@ListID)
END
GO

 

This procedure works perfectly if New Item added or any updation done in existing Item.

 

My problem is suppose any one want to remove any approval from SP list. I want to remove that records from SQL as well. Since the Approval assigment has been done though data which was save in SQL table.

 

How to delete the records when "Item Deleted methods has been fired" on SP list.???

 

Thanks

N
Badge +5

Hi Mike,

 

Did you get change to look into this issue. 

M
Badge +10

Niks,

 

Just to make sure I follow.

 

1.  The same list exists in both SP and SQL

2.  When an approver is removed from the sharepoint list, we want to delete the corresponding entry in SQL

 

This shouldn't be too hard as long as we have a common field between the two.

 

Create workflow that fires "when item is deleted" as described before

Use "deleted" values from sharepoint list to delete the entry based on a unique field that both the SP list and SQL table have in common.

 

Just want to make sure that's what's needed before I get started.  I can see this perhaps being problematic if the delete action fully completes before the workflow can fire, which would cause some difficulty in using that information as a reference value.  Maybe we'll just have to iterate through the SQL table or sharepoint list and find the ones that don't match.

 

Regards,


Mike

N
Badge +5

Hi Mike,

 

Thanks for your reply. I  will try this and get back to you.

 

Any suggestion how to achive  below issue??

 

I have sequential approval workflow. and Apporvers name stores in Sharepoint List. Suppose if i create SO base on SP list and assign as destination group . The task assigment was done randomly.It will not consider the sequance column in SP list to assign the worklist item sequentailly.

Is there any way i can used SP list as destination but it will consider the sequance column while assign the worklist items?

 

 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings