How to filter date field as a user filter on record type?

Question

Hi All, 
 I have a scenario where i need to filter the data from db based on the date. Filter values will be something like below. 
 Today 
 Yesterday 
 Last 7 days 
 FYI, 
 In Db, date column is stored in date time format. 
 Can an someone suggest me how to achieve the filtering thing from db based on the date when db value is date time? 
 Thanks

Geetha Satya Sree · Answer

I hope this code helps you, here we are using todatetime() function to convert today value, it will add 00 hours 00 MINUITE 00 Second's that is why when filtering with Today with today()+1 
 a!recordFilterList(
 name: "Created On",
 options: {
 a!recordFilterListOption(
 id: 1,
 name: "Today",
 filter: a!queryFilter(
 field:'recordType!{b99dab82-8523-47ef-a91c-20d9b66ea668}PYEOS Candidate.fields.{103f0940-5ec7-44dc-8cf8-605b30db2fb7}createdon',
 operator: "between",
 value: { todatetime(today()),todatetime(today()+1)}
 )
 ),
 a!recordFilterListOption(
 id: 2,
 name: "Yesteday",
 filter: a!queryFilter(
 field: 'recordType!{b99dab82-8523-47ef-a91c-20d9b66ea668}PYEOS Candidate.fields.{103f0940-5ec7-44dc-8cf8-605b30db2fb7}createdon',
 operator: "between",
 value: { todatetime(today()-1),todatetime(today())}
 )
 ),
 a!recordFilterListOption(
 id: 2,
 name: "Last 7 Days",
 filter: a!queryFilter(
 field:'recordType!{b99dab82-8523-47ef-a91c-20d9b66ea668}PYEOS Candidate.fields.{103f0940-5ec7-44dc-8cf8-605b30db2fb7}createdon',
 operator: "between",
 value: { todatetime(today()-7),todatetime(today())}
 )
 )
 },
 defaultOption: "Active",
 isVisible: true,
 allowMultipleSelections: true
)

baratc · Answer

a!localVariables(
 local!inputDate:"11/01/2022",
 local!numberOfDays:7,

 datetime(year(todate( local!inputDate)), month(todate( local!inputDate)), day(todate( local!inputDate)), 4) - local!numberOfDays
 ) 
 
 jojog0003 - please create expression rule with rule inputs date and number of days (refer the above code and pass the date and number of days - ex: based on the selection if Yesterday then1 , if previous week then 7 ) and rule returns the datetime based on the values that you send . The output value (the same date or datetime) can be passed as input to query entity to get the data from db. Hope this helps. Please let me know incase of any challenges.

How to filter date field as a user filter on record type?

Top Replies