Fields in the cartrule table in the SiriusSQL database
• department, category and item – These fields can either be populated with a valid department, category or item nickname, respectively or they can be left blank – leaving one blank means any value is valid for that field when items in the cart are evaluated.
Example:
For all items in the RETAIL department, SHIRTS category, the department field would be populated with RETAIL, the category field would be populated with SHIRTS and the item field would be left blank.
• cartrule – This field contains a function and a comparison – currently, the only function supported is tally(), which is designed to check the number of the item(s) in the shopping cart that match that specified in the department, category and item fields.
Example:
tally() >= 2 means that there must be two or items in the cart that match the item(s) specified in the department, category and item fields in order for the rule to pass.
Note: A cart rule with a less than tally quantity always includes 0, and therefore the item always gets added.
Example:
For Item A, if you use
tally() <=2
However, you never add Item A to the cart, the rule gets executed anyway because there is quantity = 0 in the cart. You use individual conditions instead of less than.
tally() = 1
tally() = 2
• message – This is the text displayed on the Checkout.aspx (or Checkout1.aspx) page if the cartrule fails and required is set to True.
Example:
“You must buy two or more of item X.”
• cartaction – This is an action to be performed if the cartrule passes – this is optional and can be left blank if you do not want any action to be performed. Currently the only action supported here is cartadd(), which is used to add an item to the cart if the cartrule returns a true result.
Example:
The following would be used to add the LOWSHIP item from the MISC department, SHIPPING category priced at $2.50 to the shopping cart if the cartrule passes:
CARTADD(<department>MISC</department><category>SHIPPING</category><item>LOWSHIP</item><options><price>2.50</price></options>)
Note: The regular price of the item added to the cart can be overridden using the <options><price> . . . </price></options> tags – if these are left out of the expression the item would be priced as configured in SysManager.
• required – This can be set to TRUE (1) or FALSE (0). Setting it to TRUE requires that the item designated in the department, category and item fields must be in the cart and meet the cartrule criteria.
Example:
With Required set to TRUE, department, category and item set to SHIPHANDLE, SHIPFEES and ECOMFEE respectively and the cartrule set to "tally0=1", the user must have quantity “1” of the DCI SHIPHANDLE SHIPFEES ECOMFEE in the shopping cart in order to complete the sale. Setting it to FALSE executes the cartaction only if the item designated in the department, category and item fields is present in the quantity set in the Cart Rule. However, not having the item in the sale does not stop the user from proceeding.
Example:
With Required set to FALSE, department set to TICKETS, category and item each blank and a cartrule set to “tally() > =10” if the user has ten or more items in the shopping cart from the TICKETS department, then, the action specified in the cartaction field are executed; if the user has less than ten items in the shopping cart from the TICKETS department, the sale proceeds with no further action occurring.
• notes – This field can be used to document the rule or can be left blank.
• sitefilter – This field is used for setting which set of pages a Cart Rule applies to when running multiple sets of E-Commerce pages. It contains one or more WebSiteIDs as set in the site config file for each set of pages (separate multiple WebSiteIDs with a comma). Or it can be blank if you want the Cart Rule to apply to all of your sets of pages.
Currently the fields in the cartrule table must be populated either manually or with a script.
Example:
A script that can be used to populate one record in the table:
insert into cartrule (department, cartrule, message, cartaction, required, notes) values('TICKETS', 'tally() <= 10', 'You are only allowed to purchase a maximum of 10 Print At Home Tickets in any one sale.', '', 0, 'This ensures that guests can only purchase a maximum of 10 print at home tickets in one sale')
This script would be run in Query Analyzer or SQL Server Management Studio and would populate the next record in the cartrule table in the SiriusSQL database.
