Tree Control using a Stored Procedure

  • 4 February 2016
  • 6 replies
  • 28 views
C
Badge +7

Can anyone point me to the solution of using a SQL Stored Procedure as the data for the SmartObject that builds a Tree Control. I made the sample table from the document K2 - Tree Control. I made a smartobject out of it, then created a stored procedure that returns the exact same data. I added two tree tables, configured them the same. The table is on the left, the stored procedure on the right. The table behaves normal, and they look the same at first, but expanding the table Tree Control get the expected results, expanding Stored Procedure Tree Control, I get the parents all over again and all the children all at once, open another parent and I get it all over again.  

 

 

 

 

Tree Views look the same on the surface:

 

 

14022i4A77B4C9246AFACE.jpg

 

 

 

 

 

Expanding the node is normal for a table, but wonky for a stored procedure:

 

 

14215iCA545C8100950C49.jpg

 

 

 

 

 

Table Data set up from the K2 document under "Creating Smart Objects"

 

 

 

 

 

13359i2A7EC426889E1148.jpg

 

 

 

 

 

The stored Procedure returns the same data as the table. 

 

 

16587i65C9661C89DFBC19.jpg

 

 

13974i186A79E8C0DFA536.jpg

 

 

 

 

 

If anyone has found the solution to fixing the stored procedure in a tree control, please share.

 

 

 

 

6 replies

A
Userlevel 1
Badge +8

Hi Chadg

 

I have experienced the same issue using a list method from a custom service broker. I created a smartbox table with the exact same data the list method was returning and used that as a datasource and it works fine. Change it to the custom broker list and I get similar issues to you.

 

It seems that the tree control doesn't like getting data from anything other than a Smartbox list method. I would raise this with K2 support.

N
Userlevel 1
Badge +4

I would suggest changing your SP to accept a parameter of "parentid" that has a default value such as:

 

REATE PROCEDURE [dbo].[TreeSP]
@parentId INT = 0,

...

 

Then in your sql you should make sure that it uses that parameter

SELECT * FROM TreeViewTable WHERE parentId = @parentId

 

 

Then your related SmartObject List method would accept that optional parameter.

Lastly in your tree control you would have it pass in the parent ID on each level of the tree.  This should also increase performance as it is doing the filtering of the tree records on the server rather than on the K2 server.  Note, you may have to configure the passing of the Parent Id in the rules section and not in the properties of the control.  I experiences some "strange" behavior when I try to do too much reconfiguring with the tree control  So there definetly must be some bugs in there also 😉.

C
Badge +7

@NathanBrown, 


 


I am attempting to use the solution you provided. I have ran into more errors attempting to pass parameters to the smartobject in the tree control. 


 


1) If I use the line @parentId INT = 0


The Smartobject does not a place for an input to be placed. 


instead I used Declare @ParentID INT


Which solved the that issue. 


 


2) When I attempted to Configure the Data Source in the tree control, I would enter data in the  Mapping Destination, but it would not save. 


Can you show me the rules in which you are  reffering to, "Note, you may have to configure the passing of the Parent Id in the rules section and not in the properties of the control."


Where can I pass that in the rules section?


 


Thanks for you help. 


 


Chad

G

Not sure if this will help (considering how old this thread is), but I've attached a mail thread for proposed solutions on how to configure the tree using sproc data sources.

M

we also struggled with this and managed to solve it. 

 

A few considerations. 

- All nodes should have a parentID. All root items should return 0

- The tree control expects to call data branch by branch (one level deep). For this the stored procedure should be able to return only the child nodes of a certain Parent ID,

- The tree control should be configured with 2 datasources (both refering to the same smart object). The first level data source is not recursive, the second  level data source should be recursive and should  refer to the first level as the parent (parentID = ID)

- Make sure you first do the configuration as mentioned here above, and then adjust the configuration of your datasource levels to add extra input parameters if your stored procedure requires more input. I experienced that changing the settings on parentID emptied these other configurations. 

 

Hope this helps. If you need any screenshots, let me know what you're struggling with. 

M

Any solution for this in K2 five ??

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings