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

[1minManager]

Im struggling to visualise the data here. Can you list the tables and relevant columns? Or show us a copy of the PDF template and the same with data in it?

[MultiTech]

I agree with Simon; I recorded like 5 different answers, each one ending in… well… I guess I don’t know what’s going on. haha

Here’s my 2-cents for now

[MultiTech]

SkrOYC:

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.

Seems like you might be better served by actually creating some rows here.

Whenever I’m asked to create a “reporting system” I always include an actual Report table

• This gives me a space to store some base level information about the report (date, customer, etc.)
  • It also gives me space to store system variables (lists of related orderID, a sum of all those orders, a unique list of products from all those orders)
  • By having these lists stored at the root level, it makes highly layered reports much easier to manage.
• It also gives me the ability to create child records for that report
  • and these child records can then be modified - because they’re actual records and not the result of a template building a “display table”

It seems like you would be better served by going this route, and create report tables to support all things you’re wanting to do.

[SkrOYC]

Do you think your “Looping with actions” would help here?

[MultiTech · Accepted solution]

SkrOYC:

Do you think “Looping with actions” would help here?

• Indeed, I was just about to suggest that as the solution.

What I’ll do is find some place where a user interacts with the app, like when they select the report record, and use that to sneak in a background record creation loop.

You’ll need to create some columns inside your report table, things to hold the lists I alluded to earlier. It’s from these lists that you can then generate a “Remaining Whatever” list, and from that you can process with the looping to create each of the missing elements.

[SkrOYC]

Thank you for your help.

1minManager:

Can you list the tables and relevant columns? Or show us a copy of the PDF template and the same with data in it?

Sure

MultiTech_Visions:

Seems like you might be better served by actually creating some rows here.

Actually, I think this should be my solution in the end, but will require some skills that I don’t have at the moment because I would need to create a list of child records that are the summary of the non-summarized child records.

I’m gonna add important details that I left out in the first post:

1. The template is an invoice, that’s why I need just the summary of the records and not all of them.

2. The expression is working great, I just need to make that even more useful.

3. The expression creates a list of values equal to the amount of unique [DES] records that are in the DIM_PLIST table that also match the [EMBARQUE] of the table where I’m triggering the template

Here are the screenshots:

I’m sorry I have to blurr some of the info but it’s from my client. The important thing is that it summarizes the data to 4 unique [DES] as you can see, but I need to find a way to do it inside AppSheet to be able to add manual info (UNIT PRICE) and then pass that to the template.

As @MultiTech_Visions said, I think I should place those 4 summary rows inside an actual table but I have no idea on how to make that happen, how to create those 4 records at once with the click of a button (ACTION) as children from the table where I’m triggering the bot.
After that, It would be easy to just leave PRICE column as Quick edit and add the price on inline view

[SkrOYC]

@MultiTech_Visions
I’m gonna check your videos and com here to tell you about my progress. I think I’m getting close since the expression I mentioned above creates a list so that list would be the one to check in order to make the child records using the Loop-thing. Now, do you thing I will be able to pass not just 1 value to the children? I mean, I can just make one column having the list of values (from the expression) and they are not ref.
Also, here is the post where I found the expression that @Steve made to help another user:

Summary of Ref rows Questions

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

[SkrOYC]

After some work, I got it working as I expected it. What I mean by that is creating summary rows for each concatenation of values that I needed it.
One of the things that was different from Looping with actions video is that I had to add another action to force-update the virtual column that holds the List of child records. I have no idea why but after some troubleshooting I found that it was needed. Maybe it’s related to the fact that I’m doing this from a Slice? Maybe it’s a bug? Anyway, my trusty +1 to a real column force it to update and works like charm.

In summary 

Show More

1. I created a new table (DIM_INVITEMS) and all of the necesary things to make child records for my primary record.

2. I made a VC ([DIM_PLIST_PSID]) that’s a pseudo-key and it’s the concatenation of other values from my big table DIM_PLIST and removed any " " (space):

SUBSTITUTE(
  CONCATENATE(
    [ENVASE],
    [ESPECIE],
    [VARIEDAD],
    [CALIDAD],
    [CALIBRE]
  ),
  " ",
  ""
)

3. I made a VC ([SUMMARY_LIST]) on parent table that takes all the pseudo-key values that I made on step 2 and removes duplicates:

UNIQUE(
  [Related DIM_PLISTs][DIM_PLIST_PSID]
)

4. I made my Add a new row to another table…" Action that: 1. Adds the key to table from step #1 to make sure it’ll be a child and 2. Adds the pseudo-key value that I need later for the other steps to work using this formula ([CMVINV_Inline] is the “Related something” List column of Step #1 and [DES_PLIST_ID] is where I’m saving the pseudo-key):

INDEX(
	[SUMMARY_LIST]-[CMVINV_Inline][DES_PLIST_ID],
  1
)

5. The force-update action adding +1 to a physical column

6. The repeater

7. The Grouped one that uses the other ones 1st: New Record Action, 2nd: Force-Update and 3rd: The repeater.

That’s basically it!

I have this condition to make sure it stops the loop:

COUNT(
	[SUMMARY_LIST]-[CMVINV_Inline][DES_PLIST_ID]
)<>0

Finally, I’m gonna work on the other steps to have this up-'n-running but my main problem is gone.

PS1: The pseudo-key is needed because I’m using that to take values from the big table after the record is made. I’m using SUM() or ANY() wraping SELECT() and comparing the pseudo-key stored on the DIM_INVITEMS table to the one from DIM_PLIST.
PS2: I made another action to pop-up an "INDEX() form" so that the user can add the Price field (the only field that needs user-interaction) after selecting the row from Inline table.

 

Result

Show More

The inline view with the summary:

The INDEX() pop-up after select:

Well, the only thing I can say is thank you very much @MultiTech_Visions for the time you took to made those great videos, trying to explain things as clear as posible (Even under 2 mins! That one is very helpful).
I’m gonna mark one of your posts as the solution if anyone needs this in the future.
Also, thanks @1minManager for being interested to help, the whole community appreciates people like you

[SkrOYC]

@AndrewB I was taking a look at this post where I explained how I solve it on my last reply and part of it is completely lost.

I think I used the "spoiler" BBCode on Discourse to make those parts.

Please take a look, I'm sure you have a backup copy around!

[Marc_Dillon]

I was taking a look at this post where I explained how I solve it on my last reply and part of it is completely lost.

I think I used the "spoiler" BBCode on Discourse to make those parts.

---

Ugh, that sucks...

Actually the expandable section still appears to be there, just click on the "In Summary" or "Result" text of your previous post, and it will open up. There's just no indication of anything being there. 😞

[AndrewB]

Correct, I do see it there but without any visual indication. With your permission @SkrOYC, I can work on the formatting if you like? 

[SkrOYC]

Yes, you can change it to be:

Show More

This newer but worst way

Also it seems that we can't actually have full html support, only admins can apparently? So even if I try to solve it I couldn't retain the inline code

[SkrOYC]

---

@AndrewB wrote:

With your permission @SkrOYC, I can work on the formatting if you like? 

---

Hi @AndrewB, would you mind taking a look?

[Michelle]

@SkrOYC,

I have updated the format as requested.  Please take a look.

Thank you,

Michelle | Community Manager