AnsweredAssumed Answered

Using SQL to show a count of related records by category

Question asked by garyjones on Aug 22, 2018
Latest reply on Aug 25, 2018 by garyjones

Hi,

 

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.

content type.png

 

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).

 

contentcategorygrab.PNG

 

3). The relationship between ContentTypeNumber is defined in a join table called ContentTypeRelatedCategory (of which there is another instance on the schema called EditorialTypeCategories).

 

ContentTypeRelatedCategoryGrab.PNG

 

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.

 

EditorialRelatedCategory.PNG

5). The Schema looks like this.

TableStructure.PNG

6). The following layout is a stripped back version of one I have within StudyPapers.

 

dataentrypage.PNG

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.

 

ExecuteSQL("SELECT COUNT(*)

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)

 

script.PNG

 

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.

 

Best Wishes

 

Gary

Outcomes