Best architecture - To populate dropdown values

I would suggest to be more specific about the nature of the information Definitely more details will improve our answers. How often that information changes and which kind of information it is? which is the source of that information.

Meanwhile here are some generic answers for your generic questions.

1) It all depends on your architecture, use case and who is the master data, and if you can replicate the information into Appian.
Scenario 1.1) Lets say you want to create a dropdown of promotions which are created in another system, and the promotions change a lot each day, then maybe you might need to call a service
Scenario 1.2) If you are thinking about the "US States" then those do not change every day, not even every month. In this scenario I would consider not to use even a database i would choose for an Appian constant

2) here it depends how big is the information?
Scenario 2.1) lets think your best option was to store them all in the DB . then all dropdowns need Key-values then you can have a table which have 3 columns field-key-value you query the dropdown information once on the screen for the specific drop-downs in the current screen and then using index-wherecontains or some sail expressions you can split from 1 query to multiple dropdowns/fields.

3) This answer all depends how often the dropdown changes. one way to implement cache is by saving into the process variables you query at the beginning of the process and then every time you open the screen you map the information from the pv's. with this you will hit the DB just once but the problem of doing this is that your process instance will increase on size and if some dropdown is updated then that process instance will be outdated.

hope this helps

Jose

Thanks Jose.

Your point no.3 solution I am looking.

Since i have many fields on UI. some data changes daily, some are very frequent but unpredictable. So I need deterministic approach that i should be able to define caching policy such that let's say every 5 hours cache should be refreshed. Does Appian provides such feature? If not then what's alternative.

Can anyone has information about Appian Caching, how to solve this?

I had a similar problem and I found the best solution to my problem was to have a CDT "config" with 4 parameters, "ID"," TAG", "Type" and "isActive". I have then two expression rules, one to give me back all the data from the DB with the configs and another one to do a "wherecontains" and give me the config subset pairs that I need. On the interface, I use load() with local variables, in that way I am only doing one call to the DB.

The active boolean I use to be able to make an option of the drop-down not visible for selection, but visible on readonly cases.

Thanks for reply. Is that possible to retain that data while navigating to different screen or till we are on that screen data is there.

Basically i need till user is logged in, the data should be on browser side.

it depends how you want to manage your data. If you really want to keep the same configuration set, you can either write that somewhere on the DB or if the dataset is small, have it store as a pv in your process and then pass it to your interfaces as ri,

Thanks. I will go thought the process variable it might help me. I don't want to keep data in process variable forever. I want to define policy that after every 4 hours or 24 hours the data should be refreshed & get fresh data from database something. So i want to build my caching mechanism by my own.

Best practices recommend to use pv as little as possible and use the DB as a support. That is particularly important for long processes and processes that can be running for a while.
I would suggest that on the interfaces you load the config data once and store in the DB. Your process variable should just keep the reference for your main CDT that has a reference for your config.

Thanks. Good information. Here is detailed what i want to achieve. I don't have database directly connected. I am using REST API to connect to my external SQL server. I have many such fields to populate on UI. So it's not good everytime to hit REST API to get those details, performance of screen may affect. So I was checking is there any way to Cache data at Appian side for quicker & frequent access. After defined interval it should refresh that cache automatically from database.

I may not have read the discussion closely enough, but so far, I understand that your scenario is regarding a user potentially having a task that has many dropdown fields where the task may or may not be completed in a short amount of time.

In which case, you would like to retrieve the most current/latest data without constantly querying the DB (in your case, REST API) if enough time has passed by.

If the above is correct, would the following steps be a possible solution?

1. Create 2 local variables in load() or 2 process variables:
   1. initialTime: stores the dateTime the form was initially loaded by user interaction.
   2. nextRefreshTime: stores a time that is 4 hours after the initial dateTime, which is the 1st variable (e.g. initialTime + intervalds (4, 0, 0) <--- this indicates adding 4 hours to the given time
2. Ensure the dropdown fields are inside the with() portion of the form
3. Create an expression/expression rule or local variable that checks if the current time is equal to/greater than nextRefreshTime.
4. When #3 is true, the dropdowns are locked down & a new button labeled 'Refresh Data' is available
5. When clicked, the button triggers a process model or webAPI call via a!startProcess() that will retrieve the most current/updated data to be shown on the form.
6. If data retrieval is successful, the following steps are performed: 
   1. The appropriate variables (that are associated with the choiceLabels/Values of the dropdowns) are updated
   2. initialTime & nextRefreshTime are updated with new timestamps
   3. "Refresh Data" button is no longer visible since the timestamps that determine the button visibility to force a data refresh, via button click, have been updated.

Some drawbacks are user interaction is required and the data refresh is not as automatic as you would like. I don't recall there being a way, in Appian, to automatically trigger data refresh without some level of user interaction though so I believe this to be a constraint no matter what.

Additionally, where you store the retrieved data can vary, but my typical rule of thumb is if I'm not going to use the data later on in the given workflow or for monitoring/debugging purposes, keep it on form. Selections users have made, however, should be kept in process variables if users will be able to leave & return back to the task unless this would cause potential conflict with the updated data that is retrieved.

I'm sure I didn't think thru everything possible, but if you have questions and/or you found this helpful, let me know!