I have an AppSheet application with a table containing a Patient_ID column (key) and a Full_Name column (label). This ID is unique, and it works fine for everything else. The new problem I’m having is that I’m creating calendar events and populating the Attendees field. This field requires an email address, and my patient table has them in a column called email. The problem is that to populate the emails in the Attendees field, I changed the key from Patient_ID to email. This works for Attendees when I use the following formula for the suggested values field in my Attendees column, but it’s causing problems with other parts of the application because almost the entire application uses User_ID as the key. The formula is “FILTER(“People”,ISNOTBLANK([Full_Name]))”. My atendees column in the Google Calendar table is an EnumList with a reference type to the table where the patients are stored, which is called personas. How can I make it so that when I use ID_usuario as the key, the atendees dropdown displays the patients’ full names but stores their email addresses instead of their IDs? Otherwise, when I save the appointment to the calendar, I get an error because it can’t update the calendar or something similar, since the atendees field only accepts email addresses.
The following workaround is not very elegant but you may want to consider.
- You could have another table called say “Attendees_GC” with columns as [Email] as key, [Attendee_Name] as label and [People_Ref] as reference column that references the main “People” table. Generally “email’ is not a great candidate for a key because someone’s email can change at times. However in this specific case, you may still have the “email” as key for want of better alternative.
- Whenever a new person details are added to the “People” table, also add a row to the “Attendees_GC” table through action of type “Add a row to another table using values from this row” . This can be a form save event action in the main “People” table.
- Initially for existing list of records in the “People” table, you could populate the "Attendees_GC” table with [Email], [Attendee_Name] and [People_Ref] with values from the “People” table.
Now in the Google calendar table you could have suggested values from the “Attendess_GC” table with an expression something like Attendess_GC[Email]
This will display the People names in the dropdown of the [Attendees] column of the Google Calendar form but will store the emails in the backend.
This may need more testing to ensure all your other app requirements are met.
Thanks for the suggestion, right now is working. Following I’ll do the action to erase a record for that table when the person is erased from the Personas table.
The formula fot the Email field that I used is
ANY(SELECT(Personas[Email],[ID] = [_THISROW].[ID]))
which will populate the Email field of the “Atendees_GC“ table in the respective Email field.
Thanks a lot
You are welcome.
Yes, you are correct. “Atendees_GC“ table will have a 1-1 relationship with the Personas table. So any deletion in the Personas table should delete the corresponding record in the “Atendees_GC“ table. You could do so by enabling “ispartof” option. In that case, you will not need a manual action to be created.
Thanks again, sincerely I didn’t remember the field “Is part of?”, and I did several actions to do that 
, but right away I will do it.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.