Hi @Roystreet,
Great idea to implement a top searched for functionality in the app. Below I will explain the code step by step, since it is a rather extensive code snippet. (See step 3 for complete code)
(1) Create a delegable Filter() to only display the current month's searches:
With(
{
wStartOfMonth: DateTime(
Year(Today()),
Month(Today()),
01,
0,
0,
0
),
wEndOfMonth: DateTime(
Year(Today()),
Month(Today()) + 1,
0,
23,
59,
59
)
},
Filter(
//Change to correct list name
ListName,
Created >= wStartOfMonth && Created <= wEndOfMonth
)
)
(2) Group the rows based on a column value (example is based on Title) and display the amount of records that matches the grouped value
//Add a row displaying the count
AddColumns(
//Group by Title
GroupBy(
//With function mentioned above
With(),
"Title",
"GroupedRecords"
),
"CategoryCount",
CountRows(GroupedRecords)
)
Important remark: The code above is based on grouping a text field. Should Category be a choice field, then you will need to add an AddColumns around the With function:
//Add a row displaying the count
AddColumns(
//Group by category text value
GroupBy(
AddColumns(
//With function mentioned above
With(),
//Add a text value column
"CategoryTextVal",
//Change to the correct category column name
CategoryColumn.Value
),
"CategoryTextVal",
"GroupedRecords"
),
"CategoryCount",
CountRows(GroupedRecords)
)
(3) Sort descendingly based on the new Count column and only display the top 3 searches (Complete code)
FirstN(
Sort(
AddColumns(
GroupBy(
With(
{
wStartOfMonth: DateTime(
Year(Today()),
Month(Today()),
01,
0,
0,
0
),
wEndOfMonth: DateTime(
Year(Today()),
Month(Today()) + 1,
0,
23,
59,
59
)
},
Filter(
//Change to correct List Name
ListName,
Created >= wStartOfMonth && Created <= wEndOfMonth
)
),
//Change to category column (See step 2 remark on choice columns)
"Title",
"GroupedRecords"
),
"CategoryCount",
CountRows(GroupedRecords)
),
//Display the highest counts first
CategoryCount,
SortOrder.Descending
),
//Display the first 3 records
3
)
If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.
Thanks!
