AnsweredAssumed Answered

List of Distinct Related Values... ExecuteSQL perhaps?

Question asked by TylerNelson on Nov 11, 2013
Latest reply on Nov 12, 2013 by philmodjunk

Title

List of Distinct Related Values... ExecuteSQL perhaps?

Post

     I have a table called Projects (Movie/TV projects) and a table called Documents, which stores documents in a container field related to Projects via _kp_ProjectID = _kf_ProjectID (so I can store multiple documents pertaining to a single project in a separate table). 

     In the Documents table I have a field called COTStatus (a document's "Chain of Title" status, i.e. an Option Agreement, Acquisition Agreement, Copyright Registration, etc). If this field is blank it implies that the document does not pertain to the Project's chain of title. I have a simple calculation field called "zcCOTBoolean" to track whether or not the Document record pertains to the chain of title for a Project:

     Case(Not Isempty(COTStatus) ; 1 ; "") 

     I want to make a simple report from the context of Projects with 2 field.

     1-ProjectName

     2-COTDocumentList

     So I just need a simple list of all of the UNIQUE COTStatus values in the related Documents field. If I use the List () function from the context of Projects where _kp_ProjectID = _kf_ProjectID and zcConstant1 = zcCOTBoolean, I get repeat values when, for instance, there are 2 different copyright registrations on file. However I would love to get a list of only the unique values. Is there some ListUnique() function? Or do I have to use ExecuteSQL?  

     Using SeedCode's SQLExplorer is always hit of miss for me. I can't figure out why I need to use JOING as opposed to INNER JOIN. Also Anyone good at using ExecuteSQL care to take a stab at this? Or suggest a non-SQL method? Thanks!

     I keep messing around with different SQL queries and they all return '?'. For example:

          ExecuteSQL 
          ( 
          "
          SELECT DISTINCT COTStatus
          FROM Documents
          INNER JOIN Projects ON _kf_ProjectID = _kp_ProjectID
          WHERE zcCOTBoolean = ?
          " ; 
          "" ; "" ;
          1 
          )
           
          I've tried using simply JOIN and INNER JOIN. I've also tried:
          INNER JOIN Projects ON _kp_ProjectID = _kf_ProjectID 
               also tried:
               INNER JOIN Documents ON _kp_ProjectID = _kf_ProjectID
                    also tried:
                    INNER JOIN Documents ON _kf_ProjectID = _kp_ProjectID
                    also tried:
                    INNER JOIN Projects ON _kf_ProjectID = _kp_ProjectID 
           
          How does SQL distinguish between other tables joined to Projects based on the same _kp_ProjectID = _kf_ProjectID relationship if I don't name the 2nd table in the query??? I have other related tables based on the same relationship. I keep rewatching SQL videos and retaking tutorials and I still find SQL in Filemaker thoroughly confusing!

      

Outcomes