Section 11 Export Definitions Table

This table allows you to define the format of export files that you can create for use by other systems. It can be used to export data to spreadsheet programs or other databases. This export option appears on the file menu if your security level is high enough. When you select the above option, this screen will appear. The records in the file will be ordered in RECEIPT NUMBER order, regardless of what fields are included in the file. If you later need to resort them for some reason, you can do this by simply importing the CSV file into EXCEL or another spreadsheet and doing whatever The Export Definitions Table table, edited via the TABLE MAINTENANCE function, contains settings that enable you to define the default file name (which can be overridden at export time), as well as the order of the data fields that will appear on each record of the file. The file can be set to be formatted as a fixed format export file (each field is a specified size), a CSV file, where each field is delimited by commas, or a TAB delimited file. Here is a sample of what the records in this file might look like for a simple export file. Each field in the table is listed, along with a short description, below:

1. EXPORT_DEF_NAME
   
   This field defines a specific export name. Each record that follows defines a specific data element. All the fields that are to appear in a single file must share the same EXPORT_DEF_NAME.
   
   
2. EXPORT_FIELD_NUMBER
   
   The order in which the fields are listed here (numeric order) controls what sequence in which the fields will appear in each record. The record with the EXPORT_FIELD_NUMBER of 1000 is used to store the default file name that the program will suggest you create when the program is run. This value can point to a specific drive letter, as in the example shown above or the file name can use a UNC type file naming convention such as \\FINSERVER\IMPORTS\QUADRANT_DATA.TXT. The path must be valid and the user must have access rights to create/replace any existing file with the same name. If you accept the default name any older version of the file will be overwritten. If it does not exist it will be created. The path must be valid and the user must have access rights to create/replace any existing file with the same name. If you accept the default name any older version of the file will be overwritten. If it does not exist it will be created.
   
   
   1. 1001
      
      Is used to indicate whether or not the EXPORT FIELD NAMES are exported as the first row of the file. This can be set to Y or N and will cause a checkbox on the export screen to be checked or unchecked. You can override this setting when you do the export.
      
      
   2. Y or N
      
      Indicate whether or not to include voided items in the exported data. Normally, voids are not included in the exported data. This can be set toand will cause a checkbox on the export screen to be checked or unchecked. You can override this setting when you do the export.
      
      
   3. /*/
      
      Used to tell the program which SYSTEM_CODES you want to include in the export. The SYSTEM_CODE is a field associated with each TRANCODE in the TRANCODES table. These are usually values such as RV,GL,AR, etc. If this value is not set at all, it will default to /*/ which tells the program to include all system codes. If you do define something other than /*/ you must separate each code with a slash character, e.g., /RV/GL/UB/EX/. You can override this setting when you do the export.
      
      
   4. EXPORT_FIELD_NAME
      
      The program will read in a list of field names (in the order defined by the EXPORT_FIELD_NUMBER) to determine which data elements to include in each record. The valid field names you can use are as listed below. For ease of reference, the order in which the fields are listed below is alphabetical. However, you can create your export record with the fields in any order you need for your export file.
      
      
      1. ACCOUNTING_DATE
         
         The preset accounting batch date for the transaction record. This may or may not be the same as the calendar date when the receipt was processed.
         
         
      2. BANKCODE
         
         This is the numeric bank code as drawn from the trancodes table for the specific transaction code included on the line item.
         
         
      3. COMMENT
         
         The transaction record any, as entered by the user.
         
         
      4. CR_ACCOUNT
         
         The CR account initially from the transaction codes table, or as overridden by the user at receipt entry time.
         
         
      5. DEPT_CODE
         
         The department of entered the receipt.
         
         
      6. DESCRIPTION
         
         This is the description from the trancodes table. During receipt entry it is possible for the cashier to override the trancodes table value with an alternate value. If this is done, it is found in the TRAN_DESCRIPTION field (see below). If they don't override the value both the DESCRIPTION field and the TRAN_DESCRIPTION field will have the same value.
         
         
      7. DISCOUNT_RATE
         
         If a discount is the line item, this is the rate of the discount.
         
         
      8. DISCOUNT
         
         amount of the
         
         
      9. DR_ACCOUNT
         
         The DR account initially from the transaction codes table, or as overridden by the user at receipt entry time.
         
         
      10. EXTENDED_FEE_AMOUNT
          
          This is the transaction (unit fee * qty - discount )
          
          
      11. PAYER_NAME
          
          The payer name.
          
          
      12. QUANTITY
          
          The quantity entered cashier. For most receipts, this will be 1.
          
          
      13. RCPT_DATE_TIME
          
          The actual time the receipt was processed.
          
          
      14. RCPT_NUMBER
          
          format (000-00000000)
          
          
      15. RCPT_STATUS
          
          (002=completed receipt) - (099) = voided receipt
          
          
      16. REFERENCE_NUMBER
          
          The value of number as entered by the cashier. This is often a specific customer account number (e.g., utility bill, invoice number, business license number, etc. By checking the system-code you can code your import logic to determine what type of account number you are seeing in this field.
          
          
      17. REGISTER_NUMBER
          
          format (000)
          
          
      18. SEQUENCE_NUMBER
          
          Each line item is assigned a sequence # starting with 0001, and continuing in a sequential manner up to 9999
          
          
      19. SHORT_DESCRIPTION
          
          The short value from the trancodes table linked to the transaction code for the line item.
          
          
      20. SUMMARY_FLAG
          
          This value will Summarize or Detail. This field can be used by your import process to determine whether or not you should pass records through to their destination in summary or detail form. Some types of high-volume, low dollar transactions (for example, photo copy fees) don't usually need to be tracked in the general ledger system at the finite transaction level.
          
          
      21. SYSTEM_CODE
          
          Each transaction assigned a SYSTEM_CODE which can be used by the interface processing program to tell it which host system is interested in the detail transaction data represented in a single detail record. This might include a code such as GL or RV for items that need to be updated to the GL system, UB for those that are destined to update Utility Billing, etc. The specific codes you elect to use are up to you.
          
          
      22. TRAN_CODE
          
          This field is the for the detail line item.
          
          
      23. TRAN_DESCRIPTION
          
          The description that ended up on the line item after receipt entry. If the user does not change the standard description drawn from the trancodes table when the receipt is started, this value will be the same as the DECSRIPTION field, (see above)
          
          
      24. UNIT_FEE_AMOUNT
          
          The unit fee of the transaction. For most receipts, the quantity is 1, so the UNIT_FEE_AMOUNT and EXTENDED_FEE_AMOUNT are the same. The amount of the transaction to be posted to the G/L or customer account, is always the EXTENDED_FEE_AMOUNT.
          
          
      25. USER_ID
          
          The cashier ID of the person who posted the receipt.
          
          
      26. RECORD_NUMBER
          
          This value is the sequential record number for each item in the current export file. It is calculated automatically as each record is created and added to the record if the field is specified as one of the export fields. The example above shows the RECORD_NUMBER as field #1, however, it does not need to be the first field in each record , though it usually would be.
          
          
      
      
      
   5. EXPORT_MASK
      
      This field is where you tell the program how to format the individual fields to be included in each record. There are three special keywords that provide special formatting instructions to the output processing logic. The first is {QUOTE}. This tells the program to surround the field with quotation marks (e,g, "JOHN DOE") This will typically be used for fields that are not numeric values. The second is {COMMA}. This tells the program to append a comma following the data value. In the example shown, the {COMMA} keyword appears after every field EXCEPT the last one. The last special keyword is {TAB}. This tells the program to append a tab following the data value. If you wanted a TAB delimited file format you would use {TAB} instead of {COMMA} to tell the program to use TABs instead of COMMAS. If none of the above keywords are used, the program will simply output the data values in fixed size fields, with no delimiters. For text fields, such as descriptions, names, etc., define how many bytes are to be output , just use the caret (^) symbol. For numeric fields such as amounts, dates/times, and so on you will use a few special values to define how to format and sequence the data. For dates, you can use any sequence such as YYYY-MM-DD, MM-DD-YYYY, with or without the dash or slash separators. For numeric amounts, we suggest that you defined a fixed size field with 0 place holders for each digit.
      
      
   Quadrant Support can assist you with setting up your file format if you questions not answered above.