DB-driven drop-down from multiple files?

Use this board to ask questions or have discussions with other Rich Displays users.
ppbedz
Experienced User
Posts: 147
Joined: Tue Jun 17, 2014 4:00 pm
First Name: Patti
Last Name: Bednarz
Company Name: McGard
State / Province: New York
Country: United States
Contact:

DB-driven drop-down from multiple files?

Post by 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
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: DB-driven drop-down from multiple files?

Post by 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
Experienced User
Posts: 147
Joined: Tue Jun 17, 2014 4:00 pm
First Name: Patti
Last Name: Bednarz
Company Name: McGard
State / Province: New York
Country: United States
Contact:

Re: DB-driven drop-down from multiple files?

Post by ppbedz »

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

- Patti
Scott Klement
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: DB-driven drop-down from multiple files?

Post by 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
Experienced User
Posts: 147
Joined: Tue Jun 17, 2014 4:00 pm
First Name: Patti
Last Name: Bednarz
Company Name: McGard
State / Province: New York
Country: United States
Contact:

Re: DB-driven drop-down from multiple files?

Post by 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.
Attachments
ldexample.txt
(11.71 KiB) Downloaded 742 times
Scott Klement
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: DB-driven drop-down from multiple files?

Post by 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
Experienced User
Posts: 147
Joined: Tue Jun 17, 2014 4:00 pm
First Name: Patti
Last Name: Bednarz
Company Name: McGard
State / Province: New York
Country: United States
Contact:

Re: DB-driven drop-down from multiple files?

Post by 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
Experienced User
Posts: 2711
Joined: Wed Aug 01, 2012 8:58 am
First Name: Scott
Last Name: Klement
Company Name: Profound Logic
City: Milwaukee
State / Province: Wisconsin

Re: DB-driven drop-down from multiple files?

Post by 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
Experienced User
Posts: 147
Joined: Tue Jun 17, 2014 4:00 pm
First Name: Patti
Last Name: Bednarz
Company Name: McGard
State / Province: New York
Country: United States
Contact:

Re: DB-driven drop-down from multiple files?

Post by ppbedz »

I'll check that file, thank you
kmiles@misd.net
New User
Posts: 15
Joined: Tue Aug 21, 2018 8:14 am
First Name: Keith
Last Name: Miles
Company Name: Macomb ISD
Phone: 5862283375
Address 1: 38177 LITTLE MACK AVE
City: CLINTON TWP
State / Province: Michigan
Zip / Postal Code: 48036
Country: United States
Contact:

Re: DB-driven drop-down from multiple files?

Post by 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.
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests