DB-driven drop-down from multiple files?

Use this board to ask questions or have discussions with other Rich Displays users.
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.
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've coded a subselect, but you are trying to treat it as a join.

I don't think it's possible to do a join inside the fields for a dropdown. If its important to use a join, you'd have to create a view that does the join and use that in your db-driven field properties.

Or, instead of using a join, use another subselect in the 'selection criteria'.
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

sorry I wasn't thinking clearly yesterday. Very simple change when I thought of it, use the power of SQL grasshopper!

choice database file: VENDRM, VENDRME
choice options field: RTRIM(VNAME) || ' {' || VEND# || '}'
choice value field: VEND#
choices selection criteria: VESTAT <> 'I'

very flexible!
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 »

i forgot important part of code
choices selection criteria: VESTAT <> 'I' and VEVEND# = VEND#

Thanks for your insight Scott very helpful!!
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest