Need some guidance 😊

[mjoninhas]

Hello community,

I'm developing my app, but I need to make some improvements and I now I don't know how can I make it. 😕
I have a clients database, and those clients have some invoices that are pending to pay. What I want to do is a new Menu View called Clients Debts. On this view I'll do a slice for the clients in debt. 

And inside a clients Name, I would like to see all the information and the invoices pending to pay for that client. If it was just one invoice per client, it is ok for me to do, but sometimes, one client could have more that 5 invoices to pay.

And I would like to display/show the invoices to pay like this:

Client Name
xxxxxxxxxxxxxx

Date Invoice	Invoice Nº	Amount	Due Date
xxxxxxx	xxxxxx	xxxxx	xxxxxx
xxxxxxx	xxxxxx	xxxxxx	xxxxxxx

Total Amount in Debt
xxxxxxx

Any clue how can I make something like that?
Thank you.

[Joseph_Seddik · Accepted solution]

In your Invoices table you should have a Status column indicating whether the invoice has been paid. It can be an Enum with two possible values: "Paid" and "Pending" for example. The Invoices table should also have a Ref column to Clients table. 

First,  you should create a slice from this table with a row filter condition: [Status] = "Pending". Let's name this slice pendingInvoices. 

Then in your Clients table, you add a virtual column with the following App Formula. Let's name it clientPendingInvoices: 

FILTER(pendingInvoices, [Client] = [_ThisRow].[Client ID])

Adding this column to a Detail view will show you a clickable list of invoices pending payment that belong to this client. 

The pending sum can be calculated with this formula, which you can also put in a virtual column in the Clients table:

SUM([clientPendingInvoices][Amount])