Hi Dan,
If i understood your concern really, then there are two ways to get it sorted:-
1. Create an input parameter in the stored proc and pass this as an input value in the Get list method on the view rule
2. Let the list return everything and use the Filter option in the views in Get list method
Though the former approach is advisable but still it depends on case to case.
Regards
Gaurav Mehta
Thanks Gaurav, I understand 2. That sounds a decent workaround thanks.
I don't follow 1 though - "Create an input parameter in the stored proc and pass this as an input value in the Get list method on the view rule".
I've created the parameter in my stored procedure @LS_ID however it doesn't seem to prompt me for this at all. I tried to create that parameter named the same, etc, but it doesn't seem to take it as I receive an error saying it hasn't been passed to the stored procedure, etc.
Did you refresh the service instance after adding the parameter? I create Stored Procedures quite often with K2 and SQL and this would be the correct approach to passing data into a stored procedure to use that value as a filter in your procedure.
Also, as a trick if you want to make your input parameters optional as sometimes you may want to filter and others you don't, follow these additiona steps.
1) On the service instance configuration, set the value about using parameters to "false" so that your parameters will be exposed as properties.
2) In your stored procedure, be sure to set a default vaule of NULL on your input parameters @My_Param INT = NULL
3) In the where clause of your method, use this pattern when adding the parameters you want to filter by:
WHERE MyFirstColumn = COALESCE(@My_Param, MyFirstColumn)
AND MySecondColumn = COALESCE(@My_2ndParam, MySecondColumn)
etc...
This makes your list method where it can filter on any combination of your input parameters making it much more flexible.
Finally, besure to fully qualify all your object names MySchema].cMyTable].TMyFirstColumn]. This has the benefit of allowing SQL to create an explainplan that reuseable thus squeezing a bit more performance out of SQL. Granted depending on the scenario it may not be much, but at scale every little bit helps.
Hope this helps.
S.
Thanks Scott, I needed to refresh the service instance! Sorry, I'm new to this!
Thanks Guarav, I can't mark you both as solutions sorry.
Ok so I still had problems even after refreshing the instance. I could see the input parameter fine but it wasn't picking it up.
I ended up trying something silly but it actually worked...I deleted the view from my form, added it again, voila it started working.
Wow that's pretty annoying but as least I know to try that in future.
I've run into that issue also. It was explained to me that this is normal behavior:
If I update an SMO with a new field or parameter that is used by a View. Then immediatly go to the Form that contains the View (without opening the view) then the View will not show the new field or parameter.
Thus, I make it our best practice to always edit and save any view after we modify an SMO.
Yes, unfortunately that is expected behavior. If you update the SmartObject your have to open and refresh the view and then you will see it at the form level.
SmO --> View --> Form
Glad you got it working!
S.
