Being from a relational database background, I have worked around some of the features not in Knack that I had previously taken for granted.
But this example below will highglight the need for some form of conditional processing to reduce the unnecessary overhead of replicating data for every record.
The database is a list of veterans from WW2.
In this a percentage were captured and taken POW and imprisoned in Italy, Germany or Japan. Some were imprisoned in both Italy and Germany. And some escaped.
The database has significant field POW YES OR NO
So possible scenarios are :
ITALY ONLY ; ITALY AND ESCAPED ;
GERMANY ONLY ; GERMANY AND ESCAPED;
ITALY AND GERMANY; ITALY AND GERMANY AND ESCAPED;
JAPAN ONLY (no escaped as it is a specific subset of WW2 veterans)
To support this the datafile has the following fields:
**List of Italian POW Camps, **
List of Germany POW camps &
List of Japanese POW locations
Escapee
The requirement is to display a combined data card combining “text” with the data to show a meaningful result. Such that the data card for a man held prisoner in Italy only and also not an escapee doesnt have needless words relative to Italy & Germany, Germany, Japan or an Escapee.
So the logic evaluation (and I won’t list them all) is as follows:
ITALY ONLY
Italian POW Camp is not blank
German POW Camp is blank
Japan POW Camp is blank
Escapee is blank
GERMANY ONLY
German POW Camp is not blank
Italian POW Camp is blank
Japan POW Camp is blank
Escapee is blank
etc.,
I have achieved this result BUT to do so means having 7 different FIELD DEFINTIONS within the database as text equations. Subsequently that means having 7 fields of data stored in the database 6 of which are not applicable AND MORE IMPORTANTLY 7 fields of data that do not apply to many of the records anyway.
The way it was achieved and made available was by “screen based rules” at the time of running with re-creation on every screen of the same conditional rules (7 in all).
So what I am looking for is the equivalent of either nested IF -THEN or perhaps an SQL Case Statement. Essentially we should ONLY be storing data IF THE GUY WAS A POW and as the necessary data is already in fields within his record, we should be able to store one confirmed ouput result instead of 7 and particularly not storing data when the intial qualification clearly establishes whether there is a need to have data in the text equation in the first instance.
As I see it this would be best implemented at the field defintion level rather than anywhere else. A simplistic version though lengthy would be along these lines.
CASE POW IS YES
CASE ITALIAN not ESCAPEE NOT GERMANY NOT JAPAN “Italian Text string”
CASE NO
END CASE
This is not the correct implenetation of CASE but just my thoughts of how it would need to be implemented. So the field defintion would evaluate whether it needs to be looked at and text stored in that field. If not don’t store anything.
So the 7 field definitions would determine whether text is stored in the field and logically only 1 of the 7 would succeed for a POW.
Then on the screen we look at “is the field blank or not”. If not then display it.
I hope this is understandable by all.
As mentioned I have achieved the goal but it’s long winded and somewhat precarious and invlves populating fields unnecessarily within the database. And since storage space is something we are charged for a highly desirable problem that requires a solution !
