Pivot-table like summary for templates allowing user input on the summary rows

[SkrOYC]

I need urgent help with this because I need it up and running before November 15th.

I’m sorry for that drama, but it’s actually true. I appreciate any help around this.

TL;DR

I need to summarize a lot of child records that share a certain concatenation of values and then add another columns and values manually depending on the summary column’s values.

1. The need:

You may be familiar with how pivot tables work. They are a fast and reliable way of making a summary with the fields you want. Problem is, this simple stuff is something I couldn’t be able to do inside AppSheet app.
Now, I’ve been doing it inside a template thanks to @Steve
My formula is the following:

<<Start:
ORDERBY(
  FILTER(
    "DIM_PLIST",
    ([_ROWNUMBER]=
      MAX(
        SELECT(DIM_PLIST[_ROWNUMBER],
          AND(
            ([_THISROW].[EMBARQUE]=[EMBARQUE]),
            ([_THISROW-1].[DES]=[DES])
          )
        )
      )
    )
  ),
  [DES],
  FALSE
)
>>

[DES] is a column that concatenates other columns and makes each row unique. I mean, it’s the combination of values that should be considered like a row on a Pivot Table.
[EMBARQUE] is key from the main table.
The one after[_THISROW] is inside the table in wich the action/event/process/bot is done.
The one that’s after = is inside DIM_PLIST table.
DIM_PLIST is the table that has the values to be summarize. This values/records are added directly from the database (spreadsheet) because there is no stantard for this info. Also it’s child of the [_THISROW] one. Hope makes sence.

So far, so good. The thing is that this works great when you have the values that you need to summarize without touching them in any way before the pdf is made.
That’s exactly what I need to do.

2. The problem:

Since this expression works inside the template, I can’t make any changes.
The changes I need to make is to assign a price to each of the rows after the summary.

EG:
DIM_PLIST has 9 rows columns to be summarize (they have from 20 to 100~ in reality)
They share just 3 different [DES] values, so they summarize to 3 rows.

[DES]	[BOXES]
DES 1	1267
DES 2	3231
DES 3	2324

I need to add a 3rd column called [PRICE] that is not on DIM_PLIST schema and to be able to add that price manually depending on the result of [DES] column, since it changes dynamicaly. Then there is needed at least another column that multiplies [PRICE]*[BOXES], but that’s another problem.

My knowledge is making me think that I would need some kind of Bot/Action combo and, in the end, something far from simple.
Anyway, I’m willing to do it if it solves my need.

One solution I’m thinking about is that I could make [DES] work as an actual Ref or Enum BaseType Ref to another table called DIM_PRICES. But I would need to create a record there for all the posible [DES] so it’s not practical at all for my client.

Hope you can help!

I’m gonna tag some smart people, hope I’m not making anyone mad
@Marc_Dillon @MultiTech_Visions @tsuji_koichi @1minManager @Suvrutt_Gurjar @Fabian @Kirk_Masden @WillowMobileSystems