Question

OQL Date Criteria - Zero Rows Returned?

0

Hi – I’m using the following date criteria in my OQL. No rows are returned? NOtE: I’m using two single quotes due to syntax requirements of the OQL widget from the app store. No error at runtime; however, no data is returned? mydatefield < ''[%BeginOfCurrentMonth%]'' and mydatefield >= ''[%EndOfCurrentMonth%] - 13 * [%MonthLength%]''

asked 2020-01-29

Sue Lhymn

4 answers

Sue Lhymn · Answer 1 · 2020-01-31

Turns out the issue was elsewhere, not in the OQL above. Has anyone found a way to display the %MonthLength% in OQL and not just use it as criteria? I’m unsure how to validate how %MonthLength% is calculating unless I can see the value. Does anyone have any documentation on how %MonthLength% is calculated?

Eric Tieniber · Answer 2 · 2020-01-31

I highly recommend you pre-calculate those values as variables in your microflow, and then pass them into the OQL statement instead of using these tokens. This is especially important when it comes to date math on months which have variable lengths.

Sue Lhymn · Answer 3 · 2020-02-01

I think the challenge I ran into using a variable and then passing into the OQL is, the OQL has limitations as to what kinds of date field criteria it supports, i.e., limited date functions. Can you suggest a date function that would work in OQL if I fed it a var parameter to do a rolling 12 months back calc? Appreciate any tips

Sue Lhymn · Answer 4 · 2020-02-01

You know what, I just realized I can just calculate the minimum and maximum date for rolling 12 months, put those in date vars in MF, and then feed those date vars into OQL … I will try that