Say Goodbye to Complex Dropdown Lists | AppSheet's Easy Dependent Dropdown Solution

[@vedsahu5306]

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.

[@FullOfSheets]

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.

[@vedsahu5306]

@@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?

[@FullOfSheets]

@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].

[@vedsahu5306]

@@FullOfSheets If I do that then the dependent dropdowns will not work.

[@FullOfSheets]

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].

[@GinaFer2024]

Very easy to understand. Thanks bro

[@JamesJosephFinn]

Excellent presentation. Thank you. You are indeed full of sheets!

[@FullOfSheets]

😂😂😂

[@nelsongg347]

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

[@FullOfSheets]

You're welcome. Thank you for your comment. I appreciate you exerting the effort to send your appreciation.

[@anybody007_who]

This dropdown trick is magical !

[@FullOfSheets]

It is, right? 😄

[@denzilsnyman3258]

Loving the simplicity again, thank you

[@FullOfSheets]

You are so welcome!

[@DatNguyen-o9v5l]

Thank you.

[@FullOfSheets]

Welcome!

[@dannykorman8933]

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.

[@FullOfSheets]

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.

[@marijarakicch]

But you are for sure the best in this app for sheets as i see, please continue the good work!@@FullOfSheets

[@FullOfSheets]

Thank you, I will. I am working on 3 new videos, which I will upload at the same day.

[@dannykorman8933]

excellent! looking forward to them!@@FullOfSheets

[@FullOfSheets]

Thank you¡

[@cakarbilal]

Thank you

[@sgba7923]

VERY GOOD EXPLANATION

[@FullOfSheets]

Thanks so much.

[@CODE-ut6iu]

Thank you brother for the wonderful video!!

[@FullOfSheets]

My pleasure!

[@sajinj2457]

This video really helped me. Thank you

[@FullOfSheets]

Glad it helped. Thanks for letting me know.

[@Adamakoubiabalde]

Thank for this video brother

[@FullOfSheets]

You are so welcome. I hope you subscribed. 😁

[@hermawanyulianto2645]

It is a cool video, thank Brooo...👍 keep it up

[@FullOfSheets]

Thanks, will do!

[@TheuParuguh]

1:45 need advices, what if I want to use an enumlist, but still retain the results of the dynamic dependent dropdown

[@FullOfSheets]

In my sample, there are three dropdowns. Where would you want to use an EnumList? The 1st, 2nd, or 3rd dropdown?

[@MohamedTawfik84]

many thanks for your explanation, it helped me to much , i need you to explain if i need to make login form for some users and based on the username logged in , a dropdown list having all user names is filtered to show only the logged in user only

[@FullOfSheets]

When you say username - did you mean the email address, or is it the actual name of the user?

[@ianku5208]

Nice ❤ salamat

[@FullOfSheets]

Walang anuman, kabayan!

[@koolkiwikat]

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?

[@FullOfSheets]

Well, the default selection is set through 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?

[@danielpaes6457]

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.

[@FullOfSheets]

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.

[@FullOfSheets]

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.

[@konsultannumerology838]

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

[@FullOfSheets]

Did you mean add new rows on another table, based on values from the vurrent table?

[@konsultannumerology838]

​@@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

[@FullOfSheets]

@@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?

[@konsultannumerology838]

@@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)

[@FullOfSheets]

@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?

[@khanyasser]

Fantastic

[@FullOfSheets]

Thank you! Cheers!

[@SirLoin16oz]

Hi JP. Thank you for creating these videos. These are the most helpful videos I have seen on KZbin. 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.

[@FullOfSheets]

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")

[@SirLoin16oz]

@@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!

[@FullOfSheets]

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.

[@SirLoin16oz]

@@FullOfSheets Yes, that fixed it! Thank you for the advice, JP. Really appreciate it! 🙏🙏

[@FullOfSheets]

Awesome! Good luck with your project, and Happy Building! 😊

[@vishalbagaria1468]

We need to create a regular mailing frequency via App CrM bot . Can you create a video for this

[@FullOfSheets]

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 [e-mail], right?

[@VPPLIT]

No we want the sender mail id will be formula based and reply to same mail id@@FullOfSheets

[@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?

[@JadiMudahID]

Great, how make this from one table only, I mean, we have data ini table, If use just enum only, they preview all data without filtering

[@FullOfSheets]

Sorry, i didn't quite get what you mean.

[@JadiMudahID]

@@FullOfSheets sorry, i mean If we use valid if, how to Make another choise? We want to automatic if select A sub is Aa Ab but how to create new option in sub, Ac...

[@FullOfSheets]

I think your last response was clipped or unfinished.

Thank you we are waiting new videos about appsheets. Can you make videos in series for newbie in appsheet

[@FullOfSheets]

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.

[@FullOfSheets]

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.

[@nelsongg347]

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!

[@FullOfSheets]

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.

[@Zaizen182]

What if I need to select a lot of data? Thanks

[@FullOfSheets]

What do you mean by this? Like an EnumList?

[@Zaizen182]

@@FullOfSheets I think it's like dropdown 1: I select the book item then dropdown 2: I select mathematics, physics, chemistry. dropdown 3: is a branch of dropdown 2 (branch of mathematics, physics, chemistry. If I select physics and chemistry both branches of the book will appear in dropdown 3. Can I make it like that?

[@FullOfSheets]

I see. So the second dropdown will be an EnumList. The process demonstrated in this video will not be bale to do that. But what you want is still doable by using a Filter() or Select() in the Validation of your third dropdown. I'll see if I can make a video for this.

[@Zaizen182]

@@FullOfSheets Does this happen continuously? or just getting there? thank you for helping me

[@FullOfSheets]

Sorry, I dont think I understand what you mean. Would you care to explain.

[@kareemelkafi7845]

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 ❤

[@FullOfSheets]

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.

[@FullOfSheets]

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.

[@kareemelkafi7845]

@@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 ❤

[@FullOfSheets]

Ok, I will come up with this video soon.

[@rfayz733]

wil this work with multi select lists?

[@FullOfSheets]

Yes it will if the multi select list is the last dropdowm in the series.

[@FullOfSheets]

Also see my answer to Kareem below.

[@administradorprocorp7970]

But.. how to achieve the same results , without using the dropdown tab ? in my REQUEST tab (it is collecting CV), I already have categories and subcategories filled columns

[@FullOfSheets]

If you're not goung to use a dropdown, what are you going to use then? How will the users be able to select the option based on the previous field?

[@nabinprakashrai]

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

[@FullOfSheets]

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. 😊

[@annualloadbook]

I use this process to handle the expense Type list and Expense list. I have made the app multi lingual. and i do not know how to make a multi lingual selection from the list that ref from another table. My Expenses Table has expense type column Enum Ref the Enum List Table column expense type There are 5 selections. The expense columns text ref Enum List table expense column. I need to be able to make the language change based on the usersettings(Language),Translations,Language “Expense”

[@FullOfSheets]

Hmmm, if the data that needs to change are those coming from the actual tables, then you will need to jave different views, then show/hide the views using conditions based on usersettings. This is a lot.of work. 😁

[@annualloadbook]

Thanks I made it work by making another table for each for the language switch and left the valid_if statement point the the enum list

[@FullOfSheets]

Awesome! Woot! Woot! 🎉🎉