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!
Solved! Go to Solution.
We were quite close and have found the failure.
It has been quite simple in the end:
Thank you, Suvrutt, thank you Steve!
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:
or
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
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.
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,
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.
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
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
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:
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!
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.
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.
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 >>
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.
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.
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.
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:
I may suggest, please message me the ID at which I can share the sample app created by me based on the table structure shared by you.
Hi Suvrutt,
the ID is 1423429
Please message me your email ID where I can share the app in message section of this community portal.
Dear Suvrutt,
I have checked your app. Thank you much for you effort!
Unfortunately, when I have copied the actual values into your service table (with some identical category names), the same failure appears, as described above:
Inside the offer 1 PDF I have marked the services that should not be shown in this offer 1 (because they are related to Offer 2).
So the Offer filter seem not to apply and we still need an update of the expression that makes sure, on the offer only those services are shown which are related to this specific offer. Donโt matter, if there are identical named to services that are related to other offers.
Do you have an Idea?
Here the data for updating you table, so that you donโt have to type in all the values (or I can send you the link to my google sheet table):
Related_with_ID_Offer | Category_No | Category | Service_No | Service | Category_No_and_Service_No | Category_and_Category_No | Price |
---|---|---|---|---|---|---|---|
ID_Offer_1 | 1 | Bedroom | 1 | Wallpapering | 1. 1 | 1. Bedroom | 500 |
ID_Offer_1 | 2 | Bathroom 1st Floor | 1 | Lay the floor | 2. 1 | 2. Bathroom 1st Floor | 450 |
ID_Offer_1 | 3 | Living room | 1 | Plaster | 3. 1 | 3. Living room | 750 |
ID_Offer_1 | 3 | Living room | 2 | Install sanitary facilities | 3. 2 | 3. Living room | 400 |
ID_Offer_2 | 1 | Bedroom | 1 | Brush | 1. 1 | 1. Bedroom | 200 |
ID_Offer_2 | 1 | Bedroom | 2 | Laying tiles | 1. 2 | 1. Bedroom | 200 |
ID_Offer_2 | 2 | Bathroom 1st Floor | 1 | Plaster | 2. 1 | 2. Bathroom 1st Floor | 100 |
ID_Offer_2 | 3 | Living room | 1 | Wallpapering | 3. 1 | 3. Living room | 100 |
ID_Offer_3 | 1 | Core renovation | 1 | 5000 |
Hi @stuggijo_h,
I believe I could also be missing some point because it seems to be working at my end but you are facing issues. So I believe there is some lack of understanding of requirements on my part.
I am sorry. I believe you it may be best approach that you may seek help from someone else.
OK. Thank you very much!
Even if I do not really know who to ask else :-/. Make a new request into the community?
I might not were clear enough while explaining what kind of result I needed.
Sorry for that:
The current solution works great as long as they are no identical named categories within the related data (sevice table). But in my case there might be identical named categories within the related data (related services to an offer).
Unfortunately in your table there were no dublicates. So it could not show if the goal is achieved. I have copied your app and added the โduplicatesโ (I have colored them into the same color in the upper table trying to point out, that it this case can appear) into your service table to check, if only the services were shown in the offer pdf, which are really related to the particular offer.
I would appreciate if you could copy the โduplicateโ values (into your sample service table and run the pdf), then you understand my issue.
Thank you very much for you help!
We were quite close and have found the failure.
It has been quite simple in the end:
Thank you, Suvrutt, thank you Steve!
Thank both of you very much!
User | Count |
---|---|
43 | |
30 | |
24 | |
23 | |
13 |