|
|
You may attach Data Transfer Service (DTS) to any apps using the App Gallery. In this sample, we will examine how the DTS is attached to the app eBIRForms 2550M/Q to generate DAT files for SLSP.
Step-by-step instructions:
1. Open App Gallery
2. Goto the grid called TableGroup and look for the app named eBIRForms 2550M/Q.
3. Click DTS tab
4. Add DTS Header. The header contains dts type (Import/Export) and the dts keys to be shown under the DTS combobox (Generate Sales DAT file) as shown in the sample above).
5. Add DTS Details for each header. The dts details are the actual dts to be executed when the user double-click the dts key. In this sample three dts will be executed which will retrieve the vat entries for Sales, Purchases and Importation. The data retrieved will be stored on the corresponding data grid.
6. Click Save.
Following are the data definition of DTS Header and the DTS Detail.
DTS Header - serves as the master table for DTS detail. It contains the list of dts to be shown inside the DTS dropdown combobox.
Let's look at the first DTS in the list with DtsKey "Generate Sales DAT file" as the sample value here.
|
Column Name
|
Sample Value
|
Description
|
|
OrdinalNo
|
1
|
Chronological order number of DTS list as shown in the DTS dropdown list. This is optional only and use for sorting dts list.
|
|
DTSCatalogID
|
353701
|
Primary key of the table. This is auto-filled using the ff syntax: GroupID+00 Ex: 103201
|
|
DTSTypeID
|
Export
|
Ff are the types of DTS:
1. Import - the data generated from the DTS List will be imported into the data grid.
2. Export - the data generated from the DTS will be exported to an external file.
We will be exporting the VAT entries to DAT file.
|
|
DtsKey
|
Generate Sales DAT file
|
The key name which serves as menu caption in the DTS dropdown list
|
|
Particulars
|
|
Optional only. Any descriptive note about the DTS
|
|
Instructions
|
|
Any instructions to be shown to the user before the DTS is executed
|
|
DtsConstraints
|
if({grid.Sales.GridRowCount} = 0, 'No Sales records to be generated', ''))
|
A condition, if any, to be evaluated if DTS can be executed. Leave blank if there is no constraint.
We will allow not allow generation of DAT file when there is no records.
|
|
PaginationInputPanelGuid
|
|
Pagination Input Panel to get user input which can be used as parameters for the DTS.
The 100211 contains radio button choices on whether to use Reference Date or Transaction Date in retrieving the vat entries. See image below.
|
|
PaginationSchema
|
|
This is already obsolete. The paging schema, if any
|
|
PostMessage
|
|
Message to be shown after the DTS is successfully executed
|
|
SetFocusTo
|
|
Control name to set focus after the DTS is completed, if any.
|
|
Status
|
A
|
A=Active; I=InActive
|
|
Createdby, DateCreated, Modifiedby, DateModified
|
|
These columns are called audit trails. Their values are auto-generated. One who created or modified the app. It is assumed that the current user is the one who created or modified the record.
|
|
GroupId
|
|
Child key for the master-detail relation
|
|
SeqNo
|
|
Park of primay key and use also in sorting of records
|
DTS Details - Contains the list of DTS to be executed. In this sample above, two dts will be executed. The first dts is the generation of header for the DAT file as discussed below.
|
Column Name
|
Sample Value
|
Description
|
|
OrdinalNo
|
2
|
Chronological order in executing the DTSs. If a master-detail is present, always import the data of the master table before the detail table.
|
|
FileNameExp
|
@{grid.Company.TIN}S
{grid.Return.TaxableMonth}
{grid.Return.TaxableYear}.DAT
|
For Export DTSType, the default file name to be used when exporting the data. The file name expression will be evaluated by the parser.
|
|
TargetTable
|
Header
|
The table name where the retrieved records will be loaded.
|
|
EmptyTargetTable
|
No
|
If Yes, all the existing records in the the target data grid table will be deleted. If No, the retrieved records will be appended instead.
Since this is an Export dts, the target table will always be created with an empty records.
|
|
DataSourceType
|
T-SQL
|
Type of query to be executed, you may select from the ff dropdown list:
1. T-SQL - an sql select statement
2. StoredProcedure - an sql stored procedure
3. CSV File - an sql select statement using csv file
4. DataSet - the current dataset of the app
5. Table - the table name in the current dataset of the app. Does not honor filter
6. Grid - the name of the grid of the current app. Honor filter values
7. Row - the current row in the current app.
8. JournalEntry - a generated journaly entry based on the records in the grid
9. CompoundedJournalEntry - a compounded journal entry base on records of the grid.
10. ExecuteNonQuery - an sql non-query statement
11. ExecuteScalar - an ado.net scalar statement
|
|
SourceTable
SP_Name
|
|
If the DataSourceType is either Dataset, Table, Row or Grid , the name to the table where the records will be retrieved.
For DataSourceType of Stored Procedure (SP), the name of the sp to be executed.
|
|
SelectText
SP_ParamNamesExp
|
SELECT ...
FROM bir_Return ...
@WhereClause
|
An SQL Select staement to be evaluated. If the DataSourceType is StoredProcedure, the parameter names to be passed to the SP
|
|
WhereClause
SP_ParamValuesExp
|
@WHERE DocGuid = {grid.Return.DocGuid}
|
For T-SQL datasource type, the Where Clause statement to be attached to the Select Text. The select statement must include the "@WhereClause" placeholder. It will be replaced by the actual Where Clause when the select statement is evaluated.
For StoredProcedure datasource type, the values of the parameters to be passed
Since we will be exporting the current row and we need only one record for the header, we will make use of the DocGuid as the primary key to retrieve the data from the database.
|
|
TransferMode
|
|
Mode of transferring the data. Either via datatable or via grid as follows:
1. AddToGridRows - data will be added using grid's AddNew mode. Validation of data will be executed.
2. AddToDataTable - data will be added using ado.net table AddRow mode. Faster than #1. No data validation
3. MergeToGridRows - data will be updated using grid's AddNew or Update based on the primary key
4. MergeToDataTable - data will be merged using ado.net merge function using the primary key
Needed only for Import Services.
|
|
RowState
|
Unchanged
|
The inital row state of the imported records to the data grid as follows:
1. Added - the imported records are marked as newly added. If the data grid allows saving of records the data will be saved as new records.
2. Unchanged - the imported records are marked as unchanged. If the data grid allows saving of records, the records will not be saved. If after retrieving, the user made changes then those changes will be saved.
|
|
RenumberColumnName
|
|
The column where the value will be renumbered before importing will be applied. Sometimes it is necessary to renumber the values to be consistent with the existing data in the target table.
|
|
TargetColumns
|
|
Names of columns, separated by comma, to be mapped against the Source columns. If blank, all columns with the same name will be mapped.
|
|
SourceColumns
|
|
Names of columns, separated by comma, to be mapped against the Target columns. If blank, all columns with the same name will be mapped.
|
|
PrimaryKeys
|
|
Column names of primary keys, separated by comma when merging is applied. This is important when using Transfer Mode: MergeToGridRows and MergeToDataTable
|
|
AddNotFoundRecord
|
|
When primary key is present, determine if the retrieved records that are not found will be added
|
|
ColumnTypes
|
|
Obsolete already. The type of the column for the TargetColumns and SourceColumns
|
|
QuoteColumns
|
TIN, RegisteredName ,LastName, FirstName, MiddleName
|
For Export service, the column where the value will be enclosed in double quotes
|
|
UnQuoteColumnsWhenBlank
|
TIN, RegisteredName ,LastName, FirstName, MiddleName
|
For the QuoteColumns above, when the value is blank there is no need to put double quote
|
|
Status
|
A
|
A=Active; I=InActive
|
|
Createdby, DateCreated, Modifiedby, DateModified
|
|
These columns are called audit trails. Their values are auto-generated. One who created or modified the app. It is assumed that the current user is the one who created or modified the record.
|
|
DtsCatalogID
|
|
Hidden column. Part of primary key and a child key for the master-detail relation
|
|
SeqNo
|
|
Hidden column. Park of primay key and use also in sorting of records
|
The second dts is the generation of details for the DAT file as discussed below.
|
Column Name
|
Sample Value
|
Description
|
|
OrdinalNo
|
2
|
Chronological order in executing the DTSs. If a master-detail is present, always import the data of the master table before the detail table.
|
|
FileNameExp
|
|
For Export DTSType, the default file name to be used when exporting the data. The file name expression will be evaluated by the parser.
Leave it blank to append the records into the first dts instead of creating and saving them to another file.
|
|
TargetTable
|
Detail
|
The table name where the retrieved records will be loaded.
|
|
EmptyTargetTable
|
No
|
If Yes, all the existing records in the the target data grid table will be deleted. If No, the retrieved records will be appended instead.
Since this is an Export dts, the target table will always be created with an empty records.
|
|
DataSourceType
|
T-SQL
|
Type of query to be executed, you may select from the ff dropdown list:
1. T-SQL - an sql select statement
2. StoredProcedure - an sql stored procedure
3. CSV File - an sql select statement using csv file
4. DataSet - the current dataset of the app
5. Table - the table name in the current dataset of the app. Does not honor filter
6. Grid - the name of the grid of the current app. Honor filter values
7. Row - the current row in the current app.
8. JournalEntry - a generated journaly entry based on the records in the grid
9. CompoundedJournalEntry - a compounded journal entry base on records of the grid.
10. ExecuteNonQuery - an sql non-query statement
11. ExecuteScalar - an ado.net scalar statement
|
|
SourceTable
SP_Name
|
|
If the DataSourceType is either Dataset, Table, Row or Grid , the name to the table where the records will be retrieved.
For DataSourceType of Stored Procedure (SP), the name of the sp to be executed.
|
|
SelectText
SP_ParamNamesExp
|
SELECT ...
FROM bir_VATSale ...
@WhereClause
|
An SQL Select staement to be evaluated. If the DataSourceType is StoredProcedure, the parameter names to be passed to the SP.
We will retrieve the VAT details from the table bir_VATSale.
|
|
WhereClause
SP_ParamValuesExp
|
@WHERE DocGuid = {grid.Return.DocGuid}
|
For T-SQL datasource type, the Where Clause statement to be attached to the Select Text. The select statement must include the "@WhereClause" placeholder. It will be replaced by the actual Where Clause when the select statement is evaluated.
For StoredProcedure datasource type, the values of the parameters to be passed
The DocGuid is the foreign key to retrieve all the vat entries from the table bir_VATSale.
|
|
TransferMode
|
|
Mode of transferring the data. Either via datatable or via grid as follows:
1. AddToGridRows - data will be added using grid's AddNew mode. Validation of data will be executed.
2. AddToDataTable - data will be added using ado.net table AddRow mode. Faster than #1. No data validation
3. MergeToGridRows - data will be updated using grid's AddNew or Update based on the primary key
4. MergeToDataTable - data will be merged using ado.net merge function using the primary key
Needed only for Import Services.
|
|
RowState
|
Unchanged
|
The inital row state of the imported records to the data grid as follows:
1. Added - the imported records are marked as newly added. If the data grid allows saving of records the data will be saved as new records.
2. Unchanged - the imported records are marked as unchanged. If the data grid allows saving of records, the records will not be saved. If after retrieving, the user made changes then those changes will be saved.
|
|
RenumberColumnName
|
|
The column where the value will be renumbered before importing will be applied. Sometimes it is necessary to renumber the values to be consistent with the existing data in the target table.
|
|
TargetColumns
|
|
Names of columns, separated by comma, to be mapped against the Source columns. If blank, all columns with the same name will be mapped.
|
|
SourceColumns
|
|
Names of columns, separated by comma, to be mapped against the Target columns. If blank, all columns with the same name will be mapped.
|
|
PrimaryKeys
|
|
Column names of primary keys, separated by comma when merging is applied. This is important when using Transfer Mode: MergeToGridRows and MergeToDataTable
|
|
AddNotFoundRecord
|
|
When primary key is present, determine if the retrieved records that are not found will be added
|
|
ColumnTypes
|
|
Obsolete already. The type of the column for the TargetColumns and SourceColumns
|
|
QuoteColumns
|
TIN, RegisteredName ,LastName, FirstName, MiddleName
|
For Export service, the column where the value will be enclosed in double quotes
|
|
UnQuoteColumnsWhenBlank
|
TIN, RegisteredName ,LastName, FirstName, MiddleName
|
For the QuoteColumns above, when the value is blank there is no need to put double quote
|
|
Status
|
A
|
A=Active; I=InActive
|
|
Createdby, DateCreated, Modifiedby, DateModified
|
|
These columns are called audit trails. Their values are auto-generated. One who created or modified the app. It is assumed that the current user is the one who created or modified the record.
|
|
DtsCatalogID
|
|
Hidden column. Part of primary key and a child key for the master-detail relation
|
|
SeqNo
|
|
Hidden column. Park of primay key and use also in sorting of records
|
Below is the screenshot implementation of the above DTS to generate the Sales DAT file for the month of February 2020. The default file name is taken from the FileNameExp mentioned above.
|
|
