@@FullOfSheets nice of you to show eagerness to help ... But till now didn't know where to check replies to the question that I have raised in RUclips. My question is ...... On selecting a year from a drop down, want to show the list of students (in a table) who were in the nominal roll of the school in that particular year
Fantastic video, very helpful. Thank you very much. So the dangerous one is the REF Drop down list. In my case i have Order Table and Customer table, if i make an Order its easy to pick up a Customer with Ref method cause there is a option "New" if its not the list, but if i delete one Customer thats gonna be affect the whole Order List. I think in my case best option is Enum Valid If method , what do you think? Or hide the DELETE button ? :)
The order will be affected if you delete the customer if you tick the "is a part of" checkbox of the Ref column. If you leave it blank, then your order will stay stafe. Enum-Text+Valid If works fine if you do not need dereferencing. If you do, then use Enum-Ref +Valid If
@@FullOfSheetsThank you very much finally i decided to use just REF (cause i realised , i made a mistake and ticked "is part of it" if i choose "Ref" with Source Table "Customer" somehow i have a option to add "New" Customer meanwhile im picking up the new order , its fantastic , you are very helpful , thanks again :)
Unfortunately not. It will accept newly added cities, as well as show it as part of the options mv9ng forward, but it will not be saved on the actial Cities table. Only Ref type columns would allow you to do that.
I was thinking of having the enum change its choices depending on the date and time available, example if i select the date it would only show the available time slot
GREAT..COULD YOU KINDLY EXPLAIN TO ME HOW I CAN INSERT A COMMON TEXT/NUMBER IN A DROP-DOWN MENU EXAMPLE MOBILE PHONE WITH MULTIPLE NUMBERS IN A COMPANY THERE ARE 4/5 TELEPHONE REFERENCES I INSERT THE FIXED NAME WHICH DOES NOT CHANGE IN THE DROP-DOWN MENU BUT I NEED TO BE ABLE TO INSERT THE TELEPHONE NUMBER.. THANKS AND GOOD WORK
@@FullOfSheets drop-down menu: Mario:__________ Stefano:__________ Anna:____________ Paolo:_____________ where there are hatches I want to be able to insert the number
@@FullOfSheets drop-down menu: Mario:__________ Stefano:__________ Anna:____________ Paolo:_____________ where there are hatches I want to be able to insert the number
@@FullOfSheets drop-down menu: Mario:__________ Stefano:__________ Anna:____________ Paolo:_____________ where there are hatches I want to be able to insert the number
Can you help me with a solution to populate column c, D, E automatically basis selection of column B. I have this data in a ref table. I am getting C, D, but not able to get E
Enum-Ref would only have A search bar. It wkukd not give search and add. However, Search bar will bot appear if the option values are few enough to fit into the screen.
salve. molto interessanti i tuoi video. Vorrei fare una dropdowlist in cui gli elementi scompaiano una volta scelti, mi spiego meglio: da una lista predefinita vorrei poter visualizzare gli elementi una sola volta. dopo aver eseguito la scelta per un record vorrei non vederli più. è possibile? Grazie
Yes. I think it is possible. You will need to ise the Text + Valid If or Enum-Text + Valid If. The secret will be in the Valid If expression. Let's say you have a Main table where you want to put the dropdown called City. Then you have a Cities table where there is a column called CityName. In your Main table's dropdown, the Valid If expression will be: Cities[CityName]-Main[City] Meaning you are telling appsheet to get all the CityName from Cities table minus all the City already selected in the Main table.
Great job The 4 TEXT - Valid if method only works for me if in the Valid If I write the expression: IN([_THIS], Cities[CityProvince]) Yes I use it: Cities[CityProvince] In the HOME_Form View the TEXT-VALID IF field does not appear.
I don't know if AppSheet has changed something or I'm doing something wrong.
Did you also create the first three methods before you did the Text-Valid If? If yes, that's the reason. Bec if you already setup a Valid If using the same Cities table in a prior column, Appsheet will think you are creating a Dependent Dropdown. (You can see this in my other video about Dependent Dropdowns). Setting up.a Ref to the same Cities table in a prior column will also affect this. So if you check back my video on the Text-Valid If, you can see that I removed the Ref column and changed it back to Text. I also removed the Valid-if of all the first 3 dropdowns in order to make the Text-Valid If work.
If I use ENUM or ENUMLIST, with the values written in the "Type Details", it works. BUT, if I add the "Valid If" table and column...the drop down list doesn't appear...the option isn't on the view...the field dissapears!!!
Check if you have more than 1 field using the Valid-if and getting from the same table. It would create a dynamic dependent dropdown, which would make the field invisible until you make a selection in the very first cilumn that uses Valud-If from that table.
@@FullOfSheets I found that if the table used for the dropdown is blank, then the field doesn't show. As soon as I entered data in the dropdown table, the field appeared. Thank you for responding.
drop-down menu: Mario:__________ Stefano:__________ Anna:____________ Paolo:_____________ where there are hatches I want to be able to insert the number
Appsheet follows the column structures of your underlying google sheets. So you have to add columns in the actual sheet, then regenerate your Appsheet table to reflect the changes. What did you mean by adding rows? A row is added when a new record is saved via an Appsheet form.
What do you mean by multiple columns? Method 1 is for manually adding the option values for the dropdowns. The dropdown is in itself just a single column.
@@FullOfSheets So I have a sheet in which there are 6 columns and I want that all those 6 columns as drop down in 6 different form fields Issues: 1) I cant see all the data from that particular column in the form as dropdown. 2) there are few rows which are empty as the columns have uneven number of data.
Ate all those 6 different dropdowns dependent on each other? If not, then you will need to break that sheet into separate sheets. Or create 6 slices from that table, selecting only one column at a time. Then, use the slices in your Valid if expressions. This is because AppSheet would think that you are creating a dynamic dependent dropdowm of they all are in the same table. See my video about dependent dropdowns to understand what I'm talking about.
does appsheet have event like in html ? onkeydown / onkeypress, ie: we have price, tax, total amount coloumn, when you change price by typing number in real time total amount also change, tq
The only event appsheet has would be on form save/submit. But you can simulate onChange() calculations using expression or formulas. It's just like spreadsheet formulas, but instead of cells (ie A1, A2), you use the actual field/column name. It will then auto calculate when values of the related fields change. This is WAAAYYYY easier than doing html js events.
@@FullOfSheets ok, I'm afraid when I submit/save button the column total amount value wouldn't change,, 😞, One question again, does AppSheet could make trigger when I save table a, table b also could save, table b is log for saving any action like CRUD
If the value does not change when you submit the form, it means that the formula you added is not a column formula. Are you sure you did not put it as an initial value? If a formula is entered in the column (the first box in the row of the editor view), that field should become read only in the user form. If it's still editable, then that means you did something wrong. On your second question, yes it is possible. You will have to make an Automation bot with an event monitoring all changes to the table. Then the process of the bot would be a data task using "Add another row to another table using values from this row". Many developers use this to create log records of user activities. Although you can actually already see logs using Appsheet's built-in "Monitor".
great tutorial thank you
You are welcome!
Thanks cleared my doubts about dropdowns
Happy to help
How to show a table or a list of values based on a drip down selection
What do you mean?
You are currently on a form, right?
Then you selected from a drop down?
And you want to show a table in the form?
@senthilramadhurai7616 Please elaborate your question because I want to answer it. ☺️
@@FullOfSheets nice of you to show eagerness to help ... But till now didn't know where to check replies to the question that I have raised in RUclips. My question is ...... On selecting a year from a drop down, want to show the list of students (in a table) who were in the nominal roll of the school in that particular year
You are a very good teacher. Thank you and good luck with your channel.
Thank you for the nice words. 😊
Very clear and well structured. Thank-you!
You're very welcome!
it is a cool tutorial, many thank
You are welcome. Please let us know your suggestions on what we should cover next.
You got a new subscriber!
Thank you! I appreciate it! 🙏
Galing po ninyo sir!! 🇵🇭 Salamat sa tutorial sir!
Thanks, kapatid! 🇵🇭
This video really helped me to fix my view❤
Glad it helped. I hope you subbed. 😁
Un très bon tutoriel. Merci beaucoup !
Vous êtes les bienvenus. Merci d'avoir apprécié.
Nice and clear
Thank you. Do you have any suggestions on what you want me to cover next? What do you want to know more about?
Yes ... Kindly make videos on how to make the the googlesheet (as a result of Google forms reply) as data source table for appsheet
Awesome. I've got that in the pipeline. Thanks.
Fantastic video, very helpful. Thank you very much. So the dangerous one is the REF Drop down list. In my case i have Order Table and Customer table, if i make an Order its easy to pick up a Customer with Ref method cause there is a option "New" if its not the list, but if i delete one Customer thats gonna be affect the whole Order List. I think in my case best option is Enum Valid If method , what do you think? Or hide the DELETE button ? :)
The order will be affected if you delete the customer if you tick the "is a part of" checkbox of the Ref column. If you leave it blank, then your order will stay stafe.
Enum-Text+Valid If works fine if you do not need dereferencing. If you do, then use Enum-Ref +Valid If
@@FullOfSheetsThank you very much finally i decided to use just REF (cause i realised , i made a mistake and ticked "is part of it" if i choose "Ref" with Source Table "Customer" somehow i have a option to add "New" Customer meanwhile im picking up the new order , its fantastic , you are very helpful , thanks again :)
You are very welcome. Thanks for your support!
thanks u
You're welcome!
When using the Text + Suggested values, is there a way to insert a newly added city into the City table?
Unfortunately not. It will accept newly added cities, as well as show it as part of the options mv9ng forward, but it will not be saved on the actial Cities table.
Only Ref type columns would allow you to do that.
Hello do you have a video using dynamic enum for time to avoid double booking?
Do you need a dynamic enum or jist a validation that the date has not been used in another entry?
I was thinking of having the enum change its choices depending on the date and time available, example if i select the date it would only show the available time slot
Is your timeslots Enum values being pulled from a separate table or did you supply the values manually in the enum's column settings.
GREAT..COULD YOU KINDLY EXPLAIN TO ME HOW I CAN INSERT A COMMON TEXT/NUMBER IN A DROP-DOWN MENU EXAMPLE MOBILE PHONE WITH MULTIPLE NUMBERS IN A COMPANY THERE ARE 4/5 TELEPHONE REFERENCES I INSERT THE FIXED NAME WHICH DOES NOT CHANGE IN THE DROP-DOWN MENU BUT I NEED TO BE ABLE TO INSERT THE TELEPHONE NUMBER.. THANKS AND GOOD WORK
Sorry, I didn't quite get that.
The Fixed Name and Phone Number is in the same dropdown menu?
@@FullOfSheets drop-down menu:
Mario:__________
Stefano:__________
Anna:____________
Paolo:_____________
where there are hatches I want to be able to insert the number
@@FullOfSheets drop-down menu:
Mario:__________
Stefano:__________
Anna:____________
Paolo:_____________
where there are hatches I want to be able to insert the number
@@FullOfSheets drop-down menu:
Mario:__________
Stefano:__________
Anna:____________
Paolo:_____________
where there are hatches I want to be able to insert the number
Already replied on the other comment thread.
great tutorial! How can you make the dropdown display in alphabetical order?
You can use the ORDERBY() formula in the Valid-If column setting
Can you help me with a solution to populate column c, D, E automatically basis selection of column B. I have this data in a ref table. I am getting C, D, but not able to get E
Hii Brother, in this video when you add ref to enum you get search and add button but when i do that search and add bar is not showing
Please help me
Enum-Ref would only have A search bar. It wkukd not give search and add.
However, Search bar will bot appear if the option values are few enough to fit into the screen.
Hi how i use char(10) in concatenate function i.e line break.. plz tell
No need
Simply put in a line feed.
For example
CONCATENATE([COLUMN], "
", [ANOTHER_COLUMN])
@@FullOfSheets ... THANKS A LOTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT
salve. molto interessanti i tuoi video. Vorrei fare una dropdowlist in cui gli elementi scompaiano una volta scelti, mi spiego meglio: da una lista predefinita vorrei poter visualizzare gli elementi una sola volta. dopo aver eseguito la scelta per un record vorrei non vederli più. è possibile? Grazie
Yes. I think it is possible.
You will need to ise the Text + Valid If or Enum-Text + Valid If.
The secret will be in the Valid If expression.
Let's say you have a Main table where you want to put the dropdown called City.
Then you have a Cities table where there is a column called CityName.
In your Main table's dropdown, the Valid If expression will be:
Cities[CityName]-Main[City]
Meaning you are telling appsheet to get all the CityName from Cities table minus all the City already selected in the Main table.
Great job
The 4 TEXT - Valid if method only works for me if in the Valid If I write the expression:
IN([_THIS], Cities[CityProvince])
Yes I use it:
Cities[CityProvince]
In the HOME_Form View the TEXT-VALID IF field does not appear.
I don't know if AppSheet has changed something or I'm doing something wrong.
Did you also create the first three methods before you did the Text-Valid If?
If yes, that's the reason. Bec if you already setup a Valid If using the same Cities table in a prior column, Appsheet will think you are creating a Dependent Dropdown.
(You can see this in my other video about Dependent Dropdowns).
Setting up.a Ref to the same Cities table in a prior column will also affect this.
So if you check back my video on the Text-Valid If, you can see that I removed the Ref column and changed it back to Text. I also removed the Valid-if of all the first 3 dropdowns in order to make the Text-Valid If work.
@@FullOfSheets Makes sense. I will test it and give confirmation if so.
Great. Let me know if it works. ☺️
@@FullOfSheets Indeed the problem was caused by the definition of the previous columns.
Thank you so much.
Glad it helped.
If I use ENUM or ENUMLIST, with the values written in the "Type Details", it works. BUT, if I add the "Valid If" table and column...the drop down list doesn't appear...the option isn't on the view...the field dissapears!!!
Check if you have more than 1 field using the Valid-if and getting from the same table.
It would create a dynamic dependent dropdown, which would make the field invisible until you make a selection in the very first cilumn that uses Valud-If from that table.
@@FullOfSheets I found that if the table used for the dropdown is blank, then the field doesn't show. As soon as I entered data in the dropdown table, the field appeared. Thank you for responding.
I'm glad you figured it out. 😊
drop-down menu:
Mario:__________
Stefano:__________
Anna:____________
Paolo:_____________
where there are hatches I want to be able to insert the number
You can't. Every time you want to accept entries, those fields should be separate columns in the GSheet table.
@@FullOfSheets Thank you
You're welcome.
In the last method combining two columns for suggestion dropdown is not working why so?
The last method is the text + suggested values. What did you mean by combining two columns?
Yes master how to add column and row from Appsheet App
Appsheet follows the column structures of your underlying google sheets.
So you have to add columns in the actual sheet, then regenerate your Appsheet table to reflect the changes.
What did you mean by adding rows? A row is added when a new record is saved via an Appsheet form.
Method 1 is not being applied for multiple Columns. Can you please help me out?
What do you mean by multiple columns? Method 1 is for manually adding the option values for the dropdowns.
The dropdown is in itself just a single column.
@@FullOfSheets So I have a sheet in which there are 6 columns and I want that all those 6 columns as drop down in 6 different form fields
Issues:
1) I cant see all the data from that particular column in the form as dropdown.
2) there are few rows which are empty as the columns have uneven number of data.
Ate all those 6 different dropdowns dependent on each other?
If not, then you will need to break that sheet into separate sheets. Or create 6 slices from that table, selecting only one column at a time.
Then, use the slices in your Valid if expressions.
This is because AppSheet would think that you are creating a dynamic dependent dropdowm of they all are in the same table. See my video about dependent dropdowns to understand what I'm talking about.
does appsheet have event like in html ? onkeydown / onkeypress, ie: we have price, tax, total amount coloumn, when you change price by typing number in real time total amount also change, tq
The only event appsheet has would be on form save/submit.
But you can simulate onChange() calculations using expression or formulas.
It's just like spreadsheet formulas, but instead of cells (ie A1, A2), you use the actual field/column name. It will then auto calculate when values of the related fields change.
This is WAAAYYYY easier than doing html js events.
@@FullOfSheets ok, I'm afraid when I submit/save button the column total amount value wouldn't change,, 😞,
One question again, does AppSheet could make trigger when I save table a, table b also could save, table b is log for saving any action like CRUD
If the value does not change when you submit the form, it means that the formula you added is not a column formula.
Are you sure you did not put it as an initial value? If a formula is entered in the column (the first box in the row of the editor view), that field should become read only in the user form.
If it's still editable, then that means you did something wrong.
On your second question, yes it is possible. You will have to make an Automation bot with an event monitoring all changes to the table.
Then the process of the bot would be a data task using "Add another row to another table using values from this row".
Many developers use this to create log records of user activities. Although you can actually already see logs using Appsheet's built-in "Monitor".
@@FullOfSheets many thanks brother,,, very helpful/useful answer,,, see u,,
Glad it helped. 😊