How to check duplicate records in MS SQL table , SharePoint Document Library

  • 19 April 2016
  • 2 replies
  • 0 views
I
Badge +9

HI Guys,

Workinng on Smartform , especially when user interaction with backend system is needed, is a huge challenge. so, I have a smartform with views and user input some of the values like InvoiceNumber,InvoiceDate and Invoice Amount. When I submit the form by submit button action, I need to check whether such record already exist in MS SQL Database table and also in SharePoint Document Library. If duplicate record exist, then user should be informed in a messagebox that such record already exist either in SQL table or in Sharepoint library as meta-data or in BOTH.

 

Is there a way to make the smartform enable for duplicate check. My bet is to use smartobject using stored procedure but not sure how to accomplish this.

Please help or direct me to the help resource where I can best understand the process.

 

Thanks.

2 replies

A
Userlevel 3
Badge +8

Dear ,

 

i do believe u could do this by following steps,

lets say u have table with ID , Name table name is Employees

 u want to check for duplicate names , and u got saving event on ur SUBMIT button ,

1-add data label on ur form with convinient name like "Exists" or "Duplicated" give it initiate value such as 0.

2-for ur submit button u will have rules as following

Execute get list method for Employees , go to ur filter pane and filter it by Name=(The text box from ur form)  , and in output pane get the ID back to the "Exists" data label

3-Advance condition , (if exists>0)

then show msg that the name u r tryin to save already exist .

else

save/execute the methods u want cuz it means there is no name such as the name u r trying to save.

 

 

Hint : its all about using get list method filtered by the value u want to save , and if u get any ID value back from the SQL or sharepoint list  , then it means that the record exists , if u didnt get any value , then it means ur fine to save .

 

Hope it helps!

Regards.

I
Badge +9

Thank you Ahmad for your response. I could able to find a duplicate invoice fairly through smartobject. There are the steps I performed:

1. Create a StoredProcedure that takes two input parameter : VendorID and InvoiceNumber and a simple QUERY to select the InvoiceNumber on the Table based on supplied input parameter.

 

2. Register the ServiceInstance that creates a serviceobject of that storedProcedure using service tester utility.

 

3. Create a smartobject out of serviceobject.

 

4. In the SmartForm, create a parameter of type string InvoiceNumber and set its initial value to NULL

 

5. In the rules section, when a submit button is clicked, smartobject list method is executed and map the input parameter VendorID and InvoiceNumber from the form and also map the output value (Id or any return values, I choose InvoiceNumber if exist) to the form parameter (I use InvoiceNumber).

 

Note: If the Invoice exist based on the vendorid and invoicenumber, then return value will be saved into the form parameter (InvoiceNumber). Initial value of invoicenumber from NULL will be changed returned value in this case.

 

6. Check for advance condition: if the parameter value is other than NULL, then duplicate invoice already exist in table and show the invoicenumber into the messagebox.

 

This is how I accomplish the duplicate value check. I donot know if this is the best practice but it works. Please share your experience in handling this kind of scenario when user submit data should be checked for duplicates against some database table.

 

Thank you.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings