Hello everybody
I am having the following issue:
I want to export some data into a Offer PDF. In a table there should be shown the specific Service Name and Costs (f.e. Cleaning 200, Wall painting 350), sorted by the “Category and Order” of the services (f.e. 1. Preparation (like Cleaning, Wall Painting), 2. Accomplishing etc.)
There is a Offer Table and a table with to it related Services.
What I want to achieve is:
…
1 PREPARATION> > 1.1 Cleaning $ 200> > 1.2 Wall painting $ 350)> 2 ACCOMPLISHING> > 2.1 Drying $ 50> > 2.2 Finalizing $ 130"
…
But what currently and actually is happening is:
…
1 PREPARATION> > 1.1 Cleaning $ 200> > 1.2 Wall painting $ 350)> 1 PREPARATION> 2 ACCOMPLISHING> > 2.1 Drying $ 50> > 2.2 Finalizing $ 130> 2 ACCOMPLISHING
…
As you can see the categories are being doubled (beneath the first correct category and the services).
In the google docs template for the Offer the content looks like:
…
<< Start:
OrderBy(SELECT([Related Sevices][ID_Sevice], true), [Category and Order No] >>
<< Start: [Related Services] >><< [Category No and Service No] >>
<< [Service] >>
<< [Price] >><< End >>
<< End >>
…
Appreciate help how to get the doubled categories out of the offer.
(To clearify: each service has a category that is belonging to, which is chosen inside the row).
Thank you in advance!
This observation is without going into minute details of how the workflow template is constructed.
The [Category and Order] column appears to be twice in the template. Could you possibly omit the second one?
<< Start: [Related Services] >>
<< [Service] >>
<< [Price] >><< End >>
Hello Suvrutt,
thank you for your reply.
Sorry. There was a wrong content in one of the fields. It actually looks kind of this:
Not quite sure, where you mean to remove the second Category and Order…
I would like to get this kind of result (but get the category out of the same row of the service table), don’t really know how to move on with the related table:
[Sample App for "Group By A Field" in Reports](https://community.appsheet.com/t/sample-app-for-group-by-a-field-in-reports/20744) Questions
At times, one needs to group the records in the report by a specific field. I have created a small sample app to create a 'group by a field" report. Here is the app. Group By Reports The app uses the data from the already available sample app called “Histogram” While browsing the app in the editor, please take a look at following relevant details The VC “RefSize” .This Ref type VC classifies each of the records by assigning it the " group by field category" to which the record belongs to…
or
[Single category headers in workflow templates for multiple records of same category](https://community.appsheet.com/t/single-category-headers-in-workflow-templates-for-multiple-records-of-same-category/20337/2) Tips & Tricks ?
Outer <
But I am not sure how to apply this ideas on my case (with the related table).
It seems like the failure is at the Outerer <> expression. The inner one seems to work well.
I guess the “True” within the “Select” Expression relates to the ID and thats correct. The ID of the service is not the same. But the “Category Title” (which is neither a key nor a table) is just a Column within the row of the related Service table.
So what I might need is a filtering out function after a specific Category (column) was first shown. Makes that sence?
Do you have an Idea on that, how to change the expression?
Hi @stuggijo_h,
For group by options in the report / workflow template, I believe you could take a look at the following tip by @Marc_Dillon
His suggestion is much more efficient than the approach I had shared in the sample app
[Single category headers in workflow templates for multiple records of same category](https://community.appsheet.com/t/single-category-headers-in-workflow-templates-for-multiple-records-of-same-category/20337) Tips & Tricks ?
Say you have data like this: [image] With “Group by” selection in a Table view, you can easily display this in the app like so: [image] But what if you want to do so in a PDF file generated via a START expression in a workflow? Use the following general model: [image] Which basically says “if this is the first item in this category, display a header”. The tricky part is when using a subset of values in a table, like child values of the parent record you’re working with. Then for the seco…
BTW in the image shared by you [Category and Order] seems to be appearing in first as well as second statement.
<< Start:
OrderBy(SELECT([Related Sevices][ID_Sevice], true), [Category and Order]) >><< [Category and Order] >>
<< Start: [Related Services] >><< [Category and Order] >>
<< [Service] >>
<< [Price] >><< End >>
<< End >>
I requested to evaluate if one of those could be omitted.
1 Like
Hey Suvrutt,
thank you for your quick reply.
You are right, there was a mistake in my request here. In the original data and that is not there.
It looks like this:
I have also tried Steve’s solution.
<<Start:
Filter(OrderBy(SELECT([Related Services][ID_Service], true), [Category No and Order No]), ( [Related Offer ID] = [_THISROW].[Related Offer ID] ) )>>
<< Start: [Related Services] >> << [Category No and Order No] >>
<< [Service] >>
<< [Price] >><< End >>
<<End>>
The doubled Categories disappeared, but then another failure appears:
There were some Service rows shown that are not a part of the Offer.
It seems like when no Category for a Service is chosen (which might happen in simple offers), they appear in this particular offer.
So I need to filter them out.
Do you have an Idea?
Hey Suvrutt,
thank you for your quick reply.
You are right, there was a mistake in my request here. In the original data and that is not there.
I have also tried Steve’s solution (If I have undestood i correctly).
<< Start:
ORDERBY(FILTER(Service Table, ([_ROWNUMBER] = MIN(SELECT(Service Table [_ROWNUMBER], ([_THISROW-1].[Category and Order No] = [Category and Category No] ))))), [Category and Order No] ) >>
<< [Category and Category No] >>
<< Start: [Related Services] >><< [Category No and Service No] >>
<< [Service] >>
<< [Price] >><< End >>
<< End >>"
The doubled Categories disappeared, but then another failure appears:
There were some Service rows shown that are not a part of the Offer. (They have an empty Category.) So I need to filter them out.
Do you have an Idea on that?
Hi @stuggijo_h,
stuggijo_h:
There were some Service rows shown that are not a part of the Offer. (They have an empty Category.) So I need to filter them out.
Thank you. I believe you will need to mention what filter you are trying to apply and how the tables are related if there are more than one table in your template. In general , relevant structure of the table and what exactly you are trying to achieve. The earlier suggestion was more on syntactical side.
1 Like
Ok. I try to explain:
There is the Offer tabIe. Here are all the details for making an offer.
And there is the related table “Service”.
The Service Table includes the Name of the Service, Service Number, the Service Category (Name), Category Number and further details.
The Category Numbers and the Service Numbers are generated while adding them into the table.
But it is not Necessary to choose a Category when you add a new Service row. (For simple offers that do not conain many services. It is a way to group the Services in bigger Offers.)
In the Offer PDF i want to show only all the Services that are related to an Offer.
I have build a simplified copy, that might give you a better understanding:
Currently the PDF is not generated, so there must be something wrong with this:
here is an open link to the table:
Offer Table
(Related) Service Table
and here a link to my sample app:
https://www.appsheet.com/start/0d3c08a6-eaed-4bf9-97d0-6badcc029f6f
1 Like
Thank you @stuggijo_h for all the details. I could not open the sample app shared by you because of the access denied message. Anyway the table details given by you were good. I believe I have been able to work out the template as below.
I used and tweaked the expressions mentioned by @steve in the tips and tricks post referred below. Thank you @steve for impeccable expressions as usual
[Single category headers in workflow templates for multiple records of same category](https://community.appsheet.com/t/single-category-headers-in-workflow-templates-for-multiple-records-of-same-category/20337) Tips & Tricks ?
Say you have data like this: [image] With “Group by” selection in a Table view, you can easily display this in the app like so: [image] But what if you want to do so in a PDF file generated via a START expression in a workflow? Use the following general model: [image] Which basically says “if this is the first item in this category, display a header”. The tricky part is when using a subset of values in a table, like child values of the parent record you’re working with. Then for the seco…
OfferPrint
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”, ([_THISROW-1].[Category_and_Category_No] = [Category_and_Category_No]))>>
Service: <<[Service]>>
Price: <<[Price]>>
<< END >>
<< END >>
The template needs to be fired as workflow on the parent table “Offer” . The name of the report is “OfferPrint” Also the template fields above Offer Details word and below the Offer word are from the parent table and the template below the word Offer details mainly runs on the child table “Service”
I have given the above for ease of copying the template for you. Below are the images of the actual template and the test report run with it.
Template Image Below:
Image of Test Report Run Below:
2 Likes
Hello Suvrutt!
It’s working great. You have served the solution the perfect way for a beginner like me.
I have been struggling with that for a long time.
Thank you so much for the solution and your effort!!!
You are amazing!
3 Likes
Hi @stuggijo_h ,
You are welcome. Good to know it works the way you want. We must thank @steve because we based our solution on his recommended approach. Thank you @Steve.
1 Like
Thank both of you very much!
1 Like
One more question:
I have a little problem: The second filter within the outer expression seem not to apply:
I have created an updated table with some categories within different offers but the same name (which my happen).
Unfortunately the current solution is printing all the categories with the same name in the offer, even if they are coming out of different Offers (below: orange marked services don’t belong to the offer 1).
Expressions are like suggested:
Do you have an idea, where the code is to change?
Oh. Got it. Please try below . The change is only in the second start expression and highlighted in bold.
OfferPrint
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] )))>>
Service: <<[Service]>>
Price: <<[Price]>>
<< END >>
<< END >>
1 Like
Unfortunatelly this does not help. It looks the same:
If I try to print Offer_2, the output ist blank:
Offer_3 is working (but it just has a single row of content without having the same category names as both of the other).
Do you have another idea?
Just for a better overview: I have colored the same category names between the different offers.
It is working perfectly when I run the reports through workflow on the Offer table by using following approach.
https://www.appsheet.com/samples/An-app-that-sends-email-when-you-click-a-Action-button-on-the-client?appGuidString=7fd5e590-8388-4b20-8087-ba0a7be51607
Could you please update how you have configured your workflows?
I may also suggest that please remove any color formatting in back end tables while testing.
1 Like
Hi Suvrutt,
thank you for you patience.
TABLE
BEHAVIOR / ACTIONS
BEHAVIOR / WORKFLOW
OFFER Detail (with related Servies and Push Button for updating the sending date within the offer table, which should trigger the workflow)
Hope that helps. I can also give you access, if you advise me on that
Thank you. In general your action setting seems OK. However I believe you may wish to consider [_THISROW_BEFORE] and [_THISROW_AFTER] in your workflow condition with the column [Offer_Sending_Date] because I ISNOTBLANK() condition will fire the report only once.
It also sounds that you have made some changes to the template. For example you have added category and Service number. You may want to share that template, because as I mentioned, the template suggested is working ok.
1 Like
colored
black
ТЕХТ
(I have added the “Category No and Service No”, bold marked)
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: <<[Service]>> Price: <<[Price]>>
<< END >>
<< END >>
Thank you. The template shared by you looks good and is perfectly working at my end. I tried your template in the email body section.
First Offer:
Second Offer:
1 Like
