Hidden values /fixed columns in form screen

Hey guys,

I want to pass fixed values when creating a row from glide.
Understand we can pass values from current screen as column to the form, but I would need in certain case to hardcode a value, but not a boolean like requested in Hidden Value on form?

For example, I have an app where both clients and merchants can signup directly from the app.
Both forms write to the Users sheet but i would like to automatically fill in the column “user type” with ‘merchant’ or ‘customer’.

I have other use cases :slight_smile:

Any chance this would be on the roadmap? Or if there is an existing workaround?
For now I need to use Apps script to check when a new row is created then programatically set the column I want, but it would obviously be faster to do in Glide.

Let me know
Cheers

How about adding an ARRAYFORMULA in your sheet that would automatically populate said column?

Hey @ThinhDinh thanks for your reply.
What I dont like with ArrayFormula is that it prepopulates the entire sheet for that column and burns the 500 rows quota for Free apps.

I find Apps Script is neater in this regard

It’s only burning through the rows because you aren’t addicting for and writing an empty result when it shouldn’t calculate anything for that row.
Check it the following. You need to add an IF statement to write a blank on the array formula column if a primary column is also empty. If a row looks empty in the sheet, then Glide will not count it against your row limit.

Also it’s good practice to delete empty rows in the sheet. When Glide wires to a sheet with array formulas, the rows are still considered filled and adds a new row to the button if the sheet. This is why you delete the empty rows.

If set up properly, it’s much faster and more efficient than a script without affecting the row count in Glide.

2 Likes

Hey @Jeff_Hager

wow lol
I guess I still have a lot to learn in Gsheet magic tricks hehe
Thanks, will read and apply, very cool

2 Likes

I put some good practices when using ARRAYFORMULA in that post, hope it helps.

1 Like

Was about to say and thanks @ThinhDinh for the original post.

Legend!

1 Like

Just a follow-up on your comment…the method I mentioned in the other post would work for any type of column, not just a boolean checkbox column. As long as you have a hardset value of ‘merchant’ or ‘customer’ in the detail row of where you have a form button, you can also pass that text value through the form to the sheet. It would be zero lag in the app instead of waiting for the arrayformula to apply and sync back up to the app.

Depending on your situation, you also have the option of creating a simple value column that is just a hard set value on all rows. That wouldn’t work if your merchants and customers are sharing the same sheet, but it would work if both of them were on separate sheets.

1 Like

Hey @Jeff_Hager
I did not want to add a fixed value in the glide data sheet where i call the button from, because the button is like a call to action from a completely unrelated sheet. But if that’s the only way for now, it’s ok, will do that.
All my users share same sheet for profile related logic throughout the app, so can’t opt for your second recommendation.
it’s all good, i can live with the fixed value.

Thanks again for the tip on deleting the row, that is a very useful best practice indeed

1 Like

@Jeff_Hager

Followup question:

I have 3 types of “static” value columns in a table named “Trainings”—

  1. a template column with a text Title
  2. a single value column with an image
  3. a template column that grabs the current user’s UUID

In the app, I have a tab that points to “Trainings”. I have a form button within this tab. I wanted to write these three values to three other columns within the same “Trainings” sheet, but instead they come up empty.

Is this expected behavior? I figured regardless if it was pulling from a previously submitted row or the new row, the three values would be constant and would write to the three columns I designated. I mean, this works if I were writing to a different sheet. Is it because I’m writing to the same sheet? @mark?

@Robert_Petitto I would not have expected that. I would have assumed that you could do that with glide columns. I tried it out in staging with mixed results. Tried with an If/Then column value and a template column value to the same sheet and various columns and it did not write it to the sheet as I would have thought it would, but sometimes it did.

I have a theory that it depends on the column type. I would check if you are writing numeric to numeric, boolean to boolean, text to text, etc. Or at least play with those scenarios. Sometimes it worked, but when I tried to write a boolean to a text value or a text value to a numeric value, then it didn’t write it. I’m thinking it doesn’t matter if it’s to a different sheet or to the same sheet.

Hm. Thanks for testing. I was writing lookup/template (text) to text in every scenario.

1 Like