I'm running into a problem with a dropdown on one of my forms. Right now, I have a form that is for a table in the Dataverse.
For the purpose of explaining my problem I'll provide a comparable example:
Let's say I have a Dataverse table named "Highways Sections." I have 5 columns; CityTerrain, StartingState, StartingCity, and EndingState, and EndingCity. StartingState and EndingState are both a column of data type "Choice" that has the option set "Continental US States." CityTerrain is a LookUp column to the table "City Terrain" where each row is a different type of terrain that a city can have different metadata about that type of city terrain. StartingCity and EndingCity are data type "Text" and filled with the GUID of a row in one of my 48 other Dataverse tables that have all the major cities in each state. So, let's say my starting city is Las Vegas. StartingCity would be the GUID of the row "Las Vegas" in the table "Nevada." This was to get around the limit of the number of Lookup columns that the Dataverse allows in a single table. The control for StartingCity and EndingCity are dropdowns that show all the cities in one of the 48 state tables, and instead of showing the GUID in the dropdown I show the actual city name. In order to decide what Dataverse state table to look into for StartingCity and EndingCity, you would select a choice from StartingState and EndingState, respectively. Then, your choices for StartingCity and EndingCity would further be filtered by what kind of terrain the city is, whether that be desert or plains or mountains, etc.
What I want to do is make a selection in the CityTerrain column of my Dataverse table "Highway Sections" and have that determine the list of starting cities and ending cities that are available for choosing in the dropdowns. So, let's say I want to put in a row in "Highway Sections" that is from Denver to Deer Valley. I would select "Colorado" and "Utah" in the StartingState and EndingState column dropdown, respectively. That would mean that the dropdowns for StartingCity are showing only cities in Colorado and EndingCity are showing only the cities in Utah. I would then select "Mountainous" in the CityTerrain column, since both cities are in the mountains. From there, I would select "Denver" for StartingCity and "Deer Park" for EndingCity.
My issue comes from when StartingCity and EndingCity are two different terrains. Let say I want to add in a row of "Highway Sections" for a section between Denver and Las Vegas. I would select "Colorado" in the StartingState column dropdown and "Nevada" in the EndingState column dropdown. I would then select "Mountainous" for the CityTerrain column dropdown because Denver is in the mountains. I would then select "Denver" in the list of StartingCity. But, in order to select "Las Vegas" for EndingCity, I would have to change the CityTerrain column dropdown to "Desert." This clears my previous selection of "Denver" in the list of StartingCity, because the filter for StartingCity depends on the selection in the CityTerrain column, and since Denver is not in the desert it no longer appears as an option to select.
Below is sample code of the "Items" property of StartingCity:
StartingStateCardValue.Selected.Value, //This is the card value for the field StartingState
'Continental US States'.Colorado, //If the starting state chosen is Colorado
Filter([@'Colorado'], //Look in the Colorado dataverse table
'City Terrain'.'City Terrain Id' = CityTerrainCardValue.Selected.'City Terrain Id' //Terrain selected affects filter
Etc. for each state
I don't want to add another CityTerrain column dropdown because that would mean a lot more clicking for the end user in my actual app. If I could find a way to save the initial selection I make in StartingCity and keep it displayed, that would be optimal.
Please let me know if there's any confusion, I understand this is a weird problem and would be happy to further clarify.