Problems with unique single category headers in workflow templates (PDF) for multiple records of same category

[stuggijo]

Hello everyone,

I have a problem with getting some spicific filtered data into a PDF, that should attach in an emal.

There is an Offer table that contains the offer no.

And there is a Service table that contains the services related to one specific Offer.

What I want to achieve is an automatically generated Offer PDF that is attached to an Email. This PDF should contains all of the related Services to the offer, grouped by a specific category (and category no).

Sometimes it might happen that there are same “Category” names or same “Category and Category No” names within Services that are related to different Offers (here colored for a beter overview). The 3 different offers have here (for better overview) yellow, white and green background.

When the user pushes the “Send Offer” button, an email with the Offer PDF should be created, that the user is looking at.

The case is set up by an action that triggers a workflow to send an email.

Unfortunately there seem to be something wrong inside the expression. Because within the Offer PDF not only the related Services of an specific offer are shown, but all the Services that have the same “Category and Category No” (so all the Services with identical name within the Service table). So the filter to filter out the not related services seem not to apply.

The automatically generated Offer PDF (here Offer No 1) looks like this (red marked my notices which Services are correct and wich belong to another offer no) :

The PDF template looks like this (colors just for you to see my values put into):

While there was a similar question in the past, it could’t filter out the Services that are not related to the selected Offer.

Single category headers in workflow templates for multiple records of same category

The expression is:

Offer

ID_Offer: <<[ID_Offer]>>

Customer: <<[Customer]>>

Location: <<[Location]>>

Service_Costs: <<[Service_Costs]>>

Offer_sending_date: <<[Offer_sending_date]>>

Offer Details

<<Start: ORDERBY(FILTER(“Service”, ([_ROWNUMBER] = MIN(SELECT(Service[_ROWNUMBER], AND([_THISROW-1].[Category_and_Category_No] = [Category_and_Category_No], [_THISROW-1].[Related_with_ID_Offer] = [ID_Offer].[ID_Offer] ))))), [Category_and_Category_No])>>

<<[Category_and_Category_No]>>

<<Start: FILTER(“Service”, (AND([_THISROW-1].[Category_and_Category_No] = [Category_and_Category_No], [_THISROW-1].[Related_with_ID_Offer] = [ID_Offer].[ID_Offer] )))>>

<<[Category_No_and_Service_No]>> <<[Service]>> <<[Price]>>

<< END >>

<< END >>

I am struggling with this for quite long and appreciate help very much

PS:

Here the Offer table values (above shown as screenshot of google sheets):

ID_Offer	Customer	Location	Service_Costs	Offer_sending_date	SendEmail
ID_Offer_1	Max Power	New York		14.10.2020 19:39:05	7
ID_Offer_2	Homer Hyper	New Jersey		14.10.2020 19:20:36	8
ID_Offer_3	Stefanie Sue	New Orleans		14.10.2020 17:46:54	0

Here the Service table values (above shown as screenshot of google sheets):

ID_Service	Related_with_ID_Offer	Category	Category_No	Category_and_Category_No	Service	Service_No	Category_No_and_Service_No	Price
ID_Service_1	ID_Offer_1	Bedroom	1	1. Bedroom	Wallpapering	1	1. 1	500
ID_Service_4	ID_Offer_1	Bathroom 1st Floor	2	2. Bathroom 1st Floor	Lay the floor	1	2. 1	450
ID_Service_2	ID_Offer_1	Living room	3	3. Living room	Plaster	1	3. 1	750
ID_Service_3	ID_Offer_1	Living room	3	3. Living room	Install sanitary facilities	2	3. 2	400
ID_Service_5	ID_Offer_2	Bedroom	1	1. Bedroom	Brush	1	1. 1	200
ID_Service_6	ID_Offer_2	Bedroom	1	1. Bedroom	Laying tiles	2	1. 2	200
ID_Service_8	ID_Offer_2	Bathroom 1st Floor	2	2. Bathroom 1st Floor	Plaster	1	2. 1	100
ID_Service_7	ID_Offer_2	Living room	3	3. Living room	Wallpapering	1	3. 1	100
ID_Service_9	ID_Offer_3				Core renovation	1	1	5000

[stuggijo]

For all who are interested:
Got it

It has been the second part of both of the expressions, thats needed to be substited with this (yellow marked):

[Steve · Accepted solution]

In the two places it appears, replace this:

[_THISROW-1].[Related_with_ID_Offer] = [ID_Offer].[ID_Offer]

with this:

[_THISROW].[ID_Offer] = [Related_with_ID_Offer]

[stuggijo]

Perfect, Steve, both of us had the same idea. Thank you much!