Formulas on initial values for ref type columns using appsheet databases

[Mavil]

Hi, I have this problem.

I have a database with two tables. Table A is for data and table B is for users.
Table A has some columns for data an a column named "Changed by" which is a reference to the table B which has users data and a column named "email".

Now, in the database the reference works fine, I can select an e-mail by clicking the "Changed By" cell and it will give me the emails registered on table B.

Now, the idea is to register who has changed the data in given row in table A... so in Appsheet I have the column "Changed By" as a ref column to table A... with the FORMULA and INITIAL VALUE set to =USEREMAIL()

The idea is to fill that info with the email of the person logged in. So when the user clicks on the mail in "Changed by" it will take him to the user data such as name, email, phone number, etc.

Now the problem is that it just doesnt work. The app fills the "Changed by" field with the user email but then it just dont saves anything to the database. The curious thing is that if I set the value manually from a dropdown list it will work just fine.

I mean, If the app writes, i.e. user@domain.com to the field "Changed By" it wont create the register. But if I manually click on the dropdown list and select user@domain.com or any other for the "changed by" field it works fine and creates the register.

Any ideas on how to solve this?... been the entire day trying :S 

[TeeSee1]

In table B, what is the key column? Is it the email column or something else?

[Mavil]

Well, this is the thing about Appsheet databases. You don't define a Key Column, you only define a "label" column.

In this case my label column is the e-mail.

I think that the problem is that when Appsheet writes the user email in the field it is the literal value i.e. user@domain.com... then when it tries to save it to the reference field in the database it fails 'couse it is expecting a reference like bFCjxLQedx4NafNqWRjoe2 or something.... and when you choose the value from a dropdown list you see the label value, in this case user@domain.com... but internally Appsheet assigns and saves the reference to the database... I mean you're not saving user@domain.com but bFCjxLQedx4NafNqWRjoe2 ...

So I think now I need to know the formula to get the reference for a given useremail in the users table.... I think that will solve it.

[TeeSee1]

You can actually create your own key in an AppSheet database. ROW_ID just becomes a column with unique values internally used (not sure exactly what..)

So if you want your email to be both key and label, you can set it so.

(As has been warned, Appsheet db is not ready for 'important' production)

[Suvrutt_Gurjar]

Your requirement is some what clear . You may wan to share the structure of your two tables with details such as which is key column and other relevant columns.

But general method to capture "updated by" is to set the app formula of updated by column ( type email) to USEREMAIL().  With this approach, whenever a user edits the record , the user's email is captured.

[Mavil]

Sorry, my english is not that good ...

The thing is that AppSheet databases dont let you define key columns. You only define labels....

My table A (Documents) structure is
Document | Description | Modified by
Label is Document

My table B (Users) structure is
Mail | Name | Phone | Address
Label is Mail

Table A "modified By" is refenced to Table B "Mail"

But I thik the references thing is the problem... "Label" is the field you will see from that reference... This is AppSheet will show "Mail" field every time ... but if I change the Label to, i.e. Name, It will show the Name instead of the Mail... So I thing that Appsheet internally writes the reference, not the label... thats why it's not working... =USEREMAIL returns and tryes to write the literal user email... this is user@domain.com but the reference the database is waiting is something else like bFCjxLQedx4NafNqWRjoe2 ... and thats why when I select the email from a dropdown it works, becouse I'm not selecting an email but a refence (internally Appsheet doesnt write the email to the database but the reference number)....

Now the problem would be how to know the reference for a givven data?
i.e. 
user1@domain.com is reference a
user2@domain.com is reference b

How do I know using a formula the reference for anotheruser@domain.com ?

[Suvrutt_Gurjar · Accepted solution]

In addition to @TeeSee1 's guidance, just in case you do not wish email to be key and some other column with say UNIQUEID() function as key, then you could try  the following expression in the reference column [Modified by]'s app formula

LOOKUP(USEREMAIL(), "Users", "Mail", "Key Column name of Users table")

Also as @TeeSee1 mentioned, please do note that AppSheet DB is not yet recommended for production apps.

---

@TeeSee1 wrote:

(As has been warned, Appsheet db is not ready for 'important' production)

---

[Mavil]

That worked perfectly!!!!

Thank you!!!!

I'm just testing this new AppSheet DB better be prepared for when its ready.

Once again thank's a lot!

[Steve]

AppSheet Database is currently a preview feature and under active development. Preview features are not yet suitable for use in important apps. Until it has achieved greater stability, I recommend against using this preview feature except for testing and to help the AppSheet developers in their efforts. To follow the progress of and contribute to the development effort, please visit this dedicated Community topic:

Introducing new AppSheet database feature ...