This is a re-post of a question I asked last week, but which I have refined and tried to solve myself with learning the basics of SQL today.
This has been great fun and SQL is certainly something that would benefit me as I often get tied in knots with relationships etc. Whilst I have gotten quite far I have hit a blocker and think this may be down to me confusing the use of scripts, relationships and SQL.
As background and apologies for the length in advance –
1). I have a TO called StudyPapers, in which sits a certain editorial type. As there are other editorial types required I decided to assign a ContentType number to each type – in the case of StudyPapers this is 3.
2). Each ContentTypeNumber will have assigned to it a mix of different categories, which are set out in a TO called ContentCategory (which also features on the schema as a TO called EditorialContentCategory).
3). The relationship between ContentTypeNumber is defined in a join table called ContentTypeRelatedCategory (of which there is another instance on the schema called EditorialTypeCategories).
4). There is then a relationship between EditorialContentCategory, EditorialTypeCategories and StudyPapers which marries an editorial record in the latter, ContentType and Related Category via a join table called EditorialRelatedCategories.
5). The Schema looks like this.
6). The following layout is a stripped back version of one I have within StudyPapers.
The left hand portal displays the categories that are related to this editorial ContentType as defined via the EditorialTypeCategories TO. Next to this is a number that I wanted to show the count of records within StudyPapers that have been assigned a particular category as defined within the EditorialRelatedCategories TO and this is where I cannot seem to get it to work.
7). I created a calculation field within EditorialRelatedCategories that contained the following SQL.
FROM EditorialRelatedCategories WHERE ContentCategoryTitle = 'Dementia' and ContentTypeNumber = 3";"";"")
This correctly displayed the count but hardcoding the ContentCategoryTitle and ContentTypeNumber clearly won’t work as both of these may increase in number etc and be subject to other changes.
8). I then hit upon the approach of creating an OnRecordLoad trigger looping script for the main layout that would -
- Set a global variable for the ContentID as contained within the relevant global field within StudyPapers ($$CategoryContentTypeNumber);
- Then go to the left hand portal first row and set another global variable from EditorialTypeCategories::ContentCategoryTitle ($$RelatedContentTypeCategory);
- Once the SQL is executed this creates another global variable called $$ContentTypeCategoryTypeCount taken from a calculation field within EditorialRelatedContent called ContentTypeCategoryTypeCount that contains the following SQL –
ExecuteSQL ( "SELECT COUNT(*) FROM EditorialRelatedCategories WHERE ContentCategoryTitle = ? and ContentTypeNumber = ?";"";"";$$RelatedContentTypeCategory;$$CategoryContentTypeNumber)
Unfortunately it’s returning the same count for each category within the left-hand portal as can been seen.
Any help would be very much appreciated, not least because I’d like to dive deeper into SQL.