Skip to content

Appendix 2 – Exporting Transaction Data - Sage 300

Estimated time

Estimated time: 1 hr

In this exercise you export transactional data and use the Database Writer to flag the exported transactions, so that they are not exported again.

Method

  1. Sage Optional Field Setup
  2. Create A/R Invoice
  3. Set up a Database Reader
  4. Add the Map Transform
  5. Add the Excel Writer
  6. Create the second Map Transform
  7. Configure a Database Writer

Other Considerations

  1. Data Integrity Considerations
  2. Take-on Considerations

Sage Optional Field Setup

The Sage 300 optional fields setup screen, defining an EXPORTED optional field of type Yes/No.

  1. Set up a Yes/No optional field in Sage 300, named EXPORTED.
  2. Assign the optional field to the relevant ledger and transaction. For training, that is A/R Invoices.

  3. Ensure the following:

    Setting Value
    Value Set Yes
    Default Value No
    Required No
    Auto Insert Yes

    The Sage 300 A/R optional fields screen, with EXPORTED assigned to invoices and Auto Insert set to Yes.

The export depends on Auto Insert. With it set to Yes, every new A/R invoice automatically gets an EXPORTED optional field with the default value of No, which the export query matches on.

Create A/R Invoice

  1. Create an A/R invoice in Sage 300 and post the batch.

    The Sage 300 A/R invoice entry screen with an invoice ready to post.

  2. After posting, you can query either ARIBHO or AROBLO in SQL Manager to see the optional field and its value.

The training company already has some

A company that has had the optional field for a while already holds unexported documents, so the query in the next section returns rows whether or not you post one of your own. Post one anyway, so you can see EXPORTED appear on it with the value No.

Set up a Database Reader

  1. Return to IMan, go to the Design tab and create a new integration.

    The Design screen's Options tab, with the new integration created and its Description filled in.

  2. Press the Transform Setup tab, drag a Database Reader onto the integration and double-click it to open.

    The Transform Setup tab with the Readers palette expanded and a Database Reader dropped onto the design surface.

  3. Under Source, select the Sage 300 shared database connection set up in the previous task from the Database Connection drop-down.

    The Database Reader Setup tab, with the Source section showing Database Connection/Connection String set to Sage300.

  4. Enter the following SQL query into SQL Statement:

    select I.IDCUST, I.IDINVC, IDCUSTPO, DATEDUE,
       TRXTYPEID, DESCINVC, AMTINVCHC
from AROBL I
inner join AROBLO O on I.IDCUST = O.IDCUST
                   and I.IDINVC = O.IDINVC
                   and O.OPTFIELD = 'EXPORTED'
where LTRIM(O.VALUE) = '0'
  and I.TRXTYPEID = 14

    Sage 300 stores a Yes/No optional field as 1 and 0, not as text, so the where clause tests for '0' and not for 'No'.

    TRXTYPEID = 14 limits the export to invoices. Without it the query also returns credit notes and payments, which carry the same optional field. The training company has 39 credit notes and 9 payments against 2 invoices, so most of the documents the exercise flagged would be ones it did not mean to export.

  5. Press Refresh. The query returns the unexported A/R invoices.

    The reader's preview, showing two invoices with their IDCUST, IDINVC, DATEDUE, TRXTYPEID and AMTINVCHC values.

  6. Press the Field Mapping tab and rename the transaction to Orders. The pencil is beside it in the Transaction strip above the grid.

    The reader Field Mapping tab: the Transaction strip showing Orders with its root chip, and the field grid listing the seven query columns, with Refresh Schema and a greyed Schema changes item on its toolbar.

    The rename changes only the display name. The transaction's id stays root, and the preview still shows root until you save and reload the integration.

  7. Press Close. A transform pane has no Save of its own. Closing it commits the pane to the integration, and the integration's own Save then stores it.

Add the Map Transform

  1. Drag a Map transform onto the integration and connect it to the reader.
  2. Double-click it to open it, which initialises the transform setup, then close it immediately.

  3. You will come back to this transform later.

    The Transform Setup tab with the Transforms palette expanded, and the Database Reader connected to a Map.

Connect it before you configure it

A transform copies its parent's records when you connect it, and never again. Drop it, connect it and save, and only then open it. A Map connected to a reader whose query has not yet run opens with an empty field grid, and no Refresh will fill it.

Add the Excel Writer

  1. Drag an Excel Write transform onto the integration and connect it to the Map transform.

    The Transform Setup tab with the Writers palette expanded, and the Excel Writer connected after the Map.

  2. Double-click it to open, and under Target set:

    Field Value
    Target The destination of the file. For training, select File
    File Path C:\IMan\OutputData
    File Name ARInvoice.xlsx

    The Excel Writer Setup tab: Target File, File System Windows, File Path C:\IMan\OutputData and File Name ARInvoice.xlsx.

  3. Under Options set:

    Field Value
    Excel Version Excel 2007, to match the .xlsx file name
    Worksheet Id 0

    The Excel Writer Options section: no Template Workbook, Excel Version Excel 2007, Start Writing at Row 1, Write Header Rows No Field Headers and Worksheet Id 0.

    Worksheet Id is not optional here, and it has to be the index

    With no Template Workbook the writer creates the workbook itself, and it has only one sheet. Worksheet Id takes a name or a zero-based index, but there is no sheet you have named, so a name or an empty box fails the run with "Worksheet [] does not exist in workbook." Enter 0. The message appears as a dialog after the Refresh, not as a validation message on the field, and the run behind it reports no failures.

  4. Press Refresh and open the file at the File Path and File Name you set.

    The workbook open in Excel: two rows across columns A to G, holding the customer, invoice number, due date, transaction type and amount for each invoice.

    Write Header Rows is left at No Field Headers, so the first row of the sheet is data, not field names. Columns C and F are empty because these invoices have no customer PO number and no description.

Create the second Map Transform

  1. Add a second Map transform to the end of the Write transform.

    The design surface: Database Reader, Map, Excel Writer and a second Map, connected in a row.

  2. Open it, go to Field Mapping and press Add in the toolbar to create a new field:

    Field Value
    New Field Name VALUE
    Type Text
    Evaluate Untick
    Evaluate String 1

    With Evaluate unticked, IMan uses the Evaluate String as a literal value. A Yes/No optional field stores Yes as 1.

    The Field Mapping dialog with New Field Name VALUE, Type Text, Evaluate unticked and an Evaluate String of 1.

  3. Press the green tick to save the field, then Refresh. The new field appears on every record, carrying the literal 1.

    The second Map preview, with the seven query columns and a VALUE column holding 1 on both rows.

The Map goes after the writer, not before it. In this position the VALUE field never reaches the Excel file, and IMan sets the flag only on records the export has written.

Configure a Database Writer

  1. Add a DB Writer to the end of the second Map transform.

    The design surface with the Database Writer added at the end of the chain.

  2. Open it, and on the Setup tab select the Sage 300 connection from the Database Connection drop-down.

  3. Set the SQL Operation to Update.

    The Database Writer Setup tab: Database Connection Sage300 and SQL Operation Update.

    Left on Insert this writes new rows instead of flagging

    On Insert, the writer adds new rows to AROBLO instead of flagging the existing ones. The Where Clause in the next step does not appear until the operation is Update. If the Field Mapping tab has no Where Clause, check the operation.

  4. Go to the Field Mapping tab.

  5. Set the Map To Table to AROBLO, the table in the Sage 300 database that IMan will update.

  6. Set the Where Clause to:

    IDCUST = %[Orders.IDCUST] and IDINVC = %[Orders.IDINVC] and OPTFIELD = 'EXPORTED'

    For each record in the dataset, IMan replaces %[Orders.IDCUST] and %[Orders.IDINVC] with that record's values.

    A field reference has to name its transaction. IMan accepts and saves an unqualified %[IDCUST], and the field reports "'%[IDCUST]' is an unqualified field reference - merge fields are entered as %[Record.Field]." underneath it in orange.

    OPTFIELD = 'EXPORTED' is not optional

    AROBLO's key is the customer, the invoice and the optional field. Each of these invoices carries five optional fields, so a clause of IDCUST = … and IDINVC = … alone matches all five rows and sets every one of them to 1. It overwrites COURIER, SHOPID, WARRANTY and WAYBILLNO as well as EXPORTED, and reports nothing. Restrict the clause to the optional field you mean to flag.

  7. Press Edit on the grid, set the Column for VALUE to VALUE, and leave every other row's Column empty. Press Save.

    The Database Writer Field Mapping tab: Map To Table AROBLO, the Where Clause, the Output tree showing Orders mapped to AROBLO, and the field grid with a Column only against VALUE.

    There is no Export column on a Database Writer. The writer writes a field only if it names a Column. Mapping only VALUE limits the update to that one column.

  8. Press Close, then save the integration.

  9. Reopen the DB Writer and press Refresh. This step writes to Sage 300: the update runs as the preview runs.

  10. Return to Sage 300 and view the invoice through the Customer Inquiry. The EXPORTED optional field is now set to Yes:

    The Sage 300 Customer Inquiry showing the posted invoice, with its EXPORTED optional field set to Yes.

  11. Return to the first DB Reader and press Refresh. The dataset is now empty, because the invoices it previously returned no longer match LTRIM(O.VALUE) = '0'.

    The reader preview after the flag has been set: the same columns, and no rows.

    Refresh the reader, not the writer. A Refresh anywhere else reuses the dataset the reader produced last time. That dataset still holds the two invoices, so the update appears to have done nothing.

Data Integrity Considerations

Use this method of updating data only in very narrow cases. This example changes a single optional field value.

Take-on Considerations

Invoices created before the optional field was set up have no EXPORTED optional field, so the inner join in the reader's query does not return them. You may need to run an initial export that takes every record, and only then add the where clause on the optional field value to the DB Reader.

Congratulations

You have completed the Sage 300 homework for IMan.