How Can I Pull an Initial Value from a Child Table?

[jthurston]

Hello,

We have an app that we use to track student data. Students complete a Google Form to sign-up for our classes. This form asks for basic info like phone numbers, emails, preferred schedule, county, etc. After a student signs up, I create a record for them in the Students table, which is a parent to the Sign-Up Form table. The Students table then serves a hub, which is linked to many child tables.

User need to be able to see data from the Sign-Up Form in the Students table. To accomplish this, I have created a series of virtual columns that pull from the Sign-Up Form to display the phone numbers, emails, preferred schedule, county, etc. This works fairly well. However, the issue is that this makes changing this data much more difficult, since it can’t be done from the Students table.

I was thinking/hoping I could use the same virtual column formulas to set initial values for these columns in the Students table. That way the starting value is pulled from the related record in the child table, but can then later be updated/edited. However, this doesn’t seem to work. The initial value formula just produces a blank entry. Is there a workaround, or a way to make this happen?

I thought about using a button in the Students table. But I can’t figure out a way to make the button make a change to the related record in the child table.

Thank you!

[WillowMobileSys]

I would strongly encourage to eliminate this need altogether and have the students enter their information directly into an AppSheet app.

You can provide a link that would launch the AppSheet Form view that does exactly the same thing as the Google Form would. But then you have the data encapsulated into a table that you can act on directly - no copying or massaging of data required.

[jthurston]

Hello,

Thank you very much for the suggestion.

The Google Form, and the attached sheet, do have their own table, and we can act on it directly in AppSheet. The downside is that instead of making changes in the Students table, users must navigate to the related record in the SignUp table. I don’t think making an AppSheet form feed the table would make a difference, would it?

I could have students use an AppSheet form that feeds the Students table directly. I think we did consider this in the past. However, I’m not sure that would work for us. In the Students table, we use the student’s school email as the key. That way we can have students complete a variety of Google Forms (to gather personal info, agreements, assessments, etc.), and the form will automatically pull their school email, which allows us to then automatically link all of this in AppSheet. But when a student signs up, they won’t have their email yet, so we can’t use that as the key. And my understanding is that a child table can only link to the parent table by having a column that matches the parent key. If there’s a way we can somehow have students fill out an AppSheet form, and then later on add the key, that might work. Is this possible?

We may also expect each student to complete multiple sign-up forms as time goes on, but want to maintain a single row for each student in the Students table.

Thank you!

[Marc_Dillon]

Justin_Thurston:

The initial value formula just produces a blank entry.

What is the expression you used?
Please note that Initial Value will only evaluate when a record is first created. It is not clear if these records already exist in your scenario, but you may also be interested in the “reset_on_edit” functionality, which will cause Initial Value to evaluate other times as well.

[jthurston]

Hi Marc,

Thanks so much for answering.

Here’s an example expression: [Related SignUpSheets][Phone number]. And then just swapping out the second part for each column.

The Related SignUpSheets record does already exist when the parent record is created in the Students table.

I haven’t heard of “reset_on_edit” before, but I’ll see what I can find.

Thanks!

[jthurston]

I found the “reset_on_edit” option. This is interesting. So I could have it set to pull the value from the child sheet whenever I edit the row, right?

That could be helpful. Although if a user then tries to manually update the field, it resets, doesn’t it? So let’s say John Smith calls their teacher and says he has a new phone number. If the teacher tries to update the phone number, the “reset_on_edit” would cause the field to reset to the old number? If so, I’m not sure that will work for us. Unless, is there maybe a way to tell AppSheet to reset a single time 5 minutes after the record is created or something like that?