PDF Offer - How to get rid of doubled parent information (Category)

stuggijo
Participant IV

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 Solved
0 26 665
1 ACCEPTED SOLUTION

We were quite close and have found the failure.
It has been quite simple in the end:

3X_a_1_a12eaa9fc72d9c351edd4e52689b9136210b1ec9.png

Thank you, Suvrutt, thank you Steve!

View solution in original post

26 REPLIES 26

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:

3X_b_1_b18b2073d3467c466295a9df357dcc8260370143.png

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).

3X_2_3_23b376de0d2bb598dd992dab781052072f0a79ec.png

<< 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
3X_6_c_6c3dd2ad008cba393f29571f083110ac40e90a74.png

(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


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:

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.

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 >>

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-clien...

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:

3X_a_1_a12eaa9fc72d9c351edd4e52689b9136210b1ec9.png

Thank you, Suvrutt, thank you Steve!

stuggijo
Participant IV

Thank both of you very much!

Top Labels in this Space