Appendix 2 – Exporting Transaction Data - Sage 300

In this example you will export transactional data and use the Database Writer to flag the exported transactions preventing them from being re-exported.

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

1. Setup a ‘Yes/No’ optional field in Sage300.
2. Assign the optional field to the relevant ledger & transaction.
   • For training: A/R Invoices
3. Ensure the following:

4. This will automatically add an ‘EXPORTED’ optional field for every A/R invoice, with the default Value of No.

Create A/R Invoice

1. Create an A/R Invoice in Sage300 and post the batch.

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

Set up a Database Reader

1. Return to IMan and create a new integration.
   

2. Click the Transform Setup tab, add a Database Reader to the integration and double-click to open it.

3. Select the Sage300 shared database connection that was setup in the previous task from the Database Connection drop down.
   

4. Enter the following SQL Query:
   • 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'

5. Press Refresh.
6. The query should return the AR invoices that were posted in step 5 above.

7. Click the Field Mapping tab, change the Transaction Id to Orders.
8. Press Save, then Refresh.

9. Press Apply to save and close.

Add the Map Transform

1. Add a Map transform to the integration.
2. Double click to open it (and to initialise the transform setup), and then Close immediately.
3. We will come back to this transform shortly.

Add the Excel Writer

1. Add an Excel Write transform to the integration and connect it to the Map Transform.
2. Double click to open.

3. Expand the File Options and set the File Path and File Name accordingly.

4. Press Refresh and open the file pointed by the File Path and File Name properties.

Create the second Map Transform

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

2. Create a new field:

3. This new field will be used in the next step to set the ‘EXPORTED’ optional field to ‘Yes’.
   

Configure a Database Writer

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

2. Select the Sage300 Connection from the Database Connections drop down.

3. Go to the Field Mapping tab.

4. Set the following Map To Table to AROBLO.
   • This is the table in Sage300 database that IMan will update.
5. Set the Where Clause to:
   • IDCUST = %IDCUST and IDINVC = %IDINVC and OPTFIELD = 'EXPORTED'
   • For each record in the dataset the %IDCUST and %IDINVC fields will be substituted with the value of these fields within the IMan dataset.
   • The effect will be to update only the records included in the dataset, as the IDCUST & IDINVC fields join to form the primary key for the AROBLO table.
6. Edit the grid:
   • Deselect each field except for VALUE
7. Press green tick at the bottom of the grid to save.
8. Return to Sage300 and view the Invoice through the Customer Inquiry:=.
   • The ‘EXPORTED’ Optional Field will be set to Yes:

9. Return to the first DB Reader and press Refresh; there should be an empty dataset.

Data Integrity Considerations

Please note we only recommend this method of updating data in the narrowest of scenarios. In this example, we have only changed a single Optional Field value.

Take-on Considerations

All Invoices prior to setting up the Optional Field will not have the EXPORTED optional field. Depending on your scenario you may need to perform an initial export where you export all records, then after the export is complete limit the DB Read by adding the where clause containing the optional field value.