|
|
You may attach Data Transfer Service (DTS) to any apps using the App Gallery. In this sample, we will examine how the DTS is used to import VAT entries from the GL System into the app eBIRForms 2550M/Q.
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 (Import Data From T10 GL 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 "Import From T10 GL" 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
|
353704
|
Primary key of the table. This is auto-filled using the ff syntax: GroupID+00 Ex: 103201
|
|
DTSTypeID
|
Import
|
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.
|
|
DtsKey
|
Import data from T10 GL
|
The key name which serves as menu caption in the DTS dropdown list
|
|
Particulars
|
|
Optional only. Any descriptive note about the DTS
|
|
Instructions
|
WARNING: This will empty the records in the target table.
|
Any instructions to be shown to the user before the DTS is executed
|
|
DtsConstraints
|
|
A condition, if any, to be evaluated if DTS can be executed. Leave blank if there is no constraint.
|
|
PaginationInputPanelGuid
|
100211
|
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
|
Pagination 100211 contains radio buttons to choose whether to use Reference Date or Transaction Date when retrieve VAT entries. The Reference Date is the date of the actual of the Invoice or OR while the Transaction Date is the date when the VAT is recorded in the books..
DTS Details - Contains the list of DTS to be executed. In this sample above, 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 grids. Discussed below is the first DTS list which is Sales.
|
Column Name
|
Sample Value
|
Description
|
|
OrdinalNo
|
1
|
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
|
|
TargetTable
|
Sales
|
The data grid table where the retrieved records will be loaded
|
|
EmptyTargetTable
|
Yes
|
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.
|
|
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 gl_BookSL ...
@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
|
if('{Pagination.RadioButton3}'='True' ...
|
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 value of {Pagination.RadioButton3} will be taken from the PaginationInputPanelGuid=100211 as discussed in the DTS Header above.
|
|
TransferMode
|
AddToGridRows
|
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
3. MergeToDataTable - data will be merged using ado.net merge function using the primary key
|
|
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
|
|
Obsolete already. For Export service, the column where the value will be enclosed in double quotes
|
|
UnQuoteColumnsWhenBlank
|
|
Obsolete already. 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 where the VAT entries from the GL System for the month of February 2020 are retrieved:
|
|
