Best architecture - To populate dropdown values

I need to develop Application into Appian platform.

Our UI is having many dropdowns around 20 for search criteria.

1. What is best way to get data from database? using REST service or direct connection.

2. Does all different dropdowns should call different stored procedure or any other way?

3. Does Appian provides Caching so we can define caching policy. otherwise everytime i refresh page then it will hit database to get data.

  Discussion posts and replies are publicly visible

Parents
  • 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.
Reply
  • 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.
Children
  • 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.
  • A Score Level 2
    in reply to Pravin Sawant

    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!

  • Hi Reggie,
    There is some gap in understanding my question.

    Does Appian provides Caching so I can cache the data it can be large in size i.e. say 100 rows to 10000 rows. This data i need frequently to use so want to cache. But also i want to get latest copy of this data after some interval (configurable) say 4 hours, 24 hours etc.
  • Certified Lead Developer
    in reply to Pravin Sawant

    Pravin,

    I do not think there is any way to Cache a value to be stored in the system to be used other places. I do have a few suggestions.

    1.just do additional query every time the screen is loaded to get most up to date value if possible.

    2. Get access to a DB to persistent this data some where. Then run a process model on a normal basis to update the values.

    3. Now is where it gets interesting. If you can't get ahold of a DB you could have a process that gets the values every x amount of time and this process stays active untill the next pull. This way you will allways have an active process with the updated values. Then you could use a process report with a query to the report to get the values.

    4. When you do you pull for the data you could take the data and store it to a constant. Then your drop downs would just be built from the constants.

    I would caution though that if your values used to populate the drop down are changing regularly then you end up in a situation where the value loaded into the form is not in the options array. You may want to wrap your drop down with a checker to ensure your sail does not break in this situation.

  • on the appian documentation you have the guidelines

    community.appian.com/.../managing-reference-data

    On the DOs and DONTs, you have this:

    Allow users to make changes to existing data as this will impact older data. Instead, allow users deactivate the old name and create a new name using effective dates and end dates.

    Effective dating and end dating is also useful as an alternative for deleting data.


    So, additionally, i would suggest that if you want "real time data" of your configs, use a with variable instead of a load and perhaps some configuration flags if you want the same settings to be alive in your whole process (i.e. use effective dates )