Data Entry, Prompt Dialog Boxes And Writing to Arrays

I have been working on an SQL to generate a Prompt Dialog Box that can work in conjunction with scheduling or on its own. It enables data entry for manhole surveys including incoming and outgoing pipes. The idea arose from a query where a user was trying to enter data efficiently, from Manhole Survey Cards filled in manually in the field, without having to use the Dialog Box or having to modify the Property Editor.

Example Manhole Record Card

Example Manhole Record Card

Properties Dialog Box.

Properties Dialog Box.

Properties Editor.

Properties Editor.

For this I developed the following SQL on a Manhole Survey Object:

Manhole Survey SQL.

Manhole Survey SQL.

SELECT COUNT(*) into $COUNT;
IF $COUNT > 0;

LIST $StatusList = ‘PU’, ‘PR’, ’24’, ‘HD’, ‘WC’, ‘TR’, ’17’, ’18’, ‘AB’, ‘TC’, ‘HC’, ‘U’;
LIST $FunctionList = ‘F’, ‘S’, ‘C’, ‘T’, ‘O’, ‘U’;
LIST $NodeTypeList = ‘M’, ‘J’, ‘L’, ‘H’, ‘R’, ‘F’, ‘V’, ‘P’, ‘S’, ‘D’, ‘W’, ‘G’, ‘Z’, ‘C’, ‘Y’, ‘E’, ‘O’, ‘I’, ‘B’, ‘T’, ‘U’;
LIST $CoverShapeList = ‘S’, ‘R’, ‘T’, ‘D’, ‘C’, ‘O’, ‘L’, ‘U’;
LIST $DutyList = ‘L’, ‘M’, ‘H’, ‘U’;
LIST $GassesList = ‘A’, ‘B’, ‘C’, ‘O’;
LIST $ConstructionCodeList = ‘B’, ‘P’, ‘G’, ‘I’, ‘S’, ‘L’, ‘R’, ‘U’;
LIST $ChamberSoffitList = ‘S’, ‘T’, ‘A’, ‘C’, ‘N’, ‘U’;
LIST $PipeShapeList = ‘A’, ‘B’, ‘C’, ‘E’, ‘H’, ‘K’, ‘O’, ‘R’, ‘S’, ‘T’, ‘U’, ‘Z’;
LIST $PipeMaterialList = ‘AC’, ‘AK’, ‘BL’, ‘BR’, ‘CC’, ‘CI’, ‘CL’, ‘CO’, ‘CSB’, ‘CSU’, ‘DI’, ‘EP’, ‘FC’, ‘FRP’, ‘GI’, ‘GRC’, ‘GRP’, ‘MAC’, ‘MAR’, ‘PE’, ‘PF’, ‘PP’, ‘PS’, ‘PSC’, ‘PVC’, ‘RC’, ‘RPM’, ‘SI’, ‘SPC’, ‘ST’, ‘U’, ‘VC’, ‘X’, ‘XI’, ‘XP’, ‘Z’;
LIST $PipeDirectionList = ‘1’, ‘2’, ‘3’, ‘4’, ‘5’, ‘6’, ‘7’, ‘8’, ‘9’, ’10’, ’11’, ’12’;

PROMPT LINE $MyLocation ‘Enter Location:’ STRING;
PROMPT LINE $MySurveyDate ‘Enter Date:’ DATE;
PROMPT LINE $MyX ‘Enter x-coordinate (Easting):’;
PROMPT LINE $MyY ‘Enter y-coordinate (Northing):’;
PROMPT LINE $StatusListSelection ‘Enter Status:’ STRING LIST $StatusList;
PROMPT LINE $FunctionListSelection ‘Enter Function:’ STRING LIST $FunctionList;
PROMPT LINE $NodeTypeListSelection ‘Enter Node Type:’ STRING LIST $NodeTypeList;

PROMPT LINE $CoverShapeListSelection ‘Enter Cover Shape:’ STRING LIST $CoverShapeList;
PROMPT LINE $IsHinged ‘Hinged?’ BOOLEAN;
PROMPT LINE $IsLocked ‘Lock?’ BOOLEAN;
PROMPT LINE $DutyListSelection ‘Enter Cover Shape:’ STRING LIST $DutyList;
PROMPT LINE $CoverSize1 ‘Enter Cover Size 1 (mm):’ DP 0;
PROMPT LINE $CoverSize2 ‘Enter Cover Size 2 (mm):’ DP 0;
PROMPT LINE $ToxicAtmosphere ‘Toxic Atmosphere?:’ BOOLEAN;
PROMPT LINE $GasType ‘Enter Gast Type:’ STRING LIST $GassesList;
PROMPT LINE $Vermin ‘Evidence Of Vermin?:’ BOOLEAN;
PROMPT LINE $ConstructionCode ‘Enter Construction Code:’ STRING LIST $ConstructionCodeList;

PROMPT LINE $ShaftSideEntry ‘Shaft Side Entry?’ BOOLEAN;
PROMPT LINE $ShaftRegCourse ‘Shaft Reg Course?’ BOOLEAN;
PROMPT LINE $ShaftDepth ‘Shaft Depth (mm)’ DP 0;
PROMPT LINE $ShaftSize1 ‘Shaft Size 1 (mm)’ DP 0;
PROMPT LINE $ShaftSize2 ‘Shaft Size 2 (mm)’ DP 0;

PROMPT LINE $ChamberSoffit ‘Enter Chamber Soffit:’ STRING LIST $ChamberSoffitList;
PROMPT LINE $Steps ‘Enter Number Of Steps:’ DP 0;
PROMPT LINE $Ladders ‘Enter Ladders:’ DP 0;
PROMPT LINE $Landings ‘Enter Landings:’ DP 0;
PROMPT LINE $ChamberSize1 ‘Chamber Size 1 (mm)’ DP 0;
PROMPT LINE $ChamberSize2 ‘Chamber Size 2 (mm)’ DP 0;

PROMPT LINE $DepthOfFlow ‘Depth Of Flow (mm)’ DP 0;
PROMPT LINE $DepthOfSilt ‘Depth Of Silt (mm)’ DP 0;
PROMPT LINE $HeightSurcharge ‘Height Surcharge (mm)’ DP 0;

PROMPT LINE $CoverLevel ‘Cover Level (m AD)’ DP 3;

PROMPT LINE $Remarks ‘Remarks:’ STRING;

PROMPT LINE $Incoming ‘Enter Number Of INCOMING Pipes’ DP 0;
PROMPT LINE $Outgoing ‘Enter Number Of OUTGOING Pipes’ DP 0;

PROMPT TITLE ‘Manhole Survey Data Entry’;
PROMPT DISPLAY;

ENDIF;

SET location = $MyLocation;
SET survey_date = $MySurveyDate;
SET x = $MyX;
SET y = $MyY;
SET status = $StatusListSelection;
SET system_type = $FunctionListSelection;
SET node_type = $NodeTypeListSelection;

SET cover_shape = $CoverShapeListSelection;
SET cover_hinged = $IsHinged;
SET cover_locked = $IsLocked;
SET cover_duty = $DutyListSelection;
SET cover_dim = $CoverSize1;
SET cover_dim_2 = $CoverSize2;
SET toxic_atmosphere = $ToxicAtmosphere;
SET gas_type = $GasType;
SET vermin = $Vermin;
SET chamber_construction = $ConstructionCode;

SET side_entry = $ShaftSideEntry;
SET num_regulating = $ShaftRegCourse;
SET shaft_depth = $ShaftDepth;
SET shaft_dim = $ShaftSize1;
SET shaft_dim_2 = $ShaftSize2;

SET soffit_type = $ChamberSoffit;
SET num_steps = $Steps;
SET num_ladders = $Ladders;
SET num_landings = $Landings;
SET chamber_dim = $ChamberSize1;
SET chamber_dim_2 = $ChamberSize2;

SET flow_depth = $DepthOfFlow;
SET silt_depth = $DepthOfSilt;
SET surcharge_height = $HeightSurcharge;

SET cover_level = $CoverLevel;

SET notes = $Remarks;

LET $count_in = 0;

WHILE $count_in < $Incoming;

SELECT id INTO $Id_in;

PROMPT LINE $USR1 ‘Enter Upstream Reference:’ STRING;
PROMPT LINE $Shape ‘Enter Shape: ‘ STRING LIST $PipeShapeList;
PROMPT LINE $PipeSizeHeight ‘Enter Pipe Height (mm):’ DP 0;
PROMPT LINE $PipeSizeWidth ‘Enter Pipe Width (mm):’ DP 0;
PROMPT LINE $PipeDirection ‘Enter Direction’ STRING LIST $PipeDirectionList;
PROMPT LINE $PipeMaterial ‘Enter Pipe Material:’ STRING LIST $PipeMaterialList;
PROMPT LINE $PipeDepthFromCover ‘Enter Depth From Cover (m):’ DP 2;

PROMPT TITLE ‘Manhole Survey UPSTREAM Pipes Data Entry’ ;
PROMPT DISPLAY;

INSERT INTO [Manhole Survey].pipes_in (id, pipes_in.node_ref, pipes_in.shape, pipes_in.height, pipes_in.width, pipes_in.pipe_material, pipes_in.direction, pipes_in.depth_from_cover ) VALUES ($Id_in, $USR1, $Shape, $PipeSizeHeight, $PipeSizeWidth, $PipeDirection, $PipeMaterial, $PipeDepthFromCover);

LET $USR1 = NULL, $Shape = NULL, $PipeSizeHeight = NULL, $PipeSizeWidth = NULL, $PipeMaterial = NULL, $PipeDepthFromCover = NULL;

LET $count_in = $count_in + 1;
WEND;

LET $count_out = 0;

WHILE $count_out < $Outgoing;

SELECT id INTO $Id_out;

PROMPT LINE $DSR1 ‘Enter Downstream Reference:’ STRING;
PROMPT LINE $Shape_d ‘Enter Shape: ‘ STRING LIST $PipeShapeList;
PROMPT LINE $PipeSizeHeight_d ‘Enter Pipe Height (mm):’ DP 0;
PROMPT LINE $PipeSizeWidth_d ‘Enter Pipe Width (mm):’ DP 0;
PROMPT LINE $PipeDirection_d ‘Enter Direction’ STRING LIST $PipeDirectionList;
PROMPT LINE $PipeMaterial_d ‘Enter Pipe Material:’ STRING LIST $PipeMaterialList;
PROMPT LINE $PipeDepthFromCover_d ‘Enter Depth From Cover (m):’ DP 2;

PROMPT TITLE ‘Manhole Survey DOWNSTREAM Pipes Data Entry’ ;
PROMPT DISPLAY;

INSERT INTO [Manhole Survey].pipes_out (id, pipes_out.node_ref, pipes_out.shape, pipes_out.height, pipes_out.width, pipes_out.direction, pipes_out.pipe_material, pipes_out.depth_from_cover ) VALUES ($Id_out, $DSR1, $Shape_d, $PipeSizeHeight_d, $PipeSizeWidth_d, $PipeDirection_d, $PipeMaterial_d, $PipeDepthFromCover_d);

LET $DSR1 = NULL, $Shape_d = NULL, $PipeSizeHeight_d = NULL, $PipeSizeWidth_d = NULL, $PipeMaterial_d = NULL, $PipeDepthFromCover_d = NULL;

LET $count_out = $count_out + 1;
WEND;

The SQL Creates a PROMPT dialog box, which should already be familiar to those who follow my Blog posts. The Prompt dialog has been created to allow data entry being copied from the Manhole Record Card. If the order does not suit exactly, the SQL can be rejigged to suit. Fields can also be added by adding extra lines.

Prompt Dialog Box Generated by SQL.

Prompt Dialog Box Generated by SQL.

The interesting part in this is that a user can state a number of incoming and outgoing pipes.

Lines in Prompt Dialog Box where User Specified Incoming & Outgoing Pipes.

Lines in Prompt Dialog Box where User Specified Incoming & Outgoing Pipes.

This values are then used to generate subsequent PROMPT dialog boxes once ‘OK’ has been hit on the first PROMPT dialog box. A PROMPT for each incoming pipe and outgoing pipe is presented where the user enters the data for these.

Incoming and Outgoing PROMPT Dialog Box - Note Window Bar Heading Differences.

Incoming and Outgoing PROMPT Dialog Box – Note Window Bar Heading Differences.

The code block that generates these dialog boxes is:

For incoming pipes:

LET $count_in = 0;

WHILE $count_in < $Incoming;

SELECT id INTO $Id_in;

PROMPT LINE $USR1 ‘Enter Upstream Reference:’ STRING;
PROMPT LINE $Shape ‘Enter Shape: ‘ STRING LIST $PipeShapeList;
PROMPT LINE $PipeSizeHeight ‘Enter Pipe Height (mm):’ DP 0;
PROMPT LINE $PipeSizeWidth ‘Enter Pipe Width (mm):’ DP 0;
PROMPT LINE $PipeDirection ‘Enter Direction’ STRING LIST $PipeDirectionList;
PROMPT LINE $PipeMaterial ‘Enter Pipe Material:’ STRING LIST $PipeMaterialList;
PROMPT LINE $PipeDepthFromCover ‘Enter Depth From Cover (m):’ DP 2;

PROMPT TITLE ‘Manhole Survey UPSTREAM Pipes Data Entry’ ;
PROMPT DISPLAY;

INSERT INTO [Manhole Survey].pipes_in (id, pipes_in.node_ref, pipes_in.shape, pipes_in.height, pipes_in.width, pipes_in.pipe_material, pipes_in.direction, pipes_in.depth_from_cover ) VALUES ($Id_in, $USR1, $Shape, $PipeSizeHeight, $PipeSizeWidth, $PipeDirection, $PipeMaterial, $PipeDepthFromCover);

LET $USR1 = NULL, $Shape = NULL, $PipeSizeHeight = NULL, $PipeSizeWidth = NULL, $PipeMaterial = NULL, $PipeDepthFromCover = NULL;

LET $count_in = $count_in + 1;
WEND;

… and for Outgoing Pipes.

LET $count_out = 0;

WHILE $count_out < $Outgoing;

SELECT id INTO $Id_out;

PROMPT LINE $DSR1 ‘Enter Downstream Reference:’ STRING;
PROMPT LINE $Shape_d ‘Enter Shape: ‘ STRING LIST $PipeShapeList;
PROMPT LINE $PipeSizeHeight_d ‘Enter Pipe Height (mm):’ DP 0;
PROMPT LINE $PipeSizeWidth_d ‘Enter Pipe Width (mm):’ DP 0;
PROMPT LINE $PipeDirection_d ‘Enter Direction’ STRING LIST $PipeDirectionList;
PROMPT LINE $PipeMaterial_d ‘Enter Pipe Material:’ STRING LIST $PipeMaterialList;
PROMPT LINE $PipeDepthFromCover_d ‘Enter Depth From Cover (m):’ DP 2;

PROMPT TITLE ‘Manhole Survey DOWNSTREAM Pipes Data Entry’ ;
PROMPT DISPLAY;

INSERT INTO [Manhole Survey].pipes_out (id, pipes_out.node_ref, pipes_out.shape, pipes_out.height, pipes_out.width, pipes_out.direction, pipes_out.pipe_material, pipes_out.depth_from_cover ) VALUES ($Id_out, $DSR1, $Shape_d, $PipeSizeHeight_d, $PipeSizeWidth_d, $PipeDirection_d, $PipeMaterial_d, $PipeDepthFromCover_d);

LET $DSR1 = NULL, $Shape_d = NULL, $PipeSizeHeight_d = NULL, $PipeSizeWidth_d = NULL, $PipeMaterial_d = NULL, $PipeDepthFromCover_d = NULL;

LET $count_out = $count_out + 1;
WEND

The SQL takes the values entered in the lines of the first dialog box and loops through each time creating a new PROMPT dialog box, awaiting data entry and the hitting of the ‘OK’ button and writing to the Array using the INSERT INTO keyword. This is the method to write into Arrays as SET will not work. So it is possible to insert objects into the network and insert rows into arrays.

To add individual objects the syntax is:
INSERT INTO

(field1, field 2,… fieldn) VALUES (val1,val2,… valn) INSERT INTO node (node_id,x,y) VALUES (‘N1’, 123, 456)

The number of values must match the number of fields. All the values can be scalar expressions e.g. scalar variables, expressing including scalar variables.

To add individual rows into an individual object’s structured array the syntax is:
INSERT INTO

. (field1,field,2… fieldn) VALUES (val1,val2,… valn)

The field names must be either:

a. key field name of the table
b. the array name followed by a . followed by a field in the array

All key field names of the table must be specified and objects into which lines in the structure array are being inserted must exist.

To insert values into a table from a SELECT statement, the syntax is:
INSERT INTO

(field1,field2,… fieldn) SELECT

To insert values into a structure array from a SELECT statement the syntax is:
INSERT INTO

. SELECT

The number of items being selected must match the number of fields being set in the INSERT.

🙂

Share this post!

    About Idris Nujjoo

    Idris is the Product Sector Leader for InfoNet and InfoMaster and has been working for Innovyze (and its earlier incarnations) since 2001 and specifically working on InfoNet and Asset Management for the past decade.
    This entry was posted in InfoNet. Bookmark the permalink.