Generating a table in a PDF-Invoice

AppSheet AppSheet Q&A
,
1

Dear friends,

I need some advice how to create a select-statement to generate a table in a pdf over 3 tables which are related as following:

heiner-dm.jpg
heiner-dm.jpg1459×825 48.2 KB

In my first Column I need the name of the client from the Customer table and I start with:

<Start:SELECT(INVOICES[Invoicenumber],true)> <<[CustomerID].[First name]>><< [CustomerID].[Last name]>>

In my second column I need the grave inscription from the Graves table. As my starting point is the Invoices table I do not know exactly how to retrieve this information.

Thanks for your help,

Robert

1 Like
2

I think you may be overthinking it. As odd as it may be you can just use a double dereference, so [Customer ID}.[GravesID].[Graves Column name]. It may be odd and there is likely a more efficient solution but I do believe that this will work for you. Please let me know if for some reason that does not work and I can help you get this figured out.

Craig

QREW Technologies

1 Like
3

Hi Craig,

Thank you for your reply.

I hope you understand what I try to accomplish. I want an Invoice for a client and a detailed listing of the graves and services for each grave applied.

The point is that I don’t have a reference in the table Customers to table Grave but the way around.

I cannot jump from the Table Invoice to Grave by [CustomerID].[PersonaID],[GraveID] as [PersonaID] is not a reference.

What would you suggest to create this master-detail in my Invoice-PDF?

Thx. - Robert

1 Like
4

Okay, now I understand, I apologize I misread the picture you had posted with your tables listed out.

So the nice thing about AppSheet is that it automatically is ready for this. When you establish the references and column types AppSheet automatically creates a virtual column in a parent table with the IDs of all related child table entries.

This means in your customer table there is at the very bottom of your column structure a virtual column called “Related Graves” and “Related Invoices” In case that did not pop up, you can make the expression using the REF_ROWS() function in AppSheet.

When it is time to use the Grave inscriptions lets say you have multiple inscriptions for the same customer. You could have a table that lists all the inscriptions that customer put on a grave. So you can put in that table <<Start:SELECT(Graves[ID],REF_ROWS(Graves, personalID)=[_THISROW].[personalID])>> <> <>

This will work as long as you stay within the start expression of your invoice. The ref_ROWS expression will show you the information you ask for for every grave that is associated with this customer. NOTE: If a customer comes twice, once a long time ago and once again in the present day, this will show them the inscription and information from the grave they purchased a long time ago, and the one in the present day. If that is to be avoided then the solution is a bit more complicated.

You would need to have the graves table reference the invoice as well as the customer. If you do this then it makes it so that the customer doesn’t have to see every grave that they have ever purchased in each invoice. So each invoice will be only the grave that has not been billed. Which if you do not already I recommend having a column in graves that shows if it has been invoiced for or not that way you can filter it out in your Select expression. Your parent child relationships would look like this. Please tell me what you think of this.

Screen Shot 2022-01-31 at 15.52.39.png
Screen Shot 2022-01-31 at 15.52.39.png1422×624 20.7 KB

Craig

QREW Technologies

REF_ROWS()

1 Like
5

Sorry Craig, this somehow just doesn’t work for me.

Error 1 : ‘Process Send Invoice’ task ‘Task_Send_Invoice_Email’ Attachment template. Expression ‘SELECT(Grave[GraveID], REF_ROWS(Graves, personaID)=[_THISROW].[personaID])’ is invalid due to: Expression ‘SELECT(Grave[GraveID], REF_ROWS(Graves, personaID)=[_THISROW].[personaID])’ was unable to be parsed: Object reference not set to an instance of an object…

Thx. - Robert

6

Your use of REF_ROWS() is nonsensical.

7

SELECT(INVOICES[Invoicenumber],true) makes no sense. It’s the same as INVOICES[Invoicenumber]

I assume (because you haven’t share any template nor report) that this makes a list of customers, this way: FirstNameLastName

Now, about the “grave inscription”:

You already told that a customer can have many Graves, so you want many graves’ inscriptions?

SELECT(
  Graves[InscriptionColumn],
  [PersonalID]=[_THISROW].[CustomerID]
)

I can’t go further if you don’t post an screenshot of your template and the result you are getting