DB-driven drop-down from multiple files?
-
- 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?
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
Thank you,
Patti
-
- 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?
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
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
-
- 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?
Thanks Scott! We didn't know you code a select statement on the options field.
- Patti
- Patti
-
- 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?
Patti,
Under the covers, Profound UI uses your properties to build an SQL statement like this:
In my example, the statement will look like this:
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 :-)
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]
Code: Select all
SELECT DISTINCT (SELECT B.CNAME FROM CATEGP AS B WHERE B.CATID=A.PCATID),
A.PCATID
FROM PRODP AS A
-
- 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?
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.
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
-
- 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?
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".
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".
-
- 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?
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
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
-
- 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?
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.
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.
-
- 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?
I'll check that file, thank you
-
- 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?
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.
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.
Who is online
Users browsing this forum: No registered users and 0 guests