Hi All. I’m trying to do a saved search that includes the criteria “Period is not current or future periods.” I can’t use Period filter, because it doesn’t allow absolutes. I can’t use Accounting Period Fields… and use End Date, because unapproved transactions have a Period but (somehow) not an Accounting Period, and I need unapproved transactions in my results. So I’m trying to turn Period into a Date field and use that in the criteria, by doing this: TO_DATE(SUBSTR({postingperiod}, 0,3) || ' 11 ' || SUBSTR({postingperiod}, 5,4)) But that throws an “unexpected error.” I have a ticket open with NS on the unexpected error, but in the meantime, my users need this search. Ideas?

Try to build the formula in suite analytics, it has a syntax checker that you can use and will tell you what is wrong with your formula usually.

Formula works like a dream in RESULTS, just isn’t allowed in CRITERIA 😞

what is the base record type of your search?

Transaction

Try this as a formula Numeric in the criteria where the value is 1. CASE WHEN LAST_DAY(to_date(ConCat('01 ', {postingperiod}), 'dd.mm.yyyy')) < LAST_DAY(ADD_MONTHS({today}, -1)) THEN 1 ELSE 0 END

cool rework of the formula. still bombs out with unexpected error. transaction volume through the roof.