Normalized Source Data

Beginning in version 1.9.5 Sync & Save can populate fields in repeating sections and tables from a normalized SQL data.

• Enter multiple SQL statements in the Query SQL field.
• The first query listed must return one record for each Dispatch to be created and must contain a unique key.
• After the first query, all additional queries can return one or more records that can be used to populate fields in repeating sections or tables. All queries must include the same key included in the first base query.
• Check the Normalized Source Data option. If this option is not checked only the first query entered will be evaluated.
• Enter the name(s) of the Key Field(s) that make up the unique key of the first query. If more than one field make up a composite key, separate the field names with a semicolon.
  
  

• Click Refresh on the Source to load example data. If only values from the first query are displayed, make sure the key field configuration is correct on the Settings tab.
• Click Populate Fields to load the fields available in the form. Note that repeating fields are marked with an asterisk.
• Note that Sync & Save automatically assigns table names (Table, Table1, etc.) in order to structure the incoming data.
• Map fields as usual. Note that the full path of the field is included.
• Click Preview on the Source to preview the data that will be sent to doForms.

When the job is run, two records are dispatched. The second corresponds to the source record displayed above.

Denormalized Source Data

Denormalized source data is in the format of a single line or record in a single table for each required Dispatch.

To use denormalized source data, make sure the Normalized Source Data option is not selected.

When using denormalized source data, the fields in Tables and Repeating Sections are identified by their index number, just as displayed on the View Data tab of the portal:






See the following for an example of how to denormalize data coming from SQL Server that is normalized (present in multiple tables):

• Given the following tables:

Order


OrderDetail

• We get the row number for each detail line in the OrderDetail table using the ROW_NUMBER function:

SELECT ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderDetailID) AS RowNumber, OrderDetailID, ItemCode, Quantity
FROM OrderDetail

• We can then flatten these records using GROUP BY and CASE:

SELECT  OrderID,
        MIN(CASE RowNumber WHEN 1 THEN ItemCode END) AS ItemCode1,
        MIN(CASE RowNumber WHEN 2 THEN ItemCode END) AS ItemCode2,
        MIN(CASE RowNumber WHEN 3 THEN ItemCode END) AS ItemCode3,
        MIN(CASE RowNumber WHEN 1 THEN Quantity END) AS Quantity1,
        MIN(CASE RowNumber WHEN 2 THEN Quantity END) AS Quantity2,
        MIN(CASE RowNumber WHEN 3 THEN Quantity END) AS Quantity3
FROM         (SELECT ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderDetailID) AS RowNumber, OrderDetailID, OrderID, ItemCode, Quantity
                FROM OrderDetail) OD
GROUP BY OrderID

• This query can then be joined to the Order table to produce the final query:

SELECT     [Order].OrderID, [Order].OrderDescription, Detail.ItemCode1, Detail.ItemCode2, Detail.ItemCode3, Detail.Quantity1, Detail.Quantity2, Detail.Quantity3
FROM         [Order] INNER JOIN
                          (SELECT  OrderID,
        MIN(CASE RowNumber WHEN 1 THEN ItemCode END) AS ItemCode1,
        MIN(CASE RowNumber WHEN 2 THEN ItemCode END) AS ItemCode2,
        MIN(Case RowNumber WHEN 3 THEN ItemCode END) AS ItemCode3,
        MIN(CASE RowNumber WHEN 1 THEN Quantity END) AS Quantity1,
        MIN(CASE RowNumber WHEN 2 THEN Quantity END) AS Quantity2,
        MIN(CASE RowNumber WHEN 3 THEN Quantity END) AS Quantity3
FROM         (SELECT ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderDetailID) AS RowNumber, OrderDetailID, OrderID, ItemCode, Quantity
                FROM OrderDetail) OD
GROUP BY OrderID   ) AS Detail ON [Order].OrderID = Detail.OrderID

• The Sync & Save Job Format can then be configured:

• The resulting Dispatch records: