Ok, I have tried looking but my pea brain can’t seem to find what I’m looking for here… Scenario: User is logged in to app via email; User enters form data for submission but there is a field to collect info about the user that is already stored in a “employee” sheet.
How can I auto populate that field data from the “employee” sheet into the form field to be captured on the main form repository sheet, based on the signed in user only? I’m basically trying to make it easier so the user does not have to enter that data if I already have it captured in another sheet.
I’m assuming you need more than signed in user email? The user’s email is available as a special value in the list of form components.
There’s two ways to do this:
First, you can simply just submit the user’s email with the form, then in the form response sheet, create a relation and lookup columns to link and retrieve the employee information. Any lookup columns will be kept within glide, with the exception of the user’s email, which is written to the sheet.
Option 2 makes sense if you need to additional employee data written to the google sheet. Here you can first create a template column in the sheet for the tab where the form button is placed. You will fill this template column with the email from the user profile sheet (make sure you have enabled user profiles). Next use the template column to create a single relation to the employees sheet. Then you can create lookup columns to retrieve the information you need for the form. When you are in the form, you can then use those lookup column values via the Column Value components.
Yeah…what you said @Jeff_Hager. Thank you. The data I want to automatically fill is actually a pre-populated employee ID number. I’ll try to work through your option 2 to see if I can make this work. I’ve tried playing with column templates before, but…as I said before, I’m a dummy.
Option 2 may seem confusing, but just remember that since you are pulling a value from the user profile for your template, it will fill with different values depending on who is signed in at the time. As I think about it more, you can probably shortcut and eliminate the relation and lookups. Instead just use the template to fill in the employee ID instead of email. Then you can use the ID in the form Column Values.
@Jeff_Hager…I got it to work with your info and not using the relation or lookups. What I found out, and was hanging me up, is that it template column does not like just numeric data values and only likes alpha-numeric for some reason. So…I just had to add a letter to the beginning each unique employee ID number, and it worked. Very strange!
It would not allow me to set the Template column properties. I couldn’t select “done”.
There must be an alpha in the data set included. Which will cause me an issue with the next auto populate field I was going to do (years of service). Hmmmmm.
Hi @Fred_Marziano Welcome! And Hoped I explained it right
The way you can “see other filled data from my another sheet to be selectable and writable onto new row of this sheet without user refilling it” is to:
• Make 2 sheets relate using [Relation] column in Glide’s [Data] tab
• Create another [Lookup] column, also in Glide’s [Data] tab, to show those data from sheet A onto sheet B
• Go to the [Layout] tab, and use the component to show that data on your interface accordingly.
• Now for the Add/Edit data. Allow the Add/Edit mode in the [Layout] tab on the screen, and select the components as usual. Now you will see that other columns (like my case, class id, student id, teacher id etc.) can be brought in as a prefilled data for that row.
@Fred_Marziano Very interesting. I’ll have to watch for that. I have seen in the past where you are unable to click Done on a Rollup column if the relation it is using was ever renamed from the original name it was created with. I wonder if this is a similar situation
I use numbers in templates quite often. Usually to “lock in” the rounding/precision of a math column. So it surprises me that you were unable to add the template column, but simply adding a character resolved it. Maybe it has something to do with pulling a number column from the user profile sheet. Something I’ve never had to do…yet. Maybe you discovered a bug, but at least you found a workaround.
While we’re at it, can we have an option for rounding/precision with the rollup columns as well? Would reduce a lot of columns I currently have to make for the ratings.
