How do I add OTHER to a dropdown list populated through a smart object linked to a database table?

Hi ​@drezac have you solved your question? 

​@tbyrne777 Thank you. For some reason, stored procedures are not coming up when I refresh the service instance, so we went with a view to create a union between the table data and “OTHER”. Now I’m just trying to get it to list the column data; It’s not showing data mappings and giving the error “No return properties. SmartObject: [SysOfCareOther] Method: [List]” when I run the view (just running for testing in a sandbox right now, so I’m not concerned with a form.

​@MillaZ I made progress, but have not reached a solution yet.

Is your database SQL Server or something else?

​@tbyrne777 SQL Server. I realized that I needed to modify the permissions on the stored procedure after comparing some stuff. I can now touch the SP in the editor, and it’s not throwing an error, but it won’t populate the dropdown. When I set up the rule, there is nothing listed for input mappings.

Are you able to execute the Smartobject method stand-alone via K2 Management? Just to confirm it works. Would you be able to post what the Stored Proc looks like? I’m wondering if it is formatted in such a way that it is confusing K2.

​@tbyrne777 When I execute it from Management, it returns an empty list. Below this snip is the stored procedure.

 

ST_System_Of_Care is the original table with System being the names I want, and Status being Active or Inactive. After I select just the active names (which works as a SP in SSMS) I append “OTHER” at the end, and this all works in SSMS.

I have noticed that using temp tables can confuse K2 integration - please try using a select from st_system_of_care with a union that includes your “Other” and that will work perfectly

OK, here’s the procedure with a UNION instead of INSERT INTO #temp (leaving stuff commented out until it works right). I’m getting what I want executing the snippet in SSMS, but still not getting thing anything executing the Smart Object method in Management.

I get a confirmation box when I execute the SP in SSMS. Should it do that? I have no parameters, and no need for any.

I decided to edit the Smart Object and it did something, but only returned one row.

Make sure the Type of your Smart Object Method is “List” and not “Execute”:

That got me working. There were some other tweaks I had to make because of naming similarities (System vs SysOfCare) because one is the display value, and the other is the outbound value. It’s shown below so hopefully people after me can avoid similar mistakes.

​@tbyrne777 If you’re ever around Central California, hit me up because I owe you some drinks. Thank you SO much. I sincerely appreciate all your help and patience.

Ha ha glad I could help!

​@tbyrne777 Side note: I was trying to play around with ordering so OTHER would be at the end, and I uncommented the #temp table stuff and commented out the UNION stuff, and it DID work, but sorted OTHER alphabetically, even though I don’t have any sort options set in K2. SSMS shows OTHER at the bottom, but not K2.

It will work with #temp tables until you refresh your service instance, and then you’ll be pulling your hair out wondering why it suddenly stopped working. Instead of #temp try using the “with” clause, like so:

WITH
[systems_of_care] 
(
    [rank]
    , [system]
) AS
(
    SELECT row_number() OVER(ORDER BY [system]) AS [rank], [system]
    FROM ST_System_of_Care
)
SELECT [rank], [system]
FROM [systems_of_care]
UNION
SELECT 999 AS [rank], 'Other' AS [system]
ORDER BY [rank]
 

Thank you yet again, ​@tbyrne777 ! I can’t remember if I ever learned the WITH clause, but if I did I forgot about it. I’ll be sticking that somewhere in my brain for future use. After more tweaking (so much tweaking and testing), I got it to work in my dev environment.