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.