List of Distinct Related Values... ExecuteSQL perhaps?
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.
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: