Smartsheet demo to pre-populate forms with reference data
HTML-код
- Опубликовано: 11 сен 2024
- Pre-populating a form with data from a sheet has many uses, for example it enables you to include a reference code in the form that can then match data from another sheet with the input from the form. In this video I show you how to do this, some watch-outs and how you can use this method to bring in multiple fields.
UPDATE: Please note I incorrectly state in the video you can't use strings and or use a custom field name, but you can as per the useful comment below! For strings you need to use have a formula that substitutes the spaces with '%20' as follows: "Form hyperlink" + "?Column%20Name=" + SUBSTITUTE([Column Name]@row, " ", "%20")
I'll add some correction notes to the video in due course
Any questions, or for help with your PMO Smartsheet solution, including using any of Prodactive's extensive range of solutions, please get in touch. We're an award-winning Smartsheet partner based in the UK, and helping clients all over the world, from North America to Australia, achieve great things with Smartsheet.
www.prodactive...
Actually, you can use the edited label as long as that's what you place in the formula. And the label can have spaces as I'll show you here. In your example, the formula could include this substring: ...?Job%20reference...
%20 can be used in both the field name and the inserted content anywhere after the ? where you want a space character.
I was surprised to discover this “by accident” just a couple days ago-especially the part where form completion gives precedence to edited labels over actual column names.
If you test this, and I'm right (maybe I'm wrong??) then perhaps you could add text boxes to the video clarifying this, rather than having to redo the whole video.
Amazing - thanks for this! I’ll definitely try and update accordingly. The power of community 👍
I've updated the description for now - thanks again for the pointer!
Thank you! This was extremely helpful
You're welcome! #PeakHuman right here
Hi! Great video, I wasn't looking for this, but this is FANTASTIC. THANK YOU!!! Question - should this formula work with all cell types? For some reason, the date won't populate into my form (date formatted as regional default), or a column formatted as currency?
Hi Josune - it should do. I have this formula ="app.smartsheet.com/b/form/xxxxxxxxxxxxxxxxxxxx?JobRef=" + [Ref#]@row + "&Date=" + Date@row + "&Activity=" + Activity@row + "&Cost=" + Cost@row. It gives me this result app.smartsheet.com/b/form/xxxxxxxxxxxxxx?JobRef=T0015&Date=14/09/22&Activity=Double_ref&Cost=100 and the form is populated correctly
Hi there!
Thanks for the helpful video! Is there a limit in number of charecters in a link? I am trying to autofill a form where I have 8 fields, and it breaks the link but not because of space in it.
I haven't gone as far as 8 fields. Did you get it to work? When things like that happen for me, I try it on other items to see if the same error happens or if there's something I'm doing that is causing the error...
Thanks so much for this video. Do you know if this can be done to update the row information?
Hi there - you won’t be able to update a row directly as every time a form is submitted it creates a new row. You would need to use an update request to update the row - or have another sheet connected that pulls reference data from the form and pulls in the most recent submission
Hey, great video... How did you get the form to "stay open"? That would be great for time tracking for some employees.
Hi Erin - you need to submit the form each time for it to register the time, but you can get it to reload so the form can be submitted again. We've done a video that covers recording the time a job takes and we've developed more advanced solutions of that for clients where they had specific requirements. Trust that helps 😀
Hi,
I have one issue, Actually I wanted to set up a conditional formatting which greyed out all the weekends I have a columns which has 3 quarters dates
It seems like that I have to set up manually. If you would not understand the ques, is there any way I can connect with you personally.
Hi Deepanshu - I've just posted this video that might answer your question. ruclips.net/video/rKntYwGYwqU/видео.html
It was a great tip, Intially I struggled but when I followed the %20 rule, I got the outcome.
But, I wanted to know that why only %20, we are using here, because, when I changed to some other no. , the results dissappeared.
Can you tell mw why?
It’s the HRML language for space. URLs cannot contain spaces and need to be a continuous string. Different numbers mean different things - have a look at this page for example www.eso.org/~ndelmott/url_encode.html
Hola! Estoy en modo autodidacta, si pueden subtitular los vídeos en español sería de mucha ayuda!!! Saludos.
Bien! Para prioritizar, cuáles son los videos más importantes para subtitular (o hacer en español)?