Dec 21 2022 10:35 PM
I have a situation where I need to be able to search for data using keyed variables. These range from low and high dates, to accounts, and status codes. There are 3 valid status codes (C, P, and R) In addition I need to be able to search for a combination of P or R together.
The code to search for the individual codes, (C, P, and R) works as it should but the code to select all that have either "P" or "R" returns all records. regardless of the status test What I need is to get all records with either a "P" or "R" status
When I select "P R" I need All records that have either a "P" status AND all records that have a status of "R" Status
Non-working code in red. This returns All status. Blue code works, returning only the
requested status codes
'=IF(Selection_Status="P, R",(FILTER(CHOOSE({1,2,3,4,5,6,7,8,9,10,11},Transactions!Transaction_Key,Transactions!Provider,Transactions!Expense_Center,Transactions!Item_Note,Transactions!Account,Transactions!Actual_Amount,Transactions!Split_Receipt,Transactions!Due_Date,Transactions!Date_Paid,Transactions!Date_Cleared,Transactions!Status),((Transactions!Remittance_Source="Bank Account")*(Transactions!Date_Cleared>=Selection_Start_Date)*(Transactions!Date_Cleared<=Selection_End_Date))*(OR(ISNUMBER(SEARCH("P",Transactions!Status)),ISNUMBER(SEARCH("R",Transactions!Status))))*(ISNUMBER(SEARCH(Selection_Account,Transactions!Account))),0)),FILTER(CHOOSE({1,2,3,4,5,6,7,8,9,10,11},Transactions!Transaction_Key,Transactions!Provider,Transactions!Expense_Center,Transactions!Item_Note,Transactions!Account,Transactions!Actual_Amount,Transactions!Split_Receipt,Transactions!Due_Date,Transactions!Date_Paid,Transactions!Date_Cleared,Transactions!Status),((Transactions!Remittance_Source="Bank Account")*(Transactions!Date_Cleared>=Selection_Start_Date)*(Transactions!Date_Cleared<=Selection_End_Date))*(ISNUMBER(SEARCH(Status_Search,Transactions!Status))))*(ISNUMBER(SEARCH(Selection_Account,Transactions!Account))),0))
Dec 21 2022 11:36 PM
SolutionThe formula is something of a monster and you might like to consider the use of the LET function to separate the process of selecting the columns you wish to return from that of filtering records. Assuming the defined names refer to columns of a single table, CHOOSECOLS would offer a more succinct approach to selecting columns and assigning the reduced array to a LET variable.
That aside, it is your use of OR in the filter criterion that I would focus on. OR returns only a single value when used with array arguments. The straightforward solution to getting an array of criteria is to use the "+" operator instead. Otherwise you could use MAP/LAMBDA to combine the two criteria using OR.
Dec 21 2022 11:36 PM
SolutionThe formula is something of a monster and you might like to consider the use of the LET function to separate the process of selecting the columns you wish to return from that of filtering records. Assuming the defined names refer to columns of a single table, CHOOSECOLS would offer a more succinct approach to selecting columns and assigning the reduced array to a LET variable.
That aside, it is your use of OR in the filter criterion that I would focus on. OR returns only a single value when used with array arguments. The straightforward solution to getting an array of criteria is to use the "+" operator instead. Otherwise you could use MAP/LAMBDA to combine the two criteria using OR.