{"id":979,"date":"2022-03-09T12:07:26","date_gmt":"2022-03-09T05:07:26","guid":{"rendered":"https:\/\/www.bagi2info.com\/?p=979"},"modified":"2022-09-11T14:02:04","modified_gmt":"2022-09-11T07:02:04","slug":"k2-smartform-filter-pencarian-dengan-sql-server-stored-procedure","status":"publish","type":"post","link":"https:\/\/www.bagi2info.com\/en\/k2-smartform-filter-stored-procedure-search-in-sql-server\/","title":{"rendered":"K2 SmartForm Filter Stored Procedure Search in SQL Server"},"content":{"rendered":"

Because the default search filter provided by K2 Smartform is not preferred, after searching for other alternatives, finally found the use of stored procedure(SP) as an alternative to search filters.<\/p>\n

Here are the steps to create a custom search filter with a stored procedure<\/p>\n

1. Create a Student smart object with the following table:
\n<\/strong>
\nid int,
\ncodemhs string,
\nstring name,
\ndate of birth date,
\nString major<\/p>\n

2. Create a stored procedure with the following code and run it in a sql query:<\/strong><\/p>\n

CREATE PROCEDURE [dbo].[StudentGetList]
\n@pkodemhs nvarchar(100) = ”,
\n@pnama nvarchar(100) = ”,
\n@ptgl_born date = ‘1900-01-01’,
\n@pjurusan nvarchar(100) = ”,
\n@SOP nvarchar(100) = ‘=-=-=-=’
\nUS
\nSELECT TOP 1 ID, code, name, date of birth, major
\nFROM [dbo].[Student]
\nwhere 1 = 1<\/p>\n

3. Do Refresh Service Instance
\n<\/strong>
\n\"\"<\/a><\/strong><\/p>\n

4. Then Create Smartobjects<\/p>\n

\"publishso\"<\/strong>
\n
\n5. Perform an Alter Stored Procedure with the following code:<\/strong><\/p>\n

CREATE PROCEDURE [dbo].[StudentGetList]
\n@pkodemhs nvarchar(100) = ”,
\n@pnama nvarchar(100) = ”,
\n@ptgl_born date = ‘1900-01-01’,
\n@pjurusan nvarchar(100) = ”,
\n@SOP nvarchar(100) = ‘=-=-=-=’
\nUS<\/p>\n

set @pkodemhs = LTRIM(RTRIM(@pkodemhs))
\nset @name = LTRIM(RTRIM(@name))
\nset @pjurusan = LTRIM(RTRIM(@pjurusan))<\/p>\n

set @sql = ‘SELECT ID, code number, name, date of birth, major
\nFROM [dbo].[Student]
\nwhere 1 = 1’<\/p>\n

if (LEN(@pkodemhs) > 0) begin
\nset @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 1))<\/p>\n

if @OP = ‘=’
\nset @sql = @sql + ‘ and codemhs ‘ + @OP + ‘ ”’ + @pkodemhs + ””<\/p>\n

if @OP = ‘Start With’
\nset @sql = @sql + ‘ and codemhs Like ”’ + @pkodemhs + ‘%”’<\/p>\n

if @OP = ‘Like’
\nset @sql = @sql + ‘ andkodemhs ‘ + @OP + ‘ ”%’ + @pkodemhs + ‘%”’
\nend
\nif (LEN(@name) > 0) begin
\nset @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 2))<\/p>\n

if @OP = ‘=’
\nset @sql = @sql + ‘ and name ‘ + @OP + ‘ ”’ + @name + ””
\nif @OP = ‘Start With’
\nset @sql = @sql + ‘ and name Like ”’ + @name + ‘%”’
\nif @OP = ‘Like’
\nset @sql = @sql + ‘ and name ‘ + @OP + ‘ ”%’ + @name + ‘%”’
\nend
\nif (LEN(@ptgl_born ) > 0) and (@ptgl_born <> ‘1900-01-01’) begin
\nset @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 3))<\/p>\n

if @OP = ‘=’
\nset @sql = @sql + ‘ and date of birth ‘ + @OP + ”” + convert(varchar(10), @ptgl_birth ) + ””
\nif @OP = ‘Start With’
\nset @sql = @sql + ‘ and date of birth Like ”’ + convert(varchar(10), @ptgl_birth ) + ‘%”’
\nif @OP = ‘Like’
\nset @sql = @sql + ‘ and date of birth ‘ + @OP + ‘ ”%’ + convert(varchar(10), @ptgl_birth ) + ‘%”’
\nend<\/p>\n

if (LEN(@pjurusan) > 0) begin
\nset @OP = REVERSE(PARSENAME(REPLACE(REVERSE(@SOP), ‘-‘, ‘.’), 4))<\/p>\n

if @OP = ‘=’
\nset @sql = @sql + ‘ and major ‘ + @OP + ”” + @department + ””
\nif @OP = ‘Start With’
\nset @sql = @sql + ‘ and majors Like ”’ + @pjurusan + ‘%”’
\nif @OP = ‘Like’
\nset @sql = @sql + ‘ and majors ‘ + @OP + ‘ ”%’ + @department + ‘%”’
\nend<\/p>\n

execute (@sql)<\/p>\n

6.Then create a form with the following view:<\/strong><\/p>\n

\"\"<\/a>
\n
\n7. On the Search button add a List method with input parameters as shown<\/strong><\/p>\n

\"\"<\/a><\/p>\n

K2 by default will detect whether the stored procedure has a List method or an Execute method, but there are problems in recognizing the Stored Procedure which is a little advanced in number 5, causing K2 to provide an Execute method even though what we want is a List method.<\/p>\n

Therefore, to get the List Method<\/strong>, the first time we outsmart K2 with simple sql coding at the beginning, namely number 2, after Refreshing Service Instances<\/strong> on Service Objects<\/strong> in SmartObjects<\/strong> – Services<\/strong> – Tester<\/strong> and getting List Method<\/strong> then do alter SP again at number The 5th without doing a Refresh Service Instance, so K2 will assume that the SP uses the List Method.<\/p>\n

The weakness in this method is that it enforces the List method if you do a Refresh service Instance<\/strong>, then K2 will give the Execute method to the SP, and we have to outsmart the SP with a simple SP again.<\/p>\n

The sample file above can be downloaded here<\/a><\/p>\n

Newest way to display List Method<\/h2>\n

After contacting K2, here are the steps to display the List Method for complex SP<\/p>\n

Create Parent SP that accesses SP StudentGetList<\/p>\n

CREATE PROCEDURE [dbo].[StudentGetListParent]
\n@pkodemhs nvarchar(100) = ”,
\n@pnama nvarchar(100) = ”,
\n@ptgl_born date = ‘1900-01-01’,
\n@pjurusan nvarchar(100) = ”,
\n@SOP nvarchar(100) = ‘=-=-=-=’
\nUS<\/p>\n

IF 1=0 BEGIN
\nSET FMTONLY OFF
\nEND
\nexec [StudentGetList] @pkodemhs,@pnama,@ptgl_lahir,@pjurusan,@SOP;
\n–SET FMTONLY OFF<\/p>\n

Testing environment using<\/p>\n

K2 version 11
\nSQL Server
\nSQL Server Management Studio<\/p>\n","protected":false},"excerpt":{"rendered":"

Because the default search filter provided by K2 Smartform is not preferred, after searching for other alternatives, finally found the use of stored procedure(SP) as an alternative to search filters. Here are the steps...<\/p>\n","protected":false},"author":1,"featured_media":988,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-979","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-komputer"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/979","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/comments?post=979"}],"version-history":[{"count":0,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/posts\/979\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/media\/988"}],"wp:attachment":[{"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/media?parent=979"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/categories?post=979"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bagi2info.com\/en\/wp-json\/wp\/v2\/tags?post=979"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}