I have a question about creating a search loop.
I cant seem to figure out how to do so in Appsheet.
I am looking to create a separate report table that will return a sum or count of orders for each client between a date range.
I have a table in my app that we are recording orders received from clients, including dates and order number.
From a separate report table I created, the user can input a begin date, end date, and select a client from a dropdown list.
a select in a VC returns all order rows from the main table between the dates that correspond to the client name.
similarly i can sum or count any fields i return in the select, but it will only be for all rows from the orders table or for the individually selected client.
how can i return all the clients, but count or sum the orders for each so each client is listed once?
If you want your report to have a sum for each client, then your report table will need to have as many rows as clients; perhaps you could even use the clients table to store those sum values in.
Assuming each row has [Client ID] then in your formula would be something like this: SUM(SELECT(Orders[Value],[_THISROW].[Client ID]=[Client ID]))
thanks for the help.
for anyone else looking for answers.
i was running into trouble because my report table was in detail view so i could use quick edit on the date ranges.
this only showed the first sum calculation.
by creating a dashboard and using slices i was able to keep the date range fields in detail view and the table view for the calculation fields for the same information on a single dashboard.
User | Count |
---|---|
43 | |
29 | |
24 | |
21 | |
13 |