18.6. Designing Screens

Reports, business logic, and data quick views, etc. are attached to screens.

Tip

System developers create Create, Read, Update, Delete (CRUD) screens on PostERP, a low-code information system framework, without programming.

18.6.1. Create Database Tables Constituting A Screen

Example:

--sales - master--
CREATE TABLE t_220 (
CONSTRAINT f0 FOREIGN KEY (f0) REFERENCES t45 (f1),
CONSTRAINT f3 FOREIGN KEY (f3) REFERENCES t_46 (f1),
CONSTRAINT f4 FOREIGN KEY (f4) REFERENCES t9 (f1),
CONSTRAINT f6 FOREIGN KEY (f6) REFERENCES t43 (f1),
CONSTRAINT f9 FOREIGN KEY (f9) REFERENCES t189 (f1),
f0	VARCHAR(15),			--company#
f1	INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,		--sale#
f2 	TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,		--sale date time
f3	INTEGER,				--customer#
f4	VARCHAR(3) NOT NULL,	--currency#
f5	NUMERIC(9,5) CHECK(f5 > 0) NOT NULL,	--exchange rate
f6	VARCHAR(12),	--salesperson#
f7	INTEGER,		--sales revenue received in advance#
f8	DATE,			--A/R due date
f9	SMALLINT,		--tax#
f99	VARCHAR(12)
);
--sales - item--
CREATE TABLE t_221 (
CONSTRAINT f1 FOREIGN KEY (f1) REFERENCES t_220 (f1) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT f2 FOREIGN KEY (f2) REFERENCES t_133 (f1),
CONSTRAINT f3 FOREIGN KEY (f3) REFERENCES t_130 (f1),
CHECK(verify_unit(f2,f3)) NOT VALID,
PRIMARY KEY (f1,f2),
f1	INTEGER,			   	--sale#
f2	VARCHAR(20),			--item#
f3	VARCHAR(3) NOT NULL,	--unit#
f99	VARCHAR(12)
);
			

18.6.1.1. Special Columns

  • PostERP uses column f0 to distinguish organizations (specific companies in business group, for example).

    If a table has column f0, all rows with value 1 in column f0 belong to company code 1. When designing SQL or functions, also take the values in this column into account in order to properly filter the desired rows. Also remember to add its constraint referencing to t45.f1 as foreign key.

  • Refer to Section 6.3 for information about record privileges. Column f99, if exists, keeps track the record owner of every table row. PostERP automatically fills this column with the account with which an user enters a new record. Only row owner is allowed to update/delete that row.

    If a table does not consists of column f99, then all account have write privilege on all rows in that table.

  • If a table consists of column f100 of data type BIGINT, that table automatically allows end users to attach any file to that table. Every row in that table accepts at most 32767 attachments.

    The default value of column f100 must be DEFAULT NEXTVAL('unique_int_seq')

    With column f100 added to a table, remember to also create a rule or trigger to automatically delete the associated attachment description row from table t61 when end users delete the row in question. Example:

    Example:

    --inventory items--
    CREATE TABLE t_133 (
    CONSTRAINT f2 FOREIGN KEY (f2) REFERENCES t_130 (f1) ON UPDATE CASCADE,
    f1	VARCHAR(20) PRIMARY KEY,	--item#
    f2	VARCHAR(3) NOT NULL,	--Finest Unit Of Measure (FUOM)
    f3	SMALLINT NOT NULL,	--valid number of decimals for quantity
    f99	VARCHAR(12),
    f100 BIGINT DEFAULT NEXTVAL('unique_int_seq')
    );
    
    CREATE OR REPLACE RULE r_133d AS ON DELETE TO t_133 DO ALSO DELETE FROM t61 WHERE f1=OLD.f100;
    						

    Also remember to implement required mechanism for the system to also remove the attachments from file system after end user deletes rows from table. The backup procedure must also take into account the attached files.

18.6.1.2. Note

Please follow these guidelines to avoid clash with Tera Rows's design:

  • When adding new columns to metadata tables, make sure to include _ as the new column names. For example, use these names for new columns: f_1, f_97, f_103.

  • Include _ in new table names. Examples:t_120, t_143, d_143.

  • Include _ in new view names. Examples: v_120, v1_43.

18.6.1.3. Note

  • Due to the limitation of desktop client program, use VARCHAR instead of TEXT. If client program yields "access violation" error or silently crashes, shorten the width of the displayed VARCHAR column to see if the error get "fixed". 2048 or fewer characters seems to be fine.

  • If a table's INSERT, UPDATE, and DELETE triggers want to post accounting journal but that table's primary key is not SERIAL, add a SERIAL column and use it as a surrogate key. See table t_33 in application# 2 and its triggers as an example.

  • Use column data type TIMESTAMPTZ instead of TIMESTAMP. TIMESTAMP is not standard (browser?) conformant and thus mulfunctions.

  • Do not allow NULL value in BOOLEAN columns in order to avoid ambiguous meanings and complicate business logic processing. Setting DEFAULT value for BOOLEAN data type is a good idea.

18.6.1.4. Note

If application server is running and the definition (colum data type, column name, new column, or dropped column, etc.) of any metadata table also known as dictionary is altered by DDL, then someone must connect to the same database and execute this command as soon as possible:

NOTIFY "DDLExecuted"
				

The purpose of this command is to notify application server to refresh its cache. Failing to do this in time, the thin clients using these tables may start to mulfunction and cause application server to log errors.

18.6.2. Setting Fields Properties

Figure 18-3. Setting Fields Properties

18.6.3. Assigning Underlying Tables to A Screen

Figure 18-4. Assigning Underlying Tables to A Screen

18.6.4. Completed Sample Screen

Figure 18-5. Completed Sample Screen

After you created a new screen, add it to menu for your end users to open.