Data Send

You use Data Send to post data from an Microsoft® Office Excel worksheet to your product database.

Q&A DataLink Server connects to other products through DataLinks.

The are two specific DataSend security roles available in Q&A Cloud:
  • QAA-DataSend

    Each user with QAA-DataSend role assigned can write data to the DataLink Application. With this role you have access to all DataSend definitions.

  • QAA-DataSend-<ProductCode>-<DefinitionName>

    Each user with the QAA-DataSend-<ProductCode>-<DefinitionName> role assigned is provided with role based security permission for specific Q&A Cloud definition. With this role a user can be restricted to use only specific DataSend definitions. Each user with a specific role assigned can access that specific DataSend definition and send data to Sunsystems.

Sending data between products requires access to particular security roles. To post data from Q&A Cloud to SunSystems Cloud these security roles are required:
  • SUN-SSC-
  • SUN-USERS-

A data send definition is a structure of database fields. You complete the definition by mapping each database field to a field in the Excel worksheet.

The data is extracted from Excel and validated according to the selected data send definition.

Different types of record are stored in different ways in a product database. For example, a customer record will have a different structure from a record of a financial transaction.

There are three formats for Data send definitions:

  • Basic: Each field in the source worksheet maps to an equivalent field in the product database.
  • Layout: Layout data send definitions are used to create complex records, for example, where a record consists of a header and body.
  • Matrix: Matrix data send definitions post the same data for each value in a range of values, called the Matrix Replicator.

The format of the data send definition determines the format in which you specify the data in the Excel worksheet. Each field in the Excel worksheet represents a field that exists in the database. Normally the fields in the worksheet are arranged in columns and rows. For basic data send definitions, you could specify the worksheet layout to mimic the layout of fields in the user interface of the product. This could help users who are familiar with the product to make the mappings between the worksheet and the data send definition.

In each data send definition, one of the fields is used to identify the start of a each record. In Basic and Matrix data send definitions, a driver code identifies the start of a new record. The driver code is mapped to a mandatory Key field in the database. The remaining fields are mapped to their corresponding fields in the database. The driver code is often the first column in the data set. Layout data send definitions have a Layout Identifier instead of a driver code.

Basic data send examples

This example represents the structure of a basic data send. The Driver Code is cell A2:
A B C D
1 Field 1 Field 2 Field 3 Field 4
2 10001 10 20 40
3 10002 4 6 8
4 10003 7 9 3
In the basic data send definition, map these fields and values:
Field Name Value
Field 1 A2
Field 2 B2
Field 3 C2
Field 4 D2

Data Send scans across each row and posts the cell's data. To limit the range of data which is sent, you can specify A2:A3 as the driver code. Data Send sends the data in rows two and three but not in row four.

Alternatively, you could specify the absolute reference $A$2 as the driver code. In this case, Data Send sends the data only in row two.

Matrix data send examples

This example represents the structure of a matrix data send definition. The driver code is B8.
A B C D E
2 Period 2011001
3 2011002
4 2011004
5
6 Code
7 10001 10002 10003
8 Sales 4 6 8
9 Dept IT 5 2 1
10 HR 7 9 3
The range D2:D4 is the matrix replicator.
In the matrix data send definition, map these fields and values:
Field Name Value
Dept B8
Code $C$7:$E$7
Period D2:D4
Quantity C8:E8
The same data matrix is posted for the periods 1, 2 and 4 in the year 2011.

Layout data send examples

Layout data send definitions have a Layout Identifier instead of a driver code.

This example of a data send definition creates item records in a product database. Each item record has these fields:
  • Code
  • Color
  • Quantity
  • Status
Because of the structure of the product database, these fields are assigned to three layers:
  • The first, or header, layer contains the Code and Unit fields.
  • The second layer contains the Color and Quantity fields.
  • The third layer contains the Status field.
The hierarchy is displayed in this example:
Level Field
1 Code
1 Quantity
2 Color
3 Status

Multiple Layouts are represented as hierarchy tree in the Data Send definition form. Each branch can consist of multiple layers and fields.

The source Excel worksheet contains this data:
A B C D E
1 Layout Identifier Code Quantity Status Color
2 1;3 1001 6 New Red
3 1;2;3 1002 2 Old Yellow
4 1 1003 9 New Green

The Layout Identifier codes are numeric and relate to the sequence in which the layouts are created in the definition. The Layout Identifier codes should be held in one worksheet column. Multiple Layout Identifiers can be defined by separating the Layout Identifier codes with semi-colons (;).

In the data send definition, you map these values:

Level Field Value
1 Code B2
1 Quantity C2
2 Color E2
3 Status D2
The data is processed as follows:
Layout Identifier Data Sequence
1 1001, 6
3 New
1 1002, 2
2 Yellow
3 Old
1 1003, 9