topic Re: Dropdown list from SQL Table in Analytic App on Gallery in Alteryx Server Discussions

Dropdown list from SQL Table in Analytic App on Gallery

nicolasdeldalle — Fri, 13 Dec 2019 17:57:04 GMT

Hello,

I'm building an Analytic App that I need to publish on my Gallery, and the app needs to show some dropdowns tools that need to be connected to a SQL Server table (the options of the drop down tool should vary depending on the SQL table).

For example, I have a drop down tool in my App that should be connected to the a table in my SQL Server instance, so that the end user can select which information he wants from the SQL table, and then continue running the App.

I configured my drop down tools with External Source and my query works fine when I run it from outside the Gallery (as a regular Analytic App), but whenever I try to run it from the Gallery, the drop down do not show the options (it stays empty).

OBS: I have already configured my SQL Server connection to my Gallery and have access to it (I managed to write data in my DB from the Gallery Analytic App).

This is how I configured my Drop Down tool with a List Values from an External Source:

And this is the result I get from the regular Analytic App running from my server, which shows that the drop down is reading the SQL table:

When I publish my App on the Gallery (which is on the same server), the drop down options don't appear:

Is there any way my App on the Gallery will connect to the SQL Server instance and show the drop down options from the live SQL table?

Thanks in advance!

Re: Dropdown list from SQL Table in Analytic App on Gallery

JordyMicheal — Fri, 13 Dec 2019 18:40:16 GMT

Could you show a picture of the workflow?

Sounds correct to me though

Re: Dropdown list from SQL Table in Analytic App on Gallery

nicolasdeldalle — Fri, 13 Dec 2019 18:46:04 GMT

Hi JordyMichael,

This is the picture of my WF:

Since the picture itself doesn't say much, I'll detail a little bit more:

Each one of the interface tools is supposed to update the first line of a specific column in my text input.

I'm using almost the same SQL query for each interface tool except that it looks into a different table / field in my SQL Server, and updates different columns.

Hope this will make it clearer.

Thanks !

Re: Dropdown list from SQL Table in Analytic App on Gallery

JordyMicheal — Fri, 13 Dec 2019 19:59:08 GMT

Could you try adding an "AS" in the SQL statement.

select field AS name and AS value.

It is the same, but let's rule things out.

Re: Dropdown list from SQL Table in Analytic App on Gallery

nicolasdeldalle — Fri, 13 Dec 2019 20:02:24 GMT

The result is exactly the same

Re: Dropdown list from SQL Table in Analytic App on Gallery

JordyMicheal — Fri, 13 Dec 2019 20:37:49 GMT

OK, so it's the way it's populating then.

I believe you need to connect it out of the Green Arrow, not the lighting bolt for that connection

Re: Dropdown list from SQL Table in Analytic App on Gallery

nicolasdeldalle — Fri, 13 Dec 2019 21:08:04 GMT

Which green arrow do you mean? I think the problem is more related to the connection between the Gallery and my SQL Server, since the app works fine on my server outside of the Gallery.

Re: Dropdown list from SQL Table in Analytic App on Gallery

JordyMicheal — Fri, 13 Dec 2019 21:11:15 GMT

That's the only other problem I could see

It's between Alteryx Gallery querying under a generic username that doesn't have access to the Table if I were to guess.
How you have it is correct

Re: Dropdown list from SQL Table in Analytic App on Gallery

nicolasdeldalle — Fri, 13 Dec 2019 21:13:13 GMT

I found a similar post regarding this issue:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Interface-DropDown-does-not-populate-on-the-server-with-updated/td-p/413608

Any help would be much appreciated!

Thanks

Re: Dropdown list from SQL Table in Analytic App on Gallery

WilliamR — Mon, 16 Dec 2019 08:51:03 GMT

Hello,

I manage to reproduce your behaviour so I confirm there is something weird.

As a workaround, you can populate your drop down from an input data tool connected to your DB.

Just remember that you have to transpose your field in order to have it in the drop down. It works on my Gallery.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Dropdown list from SQL Table in Analytic App on Gallery

nicolasdeldalle — Mon, 16 Dec 2019 13:12:24 GMT

Hi WilliamR, Thanks for your answer.

I also tried to connect my Input data tool to the dropdown as you mentioned, but I found out that whenever my table is updated with some new records, the app doesn't update with the modified information.

Did you find the same problem?

Re: Dropdown list from SQL Table in Analytic App on Gallery

WilliamR — Mon, 16 Dec 2019 13:25:24 GMT

hello @nicolasdeldalle , this is a standard behaviour of the dropdown.

Re: Dropdown list from SQL Table in Analytic App on Gallery

nicolasdeldalle — Mon, 16 Dec 2019 14:38:11 GMT

I understand. So is there any initiative to make some improvements for this? I think having a dropbox directly connected to a database table would be a great feature...

Re: Dropdown list from SQL Table in Analytic App on Gallery

WilliamR — Mon, 16 Dec 2019 14:50:34 GMT

You can try this approach: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Drop-Down-does-not-Refresh/td-p/67747

and if you are able to use the Crew Macro (runner and conditional runner), you can extract first your list and in a second step run the workflow with the extraction as a source for the dropdown.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.