Transactional SQL View Formats

This article outlines the rules and formats required for transactional mailing SQL views.

Transactional SQL View Formats

*Indicates a Required Field

Field Data Type Valid Field Names
ID* varchar(10) iMIS Name ID (required)
Email* varchar(100) Email (required)
Invoice ID varchar(50) invoice_id, invoiceid, invoice id, invoice number, invoice_number, invoice_num, invoice num
Invoice Date datetime invoice_date, invoice_dt, invoice date, invoice dt
Customer ID varchar(50) customer_id, customer id, customerid
First Name varchar(50) first_name, first name, first, fname
Middle Name varchar(50) middle_name, middle name, middle, mname
Last Name varchar(50) last_name, last name, last, lname
Title varchar(100) title
Company varchar(100) company
Address 1 varchar(100) address_1, address 1, address1, address, addr
Address 2 varchar(100) address_2, address 2, address2
City varchar(50) city
State varchar(50) state, province, state_province , state province
Zip varchar(50) zip, zipcode, zip code, zip_code
Country varchar(50) country
Phone varchar(50) phone, phone number, phone_number, telephone
Fax varchar(50) fax, fax number, fax_number, facsimile
Email varchar(100) email, e‐mail, e mail, e_mail
Quantity int quantity, qty
Product Code varchar(50) product_code, product code, item_code, item code, item_name, item name
Description varchar(255) description, desc, full description, full_description, item_description,  item description
Unit Price money unit_price, unit price, unit_cost, unit cost, item_price, item price, item cost, item_cost
Bill Begin datetime bill_begin, bill begin
Bill Thru datetime bill_thru, bill thru, bill_through, bill through
Bill Date datetime bill_date, bill date
Bill Amount money bill_amount, bill amount
Balance money balance, amount due, amount_due, balance due, balance_due
Cutoff Date  datetime  cutoff_date, cutoff date, cutoff_dt, cutoff dt
User Field 1 varchar(max) user_field_1, user_field1, user field 1, user field1, userfield1, uf1
User Field 2 varchar(max) user_field_2, user_field2, user field 2, user field2, userfield2, uf2
User Field 3 varchar(max) user_field_3, user_field3, user field 3, user field3, userfield3, uf3
User Field 4 varchar(max) user_field_4, user_field4, user field 4, user field4, userfield4, uf4
User Field 5 varchar(max) user_field_5, user_field5, user field 5, user field5, userfield5, uf5
User Field 6 varchar(max) user_field_6, user_field6, user field 6, user field6, userfield6, uf6
User Field 7 varchar(max) user_field_7, user_field7, user field 7, user field7, userfield7, uf7
User Field 8 varchar(max) user_field_8, user_field8, user field 8, user field8, userfield8, uf8
User Field 9 varchar(max) user_field_9, user_field9, user field 9, user field9, userfield9, uf9
User Field 10 varchar(max) user_field_10, user_field10, user field 10, user field10, userfield10, uf10
User Field 11 varchar(max) user_field_11, user_field11, user field 11, user field11, userfield11, uf11
User Field 12 varchar(max) user_field_12, user_field12, user field 12, user field12, userfield12, uf12
User Field 13 varchar(max) user_field_13, user_field13, user field 13, user field13, userfield13, uf13
User Field 14 varchar(max) user_field_14, user_field14, user field 14, user field14, userfield14, uf14
User Field 15 varchar(max) user_field_15, user_field15, user field 15, user field15, userfield15, uf15
User Field 16 varchar(max) user_field_16, user_field16, user field 16, user field16, userfield16, uf16
User Field 17 varchar(max) user_field_17, user_field17, user field 17, user field17, userfield17, uf17
User Field 18 varchar(max) user_field_18, user_field18, user field 18, user field18, userfield18, uf18
User Field 19 varchar(max) user_field_19, user_field19, user field 19, user field19, userfield19, uf19
User Field 20 varchar(max) user_field_20, user_field20, user field 20, user field20, userfield20, uf20
Date Format varchar(50) date_format, date_fmt, date format, date fmt

Rules for Importing

  1. The ID and Email fields are required. If these are not in the SQL view, the view will not appear in the dropdown.
  2. Field names are not case sensitive, so "Balance," "balance," and "BaLaNCe" are all valid.
  3. Fields with names other than those listed in the table will be ignored.
  4. The SQL view may use any number of the fields listed (in any order).
  5. Each row of the SQL view represents one transactional item.
  6. The user will be notified immediately on the success or failure of SQL view processing, and the user will be told how many records (transactional items) were processed.
  7. Any row missing an ID value will be ignored.
  8. Informz will auto-generate a Transactional ID if it is not provided. If this field does not exist in iMIS, it cannot be calculated from iMIS data (e.g. IMIS_ID + Month + Year).
  9. The Date Format field is not used as a personalization field. It is used to determine the output format of the date fields Invoice Date, Bill Begin, Bill Thru, Bill Date and Cutoff Date. If this column is not in the SQL view, then the format that is specified on the Transactional Mailing Properties page will be used instead.

    The valid values for this field are listed here:
Date Format
Example
DAYOFWEEK MONTH DD, YYYY 
Wednesday March 5, 2008 
DAYOFWEEK, DD MONTH YYYY 
Wednesday, 5 March 2008 
DAYOFWEEK, DD MONTH YYYY HH:MM 12 
Wednesday, 5 March 2008 9:32 PM 
DAYOFWEEK, DD MONTH YYYY HH:MM 24 
Wednesday, 5 March 2008 21:32 
DAYOFWEEK, MONTH DD, YYYY 
Wednesday, March 5, 2008 
DAYOFWEEK, MONTH DD, YYYY HH:MM 12
Wednesday, March 5, 2008 9:32 PM 
DAYOFWEEK, MONTH DD, YYYY HH:MM 24
Wednesday, March 5, 2008 21:32 
DD MMM YY 
5 Mar 08 
DD MMM YYYY 
5 Mar 2008 
DD MMM YYYY HH:MM 12 
5 Mar 2008 9:32 PM 
DD MMM YYYY HH:MM 24 
5 Mar 2008 21:32 
DD MONTH YY 
5 March 08 
DD MONTH YYYY 
5 March 2008 
DD‐MM‐YY 
05‐03‐08 
DD‐MM‐YYYY 
05‐03‐2008 
DD‐MMM‐YY 
5‐Mar‐08 
DD‐MMM‐YYYY 
5‐Mar‐2008 
DD/MM/YY 
05/03/08 
DD/MM/YYYY 
05/03/2008 
DDMMYY 
050308 
DDMMYYYY 
05032008 
MM‐DD‐YY 
03‐05‐08 
MM‐DD‐YYYY 
03‐05‐2008 
MM/DD/YY 
03/05/08 
MM/DD/YYYY 
03/05/2008 
MMDDYY 
030508 
MMDDYYYY 
03052008 
MMM DD, YY 
Mar 5, 08 
MMM DD, YYYY 
Mar 5, 2008 
MMM DD, YYYY HH:MM 12 
Mar 5, 2008 9:32 PM 
MMM DD, YYYY HH:MM 24 
Mar 5, 2008 21:32 
MONTH DD, YY 
March 5, 08 
MONTH DD, YYYY 
March 5, 2008 
MONTH‐DD‐YY 
March‐5‐08 
MONTH‐DD‐YYYY 
March‐5‐2008 
YY‐MM‐DD 
08‐03‐05 
YYMMDD 
080305 
YYYY‐MM‐DD 
2008‐03‐05 
YYYYMMDD 
20080305