Error Creating Name/Value Dropdown

I have a basic macro that runs a query to pull the valid Name/Value pairs we wish to use in the drop-down. When I run this on my local machine it works just fine and provides me with a listing of 100 items in the drop-down. When this code is put onto the gallery (using a gallery connection to Oracle), the list returns no values.

I can't seem to figure out why this works one place and not the other. I've tried using saved credentials and just run without requiring credentials and it hasn't made a difference.

Any ideas on what feels like a very simple task?

Gallery

Macros

Interface Tools

Accepted answers

All comments

mbarone

Can you show a screenshot of your YXWZ that has the drop down tool on it? Is the macro within that YXWZ and it feeds to the drop down tool? Or are you using the chained app methodology? The only way I know of to produce a drop down list to a user "on the fly" is to have a YXWZ run initially to produce the Name/Value pairs, and then have it chained to the YXWZ that uses that "just-produced" Name/Value pair file (and you accomplish this by marking it as an asset when you send the first one to the Gallery).

dataguyW

Here are the screenshot of the entire workflow and a screenshot of the properties. It works just fine locally, just not on the gallery.

alteryx-list-debug1.PNG

alteryx-list-debug2.PNG

mbarone

Got it.

I haven't done that before - use a query to generate the list. But I did just now, and it works both locally and in the Gallery. So it must have to do with credentials.

I'll tell you how ours is set up - for our Server, we have the Alteryx Service account set with a generic account (static username & password that doesn't change). This login has access to all our databases. Our Gallery is set up such that all apps/workflows go into one specific studio. We have credentials set up for that generic account I just mentioned, and the studio where all apps/workflows go is set to always use those credentials.

Here's some screenshots:

Service Account Set up on Server:

2022-03-02 14_39_42-Clipboard.png

The credentials below are the same as the service account above. And the Studio the right arrow is pointing to is the studio where we put all apps/workflows.
2022-03-02 14_40_42-Window.png

Hopefully that helps, but it sounds like you may have tried the same set up to no avail.

dataguyW

Yes. This was working before I made any changes sometimes, but would timeout sometimes as the query used to fill the listbox took 55 seconds. I tuned it a bit and got it down to 10 seconds to avoid the timeout, but had to add a hint to the query to do so. I'm not sure the interface tool likes what would appear to be a comment in the SELECT clause, but then again it works locally.

SELECT rslt.NAME, rslt.VALUE FROM (
select /*+ PARALLEL */ ru.model_id as NAME, ru.model_id as VALUE
from metrix.request_unit ru
where
ru.created_dttm >= to_date(sysdate, 'dd-MON-yy') - 30
and length(ru.serial_id) = 9
and substr(nvl(ru.model_id,'NOPE'),1,4) NOT IN ('100S','NOPE')
group by ru.model_id, ru.model_id) rslt
order by 1

mbarone

Hmm....10 seconds certainly shouldn't be timing out, unless the server is balancing it with other simultaneous workflows and it's causing it to timeout.

Like I said, I've never used that method due to exactly that - fearful of a bad UX with even a <10 second list build. What I do is "skin" the app with the drop down with a starter app. The starter app basically just tells the user "about the app" (basically just text they see - no interaction involved). But, the text ends with "click Next to start the app". And when they do that, what they're actually doing is executing the query to create the name/value pairs into a YXDB. In the config of the app, it's coded such that after completion, it kicks off the interactive app.

Something like this:

2022-03-02 15_00_58-Window.png

What's happening when they click "Next" is that it's seeing who they are, and creating a stand alone YXDB with name/value pairs for the models they are allowed to see, and then kicking off this app here with the interactivity (again, the drop down list was created on the fly with the prior app, but it's all transparent to them):
2022-03-02 15_03_47-Window.png

You do this using the chained app functionality - and is the most common way to build lists on the fly like that (at least the most common from the other users I correspond with).

Let me know if you're interested in going that approach. I prefer it because it takes the query out of the app itself and builds it separately (and the YXDB is created in a temp space of your server so it doesn't take up space forever).

dataguyW

The solution was something simple. I had name a connection to both a QC gallery and the production gallery the same and pointed to the one in QC. There is no way within the tool to id which gallery a connection is in unless you remove it and reselect from the gallery. The chained app is a good idea, I just happen to be fixing someone else's code and was trying to just make it run again with as little disruption as possible.

mbarone

Oh glad you figured it out!! Yes, I've had my fair share of those types of "fixes with as few modifications as possible" LOL !

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703