trying to add a User Filter where today is between two dates on record.

Question

I am trying to find records where the records fall within the effective and expiration dates on a record. 
 I tried unsuccessfully to use the function today(): 
 a!recordFilterListOption(3,"CURRENT",a!queryFilter("today()","between",{"reqeffdt","reqexpdt"})) 
 So I added a (date)field to the view "today" 
 a!recordFilterList( 
 name: "Status", 
 options: { 
 a!recordFilterListOption(1,"FUTURE",a!queryFilter("reqeffdt",">",today())) , 
 a!recordFilterListOption(2,"PAST",a!queryFilter("reqexpdt","<",today())) , 
 a!recordFilterListOption(3,"CURRENT",a!queryFilter("today","between",{"reqeffdt","reqexpdt"})) 
 } ) 
 Even though today,reqeffdt and reqexpdt are all type: DATE 
 I am getting the error.

Chris · Accepted Answer

The error here is related to {"reqeffdt","reqexpdt"} being a list of text string (type 103), where values are expected instead. Based on my tests we cannot apply the record field rf! variables in the query filter here, which is the idea - compare 2 date fields in the DB to the current date. So, as you have access to modify the view, I would suggest performing this in SQL for a new calculated column to be directly filtered on, such as:

SELECT
CASE 
	WHEN reqeffdt > GETDATE() THEN 'FUTURE'
	WHEN reqexpdt < GETDATE() THEN 'PAST'
	WHEN (
		reqeffdt < GETDATE() 
		AND
		reqexpdt > GETDATE() 
	) THEN 'CURRENT'
	ELSE 'NONE'
END AS 'status'
FROM YOUR_TABLE_HERE

trying to add a User Filter where today is between two dates on record.

Top Replies