SYSPRO Rules Engine

1. Open the Rules Administrator program in SYSPRO Avanti.

2. Select the Add New Rule icon ().

3. From the New Rule window, enter the following mandatory information:

   1. The Name of the new rule.

   2. A Description for the rule.

   3. The Table in the database to which the rule applies.

   4. Whether the rule applies at company level or system-wide in the Category field.

      If at Company level, ensure that you enter the relevant company ID at the Company field.

   5. Whether the rule should result from an Insert (new record), Update (changed record) or Delete (removed record) in the database.

      Alternatively, you can select All to cover all of these transactions.

   Tick the Enable checkbox to enable the rule immediately, or save the rule and enable it at a later stage.

   Select the Add Rule icon ().

4. From the Rule Information pane, expand the Variables section to apply any additional fields or calculations.

   1. Select the Add new variable icon ().

   2. Indicate the Name of the variable so that it becomes usable later in your rule (e.g. ContactName).

      The Name can contain letters, digits, and the underscore character (_) but it can't contain any spaces.

      The first character of the Name must be a letter.

   3. Indicate the Type and Value of the variable.

   4. Repeat for all variables that you want to apply to the rule.

   5. Select the Save Rule icon ().

5. From the Rule Information pane, expand the Conditions section to filter out your transactions.

   1. Select the Add new condition icon ().

   2. Indicate the details of the required condition.

   3. Repeat for all conditions that you want to apply to the rule.

   4. Select the Save Rule icon ().

6. From the Rule Information pane, expand the Actions section to assign the action the rule should perform.

   1. From the Actions toolbar, select the required action.

      Add Avanti Notification

      Add Bot Message

      Add Delete Rule

      Add Disable Rule

      Add Emailing Action

      Add Espresso Notification

      Add File Log

      Add Harmony Message

      Add Track History

      Add Workflow Action

   2. Repeat for all actions that you want to apply to the rule.

7. Select the Save Rule icon ().

Depending on your requirements, you can select to use any of the following variables when defining a custom variable in the Variables pane of the Rules Administrator program:

String

A String variable holds zero or more characters (e.g. letters, numbers, spaces, comma, etc.).

For example:

email@company.com

Int32

An Int32 variable is a value type that represents signed integers, with values that range from negative 2,147,483,648 to positive 2,147,483,647.

For example:

5000

Double

A Double value type represents fractional or whole values and can contain up to 15 digits in total (including those before and after the decimal point).

For example:

35,25

When selecting this variable type, ensure that it matches the Regional Format of the machine on which the SYSPRO 8 Rules Engine Service is installed.

Boolean

A Boolean variable represents either a true or false value.

Date/Time

The DateTime value type represents dates and times, with values ranging from 12:00:00 midnight, January 1, 0001 A.D. (Anno Domini) through to 11:59:59 P.M., December 31, 9999 A.D.

When selecting this variable type, ensure that it matches the Regional Format of the machine on which the SYSPRO 8 Rules Engine Service is installed.

For example:

1991/03/08 02:35:00 or 1991/03/08

SQL

A SQL type variable lets you:

• select values directly from your SQL tables.
• join a foreign table.
• retrieve aggregate values.

You can use table variables and variables defined before this variable in the SQL statement.

The format to use is {New.xxx}, {Old.xxx}, {Current.xxx}, {Var.xxx} and {Global.xxx}.

For system-wide database joins, ensure that you use the {Global.SystemWideDb} variable (e.g. [{Global.SystemWideDb}].[dbo].[AdmOperator]).

Examples:

select MAX(Description) as StockCodeDescription from InvMaster where StockCode = 'A100'

selectMAX(SellingPrice) as SellingPrice from InvPrice where StockCode = '{New.StockCode}' and PriceCode = '{New.ListPriceCode}'

selectSum((MOrderQty * MPrice) + (NMscChargeValue)) as OrderValue FROM [SorDetail] where SalesOrder = '{New.SalesOrder}'

selectMAX(CreditLimit) as CreditLimit, MAX(OutstOrdVal) as OutOrderVal from ArCustomer where Customer = '{New.Customer}'

In your statement, you can only return a single row, and the return column's alias (e.g. MAX(Description) as StockCodeDescription) is used as the variable name. We recommend that the alias name is the same as the variable name.

CSharp

CSharp variables must return a value. The return type can be any type and remain that type for the remainder of the rule.

If a Double type value is returned, it is treated as a double in the Conditions and Actions sections of the rule.

CSharp variables are very effective for performing various things, including the following:

• Calculations

  For example:

  return (New["PromotionValue"] / New["PromotionLimit"]) * 100;

• Rounding

  For example:

  return int.Parse( Math.Round((ML["PODaysLate"]), 0).ToString());

  or

  return Double.Parse( Math.Round((ML["ChanceOrderWillBeLate"]), 4).ToString()) * 100;

• Assigning descriptions to flags

  For example:

    switch((string)New["TrnType"])
   {
    case "R":
     return "Receipt";
    case "I":
     return "Issue";
    case "P":
     return "Physical";
    case "T":
     return "Transfer";
    case "A":
     return "Adjustment";
    case "C":
     return "CostChange";
    case "M":
     return "CostModification";
    case "B":
     return "BinTransfer";
    case "S":
     return "Sale";
    default:
     return "Other";
   }

• Date calculations

  For example:

  return (New["DateReceived"] - New["LineDueDate"]).TotalDays;

• Misc

  For example:

  if(Var["DaysLate"]="1")
          return "day";
  else
          return "days";

  or

  return DateTime.Parse(New["EstArrivalDate"].ToString()).ToShortDateString();

You can use table variables and variables defined before this variable in the SQL statement.

The format to use is New["xxx"], Old["xxx"], Current["xxx"], Var["xxx"] and Global["xxx"].

Conditions are a set of statements that a rule needs to pass before actions can be performed.

Values of the same type can be compared to evaluate to a true or false outcome.

The combination of these statements using the AND or OR modifiers evaluates to a single true or false outcome.

If this final outcome is true, then the rule moves to processing each action.

The following scenarios demonstrate some of the different types of conditions you can apply:

• Scenario: You want to check whether a specific value has changed for an update rule.

  For example:

  

• Scenario: You want to check a specific flag on the record (e.g. if the OnHold flag has changed and equals Y).

  For example:

  

• Scenario: You want to know when a value has increased.

  For example:

  

  Using a numeric table variable (e.g. New["SellingPrice"]) and a fixed numeric value (e.g. 10) in the same condition will fail.

  Ensure that you first convert the fixed value to a SqlDecimal.

You can use the COMQRL business object to query the hashtag table if you have configured a Track History rule.

<Query>
  <Key>
    <Function>QH</Function>
    <HistoryTableName>InvPrice</HistoryTableName>
    <HistoryColumnName>SellingPrice</HistoryColumnName>
    <HistoryColumnDataType>N</HistoryColumnDataType>
    <HistoryColumnKey1>B100</HistoryColumnKey1>
    <HistoryColumnKey2>A</HistoryColumnKey2>
    <HistoryColumnKey3 />
    <HistoryColumnKey4 />
    <HistoryColumnKey5 />
    <HistoryColumnKey6 />
    <HistoryColumnKey7 />
    <HistoryColumnKey8 />
    <HistoryColumnKey9 />
    <HistoryColumnKey10 />
    <HistoryColumnKey11 />
    <HistoryColumnKey12 />
    <HistoryColumnKey13 />
    <HistoryColumnKey14 />
    <HistoryColumnKey15 />
  </Key>
  <Options>
    <IncludeAdditionalHistoryColumn1>InvMaster.Description</IncludeAdditionalHistoryColumn1>
    <IncludeAdditionalHistoryColumn2 />
    <IncludeAdditionalHistoryColumn3 />
    <IncludeAdditionalHistoryColumn4 />
    <IncludeAdditionalHistoryColumn5 />
    <IncludeAdditionalHistoryColumn6 />
    <IncludeAdditionalHistoryColumn7 />
    <IncludeAdditionalHistoryColumn8 />
    <IncludeAdditionalHistoryColumn9 />
  </Options>
</Query>

Messages sent to an operator in SYSPRO Avanti will also appear in the operator's To Do List.

Available parameters:

• Title

• Subtitle

• Operator

• Program to call (Optional)

  This lets you indicate a SYSPRO program that should open automatically when the operator selects the notification.

• Program input key (Optional)

Messages sent to an operator on the SYSPRO Bot use the proactive messaging feature and can also begin conversations that can flow into even further processes.

Available parameters:

• Operator

• Message

• Skill (Optional)

  This lets you indicate a Bot skill to invoke after the operator reads the message.

• Skill keyfields (Optional)

Sending messages to an operator in SYSPRO Espresso is a great way to get hold of operators on the move.

Available parameters:

• Operator

• Message

• Application to call (Optional)

  This lets you indicate an Espresso application that should open automatically when the operator selects the notification.

• Application key/session variable (Optional)

Beats posted to SYSPRO Harmony can include both a message (beat text) and sentiment.

Hashtags can also be used in beat text and are included in Harmony follow feeds.

This action creates a physical file that contains all the changes resulting from the SYSPRO transactions you defined to be logged.

File types available for selection:

• TXT
• CSV
• XML

You can also indicate how much detail must be logged by defining the File Level.

This action lets you store every change to a column in the database, providing detailed history tracking.

It creates a Hashtag table (e.g. InvPrice#) with the same key, including a PostDateTime entry. If you enable the Allow Duplicates option, it records all entries, even if the value is the same as the previous entry in the Hashtag table.

Manage Business Insights

The Manage Business Insights option (available from the toolbar of the Rules Administrator within SYSPRO Avanti) enables you to create a business insight tile for any rule that has the Track History action on a numeric column assigned.

Once the tile layout is created, you can add the tile to the SYSPRO Avanti menu to display its contents graphically.

To view available insight tiles on numeric columns, navigate to TrackedValues when adding the new tile using the Visual Designer program.

These tile layouts are saved as CX_Tile_TRKxxx_xxx.sql in the \Plugin\CustomStore folder of your SYSPRO install directory.

This action deletes a rule after all its other actions have executed.

This action disables a rule after all its actions have executed (useful for once-off rules that should be disabled after a successful single execution).

This action lets you send an email.

This action lets you call into a SYSPRO Workflow Services operation.

This is a new SYSPRO Avanti program that enables you to configure rules (by company or system-wide).