How to reference data?

[paula]

Hello everyone!

I want two columns (Description & Unit Price) to show in the details view. Both are coming from the same table and as you can see in my spreadsheet, they relate with each other. So off course I only need [Description] to be the option in the Form View while it's Unit Price will automatically display uneditable, definitely. Where do I configure that? I appreciate any help, thank you!

[Aurelien · Accepted solution]

Dereference expressions - AppSheet Help

[Aurelien · Accepted solution]

I was more thinkg about the text below rather than the video 🙂

But I answered too fast anyway.

Something attracts my attention : it surprises me that you want to select two items, select one quantity, and get the two unit prices at the same time.

Are you sure the Description is an Enum ?

Which column is the key-column ?

You may want to read this documentation before going further, I think there is a description of a solution that fits exactly your case here:

References between tables - AppSheet Help

What is a key? - AppSheet Help

[Aurelien · Accepted solution]

Hi @paula 

This is the basics of reference columns.

Please do as suggested:

1) Create a table LOG with columns:

- Key (expression: UNIQUEID()), property Key ON, property Show OFF

- Timestamp (initial value: NOW(), type DateTime), property Label ON

2) Create a table ITEM with columns

- Key (expression: UNIQUEID()), property Key ON, property Show OFF

- Item Code (Type Text)

- Description (type Text), property Label ON

- Price (Type Price)

4) Create a table LOG_LINEITEM with columns:

- Key (expression: UNIQUEID()), property Key ON, property Show OFF

- LOG (Type Ref, source table: LOG), property "is as part of" : YES, property Show OFF

- ITEM (Type Ref, source table: ITEM), property "is as part of" : NO, property Show ON

- Quantity (type Decimal)

- Unit_price (Type Price, formula expression : [ITEM ].[Price])

- Total_Price (Type Price, formula expression : [ITEM ].[Price]*[Quantity])

Then in the LOG table, add a virtual column:

- TotalPrice (type Price, formula expression : [Related LOG_LINEITEMs][Total_Price ]

For reference and explanation, please have a look to this article:

Build list dereferences - AppSheet Help

References between tables - AppSheet Help

[Aurelien · Accepted solution]

Dereference expressions - AppSheet Help

[paula]

thank you so much for that quick response, I'll take a look and hope it gets solved

[paula]

Hello again. I saw that the video directs me to the 'advanced edit' pane which is in the former version. But where do I find that in this latest version? Thank you!

[Aurelien · Accepted solution]

I was more thinkg about the text below rather than the video 🙂

But I answered too fast anyway.

Something attracts my attention : it surprises me that you want to select two items, select one quantity, and get the two unit prices at the same time.

Are you sure the Description is an Enum ?

Which column is the key-column ?

You may want to read this documentation before going further, I think there is a description of a solution that fits exactly your case here:

References between tables - AppSheet Help

What is a key? - AppSheet Help

[paula]

Yes I realized that I need an option for quantity per description/item. But I don't know how to do that. And I need unit prices to automatically get referenced then computed to TOTAL after choosing items was done. Please help me. I appreciate it. Thank you!

[paula]

Additionally, I would like the app to display a table of values from the spreadsheet. Say I have these shown data and I want the user to be able to (1)view that then (2)have an option to choose items, (3)input quantity, and (4)autocompute that total price when selection is final. Is it possible to do these in appsheet. And how can I make that please? Thank you so much!

[Aurelien · Accepted solution]

Hi @paula 

This is the basics of reference columns.

Please do as suggested:

1) Create a table LOG with columns:

- Key (expression: UNIQUEID()), property Key ON, property Show OFF

- Timestamp (initial value: NOW(), type DateTime), property Label ON

2) Create a table ITEM with columns

- Key (expression: UNIQUEID()), property Key ON, property Show OFF

- Item Code (Type Text)

- Description (type Text), property Label ON

- Price (Type Price)

4) Create a table LOG_LINEITEM with columns:

- Key (expression: UNIQUEID()), property Key ON, property Show OFF

- LOG (Type Ref, source table: LOG), property "is as part of" : YES, property Show OFF

- ITEM (Type Ref, source table: ITEM), property "is as part of" : NO, property Show ON

- Quantity (type Decimal)

- Unit_price (Type Price, formula expression : [ITEM ].[Price])

- Total_Price (Type Price, formula expression : [ITEM ].[Price]*[Quantity])

Then in the LOG table, add a virtual column:

- TotalPrice (type Price, formula expression : [Related LOG_LINEITEMs][Total_Price ]

For reference and explanation, please have a look to this article:

Build list dereferences - AppSheet Help

References between tables - AppSheet Help

[paula]

Hi thank you so much for the effort to help. I've used the formula you gave. Just a follow up question please, upon getting the total price (which is for each items), how about summing up those total price as the TOTAL sum? Thank you so much!

[Aurelien · Accepted solution]

Happy to hear that worked for you.

About this question:

"how about summing up those total price as the TOTAL sum?"

Did you try the last part of my suggestion above ?

"Then in the LOG table, add a virtual column:

- TotalPrice (type Price, formula expression : [Related LOG_LINEITEMs][Total_Price ]"

[paula]

This works now. Thank you so much for your kind help! I owe you a lot! May God bless you!