How can I create an User Filter to filter the records of a record type by the last event type associated with them

Question

I've got this 'Expense' record type , and I've set up two event tables for it &mdash; 'Expense Event History' and 'Expense Event Type' . 
 Now, I'm looking to make a user filter within the 'Expense' record type. 
 I want the filter options to be based on the 'Expense Event Type' , so when I pick one, only expenses with that event type as their latest event will be shown. 
 The issue arises when selecting event type 'Pending' . Due to the 1-N relationship, the a!queryFilter exhibits an 'At least one' filter behavior. https://docs.appian.com/suite/help/24.1/fnc_system_a_queryfilter.html#%22at-least-one%22-filter-behavior 
 Consequently, the list displays expenses with their latest event being event type 'Pending' (first row on the image) , but it also includes expenses that have ever had events with event type 'Pending' (second row on the image). 
 
 Could you suggest a workaround or alternative approach to achieve what I want?

Shubham Aware · Answer

You're right, the a!queryFilter function exhibits "at least one" behavior when dealing with 1-N relationships. This can lead to unintended results in your user filter for "Pending" event types. Here's how to address this issue and achieve the desired filtering: a!recordFilterList(
 "Expense", -- Replace with your actual record type name
 [
 a!recordFilter(
 "LatestEventType", -- Replace with your actual related record field name
 a!max(
 "EventDate", -- Replace with the date field in "Expense Event History"
 "Expense Event History", -- Replace with your actual related record type name
 [
 "ExpenseID", -- Replace with the foreign key field in "Expense Event History"
 a!currentRecord().getID() -- Get the current expense ID
 ]
 ),
 "=",
 a!userFilterValue() -- Selected event type from the user filter
 )
 ]
) 
 
 The a!recordFilterList function builds the list of filter options. 
 The inner a!recordFilter checks for each expense if the latest event type (identified using a!max on the "EventDate" field) in the "Expense Event History" table matches the selected event type from the user filter.
 
 a!max ensures we only consider the latest event by finding the maximum "EventDate" for each expense ID matching the current expense.