Hi,
I am having difficulties with integrating 'AddColumns' into formula with 'Switch' function. I want to search the gallery based on two columns, where one is a lookup column, other is of text type. Depending on which column I select from a combobox, I should be able to search the gallery. This search should also have additional logical tests with dates ('Filter' function).
Data source is Dataverse table named 'Opportunity'.
One column is 'Account' and that one is a lookup column for which I add 'AddColumns' function.
Another column is 'Opportunity' and that one is text column.
When used outside 'Switch' function, this formula works just fine.
// This works
Search(AddColumns(Opportunities, "AccountName", Account.'Account Name'), txtSearchBox.Value, "AccountName", "ppt_opprotunityname")
This is how it looks like in the application:
Here is the complete formula with 'Switch' function where I can search the gallery based on the "Opportunity" and filter the gallery based on "Date", but I don't get any results for "Account",
Sort(Switch(varSelectedFilter, "Opportunity", Search(Opportunities, txtSearchOpportunity.Value, "ppt_opprotunityname"), "Account", Search(AddColumns(Opportunities, "AccountName", Account.'Account Name'), txtSearchAccount.Value, "AccountName"), "Date", Filter(Opportunities, And('Start Date' >= pckStartDate.Value, 'End Date' <= pckEnddate.Value))), 'Start Date', Ascending)
varSelectedFilter = selected item from combobox, that is Account, Opportunity or Date.
Please let me know if any additional explanation is necessary.
Any advice is appreciated. Ty.
