Creating a drop down menu in Prevasive.SQL V8.7

FarrukhZ · #1 (**permalink**) 06-27-07, 08:29

Hello Everyone,

I am currently using Pervasive.SQL V8.7 to work on a database system. This is my first time working with SQL and I'm not all the famaliar with the commands yet but I'm learning.

Right now I'm haveing a problem creating a drop down menu for one of my queries. Just to give you an idea following is the code that I have thus far.

Code:

SELECT
  WorkOrd.szID AS "ID" ,
  PersGroup.szID AS "PersGroup" ,
  WorkOrd.szDescription AS "Description" ,
  WorkOrd.nProgress AS "Progress" ,
  ListWOState.szID AS "State" ,
  ListWOState.szDescription AS "StateDescription" ,
  WorkOrd.szBatchID AS "BatchID" ,
  WorkOrd.dateScheduled AS "DateScheduled" ,
  WorkOrd.szNote AS "Note" 
FROM
  ((((WorkOrd INNER JOIN ListWOState ON WorkOrd.gWOState = ListWOState.gPK)
  INNER JOIN WorkOrd_PersHier ON WorkOrd.gPK = WorkOrd_PersHier.gWorkOrd)
  INNER JOIN PersHier ON WorkOrd_PersHier.gPersHier = PersHier.gPK)
  INNER JOIN PersGroup ON PersHier.gParent = PersGroup.gPK) 
WHERE
  (PersGroup.szID =<#Personnel Group$txt!None#>) AND
  (WorkOrd.nProgress <#Progress$PROG_!All#>) AND
  (WorkOrd.dateScheduled >= <#Date Start$DTE#>) AND
  (WorkOrd.dateScheduled <= <#Date End$DTE#>) 
ORDER BY
  WorkOrd.szID ASC

Specifically the code that I'm having a probem with is the one that I have in Bold above. Right now I have it set so that user is able to enter a txt value for the PersGroup and then I take that text value and match it with what is on the database system and display only the matching rows. To do this I have set the parameter

Code:

PersGroup.szID =<#Personnel Group$txt!None#>

Although what I would like to do is set up a drop down menu that lets the user pick a specific PersGroup from a list of all the PersGroups as well as has an ALL selection build into the drop down menu that will allow the user to select all PersGroups at the same time if necessary.

Someone who was working on this database before me accomplished this task for the nProgress column by using the code

Code:

WorkOrd.nProgress <#Progress$PROG_!All#>

So now when I run the query it gives me a drop down menu for Progress and lets me select one or all of the available progresses, depending on what I want. However, I can't seem to figure out how that command works or how I can duplicate its effect on the PersGroup column. Could someone please help me out here.

Thanks!

FarrukhZ · #2 (**permalink**) 06-28-07, 08:26

Ok, here's a screenshot of the Database that I'm working with. This is what query that I'm trying to create looks like in design view

Then if you click the SQL button near the top let in the window above it gives me the SQL code that I pasted in my initial post. However there are certain things that can't be done in design view. Like for another query that I created I needed to write IF statements so I had to made it a SQL only query and just work with the SQL code, which is why I didn't bother putting in the design view initially.

Internet Services

Web Development

Digital Marketing

Consumer Tech