Summary in a Report

[Paul_M_Crawford]

Hello to All this is my first post. I am new to Appsheet but I am in the process of building several Apps. Currently I am working on an Expense Reporting App. Part of that App is of course an expense report. Using the concepts in this post: Workflow pagination, headers and footers I have been able to create a report that is laid out exactly as I need it. However I am trying to add a summary in my pseudo footer that lists the total expenses for each Category. I am able to get a list of all the Categories, their description and the total for each category which comes from a VC [Category Total] using this Start expression:

<<Start:OrderBy(Select(Current Expenses[Exp Rec ID],TRUE),[Assign Cat ID].[Category ID])>> | <<[Assign Cat ID].[Category ID]>> <<[Assign Cat ID].[Short Label]>>: <<[Category Total]>><<End>>

The result looks as follows:

I need only one of each of the categories listed. I have tried several different approaches using UNIQUE() and INDEX() but I have been unsuccessful. If I change the focus of the expression to the [Assign Cat ID] I can can get the list to show one of each category but I am unable to get the VC Column [Category Total] to display as (I assume) it needs the Key [Exp Rec ID]. It is probably important to mention that this Start expression is nested in another Start expression. Just looking for a little help to get me back on track.

Thanks in advance for any help.

[LeventK]

Chiming in @Aleksi and @Steve

[Steve]

Perhaps this?

<<Start: OrderBy(Select(Current Expenses[Exp Rec ID], ([_ROWNUMBER] = MIN(SELECT(Current Expenses[_ROWNUMBER], ([Assign Cat ID] = [_THISROW-1].[Assign Cat ID]))))), [Assign Cat ID].[Category ID])>> | <<[Assign Cat ID].[Category ID]>> <<[Assign Cat ID].[Short Label]>>: <<[Category Total]>><<End>>

[Paul_M_Crawford]

I tried your suggestion and the following error was given in the Log.

Expression ‘OrderBy(Select(Current Expenses[Exp Rec ID], ([_ROWNUMBER] = MIN(SELECT(Current Expenses[_ROWNUMBER], ([Assign Cat ID] = [_THISROW-1][Assign Cat ID]))))), [Assign Cat ID].[Category ID])’ is invalid due to: Column ‘_THISROW-1’ is used in a SELECT or list dereference expression and should be a List/EnumList of Refs. Error: Report ‘Expense Report’ action ‘CreateExpenseReport’ Attachment template. Start expression ‘OrderBy(Select(Current Expenses[Exp Rec ID], ([_ROWNUMBER] = MIN(SELECT(Current Expenses[_ROWNUMBER], ([Assign Cat ID] = [_THISROW-1][Assign Cat ID]))))), [Assign Cat ID].[Category ID])’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table.",

[Steve · Accepted solution]

Whoops! I forgot a period. I’ve updated the expression above. Please try the updated expression.

[Paul_M_Crawford]

Steve:

<<Start: OrderBy(Select(Current Expenses[Exp Rec ID], ([_ROWNUMBER] = MIN(SELECT(Current Expenses[_ROWNUMBER], ([Assign Cat ID] = [_THISROW-1].[Assign Cat ID]))))), [Assign Cat ID].[Category ID])>> | <<[Assign Cat ID].[Category ID]>> <<[Assign Cat ID].[Short Label]>>: <<[Category Total]>><<End>>

That did the trick. Many thanks. Is there a link you can point me to that helps me understand the approach and the Select statement you added. What is the significance of the [_ROWNUMBER] = MIN portion of the statement you added.

Thanks again Steve.

[Steve]

Here’s the SELECT() expression:

Select(
  Current Expenses[Exp Rec ID],
  (
    [_ROWNUMBER] =
    MIN(
      SELECT(
        Current Expenses[_ROWNUMBER],
        ([Assign Cat ID] = [_THISROW-1].[Assign Cat ID])
      )
    )
  )
)

1. The inner SELECT() expression gathers all row numbers (SELECT(Current Expenses[_ROWNUMBER], ...)) of the rows having the same Assign Cat ID as the row currently being examined by the outer SELECT() expression ([Assign Cat ID] = [_THISROW-1].[Assign Cat ID]).

2. MIN(...) determines the lowest of the row numbers of the set gathered by (1). We don’t have to use MIN(); we could have used MAX() instead. We just need to find some way to always get the same one value from the same set of row numbers.

3. [_ROWNUMBER] = MIN(...) asks, “is the row number of the row currently being examined equal to the one given by (2)?” If yes, select this row and return this row’s Exp Rec ID column value.

In effect, gather the Exp Rec ID column values from the first of the rows having the distinct Assign Cat ID column value.

[Paul_M_Crawford]

This is great. Thanks for the explanation.