Knowledge Base

iMIS Transactional Mailings Formats

  • Updated

This article outlines the formats and importing rules for Transactional Mailing SQL Views and Recipient Data Files.

SQL View formats

*Indicates a required field

Field Data Type Valid Field Names
ID * varchar(10) iMIS Name ID
Email * varchar(100) Email
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
Date Format varchar(50) date_format, date format, date_fmt, date fmt (see The Date Format Field)
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) 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

Importing rules

  • The ID and Email fields are required. If these are not in the SQL View, the View will not be available from the dropdown.
  • Field names are not case sensitive; "Balance," "balance," and "BaLaNCe" are all valid.
  • Fields with names other than those listed in the table will be ignored.
  • The SQL View can use any of these fields and in any order.
  • Each row of the SQL View represents one transactional item.
  • The user will be immediately notified about the success or failure of SQL View processing, with an indication of how many records (transactional items) were processed.
  • Any row missing an ID value will be ignored.
  • Higher Logic Thrive Marketing Professional (Thrive Marketing Professional) 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).

Recipient Data File formats

*Indicates a required field

Field Data Type Valid Field Names (in first row of data file)
Email * varchar(100) Email
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
Date Format varchar(50) date_format, date format, date_fmt, date fmt (see The Date Format Field)
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

Importing rules

  • Your data file can be a .csv (comma‐separated values) or .txt (text) file.
  • The Email field is required. If it is not in the data file, the upload will fail.
  • The first row of your data file must contain the names of the fields. If it does not, the data file will be rejected.
  • Subsequent rows can be blank. They will be ignored and the import will proceed normally.
  • Each row of the data file represents one transactional item.
  • Rows must be separated with a line break (CR/LF).
  • The data file can use any of these fields and in any order.
  • Field names are not case sensitive; "Balance," "balance," and "BaLaNCe" are all valid.
  • Fields with names other than those listed in the table will be ignored.
  • Fields must be delimited with a comma (,) or a Tab space (key stroke). Use the same delimiter throughout the data file; do not use both.
  • You must use a quotation mark (") character as a qualifier before and after the data in each field if the data contains a comma or a tab.
  • If the Invoice ID field is missing from the file, a unique value for it will be assigned automatically for each row.
  • The user will be immediately notified about the success or failure of data file processing, with an indication of how many records (transactional items) were processed.
  • Every row must contain the same number of fields. If they do not, the entire data file will be rejected. Every field does not have to have data; however, when a field is blank, the delimiter (comma or tab) must still be present.
  • Valid: first_name, middle_name, last_name = "John",,"Smith" (the comma delimiter for middle_name is present, despite there being no value for the field)
  • Valid: first_name, middle_name, last_name = "John","","Smith" (the quotation marks for middle_name are present, despite there being no value for the field)
  • Invalid (file upload will fail): first_name, middle_name, last_name = "John","Smith" (the middle_name field is not represented)

Data Types

Field data must match the Data Type shown in the table. If any field — in any row of the data file — does not match the indicated data type, the entire file will be rejected.

NOTE: The "varchar" data type will accept any data you input.

All the other data types will cause the upload to fail if incorrect data is specified. For example, putting "John" into:

  • The Quantity field (an "integer" field), will cause the upload to fail.
  • The Cutoff Date field (a "datetime" field) will cause the upload to fail.

Examples

Below are three examples of valid files. Note the use of commas and quotation marks.

invoice_id,CITY,email,balance
1,"Fresno","go@fresno.com",23.44
2,"Augusta","jsmith@augusta.com",444
"quantity", "description", "unit price", "bill amount", "email", "user_field_1"
20, "All-beef patties", 0.25, 0.55, "mcd@homedelivery.com", "0.05"
99, "Special sauce", 0.10, 0.11, "one@gmail.com", "0.01"
"customerID","Company","EMAIL","BILL_BEGIN","BILL_THRU","BILL_AMOUNT",invoice_id
"B07833","Microsoft","bill@ms.com",8/1/2007 0:00:00,6/19/2007 0:00:00,40.00,"MS01"
"B08219",,"me@unemployed.com",8/1/2007,7/31/2008,6/19/2007,1240.00,"UN01"

The Date Format field

The Date Format field is not used as a personalization field; it determines the output format of the datetime fields of the SQL View and the Recipient Data File. If this column is not in the SQL View or Recipient Data File, then the format that is specified on the Transactional Mailing Properties page will be used. The valid values for this field are:

Specified Date Format Output
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 08
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

 

Was this article helpful?
1 out of 1 found this helpful
Return to top

Can’t find what you’re looking for?

Create a Case Call US Support