Hi all;
Struggling with the issue of how to implement the following on my
eGroupware remake:
How can I do conditional lookups for a dropdown which needs to go through a details table that will cross-link any record to any record in the project. The fields are ID, link_app1, link_id1, link_app2 and link_id2. In this case you choose a CUSTOMER and then choose a SITE filtered by that CUSTOMER ID through the links table. Because you can link in either direction, the CUSTOMER or SITE could either be link_id1 or link_id2. In order to sort that out, I used this as the SITE query:
SELECT
a3.cat_id,
a3.n_fileas,
a3.contact_id,
egw_links.link_app1,
egw_links.link_app2,
egw_links.link_id1,
egw_links.link_id2,
(CASE
WHEN egw_links.link_id1 = a3.contact_id THEN egw_links.link_id1
WHEN egw_links.link_id2 = a3.contact_id THEN egw_links.link_id2
END) AS record_id,
(CASE
WHEN egw_links.link_id1 = a3.contact_id THEN egw_links.link_id2
WHEN egw_links.link_id2 = a3.contact_id THEN egw_links.link_id1
END) AS matched_link
FROM addressbook AS a3
LEFT OUTER JOIN egw_links ON (a3.contact_id = egw_links.link_id1 OR a3.contact_id = egw_links.link_id2)
WHERE (a3.cat_id =303) AND
(a3.n_fileas !="") AND
(egw_links.link_app1 = "addressbook" AND egw_links.link_app2 = "addressbook") AND
(a3.contact_id = egw_links.link_id1 OR a3.contact_id = egw_links.link_id2)
This seems to give the results I need in the plain query,
results
but when I try to use any of the aliases as a link in the conditional ("This dropdown is dependent on"...) it just returns all records. I thought the aliases may not be available in this scope, but they are listed as selectable fields. I also tried running the same basic query for BOTH the CUSTOMER and the SITE with similar results. I think I have a fundamental error in my logic here somewhere. I'm sure there must be some way to do this, but I'm a little lost. Any help to give me a kick in the right direction will be appreciated. Thanks.
Hi Again;
I just realized that my example link didn't work for anyone but me, so I hopefully fixed that so maybe I could get some feedback. Since I posted, I've tried other ways to do this but keep coming back to the issue that whenever I try to filter on either of the alias fields, I get all records returned. And I'm not sure how else to be able to filter on either link_id1 OR link_id2 and return it's complement unless I do some conditional using aliases. In other words, if I make the dropdown dependent on link_id2 it works as expected (for all records where link2 represents the CUSTOMER) but get totally different results when I make it dependent on matched_link; which according to the query results should (most of the time) give the same list. It also seems to break any of the WHERE filtering added to the query.
What am I missing? Any ideas on how I can get this to work? Thanks again.
