Need help with reference expression

AppSheet AppSheet Q&A
1

I am building one Appsheet app that all of our automotive dealership accounts can use instead of individual apps for each dealership. When new rows are added for vehicles, I would like an App formula to set the value of [DEALERSHIP NAME] based on the user’s email.

Also, when new client accounts are acquired, I hope to simply add the new dealership name and user email to the corresponding Google sheet instead of having to make multiple expression changes in the app editor. I assume this would be best accomplished using references or dereferences. I have read, re-read and re-re-read documentation on references and dereferences, but I still can’t fully understand them and I couldn’t find examples similar to what I’m trying to accomplish. If anyone can please help guide me on how to set this up, I’d very much appreciate it, as I’ve been stuck on this for several days.

Currently there is a table named “DEALER CAR LOG”, which is a master table for all vehicles, and a second table named “DEALER INFO”. Both tables include columns [DEALERSHIP NAME] and [USER EMAIL].

2

Here’s a basic approach.

  • In DEALER INFO, make USER EMAIL the key column.
  • In DEALER CAR LOG, make USER EMAIL a Ref type that references the DEALER INFO table.
  • Anywhere you need DEALERSHIP NAME in DEALER CAR LOG, use [USER EMAIL].[DEALERSHIP NAME] in the expression.
  • Anytime you have a new dealership, add a row to DEALER INFO.
2 Likes
3

Your suggestion appears to work exactly as I need it to. Thank you for your help!!

2 Likes
4

I’m not sure why, but I am not being shown the option to accept your response as a solution.

5

try?

Untitled.jpg
Untitled.jpg819×460 29.6 KB

1 Like
6

To set the value of [DEALERSHIP NAME] based on the user’s email when new rows are added for vehicles in the “DEALER CAR LOG” table, you can use the following App formula:

IFS(
USEREMAIL() = “user1@example.com”, “Dealership A”,
USEREMAIL() = “user2@example.com”, “Dealership B”,
USEREMAIL() = “user3@example.com”, “Dealership C”,
…
)

Replace the email addresses and dealership names with the appropriate values for your use case. This formula uses the IFS function to check the user’s email address against a list of predefined email addresses and corresponding dealership names, and sets the value of [DEALERSHIP NAME] accordingly.

To simplify adding new dealership names and user emails to the “DEALER INFO” table instead of making multiple expression changes in the app editor, you can use the REF_ROWS function to reference the “DEALER INFO” table from the “DEALER CAR LOG” table. Here’s how to set it up:

  1. In the “DEALER INFO” table, create a column named [DEALER ID], which should contain a unique identifier for each dealership.

  2. In the “DEALER INFO” table, create a virtual column named [DEALER REF], with the following App formula:

[DEALER ID] & “-” & [DEALERSHIP NAME]

This formula concatenates the values of [DEALER ID] and [DEALERSHIP NAME] to create a unique reference for each dealership.

  1. In the “DEALER CAR LOG” table, create a column named [DEALER REF], with the following App formula:

[DEALER ID] & “-” & LOOKUP(USEREMAIL(), “DEALER INFO”, “USER EMAIL”, “DEALER REF”)

This formula uses the LOOKUP function to find the [DEALER REF] value in the “DEALER INFO” table that corresponds to the user’s email address, and concatenates it with the [DEALER ID] value to create a unique reference for the dealership.

  1. In the “DEALER CAR LOG” table, create a virtual column named [DEALERSHIP NAME], with the following App formula:

INDEX(REF_ROWS(“DEALER INFO”, “DEALER REF”), 1, 2)

This formula uses the REF_ROWS function to create a list of all rows in the “DEALER INFO” table that reference the current row in the “DEALER CAR LOG” table, and the INDEX function to retrieve the value of [DEALERSHIP NAME] from the first row in the list.

With this setup, you can simply add new dealership names and user emails to the “DEALER INFO” table, and they will be automatically referenced in the “DEALER CAR LOG” table without needing to make multiple expression changes in the app editor.