Populate values in drop downs based on previous user selections

...rsely affect the performance.

Any suggestions how to go about it?


Thanks in advance!!

Please check the link below. Use query entity to fetch data from backend.
forum.appian.com/.../SAIL_Recipes.html

As navajith has suggested, use the sail recipe to go over the cascading design but in order to improve performance you can think if using a view ( if data is coming from multiple tables ) and query all the data in the local variables at once rather than querying the backend every time whenever drop down value changes.

@komalc I don't think there is a need to worry about the performance. Though you might have 10 dropdowns, every time you interact with a dropdown you will be invoking one query rule and resetting the other dropdowns to null values. That is if you consider dropdown 1, its saveInto consists of modifying the second dropdown data(choiceLables, choiceValues) by invoking a query rule,resetting its 'value' and resetting the rest of the 8 (3,4,5,6,7,8,9,10) dropdowns(choiceLables, choiceValues, and their associated values). If you consider dropdown 2, its saveInto consists of modifying the third dropdown data(choiceLables, choiceValues) by invoking a query rule,resetting its 'value' and resetting the rest of the 7(4,5,6,7,8,9,10) dropdowns (choiceLables, choiceValues, and their associated values) and this goes on and on. Simply speaking at a time, you will invoke only one query as per the scenario you have described. If you take some care while coding, such as avoiding with(), effectively using saveInto so that queries will be made on-demand, getting only two columns(identifiers for choiceValues and text values for choiceLabels) using query entity etc, I think that should be fine.

I might be missing something but above are my thoughts are at the moment on the traditional approach from the performance perspective and let's see what other says about it.

Another approach you can give thoughts about is, build an entity (either a table or view) in the database which consists of all the possible combinations. When it comes to interface, provide the search criteria on the desired columns and get a minimal number of records(maybe 10 or 20 at a time) at a time and query the data afresh only when the user paginates. This also has an upper hand over traditional approach as there is a possibility of exploring many combinations without actually need to working through all the dropdowns in a cascading manner.

If I am not wrong, in your case, it may be more about flexibility rather than performance.

@komalc In the some of the comments, it was being suggested that the data should be maintained in load. If the datasets are huge, I would suggest refraining from doing so as there is a chance of performance degradation again if we keep on placing lots of data in the load(). Let's take an example as per your scenario - Invoking 10 query rules in the load() at a time versus on-demand querying(that too one query rule) per one interaction(that is in the saveInto). As per my knowledge, obviously the earlier one shows a significant impact during the form load and I don't think it's worth doing so. I opine that opting for load()/with() has to be done by analysing the situation rather than going for load() unthinkingly . But again, let's see what others opine on this.

@komalc - Was guessing if your requirement to use dropdown is fixed ?
Because as you are in 16.2 you can go for Column Browser Component instead of dropdown component. Please refer the below links.
https://forum.appian.com/suite/help/16.2/Columns_Browser_Component.html
https://forum.appian.com/suite/help/16.2/Columns_Browser_Recipes.html

@komalc - As considering the performance, we can have the all the datasets required for the dropdowns during initial loading by keeping in local variables and based on the each dropdown selection we can take the subset of data from the datasets (local variables). i.e.no need to have do call for each interaction.Here one time data fetching from database.

Alternatively we can have database call for each selection to get the particular set of data based on selected value.Here for each interaction it will take some time to fetch the data from database.

Thank you @navajithh, @abhinavg712, @sikhivahans, @sidhantb, @ramanjaneyulut for replying!!

@sikhivahans,@navajithh your suggestion of using cascading drop downs along with reseting the values in saveInto is helpful.

@sikhivahans can you please elaborate your suggestion of building an entity in the database which consists of all the possible combinations. How can i apply various combinations in the view if the data for drop down 1 and drop down 2 values is dynamic, then how can i pre assume and apply a logic in view to fetch values for drop down 3 in my view.

@ Komal, please create a view with 'union all' of all the data and one computed column to describe the entities to which the row belongs. In this way you can use the same expression with variable entity name. Hope this helps. Thanks.

@narasimhaadityac thank you for replying!!