Scenario
The user wants to create a Transaction Saved Search which shows Transactions whose Transaction Dates do not fall within the Transaction Posting Period. This tailored search will enable them to quickly access and review any transactions that might require further attention or adjustments.
Solution
1.Navigate to Reports > Saved Searches > All Saved Searches > New.
2. Click Transaction
3. Search Title: Enter Title
4. Navigate to Criteria tab > Standard subtab and add Posting = T and filter Formula (Numeric), populating as below:
5. Navigate to Results tab > Columns subtab, add the following fields:
The formula (text) added = DECODE(To_Char({trandate},'Mon YYYY'),{postingperiod},'YES','NO') is used to verify and compare the transaction date to the posting period and return a result accordingly.
Breaking it down:
- {trandate}: This is the transaction date field.
- To_Char({trandate},'Mon YYYY'): This converts the transaction date into a string in the format of "Mon YYYY" (e.g., "May 2025").
- {postingperiod}: This refers to the accounting period to which the transaction is posted. This is also usually formatted like "Mon YYYY".
- DECODE(): This functions like an IF statement.
What it does:
It checks if the formatted transaction date (Mon YYYY) matches the posting period.
- If they match → returns "YES"
- If they don’t → returns "NO"
Meaning:
This formula is used to check whether a transaction’s date falls within its assigned posting period. This is important for auditing or ensuring proper period alignment.
Here's how it looks like:
As you can see, the results are already filtered to transactions posted outside its date. For example: Transaction Date = 9/30/2021 but it was only posted under Period = October 2021. This is further verified by the Formula (Text) column which shows NO based on the formula created above.
___
If you find this information useful, let us know by reacting or commenting on this post!