I have a question about creating a search loo...

(Seth Berman) #1

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?

(Reza Raoofi) #2

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]))

(Seth Berman) #3

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.