I'm experimenting with stats-type functionality that reports back onto a dashboard how many public-facing information fields are empty. I'm probably not going about it the right way and suspect SQL would serve me better but have tried that and struggled as well.
I have two related TOs - Editorial and EditorialStatusStats. The field names in the latter match relevant ones in the former. Content is imported into the Editorial table in bulk but often data is missing from key fields.
The editable number, container, date and text type fields auto-enter the text "Incomplete" and post-import if data is missing this text is displayed.
I have two related problems that have driven me mad...again -
1). My first problem is I can't seem to find a solution for calculation fields. Below is the field content from Editorial that takes the names of related authors from the Author table and creates a horizontal display.
Substitute ( List (ContentAuthorRelatedEditorial::ConcatenatedAuthorNameSurnameInitials);"¶";",")
If this field is empty I want the content to say "Incomplete". I've tried Case and If but can't get anything to work. Using the Incomplete statement makes it easier for editors etc.
2). The objective of the EditorialStatusStats table is to eventually store data that can provide info to assist workflow - such as how many Editorial records are in complete and what the missing field data is, how many are live, how many fall into each category such as news etc and so on.
The first need is to flag records that have incomplete fields, how many there are and what those fields are.
Each of the fields in the EditorialStatusStats TO, whose name matches that of those in the Editorial TO, is a calculation field - EditorialStatusStats::Publication Date, Editorial::PublicationDate.
I have been trying to create functionality that records, in EditorialStatusStats, what fields are empty in Editorial. To do this I wanted to create a variable that pulled on the name of the EditorialStatusStats field using Let and then identified records where the relevant field in Editorial is either empty or contains "Incomplete".
I've tried Case and If within the calculation part of the Let Statement but can't seem to get the correct numerical results within EditorialStatusStats that represent the relevant field status/content within Editorial. I stopped at the following -
Let ([ $FieldName = GetFieldName( Self); Substitute ( $FieldName; "StatusStats::";"Editorial::"))
If(IsEmpty ( $FieldName ) or $FieldName="Incomplete";0;1)
The Let Statement, minus the square brackets and calculation works fine. As soon as I put the Square brackets back I get an error message saying "a number, text constant, field name or "(" is expected straight after the Substitute function. I have played with most of the options I understand but have gone blank.
If the square brackets are removed the message goes away but the data returned doesn't represent the field content in Editorial.
Any and all help vastly appreciated as always.