2 Replies Latest reply on Nov 12, 2013 9:10 AM by philmodjunk

    List of Distinct Related Values... ExecuteSQL perhaps?

    TylerNelson

      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!