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

[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])

[mjoninhas]

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

[Joseph_Seddik]

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.   

[mjoninhas]

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. 

[Joseph_Seddik · Accepted solution]

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

[mjoninhas]

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 😅

[Joseph_Seddik]

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.

• References between tables - AppSheet Help 
• Dereference expressions - AppSheet Help 

[mjoninhas]

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?

[Joseph_Seddik]

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.

[mjoninhas]

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

[Joseph_Seddik]

Yeah I can generally understand written Portuguese 🙂 and Lambada 😀

Would you show me please the columns of the table where you have this behavior?

[mjoninhas]

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

[Joseph_Seddik]

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?

[mjoninhas]

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:

[Joseph_Seddik]

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  🙂

[mjoninhas]

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

[Joseph_Seddik]

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. 

[mjoninhas]

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 

[Steve]

Can you confirm that the label column in fact has a value?

It appears the label column is a Ref value.

Can you confirm that the label column's value refers to an existent key column value?

Can you confirm that the label column references a slice or row that includes the referenced row? Does the slice row filter exclude the row? Does the table's security filter exclude the row?

[Joseph_Seddik]

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. 

[Steve]

Could someone please summarize the specific problem you'd like me to consider. There's a lot to this thread.

[Joseph_Seddik]

@Steve Sure, thank you. 

According to the description:

1. Ref  type column, referencing a slice 
2. The underlying table of this slice has a Label column
3. But the referencing column (in 1.) is showing Key not Label values in the view.

[mjoninhas]

Hello,

What do you think @Joseph_Seddik and @Steve  about this:

[Joseph_Seddik]

What view are you exactly referring to? are you generating a file? if this the case, this is not a "view". Anyway, labels are for app views. In templates you have to explicitly specify the columns using reference expressions. 

[mjoninhas]

I give up on this. I turn arround this question by adding a new column and inside of it I placed a formula to bring up the cliente name. I used a Vlookup. Thank you all for your suggestions.