VFP9 Form with drop-down box list of DB items

[Labell]

I'm trying to create either a list or an array of unique 'location' items in my database, and place them in a drop-down box for a selection.

So, you would click the drop-down box in the Form and you are presented with all the unique locations in the the DB. From that I can take the chosen location and produce a report of all the items in that location.

What is the best way to do this? I'm stumped. I watched the videos on producing reports from

http://www.garfieldhudson.com/FreeVideos.aspx.

He didn't do much along the lines of what I'm asking.

 

[TamarGranor]

Where are the locations stored now? I'll assume that what you have is a Location field in some table. So to populate your dropdown, put code like this (substituting the actual table and field names) into the Init method of the dropdown:

SELECT DISTINCT Location ;
   FROM YourTable ;
   INTO CURSOR csrLocations

Set the RowSourceType property of the dropdown to 2-Alias and set RowSource to "csrLocations" (in the Property Sheet, you don't want the quotes). Set the Name property for the dropdown to cboLocation. (That last isn't required, but it's good form.)

After the user makes a selection from the dropdown, you can find out what was chosen by examining the dropdown's Value property.

So you might have a button on the form to run the report. In the Click method of that button*, you'd use code like this:

LOCAL cLocation
cLocation = ThisForm.cboLocations.Value
SELECT <the fields you want in your report> ;
  FROM YourTAble ;
  WHERE Location = m.cLocation ;
  INTO CURSOR csrReportData

REPORT FORM YourReport PREVIEW

Again, you need to substitute the right names into that code.

* It's actually not good form to put this code right into the button's Click method, but since you're a beginner, I'm keeping it simple. Come back in a while and ask about creating custom methods.

Tamar

 

[Olaf Doschke]

You are talking of a preparation step filtering data before reporting it, so this has nothing to do with building a report at all, has it? If you want to know something about data querying, then watch a video about that. SQL. Try the video "FoxPro and the SQL Language".

Did you create that database, or was it already there and you need to report things from it? If it is by you, I suggest you learn a bit about data normalisation. Locations scream for being a seperate table and in other tables you'd normally only find a location ID coming from that location table as reference.

The drop-down list then is simply displaying the location name field of that locations table. The selection gives you the location ID and you query data from other tables with that locaion id.

Bye, Olaf.