Outer Join and CFTree

[NickyJay]

Hi all,

can anyone give me an idea on this...

I have a cftree that outputs a nice windows explorer style
list of related documents in our quality manual - this is based on an outer join query based on several access tables.

Basically, a Policy has a related procedure, which has a related work instruction which in turn potentially has a form to fill out (a quality document).

Where there are no work instructions or quality documents, i don't want anything to display past the procedure folder on the tree, but if a work instruction and/or a quality document does exit, then it should display according.

I have is ouputting everything ok so far, just that where there are no related documents, a blank icon is displaying (the others output the title of the document as a hyperlink to the pdf file.)

i have been looking at using cfif isDefined around the Work instruction and quality document tree items, but this does nothing!

Can anyone provide any hints on what to try next?

 

[SnakeHollywood]

If you do an outer join, this will select columns from the joined table even if there no matches. If you use an inner join then you will not have this problem.

Regards
--
Russ Michaels
CFDeveloper.co.uk
ColdFusion Developer community and FREE developer hosting

 

[NickyJay]

Hi, thanks for your reply

I have just checked and the query is acctually a right join - should i be changeing this?

I am aware of access having different syntax etc from other database programs, but the query in access does bring out what i want - i just want to be able to hide the nulls in the tree output so that on the lowest child levels other wise where, for example, there is no Quality Document, nothing displays (at the moment it is a blank icon), or if there is no work instruction and quality document to disply these are both hidden...

does this make any sense?

thanks!

 

[SnakeHollywood]

in order to hid ethe nulls, you have to explude this data from the query.
If you needs these value in your original query, why not do a Query of Query for the CFTREE so that you can exclud ethe nulls.

Regards
--
Russ Michaels
CFDeveloper.co.uk
ColdFusion Developer community and FREE developer hosting

 

[NickyJay]

Hi,

thanks again

I'm not quite sure what you mean about the query of a query??

My current query is this:

SELECT tblRelated.*, tblPolicies.Title AS PolicyTitle, tblProcedures.Title
AS ProcedureTitle, tblWorkInstructions.Title AS WorkInstruction, tblQDDocuments.Title
AS QualityDocument FROM tblWorkInstructions RIGHT JOIN (tblQDDocuments RIGHT
JOIN (tblProcedures RIGHT JOIN (tblPolicies RIGHT JOIN tblRelated ON tblPolicies.PolicyID
= tblRelated.Policyfk) ON tblProcedures.ProcedureID = tblRelated.Procedurefk)
ON tblQDDocuments.QDID = tblRelated.QualityDocumentfk) ON tblWorkInstructions.InstructionID
= tblRelated.WorkInstructionfk ORDER BY tblPolicies.Title;

I know its on the large side, but anything else i tried seemed to put more strain on the page loading! I guess it would be better to get the database to do the query rather than my page but im not sure on this either! Its the first time ive used the cftree tag, im not so sure i like it now!

Nicola

 

[r937]

Basically, a Policy has a related procedure, which has a related work instruction which in turn potentially has a form to fill out (a quality document).

based on this explanation, the following is what you should have in the FROM clause --

  from (((
       tblPolicies
left outer
  join tblRelated
    on tblRelated.Policyfk 
     = tblPolicies.PolicyID    
       )
left outer
  join tblProcedures
    on tblProcedures.ProcedureID 
     = tblRelated.Procedurefk          
       )
left outer
  join tblWorkInstructions
    on tblWorkInstructions.InstructionID 
     = tblRelated.WorkInstructionfk   
       )
left outer
  join tblQDDocuments
    on tblQDDocuments.QDID 
     = tblRelated.QualityDocumentfk

as for what you need to do to make the CFTREE behave, sorry, i can't help there

r937.com | rudy.ca

 

[SnakeHollywood]

If you don't nee dthe NULL values to be dispalyed, can you tell me why you need to return them in the query?

Regards
--
Russ Michaels
CFDeveloper.co.uk
ColdFusion Developer community and FREE developer hosting

 

[NickyJay]

Basically, the table tblRelated is a table that links the other four together - each row in this table consists of a foreign key for the others, but a row can contain either two, three or all four foreign keys (any combination)

I don't know if im making it too complicated or not

 

[SnakeHollywood]

From the information you have provided, I can't se ehow you can avoid the null, because the related items column has to be there for all rows which may need it, youc annot remove a column for just one row.

Maybe you can post a screen shot to better show the problem.

Regards
--
Russ Michaels
CFDeveloper.co.uk
ColdFusion Developer community and FREE developer hosting

 

[NickyJay]

i can't seem to attach my screen shot!
its like this thou:

*Policy Title
*procedure title
*
*
*procedure title
*work instruction
*Quality Document
*Quality document

and where there tw blank links on the top bit appear, i wanted to hide them - i tried a cfif isdefined to hide them but this doesnt work!

 

[r937]

try <CFIF LEN(xxxx)> instead

r937.com | rudy.ca

 

[NickyJay]

Hi, changed it from :

<cfform name="qmTree" action="#cgi.script_name#" preservedata="Yes" format="flash" skin="haloSilver" style="backgroundAlpha: 0;" wmode="opaque">
<!--- --->
<!---display Tree --->
<cftree name="QMDocs" required="yes" fontsize="11" font="Tahoma" hscroll="no" vscroll="yes" height="500" width="718" style="borderStyle:none;indentation:15">
<!---Parent Level--->
<cfoutput group="PolicyTitle" query="qryRelated">
<cftreeitem value="#PolicyTitle#" parent="QMDocs" expand="yes" href="

http://hq-int/pdf/#PolicyTitle#.pdf"

target="_blank">
<!---child 1 level--->
<cfoutput group="ProcedureTitle">
<cftreeitem value="#ProcedureTitle#" parent="#PolicyTitle#" expand="yes" href="

http://hq-int/pdf/#ProcedureTitle#.pdf"

target="_blank">
<!---child 2 level--->
<cfoutput group="WorkInstruction">
<cftreeitem value="#WorkInstruction#" parent="#ProcedureTitle#"
href="

http://hq-int/pdf/#WorkInstruction#.pdf"

target="_blank">
<!---child 3 level--->
<cfoutput>
<cftreeitem value="#QualityDocument#.pdf" parent="#WorkInstruction#"
href="

http://hq-int/pdf/#QualityDocument#.pdf"

target="_blank">
<cftreeitem value="#QualityDocument#.doc" parent="#WorkInstruction#"
href="

http://hq-int/pdf/#QualityDocument#.doc"

target="_blank">
</cfoutput>
</cfoutput>
</cfoutput> </cfoutput>
</cftree>
</cfform>

to cfform name="qmTree" action="#cgi.script_name#" preservedata="Yes" format="flash" skin="haloSilver" style="backgroundAlpha: 0;" wmode="opaque">
<!--- --->
<!---display Tree --->
<cftree name="QMDocs" required="yes" fontsize="11" font="Tahoma" hscroll="no" vscroll="yes" height="500" width="718" style="borderStyle:none;indentation:15">
<!---Parent Level--->
<cfoutput group="PolicyTitle" query="qryRelated">
<cftreeitem value="#PolicyTitle#" parent="QMDocs" expand="yes" href="

http://hq-int/pdf/#PolicyTitle#.pdf"

target="_blank">
<!---child 1 level--->
<cfoutput group="ProcedureTitle">
<cftreeitem value="#ProcedureTitle#" parent="#PolicyTitle#" expand="yes" href="

http://hq-int/pdf/#ProcedureTitle#.pdf"

target="_blank">
<!---child 2 level--->
<cfif LEN ("WorkInstruction")>
<cfoutput group="WorkInstruction">
<cftreeitem value="#WorkInstruction#" parent="#ProcedureTitle#"
href="

http://hq-int/pdf/#WorkInstruction#.pdf"

target="_blank">
<!---child 3 level--->
<CFIF LEN("QualityDocument")>
<cfoutput>
<cftreeitem value="#QualityDocument#.pdf" parent="#WorkInstruction#"
href="

http://hq-int/pdf/#QualityDocument#.pdf"

target="_blank">
<cftreeitem value="#QualityDocument#.doc" parent="#WorkInstruction#"
href="

http://hq-int/pdf/#QualityDocument#.doc"

target="_blank">
</cfoutput>
</cfif>
</cfoutput>
</cfif>
</cfoutput> </cfoutput>
</cftree>
</cfform>

- no change in the output! Im starting to think im not going to get what i want out of a tree

 

[SnakeHollywood]

ok the nulls obviously serve no purpose for you in this display.
So either

a) redo the query and do not select the NULL columns
b) do a query of query and exclude rows with null values.

Regards
--
Russ Michaels
CFDeveloper.co.uk
ColdFusion Developer community and FREE developer hosting

 

[r937]

this is incorrect --

<cfif LEN ("WorkInstruction")>

that calculates the length of the string "WorkInstruction"

what you want is to calculate the length of the query variable

<cfif LEN(qryRelated.WorkInstruction)>




r937.com | rudy.ca

 

[NickyJay]

Hi, thanks for all you help, i managed to sort it - i used a cfif and tweaked it some:

<CFIF IsDefined("QualityDocument") AND #QualityDocument# NEQ "">
<cftreeitem value="#QualityDocument#.pdf" parent="#WorkInstruction#"
href="

http://hq-int/pdf/#QualityDocument#.pdf"

target="_blank">
</cfif>

 

[r937]

of course QualityDocument will be defined -- it's one of the qryRelated query columns

whether it is NULL as a result of the outer join doesn't matter, it will still be defined

you'll get (slightly) faster performance by my suggestion of LEN() rather than testing it to see if it's not equal to an empty string

you'll also get faster performance by scoping your variables, i.e. qryRelated.QualityDocument instead of just QualityDocument

finally, don't use hash marks inside CF tags

r937.com | rudy.ca