Configuring for Sage 300

From DataSelf Knowledge Base
Revision as of 00:01, 21 February 2014 by Lwright (talk | contribs)
Jump to navigation Jump to search

Option Fields

Some of the major option field tables are already prepared within the Out-of-the-Box data warehouse. To add an option field to a table, do the following within the ETL:

  1. Add the option table (such as ARCUSO_CustomerOptionFields) and CSOPTFD_OptionalFieldsValues to the Refresh Wizard, in the same first step of the main refresh batch (usually, batch 0).
  2. Either let the whole refresh run once, or manually refresh these option field tables so that you can work with them.
  3. Create filters on the option field tables, where OPTFIELD = (the option field name)
  4. Create formulas (Character 60) on the main table (such as ARCUS_Customers). Select the child table (such as ARCUSO_CustomerOptionFields). You will be able to use either the Value or the VDesc (from CSOPTFD). Open up the list under Value or VDesc and find the correct filter. Double-click on the filter to create the formula value. SAVE the formula: by default, we name the option fields being imported: OPTFIELD_FIELDNAME or OPTFIELD_FEILDNAME_VDesc.
  5. The ETL will automatically add the new fields to the main table (such as ARCUS_Customers).

Add the fields to SQL reporting views, DSA, and other report writers:

  1. Add these fields to the appropriate SQL views (such as _C_Customer and then to _B_SalesHeader and _B_Sales).
  2. Modify the DSA data source to utilize the fields.

NOTE: The Sales reporting in the DataSelf BI for Sage 300 is based on the OEAUDH and OEAUDD tables. Therefore, if making new option fields for orders or OE invoices, it easier to set up the formulas for the option fields if they are also created in OEAUDH and OEAUDD. However, they can be copied in from OEINVH/D and OECRDH/D.