Command buttons with SPs

There are few flavors of commands.

 

  • Command at row level - which we handle through related pages - is not part of this discussion.
  • Command at table/grid level, placed at the grid header.
  • Command at page level.

we are going to look at implementing -   Command at table/grid level, placed at the grid header.

 

Command at table/grid level, placed at the grid header - is used widely - in all etask/CRM/payroll workflow grids.

In payroll the entire menu "Payroll Process" uses this. In addition to workflow, it can be used for other purposes also.

In CUD, all operations are done internally through Entity service, and hence there is no need to access the #table directly. 

We had ability to access the #table data with DLL methods or using System method "QueryResponseTables". But they all required coding in page or writing DLL method.

With the latest change, it is possible to directly access the #table data in queries and stored procedures.

How to do

Refer to the sample page in https://web1.mclsystems.com/CadebillV320Debug 

Setup - Types - Campaign Type 

Here there is a sample command button "Transfer Data". This button is put in footer instead of header, and hence you see at bottom of grid.

In NF - See Entity - CampaignType

Read method CampaignTypeRead - reads the data

Execute method - CampaignTypeTransferData - is set as "Table Non-Read Target Method" in read method

This  method has a request table  - CampaignTypeTransferRequest

There are two settings you need to do here.

1)Check the box "data_transfer_table".

2) In Table fields - there is one field - CampaignTypeDataTableName - set the "data_transfer_table_name" value same as as Read response table "Campaign Type".

In the above example there is only one field CampaignTypeTransferRequest. But it can have other request fields, like any other request table.  It can also have zero or more data_transfer_table, if the read method has many tables, zero or more tables can be set as data_transfer_table.

 

These two settings -  sends the #table name ( e.g. [#campaign_type_read_response_57811566295040_1]) as a  parameter to the execute method. In this case SP has one parameter to receive the #table name.

The sample sp code is like below. As the #table name is passed to the SP, it can use the name to directly  read and use the data.

 

-------------------

ALTER proc [temp].[campaign_transfer_data]( @campaign_type_data_table_name varchar(256))

as

begin

 

truncate table temp.ctd

 

exec('

insert into temp.ctd(cam_type_id, cam_type_name, cam_type_desc, week_days, week_days_int)

select cam_type_id, cam_type_name, cam_type_desc, week_days, week_days_int from ' + @campaign_type_data_table_name

)

 

insert into temp.ctd_log(transfer_date) 

select getdate()

 

end


 

Once you get the name of the table, you can use in any way. For example, to get the chosen rows - you will use like:  select * from #table where choose = 1

You can also join this table with any queries within the SP and directly access it.

For the workflow perform action - you can simply use the parameter for giving data source. wf_perform_action  should use @app_pk_array_data_source = (select * from #table where choose = 1)

 

workflow/Payroll process should eliminate all code using Queryresponsetables and javascript.

You can simply reuse existing SPs as the sample code below ( method_type = Sql)

 

declare @emps

set @emps= stuff((select '~' + emp_no from #table for xml path('')), 1, 1, '')

--@emps gives the string that you build in javascript using queryresponse tables.

-- use the @emps in your existing SPs.

execute payroll_sp @emps

 

As you can see - you are building a string, and then splitting this back in the payroll process SPs..  and processing one record at at time (?? or bulk?)

If you change your payroll processing SP to take the #tablename directly (as in sample campaign_transfer_data), and use @app_pk_array_data_source, perform action will be extremely fast.