|
Hi all! I'm back ;)!
I'm trying to create a local view and having some problems. I have 3 tables I want to include which are part of a many-to-many relationship
The tables are:
allcodes <--- contacts ---> person
The fields I want to display in my view are:
- allcodes.dispval for codetype='position'
.and. contacts.poscode=allcodes.codeno
- person.first,middle,last,phone,email for
person.persid=contacts.persid
I also want to filter only contacts.agencycode="1234" so only one agency's contacts display in a view (or can I link that filter to my form's data environment?)
The final view should look like this:
Position........First.......Middle Initial......Last.......Phone
In View Designer I can create the relation between Contacts and Person and even show only agency "1234" contacts. But when I try to link Allcodes.displayvalue to my position code in Contacts.dbf I'm getting 0 records in my view.
Oh, yeah, here's the sql that's screwing up:
SELECT Allcodes.dispval, Contacts.poscode, Person.first, Person.middlei,;
Person.last, Person.phone, Person.email;
FROM ird!contacts, ird!person, ird!allcodes;
WHERE Contacts.persid = Person.persid;
AND (Contacts.acode = "11000";
AND Contacts.poscode = Allcodes.codeno);
ORDER BY Contacts.poscode
Please help :)
Mandy
|
