Say Goodbye to Complex Dropdown Lists | AppSheet's Easy Dependent Dropdown Solution
ฝัง
- เผยแพร่เมื่อ 16 มิ.ย. 2024
- In AppSheet, Dynamic Dependent Dropdowns only show the users options related to a previous selection or entry.
Implementing Dynamic Enums or Lists is quite easy and straightforward to implement in AppSheet.
This video shows the exact step-by-step demonstration on how to implement this in your Appsheet applications.
Get the AppSheet Best P4actices eBook:
bit.ly/4bh3JSp
Use Coupon Code: imsofullofsheets
(Thanks for your suppport. 🙏)
Related Videos:
5 ways to Create Dropdown Lists in AppSheet
• Top 5 Ways to Make Dro...
Best Practices you ahould know by now
• Level Up Your App Deve...
==========================
Video Chapters:
00:00 What are Dynamic Dependent Dropdown boxes in AppSheet
00:42 Sample Use Case: Ticketing app with dynamic dropdowns
01:16 Setting up the Appsheet app columns based on reporting requirements
01:55 How to setup the dependency of Categories, Subcategories, and Specific Issues in the Google Sheet to prepare creating dropdown boxes in Appsheet
04:00 Setting up the first dropdown boxe in the Appsheet UI form
05:51 Setting up the next dropdown - dependent on the previous dropdown selection in the Appsheet form
07:30 Setting up another dropdown - dependent on the selection on the previous 2 dropdowns in the Appsheet form
09:04 What are the limits of dependent dropdowns
09:32 Is this really the easiest and most straightforward way of creating dependent dropdowns in AppSheet
10:19 What will be our next video?
================================
#appsheet #fullofsheets #dynamicdropdowns #dependentdeopdownlist #buildingwithappsheet #appsheetforbeginners
Thank you for explaining it efficiently and in an easy way!!!
I have some question though...
1. If I want to insert some other value other than predefined values, how can I do that??
For the main category, we can tick the "allow other value" check box. But for others how can I do that?
Suppose I have one main category as "System" and the other 2 subcategories as "Subsystem" and "Item Name".
If I want to add a new system I can use that checkbox option, but then it does not show options to add subsystem and item name.
2. Similarly if I want to use a predefined system from dropdown but want to add another subsystem, how to do that.
3. Likewise for the item name too.
Even if you tick allow other values in your [System], it will not allownusers to add a new system since the Valid-If expression will only allow those that are already existing in the table.
Therefore, the method I've shown in this video will not work.
But what you want is still possible using a not so straight-forward method.
First, instead of using Valid-If in your [System], put it in the Suggested Values. This would still show the options saved in the Sheet, but still allow your users to enter a new system.
Next, remove the Valid-If from your [Subsystem] and then use a SELECT() in the Suggested Values.
SELECT( Dropdowns[Subsystem], [System]=[_THISROW].[System])
Lastly, do the same for [Item Name] but modify the select statement like this:
SELECT( Dropdowns[Item Name], [Subsystem]=[_THISROW].[Subsystem])
Take note that if you ticked auto-complete other values, then other users will see the new items added as options next time. However, it will not save those options in the actual google sheet.
If that is what you want, then use Ref type for your [System] column.
Hope this helps.
@@FullOfSheets Thank you so much!!!! IT WORKED... I changed the [System] column type to text. As it was asking for values in ENUM. Is there any way so that the new item added it also reflects it in actual google sheet after saving?
@vedsahu5306 yes. As mentioned in my last comment, use Ref in your [System], remove the valid-if and Suggested Values.
This forces your user to create new set of of combination of [System], [Subsystem], and [Item name].
@@FullOfSheets If I do that then the dependent dropdowns will not work.
It should. Or kaybe don't remove ylthe valid if.
If all else fails, then you would have to change the Valid if of the succeeding dropdowns to SELECT() based on the newly added and selected [system].
This dropdown trick is magical !
It is, right? 😄
Loving the simplicity again, thank you
You are so welcome!
Excellent presentation. Thank you. You are indeed full of sheets!
😂😂😂
Excellent!! Thank you a lot my Full of Sheet friend! I don't think I could find a simpler way to do dependencies, thank you again!!! Big hug from Argentina
You're welcome. Thank you for your comment. I appreciate you exerting the effort to send your appreciation.
This video really helped me. Thank you
Glad it helped. Thanks for letting me know.
Thank you brother for the wonderful video!!
My pleasure!
Thank you.
Welcome!
Thank for this video brother
You are so welcome. I hope you subscribed. 😁
You're the best appsheets guru on the web. Thank you so much for posting such great, easy to follow videos! Please post more! I'm really looking forward to your next instalment.
Thank you so much. I appreciate that.
But I'm not so sure about being "the best appsheet guru".
I am just so passionate about the infinite possibilities that Appsheet can do, if used correctly, so I want to share what I have learned from actual experience in developing with AppSheet.
But you are for sure the best in this app for sheets as i see, please continue the good work!@@FullOfSheets
Thank you, I will. I am working on 3 new videos, which I will upload at the same day.
excellent! looking forward to them!@@FullOfSheets
Thank you¡
It is a cool video, thank Brooo...👍 keep it up
Thanks, will do!
Fantastic
Thank you! Cheers!
Nice ❤ salamat
Walang anuman, kabayan!
please continue to send more videos of appsheet, ur saving me to develop an app, and one of my dificults was with dropdowns and refs. Thank u and God Bless man!
One question: how to make a field after being filled in (e.g. customer identifier) the next field is automatically filled in with the customer's name (let's say you already have their registration and just need to reference it with the customer's ID or something of the genre). I ask this because I haven't found a way to use expressions to be able to filter and get the specific data I want from another column, even though I have the key of that table.
You will need to use "Dereferencing".
I demonstrated this a bit in my other video on 5 ways to create dropdowns.
I will come up with a video for this. Stay tuned.
Hi @danielpaes6457,
The Dereferencing video is in the last phases of editing already. It should be out in the mext 2 days.
Thanks for your patience.
Hi JP. Thank you for creating these videos. These are the most helpful videos I have seen on TH-cam. Keep it up!
I just saw your Dereferencing video and I am not sure whether to use that or a dropdown (or both?) to solve my use case. Assume we have the same table as you have here, but with two additional columns: Group and Group Location. After the user specifies the Category, Subcategory, and Specific Issue, we want the app to automatically display the name of the Group (eg, Tier 1, Tier 2, etc.) and the location of the Group (eg, Ohio, Texas) that is assigned to each Specific Issue.
How would you go about doing this? Would really appreciate your advice!
Thank you.
Next, you cannot use a Ref nor an Enum-Ref, because as discussed in my "Top 5 Ways to Create dropdowns in Appsheet" video, a Ref and Enum-Ref column type will always expect and save the key column. So by using the Valid-If formulas to return the Category, SubCategory, or Specific Issue like in this video, Appsheet would throw you those Nasty yellow exclamation marks in your dropdown.
In other words, you can not use dereferencing .
Ok, so first things first, the Group and Location information should be available already in the same Dropdowns table where the Categories, Subcategories, and Specific Issue is located. Meaning, your dropdown tables will now have the following columns:
Category
Subcategory
Specific Issue
Group
Location
Next, use the same Valid if formulas demonstrated in the vudeo to create the dependencies.
Then, on your Group and location columns, use LOOKUP() in the App Formula to get the Group and Location Values from the Dropdowns table.
For the Group:
LOOKUP([SPECIFIC ISSUE], "DROPDOWNS", "SPECIFIC ISSUE", "GROUP")
For the Location:
LOOKUP([SPECIFIC ISSUE], "DROPDOWNS", "SPECIFIC ISSUE", "LOCATION")
@@FullOfSheets Hi JP! Thank you for the detailed advice. I've followed your instructions. They seem to mostly work. The LOOKUP however always provides the value in the first row of the dropdown table. Would I need to modify the LOOKUP equation or use ANY(Select)? In case you or others may find it helpful, here's a link to a Google Drive with the tables I recreated per your video.
drive.google.com/drive/folders/1XWvVMnkpoNwIQhxUaWdwjcZXjA7r-G5z?usp=sharing
Thank you!
It shouldn't be if the [Specific Issue] unique for each row.
You can try using Index(lookup(), 1) or maybe changing it to select. But then again, both assumes that the Specific Issue is unique across all rows in the dropdowns table.
Thanks for sharing your tables. I'm sure it would help some sheety pipz in the community.
But I think, try first using [_thisrow] on the match value.
Lookup([_thisrow].[SPECIFIC ISSUE], "DROPDOWNS", "SPECIFIC ISSUE", "GROUP").
I suspect this should do it.
@@FullOfSheets Yes, that fixed it! Thank you for the advice, JP. Really appreciate it! 🙏🙏
Awesome! Good luck with your project, and Happy Building! 😊
thank you, Id like to know how to reuse previously selected data from a drop down as the new default for a new record until I change it, how do I takle this?
Well, the default selection isnset theough the Initial Value of the column settings. But then, if you want to change it, then you will have to access the app editor and change the initial value.
Is what you want to make the last selected value in the last record as the default moving forward, and then if you change it in a new record, then that last record's selection will be the new default?
Thank you we are waiting new videos about appsheets. Can you make videos in series for newbie in appsheet
Thank you for your comment. I was waiting for so.eone to suggest what videos I will make because there's a lot to cover and I can't decide which one.
So you want an Appsheet for beginners video? Have you already seen my Appsheet Best Practices one?
@@FullOfSheets i think no. Your channel has only 3-4 videos. Do you have another channel for beginners. i m sorry my english. A simple project can be handle. ie car filo track in a company. Track for due dates for taxes , insurances , driver-car coupling ( which and when a driver ride which car). Periodically checking time. Repair result. Defects photos etc. Thank you.
Great suggestions. I will make a series of creating app from scratch and will consider your suggestions. Please wait for it. I'm still finishing my Holiday vacation.
1:45 need advices, what if I want to use an enumlist, but still retain the results of the dynamic dependent dropdown
In my sample, there are three dropdowns.
Where would you want to use an EnumList? The 1st, 2nd, or 3rd dropdown?
pls make series of video on :
1. Task Delegation App
2. Sales CRM App
3. Expense Tracker App
4. Hiring Tracker App
5. Salesman Order Tracking System App
Great suggestions!
Thank you. I've added them to my list.
Please be patient as production takes time. I want to make sure my videos are all concise, straight to the point, and easy to follow.
So editing takes a little bit of time. 😊
Could you make a tutorial adding to this dependencies the way to choose between dates, I mean, I have a series of room inspections with dependencies, building, floor, rooms but before choosing this I have to select inspections between a range of dates. Thank you in advance!
What exactly is the entry that your form is making?
If the inspections are already made, you could just filter the list by clicking Search, then clicking that dropdown button on the seatch bar. This should show you an advanced filter where you can select the inspection date, put a start and end date range, and then it filters the list for you.
Sorry, I can't quite picture out what you're trying to do.
Heyyy;
great video; i actually learnt something new,
though exploring this idea i came along an issue, what are the process or actions to update the rows that are already filled in with the previous data if you were to edit the already saved data from previous selections from the dropdowns??
This is what I understood from your question.
You already filled out and saved the form. Now, you want to edit the record, and you want to change the vakue of the 1st dropdown.
What you are asking is how to update the values of the second dropdown and so on and so forth?
What i want is to edit the table, the values filled in from the previous selections of the various dropdowns
Sorry. I don't follow. 🙏
You say edit previous dropdowns, so it means you are currently on a form.
So what do you mean by editing the table?
Sorry i luck better words to explain…. I found another way out… thanks, could you do a video on how to add the enumlist values each value on its own row , with unknow/ unlimited number of enumlist values😊
What do you mean? Like when selecting multiple values from an EnumList, instead of saving the selected values as a comma separated list in a single cell in the same row of the Gsheet, it will actually create multiple rows for each selection?
But what will be the data for the other columns that you have not filled out yet aside from the EnumList column?
You are Great ❤
What if i need make Dependent dropdown list .. But still search and add new values like normal enum list ?
Because i noticed when you use enum with specific issue Column still no search available .. I want to make search with list to make this easy with large choices
Thanks for your Effort ❤
Hmmm. Dependent dropdowns must know already what options are available when a previous dropdown option is selected. If you will allow to add new entries, then the succeeding dropdowns will not appear. So this will only work on the very first dropdown.
Otherwise, you will have to use IF() or IFS() conditional statements in your Valid If expressions.
In my sample, there is no search bec my choices are just a few. But if the selection of my Specific Issue dropdown is a long list, then Search button automatically appears.
@@FullOfSheets
Thanks for your reply 😍
Please Make video explain another way by if and ifs
And i will try long list and see if search available and tell you
Thanks sooo much ❤
Ok, I will come up with this video soon.
question in category there is a text box popping up "add or search" how do I remove it?
Add or Search appears if you use a Ref Column. Because Ref columns allow you to add a new Value to the Referenced Table.
If you don't wany this, then Use an Enum-Ref or Enum-Text column with a Valid If expression.
However, Search will still appear of tje options in the list is more than what the screen can fit.
wil this work with multi select lists?
Yes it will if the multi select list is the last dropdowm in the series.
Also see my answer to Kareem below.
Hi do you have video of how to make a new row in a table with a condition that we have to fill a data in other table. Thanks
Did you mean add new rows on another table, based on values from the vurrent table?
@@FullOfSheetswe are allowed to add new row on another table if only we update the certain value of a column on current table. Otherwise, we can't add new data to that related table
@@konsultannumerology838 and is the other table a child table of the current one?
How are you adding a record to it, assuming the condition is met?
@@FullOfSheets i'm not sure about child table, but 2nd table related to the 1st one. it's like remove the add action button on 2nd table, until the condition is met (which is updated value in a certain column on table 1)
@konsultannumerology838 yes. It's called a child table. This happens when the child table has a Ref column poit ing to the parent table, and the parent table automatically creates a [Related xyz] virtual column.
What happens is that in the Detail View of the Parent table, you will see an inline view table with the "View" and "Add" buttons on the bottom right.
Is this what you're referring to?
We need to create a regular mailing frequency via App CrM bot .
Can you create a video for this
Did you mean, you will be sending out regular email messages to your list using the app? If so, you understand that the sender will always be noreply@appsheet.com, right?
No we want the sender mail id will be formula based and reply to same mail id@@FullOfSheets
I didn't get this.
You said the sender mail id.
Then you said reply to the same mail id.
My first question was, do you want to send emails to multiple recipients using your app?