Decision table rule not triggering in approval workflow due to datetime comparison issue

BPM Microsoft Power Platform
, , , , , , , ,
1

I have an approval workflow in Power Automate that uses a decision table to route requests based on urgency and submission time. The rule should escalate to senior management if a request is submitted after 3 PM and marked as urgent, but the rule never triggers even when both conditions are met.

My decision table logic compares the submission datetime against a fixed time (15:00). Here’s the condition:

"condition": "@and(equals(triggerBody()?['Urgency'], 'High'), greater(triggerBody()?['SubmittedOn'], '15:00'))"

The workflow runs without errors but always takes the default route instead of the escalation route. I’ve verified the data - SubmittedOn contains values like “2024-11-22T16:30:00Z” and Urgency is definitely “High”. The datetime parsing seems to be the issue, but I’m not sure how to fix the comparison in the decision table. Anyone dealt with time-based approval workflow troubleshooting like this?

2

That makes sense. So something like formatDateTime(triggerBody()?['SubmittedOn'], 'HH') to get the hour? And then compare that number against 15?

3

The rule order looks correct - the escalation rule is first, then the default. But I’m still unclear on the exact syntax for the timezone conversion plus hour extraction. Can someone show me the complete expression?

4

Good catch on the timezone issue. Also, make sure your decision table is evaluating rules in the right order. If you have a catch-all rule above the time-based rule, it might be matching first and never reaching your escalation logic.

5

Yes, exactly. But you also need to handle timezone conversion. Your SubmittedOn field is in UTC (the Z at the end indicates that), so if your business hours are in a different timezone, you’ll need to convert first using convertTimeZone() before extracting the hour. Otherwise, a submission at 4 PM local time might show as 9 PM UTC and incorrectly trigger the rule. Decision table logic needs to account for this, especially in approval workflows where timing is critical. Convert to your local timezone, extract the hour as an integer, then do the numeric comparison.

6

Here’s the complete solution for your datetime comparison issue in the decision table. You need to fix three things:

Datetime Parsing: Your current condition is comparing incompatible formats. You’re trying to use greater() on a full ISO datetime string against a partial time string, which will always fail. Power Automate doesn’t implicitly convert these.

Decision Table Logic: Replace your condition with proper datetime manipulation:

"condition": "@and(equals(triggerBody()?['Urgency'], 'High'), greaterOrEquals(int(formatDateTime(convertTimeZone(triggerBody()?['SubmittedOn'], 'UTC', 'Eastern Standard Time'), 'HH')), 15))"

This expression:

  1. Converts the UTC timestamp to your local timezone (change ‘Eastern Standard Time’ to your timezone)
  2. Extracts just the hour component as a two-digit string
  3. Converts that string to an integer
  4. Compares it numerically against 15 (3 PM)

Approval Workflow Troubleshooting: Beyond fixing the expression, verify these common issues:

  • The decision table rule must be ABOVE any default/catch-all rules in the evaluation order
  • Test with the “Test” button in the decision table designer using sample data
  • Add a “Compose” action right before the decision table to output the converted hour value - this helps debug whether the conversion is working
  • Check that your SubmittedOn field is actually populated (not null) when the workflow runs

For more complex time-based routing, consider creating a separate calculated field in Dataverse that stores the submission hour as an integer. This makes the decision table logic simpler and more performant, since the datetime parsing happens once during record creation rather than on every workflow execution. You can use a calculated column formula: `HOUR(CONVERT_TIMEZONE([SubmittedOn], ‘UTC’, ‘Eastern Standard Time’)) The root cause of your issue is that Power Automate’s expression language requires explicit type conversions for datetime comparisons. Unlike some other platforms, it won’t automatically extract time components or convert timezones - you must do this manually in your decision table conditions.

7

Your comparison is comparing a full datetime string against just a time string. The greater() function doesn’t work that way for datetime values. You need to extract the time component from the SubmittedOn field first. Try using the formatDateTime() function to convert the full datetime to just the hour component, then compare that as an integer against 15.