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.
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])
5 Likes
Thank you @Joseph_Seddik 
After struggling a little bit I finally manage to do this. I made, perhaps, a noob mistake. I just now figured out that the ref column should have the Key on the field that I want to use as “condition”. On my clients table I had set the Key and the Label on my Clients Name, and because of that nothing worked. It was enough just to change the key and voilá 
But know by changing the key to another column, on the clients name that had already made some purchases, in front of their names now I see a yellow sign warning. Why is this? If I change my key back to Clients Name, this sign disappears. 
The yellow triangle indicates a broken reference. This is because the value the Ref column holds does not exist in the list of available keys. These columns still store the names of the client instead of client IDs.
To fix, just open these records, choose the client again and save.
1 Like
Hello again @Joseph_Seddik 
With keys and label I found a new thing, that you might be able to once again help me 
I have a Clients table, and inside of it I have the column NIF (its more like clients number) and another column that is Client Name. I set my NIF as key and Client Name as label.
Saying this, I have an automation to send an email and in my Email subject I set it as <<[Client Name]>>. What happens, is with that, when I get the email, instead of the Client Name I get the NIF (Client Number). I believe this happens because I set the NIF as Key. Is any way to display the Client Name and don’t remove the NIF as key?
Thank you.
NIF? are you in Spain? 
Keeping the NIF as your key is the correct thing to do. As for template, from what I can see, I believe you are running your task in the wrong context, and in that case you should instead be using:
<<[Client Name].[Client Name]>>
1 Like
No, I’m from Portugal Are you from Spain?
Thank you once again, it worked well. But why if I had the key and the label as the Client Name, I only needed to put on my template <<[Client Name]>>?
I still have a lot to learn
1 Like
I live in Spain. I guess we have similar systems
The problem is that the template is used in the context of a table other than the Clients table, and in this table the Client Name is actually a Ref column.
Please read these guides and you’ll get it easily. Tell me if you have any difficulty.
1 Like
Thank you. If you someday come to Portugal, I buy you a beer
As the app is becoming more complex, I found new things that are wrong (for me). Since I changed the keys and labels, now I found that in my orders table, my Client Name is getting the NIF instead the name. Is possible to fix this instead of changing the Key to Client Name?
Deal 
No no! NIF should remain as your Key column, and your Client Name column in all other tables should hold NIF values. Please read the guides I gave you, and also this one: What is a key? - AppSheet Help
Having this setup, in the Clients table, where you have your NIF column, you should mark the Client Name column as Label. In this way, in all of your app views, where you reference the Clients table the client’s name will be displayed instead of his NIF, but the value stored in the column will be the NIF value as it should be.
1 Like
I belive I’m doing something wrong. I also have a Reports table and on this one too, the client name is displaying the NIF.
For the following picture, is possible to understand if my key and label are right?: (hope that you might understand a little Portuguese )
Yeah I can generally understand written Portuguese and Lambada
Would you show me please the columns of the table where you have this behavior?
humm Lambada 
I managed to fix Reports already. The error on the template was because I only had <<[Client Name]>> and I fix it to <<[Client Name].[Client Name]>>
But I’m not figure out how to fix the names on the Encomendas table:
I believe I miss something. ?
OK, thank you.
The column “Nome Cliente” is a Ref to another table. Would you please check to what table this reference is established and show me the column config of that table?
1 Like
The column “Nome Cliente” is a Ref to “Lista Clientes” (slice). This slice works to filter the clients by the employers.
The column config of the table “Lista Clientes”, is the following:
OK, thank you. Please show the columns of the table underlying the slice, from the editor. We need to check how the columns are configured. Showing the sheet is not useful
Hello my mentor 
How are you?
Do you mean show you the column config like this:
The Filtro Lista Clientes (slice), have this formula:
if(LOOKUP(useremail(),“Usuários”,“Email Usuário”,“Função”)=“admin”,[Email Comercial]=[Email Comercial],[Email Comercial]=USEREMAIL())
Thank you
Hello my friend :).
What I can see is that you have correctly set the Label to the right column. I see no reason for the Customer Name not to show in the referencing tables views. Perhaps you could check that the slice is correctly filtering that particular table you are showing, not another one.
1 Like
Hello,
I checked the slice and it is correct. And my “Encomendas” is well configured:
If so, is possible to do what I want by other means? Maybe adding the NIF column and on the Client Name doing some kind of lookup maybe
What do you think? Thank you
That’s strange, I don’t know if this is a bug.. the last thing I’d do is a “regenerate structure” for the table, but this would be just a blind attempt. Perhaps our supreme mentor @Steve could help
Yes, of course you can add a new “Nome Cliente” column, but with a dereference expression; lookup is not needed.
1 Like
