Is it possible to have 'Get List items by View' on a SQL Service Instance?

  • 20 October 2020
  • 2 replies
  • 16 views
J

Hi,

Usually I use SharePoint Lists as my data Source and us the ‘Get List by View’ if I have a very large database (to speed up results).

 

However, I am hooking into a Smart Object that has a SQL Database as a source, and the only Method available is ‘List’ …. Is this a restriction of using a SQL database or is it because this is the way the service instance is set up?  Could this method be made available by editing the service instance and redeploying a smart object?

 

(I am a novice where SQL is concerned).

 

Thanks

 

2 replies

P

Hi @JulieBird,

 

In order to get the other methods (create, read, update, delete), your SQL tables must have primary keys. Incase you are doubtful of your service instance setup, please look at this document. As for the primary key, if you dont want to delete your table/s and re-create them with primary keys, I recommend that you look into some “alter table” sql queries in order to make a field/column a primary key field. You might find this useful:

https://www.w3schools.com/sql/sql_primarykey.ASP

 

Hope this helps :)

 

Kind Regards 

Prineel

 

 

 

Y
Badge +6

Please check it out to create advanced sql server smartobjects

https://help.k2.com/onlinehelp/classic/userguide/5.4/default.htm#How-Tos/AdvancedSmartObjects/SQLServer/CreateAdvSmartObject-SQL.htm

 

Table object (with primary key),  The generated smartobject have method list, read, update, delete, create

Stored procedure object,  the generated smartobject has method Execute.  

View object, the generated smartobject has method List.  

 

You can generate List method from stored procedure smartobjects (sometimes a bit tricky in complex stored procedure)

  1. ensure SET NOCOUNT ON
  2. stored procedure just simply return query results  or, 
  3. stored procedure output record from function table or,
  4. for complex stored procedure with ton’s of logic, you can use variable table to stored processing result then later do select statement on the last line.

 

 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings