Grouping Issue - Help me in this

Sayad · 04-28-2023 10:46 PM

I have been doing something for a long time by making group template. But haven't been able to make it yet. I want you to help me in this. I want to create a group template in this way. As you can see in the image below.

Below I have given you the image of two tables, with the help of which I have to generate the group template. As you can see in the image, I have highlighted two columns. I want that when I select [Start date] and [End date] in the column of "PDF Scan Sheet" Table then [DATE] and [CHANNEL] group should be generated in PDF, And its [Quantity] should be SUM together.

$pdf scan sheet 2\1$ pdf scan sheet 2\1

pdf scan sheet 2/2

filter data sheet

all data in this

I have created both Behaviour Action and Bots to generate PDF. As you can see below.

2/12/2

I hope that you will definitely help me. I tried hard to learn the expression but could not learn it. You are a genius.

Thanks .

Sayad

Template Expression

DATE GROUP	CHANNEL	TOTAL QTY
<<Start: ORDERBY(FILTER("SCAN SHEET", ([_ROWNUMBER] = MIN(SELECT(SCAN SHEET[_ROWNUMBER], ([_THISROW-1].[DATE] = [DATE]))))), [DATE])>>	<<[Channel]>>	<<SUM( SELECT(SCAN SHEET[Quantity], ([_THISROW].[DATE] = [CHANNEL])))>><<END>>

. Bots error

Run task "Auto Update"

Error 1 : 'Process for PDF template - 1' task 'Auto Update' Body template. Expression 'SUM( SELECT(SCAN SHEET[Quantity], ([_THISROW].[DATE] = [CHANNEL])))' is invalid due to: Error in expression '[_THISROW].[DATE]' : Unable to find column 'DATE'.
Error 2 : The document body is empty

I need your support please

Tommy62

Try <<SUM( SELECT(SCAN SHEET[Quantity], ([_THISROW-1].[ DATE] = [CHANNEL])))>><<END>>

Sayad

Thank you very much for replying.

@Sayad wrote:
Process for PDF template - 1' task 'Auto Update' Body template. Expression 'SUM( SELECT(SCAN SHEET[Quantity], ([_THISROW].[DATE] = [CHANNEL])))' is invalid due to: Error in expression '[_THISROW].[DATE]' : Unable to find column 'DATE'.

This error is still showing.

Tommy62

Try <<SUM( SELECT(SCAN SHEET[Quantity], ([_THISROW-1].[DATE] = [CHANNEL])))>><<END>>

[ DATUM] falsch-->[DATUM] ohne Leerzeichen

Sayad

Hi @Tommy62 It is working.

@Sayad wrote:
<<Start: ORDERBY(FILTER("SCAN SHEET", ([_ROWNUMBER] = MIN(SELECT(SCAN SHEET[_ROWNUMBER], ([_THISROW-1].[DATE] = [DATE]))))), [DATE])>>

This expression is not working. When I generate the PDF, it does not appear in the date group.

WillowMobileSys

Hi @Sayad !

Sorry for late response.

One thing that jumps out at me is that there are several table references and its not clear how they fit together. First, the Bot is triggered by and add/update to a row in a table named "PDF Template". It is this row that is sent to your PDF Template so any usages of [_THISROW] will be referring to the PDF Template row. I don't see the definition of this table so it's hard to determine if your expressions are referencing the correct thing.

In the "Date Group" column of the PDF table, you need a way to provide a UNIQUE list of the value you wish to use as the Group Name and then access that value in the SUM() expression. I don't see how that can work with the expressions you have in the template now - BUT I don't yet know all the details.

Which column value in which table do you wish to show in the "Date Group" column of the template table?

Sayad

Hi @WillowMobileSys

@This is my reference sheet. In which PDF has to be generated with the help of "PDF template".

WillowMobileSys

No no no. In order to accurately direct you to make the proper changes in your template we will need to understand ALL of the Tables involved and how they are related. You have given some but then covered table names so we can't reliably tell what table is used where to make sure everything lines up like it needs to.

UPDATED: The below is incorrect. [_THISROW-1] refers to a row from an outer expression when there are nested expressions one inside the other. However, [_THISROW] does refer to the row passed into the template form the Bot.

To start, your bot refers to a table named "PDF Template". A row in this table triggers the bot and sends that row to the template. In the template, the expressions refer to [_THISROW] and [_THISROW-1] - both of which refer to the PDF Template row being passed in. More critically, [_THISROW-1] refers to the Parent of the PDF Template row passed in. Do you even have a Parent reference implemented? If so, how is it related?

Where are you setting the value for the Date Group? There is no column assigned.

How do you tie the values used in the Date Group to the Total Qty to make sure the summed values are related to the Date Group value? I don't see anything that provides that correlation

These things might be hidden in the way you named columns but we can't give accurate advice if we don't understand the relationships and correlations.

Sayad

Hi @WillowMobileSys @I have Connected the ref table to my second sheet. As you can see below

I have added [filter ref] with [Report Print]. Here the function is working but not converting to [Date] grouping.

I have added this expression in this column.

FILTER("SCAN SHEET",

IFS([_THISROW].[SELECT TYPES]=BY DATE,
AND([DATE]>=[START DATE],[DATE]<=[END DATE]),

[_THISROW].[SELECT TYPES]=BY DATE AND CHANNEL,
AND([DATE]>=[START DATE],[DATE]<=[END DATE],[CHANNEL]=[_THISROW].[Select Channel]),

[_THISROW].[SELECT TYPES]=BY DATE AND COURIER WISE,
AND([DATE]>=[START DATE],[DATE]<=[END DATE],[COURIER NAME]=[_THISROW].[Select Courier Name])

)
)

WillowMobileSys

@Sayad wrote:

Here the function is working but not converting to [Date] grouping.

I have no idea what this means in relation to your prior questions.

For your template to show a value in the Date Group column, you MUST explicitly specify the column you want shown. I'll assume its the [Date] column so you would need to adjust your expression like this (the red text is the new part):

<<Start: ORDERBY(FILTER("SCAN SHEET", 
 ([_ROWNUMBER] = MIN(SELECT(SCAN SHEET[_ROWNUMBER], 
([_THISROW-1].[DATE] = [DATE]))))), [DATE])>><<[Date]>>

I wonder though if this expression will give you the Date Group results you want. I suspect you will see the same Date multiple times but since I don't really know your data I am guessing.

Try the above out and see what results you get.

Sayad

Your reply is very beneficial for me. But after using this expression a new error is showing.

DATE

CHANNEL

TOTAL QTY

<<Start: ORDERBY(FILTER("SCAN SHEET",

([_ROWNUMBER] = MIN(SELECT(SCAN SHEET[_ROWNUMBER],

([_THISROW-1].[DATE] = [DATE]))))), [DATE])>><<[DATE]>>

<<[Channel]>>

<<SUM(SELECT(SCAN SHEET[Quantity], ([_THISROW-1].[CHANNEL] = [CHANNEL])))>><<END>>

WillowMobileSys

Make sure there are no spaces or line breaks between the START expression end brackets and the column to be displayed i.e. <<START:.........>><<[Date]>>

Is this the only START/END block you have in the template?

Sayad

@WillowMobileSys wrote:
Make sure there are no spaces or line breaks between the START expression end brackets and the column to be displayed i.e. <<START:.........>><<[Date]>>

I have checked there is no extra space anywhere.

WillowMobileSys

Looking closer at the START expression, it looks like the FILTER() criteria is possibly missing an AND()????

Sayad

@WillowMobileSys wrote:
AND()

You can rewrite the expression and send it again.Please

I do not understand the expressions of Appsheet.

Sayad

@WillowMobileSys I have been trying to create a group template for a long time. But I haven't been successful yet. Tell me the whole process how to make group template. With the help of reference table.

WillowMobileSys

First, I guess my response yesterday didn't get sent. I am busy and probably just failed to tap the "Post Reply" button. I was trying to say to forget what I said about AND(). What I saw was a part of the SELECT().

Second, I mentioned at one point that [_THISROW-1] refers to a Parent row. This is wrong. I can never seem to remember it properly. Inside a template [_THISROW-1] refers to the row being processed from an OUTER expression when you have NESTED expressions

So let's back up...You wish to Group on two columns, Date and Channel, and then show a calculated SUM() for the Group. I assume you expect to several Dates and several Channels and therefore many combinations of Date+Channel.

Next thing is to identify how do you want to display the data

Example table 1

DATE GROUP	CHANNEL	TOTAL QTY
Date 1	Channel 1	9999999
Date 1	Channel 2	9999999
Date 1	Channel 3	9999999
Date 2	Channel 1	8888888
Date 2	Channel 5	8888888
Date 3	Channel 4	7777777
Date 3	Channel 6	7777777
Date 4	Channel 2	6666666

Example table 2

DATE GROUP	CHANNEL	TOTAL QTY
Date 1	Channel 1	9999999
	Channel 2	9999999
	Channel 3	9999999
Date 2	Channel 1	8888888
	Channel 5	8888888
Date 3	Channel 4	7777777
	Channel 6	7777777
Date 4	Channel 2	6666666

Then you need to identify where the data comes from. This is where I get lost.

If you are grouping by Dates, where do the list of Dates come from? Your expression implies its from the the "PDF Template" row that is passed into the template from the Bot. But this is only a single Date value. Is that all you expect for this instance of your PDF Template?

Tommy62

Try <<Start: ORDERBY(FILTER("SCAN-SHEET", ([_ROWNUMBER] = MIN(SELECT(SCAN-SHEET[_ROWNUMBER], ([_THISROW-1].[DATUM] = [DATUM]))))), [DATUM])>>

[ DATUM] falsch-->[DATUM] ohne Leerzeichen

Sayad

Hi @Tommy62 @WillowMobileSys

Why is it not converting into [Date] grouping? This column is getting generated blank.

Tommy62

As I understand it, you have a data table (CAMPUS SCAN SHEET) and a report filter table (PDF Template).
I recommend you watch the following video to understand the basics of creating a PDF template.
Report Table [BUILD] | Foundation - YouTube
I just saw that you created a filtered ref column, which you could just put in the start expression.
<<Start:[REPORT PRINT)>><<[DATUM]>>