Solved: Issue with PDF generate with filtter

yomri001

I need help please to create an invoice with specific details:

In the app, I have the following tables:

Category (Category_ID, Category_Name)
Product (Product_ID, Product Name, REF_Category_ID, QTY)
Order(Order_ID, Total_Price)
Order_Details(OrderDetails_ID, REF_OrderID, REF_ProductID, Price, QTY ,REF_Category_ID)

I want the invoice to show a table in the first column Category name, second column the total quantity ordered for products of the same category, and then the third column "the total price of the products" of the same category)

I want to do this because in order we do have many products sometimes 200 products, so there is no need to show the 200 line in the table, we only want to show the category and the total QTY ordered for that category, but I couldn't achieve that, PLEASE HELP ME !!! I have to deliver the app very soon.

I tried many ways on the doc template, but I wasn't able to make it.

TeeSee1

I optimized the template further.

This gives you a better table alignment with simpler expressions.

View solution in original post

yomri001

Again, thank you so much @TeeSee1, I really appreciate your help. I did fix the quantity also now.

Thank you so much, here is the final code:

View solution in original post

TeeSee1

Try something like..

Order
<<Order_ID>>
<<Total_Price>>

Order_Details
<<START:Catetory[Category_ID]>>
<<If:SUM(SELECT(Order_Details[QTY], AND([REF_OrderID]=[_THISROW].[Order_ID],[REF_ProductID].[REF_Category_ID]=[_THISROW-1].[Category_ID]))>0>>
Category=<<[Category_Name]>>
Total Category QTY=<<SUM(SELECT(Order_Details[QTY], AND([REF_OrderID]=[_THISROW].[Order_ID],[REF_ProductID].[REF_Category_ID]=[_THISROW-1].[Category_ID]))>>
Total Category Price=<<SUM(SELECT(Order_Details[Price], AND([REF_OrderID]=[_THISROW].[Order_ID],[REF_ProductID].[REF_Category_ID]=[_THISROW-1].[Category_ID]))>>

<<EndIf>>
<<End>>

This is only to get you the data and display it in plain text. For table formatting, you need to tweak it to your liking. A Google Doc template's table does not seem ideal. And you may have to use an HTML template if you want to make it look pretty..

yomri001

Thank you so much for your time and assistance, I tried the code you shared with me but Order Details section is empty! this made me crazy.

I found that there was some missing parentheses on the code that i fixed them, but still empty, please can you help more?

I only want the data to show up then I'll edit the design on the invoice.

thank you so much 🙏

TeeSee1

This template

produces

This is the underlying data

I sort of translated my template into your schema based on what you described.

Please compare yours and mine carefully and adjust as required.

yomri001

I can't thank you enough TeeSee, i hope this works for me, i'll adjust it now and let you know in a bit 🙏🏻

Thank you so much

TeeSee1

I optimized the template further.

This gives you a better table alignment with simpler expressions.

yomri001

Thank you so much again sir, I tried the last template you shared with me, it did show values but for all categories I have on the database not only the ones related to he order! I don't know why to be honest.

If you can share with me the test project you used I think it will help me understand well how to do that.
Here is the code

Here cod

Here the result:

yomri001

The table and column names are different, in my first request I tried to share just a simple example to understand how I can do the thing and then I can adjust it

yomri001

Hello again, I think the issue is in the select code in the Start.

Please the id on the IN function it's the id of which table?
Also for the code in Category=<<Item>> does Item mean the Category name in your example?

Thank you so much again for your help, I really appreciate that 🙏

TeeSee1

Non qualified columns in a SELECT expression always refer to one in the table specified in the first argument, which is categories in this case.

[item] is a column in the categories table, actually the name of the category. (one would name it category_name or something but my app is a playground and a lot have been reused. So sometimes names can be confusing...)

One thing I noticed in your expression.

Shouldn't [Related Order_Details] be [Related Order_Detailss] if your child table is named Oreder_Details as can be seen in your SUM expression. (though it should give you an error unless you actually have a column named [Related Order_Details] somewhere, in which case the system 'intelligently' searches for it and uses it automatically.)

I can share my app but again it is super messy..

https://www.appsheet.com/portfolio/3401559 (it is called TEST05)

yomri001

I checked the app, and everything looks similar to mine but still in the generated PDF I got all the list of Designs (Categories), and not only the ones related to the order selected! 😢
Regarding Related Order_Details, I do have a column in the order table that refers to the related orders as you can see from the screenshot (auto-generated by the system)

Can I share the app with you to check that from your side?

TeeSee1

I can take a look at it and see if I can detect any differences.

yomri001

Again, thank you so much @TeeSee1, I really appreciate your help. I did fix the quantity also now.

Thank you so much, here is the final code:

Suvrutt_Gurjar

Hi @yomri001 ,

Nice to know that you have solved the issue. May I request you to mark one of @TeeSee1 's post also as a solution. Your requirement was a tricky one and extensive one and @TeeSee1 has obviously helped you by taking lots of efforts and even creating a sample app for the purpose.

I believe he very much deserves one of his posts being marked as a solution.

TeeSee1

Thanks, @Suvrutt_Gurjar .

I wish solutions credits fattened my bank account!😁

Suvrutt_Gurjar

@TeeSee1 wrote:

wish solutions credits fattened my bank account!😁

@TeeSee1 wrote:

I wish solutions credits fattened my bank account!😁

🙂🙂 Yes, that would be fantastic.

yomri001

Of course, @tetes really deserves all the credits, s without hia help i wouldn't solve the issue. Thank you so much again @TeeSee1 and i did mark your reply as solution.

Thank you so much again for the efforts and time 🙏🏻🙏🏻

Suvrutt_Gurjar

Thank you @yomri001.👍

Best wishes for your app creation.

TeeSee1

I think I just found where it's wrong. My bad...

Try

IN(
 [Design_Barcode], 
 SELECT(
  Order_Details[Ref_Design], 
  [Order_ID]=[_THISROW].[Order ID], 
  TRUE
 )
)

without carriage returns.

My previous expression works because my data only has limited cases.

Hope this solves it..

yomri001

Thank you so much @TeeSee1, I fixed the issue now, I couldn't do it without your help 🙏 I still have now an issue with the quantity it is wrong, but I think it will be easy to fix now.

Here is the code I used in the template:

I don't know whats the reason why it wasn't working using the Related Order Details column, so I tried to do it the manual way (using the select)

For the quantity I'll have to see how to fix it, apparently, it shows the total available quantity in a category instead of the quantity ordered.