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.
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 |
Solved! Go to 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]
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):
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]
Perfect, Steve, both of us had the same idea. Thank you much!
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |