How to only show a selected few from a long list in a dependent dropdown?

[djmalaka]

***strong text***Hi,
If the ValidIf expression Surgery List[Surgery Title] shows all of the values in [Surgery Title] column of “Surgery List” table,
and, if the Valid if expressiong Surgery List[Surgery Title]-List("Colonoscopy","Sigmoidoscopy","UGIE","ERCP","Rigid Sigmoidoscopy","Proctoscopy","EUS") removes those 7 values from the long list of values in [Surgery Title],
if I wanted to only show those 7 values in a different dropdown menu, fetching from the long list of values in the [Surgery Title] column in the “Surgery List” table, what should the expression be for that? (because since the [Surgery Title] column has over 150 unique values in it, and I cannot write an expression to remove all those 150 values just to show those 7 values using the same -LIST() expression).

Thanks!

[LeventK]

@djmalaka
If you want to show only first X number of list items in a dropdown, you can use:

TOP(SELECT(TableName[ColumnName],{Y/N Condition})

However, if your intention is different, can you please elaborate bit more?

[Aleksi]

Why don’t you then use that same formula like… LIST(“Colonoscopy”,“Sigmoidoscopy”,“UGIE”,“ERCP”,“Rigid Sigmoidoscopy”,“Proctoscopy”,“EUS”)

[djmalaka]

@LeventK @Aleksi
Thank you for your reply.
I have a major issue now. The “Surgery List” table has 6 columns, [Surgery Title], [Specify - 1], [Specify - 2]… and so on. They work for a dependent dropdown menu, so each surgery title has specifications spread across the other 5 columns that will be shown in the dependent dropdown menus.

Now I was using this “Surgery List” table’s 6 columns for only a single form based on a table called “Surgery Logbook”. But then I wanted to create another table called “Endoscopy Logbook” in the same app and I wanted to use the same “Surgery List” table’s 6 columns to specify endoscopic procedures in it, but as you can realise, I wanted the “Endoscopy Logbook” table’s [Endoscopy Title] to only fetch endoscopy related data and leave the surgery titles behind.

At the same time, I wanted the “Surgery Logbook” table’s [Surgery Title] column to fetch data from the “Surgery List” table’s [Surgery Title] leaving behind the endoscopy titles in it, and to that, I wrote Surgery List[Surgery Title]-List("Colonoscopy","Sigmoidoscopy","UGIE","ERCP","Rigid Sigmoidoscopy","Proctoscopy","EUS"), but the issue is, eventhough those values were taken out of the list, the dependent dropdown stopped working for the [Specify - 1], [Specify - 2]… columns! Guess, the moment you write an expression like that, the dependent drop downs stop working.

So I guess I have to create a different table for the “Endoscopy List” similar to a “Surgery List” and then get the “Endoscopy Logbook” to fetch data from that to work the dependent drop downs. My issue is, by doing that I increase the number of tables in the app and it feels that the app sync times are increased when the number of tables increases!

[LeventK]

@djmalaka
My wife is a cardio-vascular surgeon where I have made a couple of apps for her and I do understand your issues. What I may advise is, rather than creating a separate table, why don’t you create a slice for Endoscopy Logbook out of your Surgery List which I believe is also a subset of this table already just like the Surgery Logbook?

[djmalaka]

@LeventK Thank you for your suggestion.
But wouldn’t I still have the same issue? If I want to keep surgery titles and endoscopy titles in the same dropdown menu system, and then remove the endoscopy titles from it and show only the surgery titles for the Surgery Logbook using a -LIST() expression, or if I want to show only the endoscopy titles and remove surgery titles in the Endoscopy Logbook by means of your expression TOP(SELECT(TableName[ColumnName],{Y/N Condition}), still the dropdown menu stops working isn’t it? Because those expressions conflict with the dropdown menu concept I guess. Am I correct?

[LeventK]

@djmalaka
I may advise using Suggested_Values property rather than Valid_if, so if you somehow need to verify the entry, it will be more effective. For the dropdown suggestion, I need to know how your columns are set in the Surgery List table, so that I may propose an effective expression. Is it possible that you can share that gSheet with levent@able3ventures.com so that I may have a look at it? Thnx.

[djmalaka]

Yes, I’ll share it with you.

[LeventK]

djmalaka:

Yes, I’ll share it with you.

Helle @djmalaka
I have received your sheet, thank you. First of all, I can say that your Surgery List table does not contain a Key Column. Provided you have set [Surgery Types] column as a Key column, that’s also incorrect as this column contains blank values. So I believe, [_RowNumber] shall be your key column. Whatever the case, [Surgery Types] cannot or should not contain blank values. Same is valid for the Endoscopy List table. Also the additional detail columns contains blank values. Possibly this is the reason your dependent dropdown valid_if expressions are not working correctly and as expected.

You need to fix these, before going any further for setting the appropriate expressions for the dropdowns.