DB-driven drop-down from multiple files?

[ppbedz]

Other than creating a view, is there a way to populate a data-base driven select box with data from multiple files? I have a userid in one file and the user name in a different file. I would prefer to show the name in my drop-down selection, but I need the userid as my returned value. The userid is my link to other relevant data in the application.

Thank you,
Patti

[Scott Klement]

You could use a subselect. For example, suppose you wanted to get the categories of the products in the PRODP file (in the PUISAMPLES library, so PUISAMPLES must be in your library list) but show the description from the CATEGP file for the categories, you could do this:

choices database file = PRODP AS A
choices options field = (SELECT B.CNAME FROM CATEGP AS B WHERE B.CATID=A.PCATID)
choices values field = A.PCATID

[ppbedz]

Thanks Scott! We didn't know you code a select statement on the options field.

- Patti

[Scott Klement]

Patti,

Under the covers, Profound UI uses your properties to build an SQL statement like this:

Code: Select all

SELECT DISTINCT [choices options field], 
         [choices values field] 
  FROM   [choices database file]
 WHERE   [choices selection criteria]
ORDER BY [order by]

In my example, the statement will look like this:

Code: Select all

SELECT DISTINCT (SELECT B.CNAME FROM CATEGP AS B WHERE B.CATID=A.PCATID), 
       A.PCATID
  FROM PRODP AS A

So once you know what Profound UI is doing, and you know what is allowed in SQL, you can see how you can put these properties together to make fancy things happen :-)

[ppbedz]

Scott,

I finally had an occasion to try this and it is not working for me. I am enclosing a dump of my screen. Can you see if you can spot my error?

I ran this in the developer tools and I am not getting any errors. The sql that loads the drop-down just does not return any results.

Thank you,
Patti

Note: I have our employee master joining to itself. Every employee has an assigned supervisor. The drop-down is supposed to present a list of supervisor's names. The supervisor user id should be joined to the employee userid field in order to pull the supervisor's name.

[Scott Klement]

Patti,

I don't have your files (database tables) on my system, so there's no way I can troubleshoot your application.

I would suggest that you:

1) Look at the statement in PUISSNVP. Does it look correct? if not, adjust your properties until it does.

2) Look at the error coming back from the SQL statement. Does it provide clues as to what is wrong? You can view the error by looking at the response sent to the PUI0009xxx program in your browser's developer tools, under "network".

[ppbedz]

Scott,

What or where is puissnvp? There were no errors under the Developer Tools. That's why I didn't know what to do. I wasn't sure if I coded the properties correctly since I have never tried coding the "choice options field" this way before.

Patti

[Scott Klement]

What do you mean by "there are no errors in the developer tools"? That you are looking at the network response, and see that it is successful? (IF so, what are you asking? I was under the impression it was failing!)

PUISSNVP is a physical file in the library where you installed Profound UI. If you have a database-driven widget currently on your screen, this file will have an entry showing the SQL statement that it is running.

[ppbedz]

I'll check that file, thank you

[kmiles@misd.net]

Hello Scott

I am trying to use this option of course with a twist. Can i use the choices options field for both SQL and displaying a selection drop down list? currently we have RTRIM(VNAME) || ' {' || VEND# || '}' in choices options field, we would like to keep this feature. I have 2 tables one for the vendor name and one for the vendor status.
A R $VENDRM TEXT('VENDOR MASTER FILE')
A VEND# 6 0 COLHDG('NUMBER')
A VNAME 35 COLHDG('NAME')

A R $VENDRME TEXT('VENDOR EXTENDED')
A VEVEND# 6 0 COLHDG('NUMBER')
A VESTAT 1 COLHDG('STATUS A/I')

i would like to only select those records that do not have a status of "I", i would also like to present a drop down list of "Active" vendors.
here is what i came up with but it does not seem to work

Choice DB file : VENDRM AS A
choice options field: RTRIM(VNAME) || ' {' || VEND# || '}'
choice value field: (SELECT B.VESTAT FROM VENDRME AS B WHERE A.VEND# = B.VEVEND#)
choice selection criteria: B.VESTAT <> 'I'

i did check PUISSNVP and no new records were added to that table, it leads me to think i did not do it right.

any insight you have would be helpful.