Hi,

If you need to search for account balances you can use field YearPeriod in your query. This field is connected with every balance record and every voucher row. Format is YYYYMM

example:

This query contains periods less than 3 in 2021 and all periods in previous years. Using query based on fields Year and Period it is harder to archieve this.

You can also use this in query for voucher row analysis.

Best Regards,

Pawel

Hi @SMcLees  

I think you want a general query to fetch the records of 6 months old.

Maybe you can try the advanced query for it.

• Go to query window.
• Click on “Advance” button.
• Go to “SQL” tab
• Write your SQL there. (only the where condition)
• Maybe you can save this query. So you can use it whenever you want.

Maybe you can use the following where condition for your requirement.

 TRUNC(CLOUMN_NAME)<=TRUNC(ADD_MONTHS(SYSDATE,-6)) 

Replace the column_name with the exact column name of connected view.

eg: TRUNC(voucher_date) <=TRUNC(ADD_MONTHS(SYSDATE,-6))
 

Hope it helps.

Thanks and regards,
Viraj

Hi @Pawel Dworzynski ,

I believe it is not possible to set substitution parameters from front and and will require code modification as it is handled through a ins file. However as a workaround you can use Advanced search SQL option to create the search with given conditions.

In the search window select the Advanced button and then select the SQL tab. In there you can give the SQL condition similar to below for month calculation.

TRUNC(IFS_DATE_FIELD_COLUMN_NAME) <= TRUNC(SYSDATE) AND TRUNC(IFS_DATE_FIELD_COLUMN_NAME) > ADD_MONTHS(SYSDATE, -2)

ie. If we are searching data in Customer Order window based on Wanted Delivery Date you can use SQL condition as  TRUNC(WANTED_DELIVERY_DATE) <= TRUNC(SYSDATE) AND TRUNC(WANTED_DELIVER_DATE) > ADD_MONTHS(SYSDATE, -5)

Hope this will help.

Regards,

Akila