Summary of Ref rows

I am trying to create a summary report of my referenced rows.

Here is the report of my referenced rows…

Underneath this I would like a summary. It would look something like this.
ESM Summ

The report is summarized by species and grade…

Any sugestions?

  1. Are the number of Species fixed, and if so, how many?
  2. Grade: same question
  3. Are the Number of Logs and Net already calculated/displayed elsewhere in the app?

If no to #3, and you don’t need display them in the app, then I recommend ONLY calculating them in the workflow template for better performance.

However, if you need to see these values in the app anyway, set up virtual columns for each, then just reference them in the template, after the << End >> of your first table.

1 Like

Species

<<Start: FILTER(
  "Logs",
  (
    [_ROWNUMBER]
    = MAX(
      SELECT(
        Logs[_ROWNUMBER],
        AND(
          ([_THISROW] = [System Tally #]),
          ([_THISROW-1].[Species] = [Species]),
          ([_THISROW-1].[Grade] = [Grade])
        )
      )
    )
  )
)>><<[Species]>>

Grade

<<[Grade]>>

Number of logs

<<COUNT(
  FILTER(
    "Logs",
    AND(
      ([_THISROW] = [System Tally #]),
      ([_THISROW-1].[Species] = [Species])
      ([_THISROW-1].[Grade] = [Grade])
    )
  )
)>>

Net

<<SUM(
  SELECT(
    Logs[Net],
    AND(
      ([_THISROW] = [System Tally #]),
      ([_THISROW-1].[Species] = [Species])
      ([_THISROW-1].[Grade] = [Grade])
    )
  )
)>><<End>>
6 Likes

@Steve, Nice! You went straight for the ‘fully-dynamic’ approach that will work with any number of Species and Grades.

I was going to keep it simple if there are only the 2 species. That was too much work to knock out on my break lol.

2 Likes

Ok. Getting back to work here…

I have approximately 12 Species and 20 Grades. So yes I’m looking for a fully dynamic solution.

I’m taking it one step at a time here so I’m trying to first get the species column to do what I want…

I plugged the following into a virtual column on my “Tally” Table to test its functionality.

FILTER(
“Logs”,
(
[_ROWNUMBER]
= MAX(
SELECT(
Logs[_ROWNUMBER],
AND(
([_THISROW-1].[Species] = [Species]),
([_THISROW-1].[Grade] = [Grade])
)
)
)
)
)

2 things.
It seems that it is “looking at” everything on my Logs table not just the referenced logs. When I test It it returns the same values for every tally regardless of the species on that tally.
Also it seems that it is returning a list of the keys on my “Logs” table rather than the species.

The expression is designed for a template, not a virtual column. I don’t know what to expect when evaluated in a virtual column.

Screenshot?

Yep, that’s what a FILTER() expression returns, and what a <<Start>> tag requires.

Ok. That makes sense that a <> tag needs a list of keys…

I also noticed you are having a similar discussion here Struggling with Parent/Child Start: Functions in Workflow

By removing the spaces I got it to work without an error but it is returning this.

WAHMWAHMPOCHASASASHM

Which is the list of species, with some duplicates, all in the same cell togeter.

Please post a screenshot of your template.

Ok Im getting somewhere. Thanks for your help @Steve.

Here is my current template.

Which produces this the chart at the bottom of this.

This is what I’m looking for although it is still a summary of everything on my “Logs” table not just the logs reference in this tally.

This is the same report on a different tally.

I also would like to sort them by species and then Grade (within the species) to make it easily readable.

Where do the logs referenced in the tally come from? How would they be identified in the Logs table?

Easy enough: wrap the FILTER() in the <<Start>> tag with ORDERBY():

ORDERBY(
  FILTER(...),
  [Species],
    FALSE,
  [Grade],
    FALSE
)

FALSE will produce low-to-high/A-Z/0-9 sorting; change to TRUE for high-to-low/Z-A/9-0 sorting.

See also:

2 Likes

All Logs entered in the app are stored on a table called “Logs” which includes a column called “System Tally #” which is ref column to the uniqueid() key of the “Tally” Table.
On the Tally Table I have a virtual column “Tally Logs” to reference those logs with the same “System Tally #”.

1 Like

Okay, if I’ve understood correctly, we need to add this sub-expression:

([_THISROW] = [System Tally #])

into the FILTER() and SELECT() expressions in the template expressions. I’ve updated my original template expression suggestion in my previous post accordingly.

1 Like

Yes! Thank you so much for your help @Steve . I’m still relatively new to AppSheet and feel like I was pushing the edge of my understanding on this one.

2 Likes

What we just did is definitely advanced magic. Congratulations! :slight_smile:

2 Likes