>Here's the situation: table Photos has photoid integer, various other lookupid fields; table kwrdlink has photoid, kwrdid both integer; table keywords has kwrdid, keyword c(35). I wanted to be able to select one or more keywords (text), and generate a report of all photograph information that contain ALL of the selected keywords. What I ended up with works, but it's very confusing to follow and probably isn't the best solution. Here's what I did: I set up a form with two arrays, aKeywords and aSelects, each 2dim, with textval and intval. I built two listboxes, and populated them with the 2dim arrays. I set up some buttons and built a makeshift mover control. That works great. When I finally end up with the array of selected keywords/kwid's, I'm not sure what the best method of getting photos from this is. Here's what I'm doing now: Take first element of aSelects, get kwid, and query the kwrdlink table for this kwid. Scan thru kwrdlink table pulling out all photoid's with
>this kwid into an array aPhotos. I dimension aPhotos to [ photocount, selectedkeywordcount+1] to hold photoid, and T/F for each keyword. Then I basically loop thru the aPhotos array, looping thru each keyword checking for the existence of this combination of photoid/keywordid in the kwrdlink table. If found, set the element to .t., otherwise set it to .f. After looping thru all rows of the aPhoto array, I set up a do/while loop checking for .f. values, and remove each row with a .f. value and redim the aPhoto array to 1 less row. After all .f. values are removed, I now have an array of photoid's that match all keywordid's I've selected. I create a temporary cursor from the photo table and lookup tables, and populate cursor with photo values for each photoid in aPhotos. Then I can run my report with this cursor as the datasource.
The easy way to get your cursor, starting at the point you got the array to the right populated with selected keywords:
create cursor selectedkwds (kwdid i, kwdtext c(20))
append from array YourSelectedArray
select * from kwrdlink where kwrdid in (select kwrdid from kwrdid);
into cursor tmp1
select * from photos where pictureid in (select pictureid from tmp1);
into cursor GotThePics
* not sure about the exact names of your fields here, they are out of sight now
Disclaimer as usual - this code is not intended to work, launch a shuttle, or do anything smart. This is just a slight push, hopefully in the right direction.