Summary of Ref rows

[DavidESM]

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.

The report is summarized by species and grade…

Any sugestions?

[GreenFlux]

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.

[Steve · Accepted solution]

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

[GreenFlux]

@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.

[SkrOYC]

This is an awesome solution to the problem I had. I have a bot (workflow) that triggers invoice generation. I was looking for a way to make a summary of ref rows (as the tittle of this topic says) and struggled a lot.
I’m reviving this topic just to help anyone out with this detail (if you are new, relatively speaking).
The dynamic part of the formula regarding parent-child relationship of [_THISROW-n] is the one that helps this to work (among other thins, of course). This is thanks to the fact that it’s asummed that you are taking about the children records of the table you are in for the bot/workflow to be triggered. NOW, if you want to do the same from another table that is not the parent of the records you want to reference, you can do it as far as the parent table is reachable from the table you are in.
Example:
I can reach the main table (that’s the parent table for the ref rows I want to summarize) using a dereference expression. [ActualTableRefColumn].[AnotherTable’sRefColumn].[AnotherOne].[TheOneWhoseChildrenWeNeedToSummarize]
This expression needs to be after [_THISROW] in order to work. So, in this example:
[_THISROW].[ActualTableRefColumn].[AnotherTable’sRefColumn].[AnotherOne].[TheOneWhoseChildrenWeNeedToSummarize] = [RefColumnOfTheChildTableToTheParent]

Hope this helps someone.

Also, I think it would be awesome if @Steve explains this formula step by step so it can be applied to more complex situations.

Thanks for your help

[DavidESM]

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.

[Steve]

DavidESM:

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

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

DavidESM:

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.

Screenshot?

DavidESM:

Also it seems that it is returning a list of the keys on my “Logs” table rather than the species.

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

[DavidESM]

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.

[Steve]

Please post a screenshot of your template.

[DavidESM]

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.

[Steve]

DavidESM:

it is still a summary of everything on my “Logs” table not just the logs reference in this tally.

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

DavidESM:

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

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:

help.appsheet.com

ORDERBY()

Sort row references

[DavidESM]

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 #”.

[Steve]

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.

[DavidESM]

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.

[Steve]

What we just did is definitely advanced magic. Congratulations!