Ignition Expression Tags Need Nested if for Shift Logic

An Ignition expression tag runs the expression language. That language is a set of functions that return values, and it is not Python. The if/elif/else block with print statements works in the Script Playground because the Playground runs Jython. In a tag it fails to parse. Rewrite the shift logic as nested if(condition, trueReturn, falseReturn) calls. The follow-on SQL date-range query fails for a separate reason: the dates are unquoted, and they need single quotes around them.

Symptom Reading: Tag Error vs Query Error

This workflow can fail in two places: the tag expression and the table query. They produce different symptoms. Identify which layer failed before you edit anything.

Expression Language Evaluation Model

An expression is one value-producing formula, evaluated as a whole each time the tag executes. It has no statements, no indentation-scoped blocks, and no print. The tag value is whatever the outermost function returns. if(condition, trueReturn, falseReturn) is a function with three arguments. It can only choose between two results, so a three-way decision means you place a second if() in the falseReturn slot of the first.

Boolean operators follow the expression-language forms: && for AND and || for OR. Comparison operators behave as expected. String literals take single or double quotes, and the tag data type must be String so that 'A' is stored without coercion.

dateExtract(now(0), "hour") does return the hour of day as an integer 0 to 23, on the 24-hour clock. The function call was never the problem. The problem was the syntax wrapped around it. now(0) returns the current time with a poll rate of zero, so the expression does not schedule its own re-evaluation. In a tag, the tag's execution rate drives re-evaluation. Leave the tag on a group that executes at least every few seconds so the shift letter flips promptly at the boundary.

Shift Boundaries and Hour Arithmetic

The number that matters is the integer hour. At 14:59:59 dateExtract returns 14, and at 15:00:00 it returns 15. Minutes and seconds never enter the comparison, so the boundaries below are exact to the second.

Shift C wraps across midnight, so it cannot be written as one >= / < pair. Leaving it in the final falseReturn handles the wrap for free. An ascending cascade of upper bounds does the same thing with fewer comparisons.

Procedure: Building the Shift Expression Tag

1. Create an Expression tag and set its data type to String.
2. Paste the nested expression into the Expression field:

   if(dateExtract(now(0),"hour") >= 7 && dateExtract(now(0),"hour") < 15, 'A',
     if(dateExtract(now(0),"hour") >= 15 && dateExtract(now(0),"hour") < 23, 'B', 'C'))

3. You can also use the equivalent cascade, which tests upper bounds only:

   if(dateExtract(now(0), "hour") < 7, 'C',
     if(dateExtract(now(0), "hour") < 15, 'A',
       if(dateExtract(now(0), "hour") < 23, 'B', 'C')))

4. Save and watch the tag quality. A parse error shows up immediately as bad quality. Correct syntax shows a single letter.
5. Add the tag to the transaction group alongside the production tags. Each logged row then carries the shift letter in effect when it was written.

Date-Range Queries: Quoting and the "near '16'" Error

A SQL query binding substitutes {Root Container.Start.date} as plain text before the query reaches the database. The failing statement arrived at SQL Server as:

... WHERE t_stamp BETWEEN 2015-06-16 16:19:04 AND 2015-06-23 16:19:04;

Without quotes, SQL Server reads 2015-06-16 as integer subtraction. The next token is the hour 16 of the time portion, and it has no valid place in the grammar. '16' is that literal token, not a line or column number. Quote both substitutions so each arrives as a date string:

SELECT t_stamp, Part_Number, Running_Percentage, Time_Percentage, Cycle_Time,
       seconds_for_current_part, twelve_hour_Shifts
FROM JK_LH_Monthly
WHERE t_stamp BETWEEN '{Root Container.Group 3.Start.date}'
                  AND '{Root Container.Group 3.End.date}';

Every column in the SELECT list must exist in the table the transaction group writes. If you rename a tag in the group, the column name changes, and the query fails with an invalid-column error instead of a syntax error.

Production-Day Window: 7:00 to 7:00

The query is plain SQL text, so expression functions such as dateArithmetic or dateFormat cannot go inside it. Build the boundary strings in the window instead, then reference those strings in the query.

1. On the Start popup calendar, add a String custom property named datetime. Bind it to:

   concat(dateFormat({Root Container.Group 3.Start.date}, "yyyy-MM-dd"), " 07:00:00")

2. On the End calendar, add the same property with the end time:

   concat(dateFormat({Root Container.Group 3.End.date}, "yyyy-MM-dd"), " 06:59:59")

3. Point the query at the custom properties:

   WHERE t_stamp BETWEEN '{Root Container.Group 3.Start.datetime}'
                     AND '{Root Container.Group 3.End.datetime}';

With two calendars, viewing the June 23 production day means picking June 23 as Start and June 24 as End. For a single-picker design, derive the end from one date with dateArithmetic, and use a half-open range so no row between 06:59:59 and 07:00:00 is dropped:

// Custom property startDT (String) on the Day calendar
concat(dateFormat({Root Container.Day.date}, "yyyy-MM-dd"), " 07:00:00")

// Custom property endDT (String) on the Day calendar
concat(dateFormat(dateArithmetic({Root Container.Day.date}, 1, "day"), "yyyy-MM-dd"), " 07:00:00")

// Query
WHERE t_stamp >= '{Root Container.Day.startDT}'
  AND t_stamp <  '{Root Container.Day.endDT}'

The same pattern gives a rolling window. dateArithmetic(date, -7, "day") moves the date back seven days. Because dateFormat formats only the date, the time is pinned to whatever literal you concatenate.

Verification

1. Shift boundaries: temporarily replace each dateExtract(now(0),"hour") with a literal integer and read the tag value. Check 6 gives C, 7 gives A, 14 gives A, 15 gives B, 22 gives B, 23 gives C, and 0 gives C. Restore the function when all seven pass.
2. Clock source: compare the tag's flip time against the gateway clock, not the client PC. Tag expressions run on the gateway, so the gateway's time zone sets the shift boundaries.
3. Rendered query: when a binding errors, the message shows the fully substituted SQL. Confirm the dates appear inside single quotes and that the time portion reads 07:00:00.
4. Shift column: filter the returned rows by twelve_hour_Shifts and confirm no shift letter appears outside its hour band.

Recurring Pitfalls

• Table jumping back to the newest row. A propertyChange script that sets selectedRow to rowCount - 1 fires on every data update. With a polling binding, it therefore overrides the operator's scroll position on every poll. Restrict it to the first load:

  if event.propertyName == "data" and event.newValue is not None:
      old = event.oldValue
      if old is None or old.rowCount == 0:
          table = event.source.parent.getComponent('Table')
          table.selectedRow = event.newValue.rowCount - 1

  The simpler alternative is ORDER BY t_stamp DESC, which puts the newest row at the top and needs no script.
• Format pattern case. In dateFormat, MM is month and mm is minutes, and HH is 24-hour while hh is 12-hour. A wrong case produces valid-looking strings that point at the wrong day.
• SQL Server date parsing. Server language and DATEFORMAT settings can reinterpret yyyy-MM-dd hh:mm:ss strings for the legacy datetime type. If a range returns data from a different month, use the unambiguous ISO form with a T separator (yyyy-MM-dd'T'HH:mm:ss).
• String-built SQL. Substituting text into a query is fragile. It breaks on quoting and exposes the query to injection when the value comes from free-text input. For user-typed values, move the query to a parameterized form such as system.db.runPrepQuery in a script, where dates pass as typed parameters.
• Testing in the wrong engine. The Script Playground validates Jython only. Validate expression logic in the expression editor, or on a test expression tag.

FAQ