Interface DropDown does not populate on the server with updated value from database

[ManidipaB]

Hi,

I am facing two issues while working with the Drop down menu (naming as Actual App). First, I need to display the drop down menu with a list that is connected to a live database. The functionality of the Actual app is to modify one value and update the database. Secondly, while running the app next time, the drop down menu should display the modified list (changed before via App).

I have used 3 types of configuration for this issue, although they work fine on my local machine, none of them execute successfully on the server. 

Configuration 1: The drop down menu is connected to a LIVE database connection, it fails to display any thing on the server.

Configuration 2: The drop down is forced to connected to a dynamic rename tool that connected to a live database connection, it displays the old list, not the new one after previous run.

Configuration 3 : Used a Chained app (user wont see the app) that would run and create a .yxdb file, the Actual App is using drop down to retrieve the .yxdb file to display the list. This configuration displays a list but does not update with modified value after the previous run.

None of these work successfully in the server.

Any suggestions?

Thanks,

Manidipa

[afv2688]

Hello @ManidipaB ,

Had the same issue. It doesn't work on the gallery. The way I resolved it was to create an simple app which writes first the file containing the names and values of the dropdown menu (Even if you do manually the updates of the dropdown menu create at least an input connected to the output for refreshing the list on the gallery). Then, on the designer interface (control + alt + d) deselect the 'show output results' and click the function 'On success run another app' and select the actual app. This is the configurattion I'm using right now. I know its not perfect but at least it works.

Cheers

[paulhurley]

Just registering interest in this topic - I have the same issue. It appears that server caches the values that were present in designer the last time I ran the workflow in designer. It NEVER checks the sql server for these values once the workflow is saved to the alteryx server.

trying to update an existing record by selecting it from a drop down list, which is connected to a database.

My workflow (below) isnt great - I have tried moving things around, reconnecting the same table directly to the dropdown etc etc. 

[DanM]

@paulhurley,

in order to update the interface tools at this time, you will need to create a chained app to update your interface tools dynamically. The interface tools before run cannot read your database, but can read a file. The reason why it can't read the database before run is do to the connections needed to be passed to the database to access it. With a file that doesn't need to happen. Your first app can read the SQL database and output the interface tool data you need in a file with the columns Name & Value. You can then have the second app (your original app) read those files. This will then update your interface tools.

Hope that helps.

DanM 

[paulhurley]

@DanM

Is this considered a bug for server? Even AFTER running the server workflow, the list does not update when you re-run the workflow the second, third ... tenth time. Its permanently stuck with the cached list - imho the workflow should not allow the database input to join to the interface. As to the connections to the database, my workflow outputs to the database via the same database which we have configured in the gallery, are you saying connections can only be established during "run" ? If so why does the "run" not recache the database values for the next time the interface is opened?

Is there a list of server behaviours that differ from designer? Its starting to feel alot like server is purely for scheduling workflows and has not been built for reacting to interface inputs based on database content.

Our most obvious use case is taking two lists and asking the user to match them where this cant be done programatically. EG we are a landlord and have assigned all our assets a unique identifier. Our insurers, government agencies and many others dont use this (normally use their own). Therefore we build matching workflows that might get 90% of the records right, but still need to show the user the properties we own, and the remaining unmatched gov/insurance options for the user to manually close out. In designer, the app works fine, in server it fails as described. Getting all our datapoints talking to each other requires this unique ID to be attached to all records from all sources so we repeat this remediation over and over.

I am constrained in that i want to edit database records that are also connected to my visualisation tool (powerbi), so creating files is not going to hit this requirement.

As to the macro - do you have an example?

[DanM]

@paulhurley

The behavior isn't a bug. Alteryx cannot update an app in real time. When you run any workflow on the server it takes the package that is loaded and runs that package. When the package runs it will reach out to your databases, but the app isn't fast enough to pick up new data for the interface tools. This is the reason for using a chained app, so that you can essentially have the real time update. As I mentioned you will want to create the first workflow with the fields you want in your interface tools from the database. Update the field names to have a field name Name and Value. Output that file as a yxdb file. Then in the 2nd app, call that yxdb within the interface tool. That will update your interface tools with the current data each time.

Here is a post that should help

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Chained-Alteryx-Analytic-Applicatio...

[bneves_tim]

Hi. Reviving the post. On 2020.4 version, i can populate a dropdown list with an sql query, but with 2021.3 version, dont work, as you sad.

So i believe that the application should be updated to execute the query first and then render the interface button to show the list.

Doesn't make sense have the option to connect on a database to populate the component and dont work on gallery...

[Annalect_Aman]

can I have the workflow that you have developed. that would be a great help. thanks buddy.