D2M2_010.TPW

#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! File Name:   D2M2_010.tpw
#! Purpose:     METABASE: Options outputed to SQL 
#! Author:      Copyright © 1999-2999 by Roberto Artigas Jr
#!              All rights reserved world wide.
#! Changes:
#! 2020.07.19.SUN:      Roberto Artigas
#!  * Added something simple: #GROUP(%GetFieldType,*%ThisType,%pType,%pMemo,%pPlaces)
#! 2020.05.21.THU:      Roberto Artigas
#!  *   Enhancements to the field width calculation for list boxes (Clarion, Noyantis)
#! 2020.02.26.WED:      Roberto Artigas
#!  *   Added %IsThisTimeStampName for the Capesoft defined timestamp field to be translated to SQL side field name.
#! 2019.11.20.WED:      Roberto Artigas
#!  *   Added logic to handle EncryptionVersion for MyTable, JFiles, Cryptonite
#! 2019.07.24:          Roberto Artigas
#!  *   Separated the SQL default options #GROUP to its own source file.
#! 2017.09.29           Roberto Artigas
#!  *   Creation of this generic structured template
#!---------------------------------------------------------------------
#!--------------------------------------------------------------------- 
#GROUP(%IsThisTimeStampName,*%UseThisName,%WhatFieldName)               #! RA.2020.02.26.WED: Added 
#CASE(UPPER(%WhatFieldName))
#OF  ('TIMESTAMP')
  #SET(%UseThisName   , 'ts')
#OF  ('SERVERTIMESTAMP')
  #SET(%UseThisName   , 'sts')
#OF  ('DELETETIMESTAMP')
#OROF('DELETEDTIMESTAMP')
  #SET(%UseThisName   , 'dts')
#ELSE
  #SET(%UseThisName   , %WhatFieldName)
#ENDCASE
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! RA.2018.05.01: Check for SQL functions. 
#GROUP(%IsInitialFunctionSQL,*%pResult,%pFieldInitial)
  #SET(%pResult,FALSE)
  #SET(%nLoc1, 0)
  #SET(%nLoc2, INSTRING(')',%pFieldInitial,-1,LEN(CLIP(%pFieldInitial))))
  #SET(%nLoc1, %nLoc1 + %nLoc2)
  #SET(%nLoc2, INSTRING('(',%pFieldInitial,-1,LEN(CLIP(%pFieldInitial))))
  #SET(%nLoc1, %nLoc1 + %nLoc2)
  #IF(%nLoc1>0)
    #SET(%pResult,TRUE)
  #ENDIF
#IF(%TraceOption)
---- #GROUP(%%IsInitialFunctionSQL, %pResult, "%pFieldInitial")
#ENDIF  
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! RA.2019.07.24: Add ===SQL field type=== (%NFieldOpt2) being passed to this routine.
#! This might be necessary for some BACKENDs to CAST %NFieldOpt2 to the correct %NFieldOpt3
#! Example: PostgreSQL: TimeStamp TIMESTAMPTZ DEFAULT current_timestamp::TIMESTAMPTZ
#! This goes back to GUID tables and DISTRIBUTED data that gets syncronized accross locations.
#!---------------------------------------------------------------------------------------------
#! RA.2019.07.04: Some reseach into PostgreSQL options for date, time, user, guid [not tested in production!!!]
#! RA.2019.07.04: Some reseach into MySQL options for date, time, user, guid [not tested in production!!!]
#! RA.2018.06.09: Added the [USER] type backend SQL keyword.
#! RA.2018.05.07: Experimental ease of use coding. The goal is to provide
#! some equivalence of the DATE()/TIME() function in clarion in a form that
#! the backend understands. So creating the record from the backend provides
#! as much of a same or similar functionalility as the clarion functions do.
#! RA.2018.05.03: Some replacing based on the backend and field type.
#GROUP(%IsInitialFunctionReplaceSQL,*%pResult,*%pFunctionReplace,%pBaseName,%pFieldType,%pFieldInitial,%NFieldOpt3,%NFieldOpt2)
  #SET(%pResult           , FALSE)
  #SET(%pFunctionReplace  , '')
#IF(%TraceOption)
----
---- #GROUP(%%IsInitialFunctionReplaceSQL,"%pResult","%pFunctionReplace","%pBaseName","%pFieldType","%pFieldInitial","%NFieldOpt3","%NFieldOpt2")
#ENDIF
  #!
  #CASE(UPPER(%NFieldOpt3))
  #OF  ('[USER]')                                                               #! RA.2018.06.09.SAT: Added
    #CASE(%pBaseName)                                                           #! BACKEND EXCEPTIONS
    #OF  ('METABASE')
      #SET(%pFunctionReplace, '')
    #OF  ('MIMER')
      #SET(%pFunctionReplace, '')
    #OF  ('MSSQL')
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'current_user')
    #OF  ('POSTGRESQL')                                                         #! RA.2019.04.30
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'current_user')
    #OF  ('SQLANYWHERE')
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'current user')
    #OF  ('MYSQL')
      #SET(%pResult         , TRUE)                                             #! RA.2019.07.04: NEEDS TESTING !!!
      #SET(%pFunctionReplace, '( current_user() )')                             #! RA.2019.07.04: Signals a default function
    #OF  ('ORACLE')
      #SET(%pFunctionReplace, '')
    #OF  ('DB2')
      #SET(%pFunctionReplace, '')
    #ENDCASE                                                                    #! BACKEND EXCEPTIONS
  #OF  ('[DATE]')
    #CASE(%pBaseName)                                                           #! BACKEND EXCEPTIONS
    #OF  ('METABASE')
      #SET(%pFunctionReplace, '')
    #OF  ('MIMER')
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'current_date')
    #OF  ('MSSQL')
#!
#! RA.2020.03.34: https://docs.microsoft.com/en-us/sql/t-sql/functions/sysdatetime-transact-sql?view=sql-server-ver15
#!
#! SELECT SYSDATETIME() AS [SYSDATETIME()]  
#!    ,SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()]  
#!    ,SYSUTCDATETIME() AS [SYSUTCDATETIME()]  
#!    ,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]  
#!    ,GETDATE() AS [GETDATE()]  
#!    ,GETUTCDATE() AS [GETUTCDATE()];
#! 
#! /* Returned:  
#! SYSDATETIME()      2007-04-30 13:10:02.0474381  
#! SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00  
#! SYSUTCDATETIME()   2007-04-30 20:10:02.0474381  
#! CURRENT_TIMESTAMP  2007-04-30 13:10:02.047  
#! GETDATE()          2007-04-30 13:10:02.047  
#! GETUTCDATE()       2007-04-30 20:10:02.047  
#! */
#!
#!SELECT CONVERT (date, SYSDATETIME())  
#!    ,CONVERT (date, SYSDATETIMEOFFSET())  
#!    ,CONVERT (date, SYSUTCDATETIME())  
#!    ,CONVERT (date, CURRENT_TIMESTAMP)  
#!    ,CONVERT (date, GETDATE())  
#!    ,CONVERT (date, GETUTCDATE());
#!
#! /* All returned 2007-04-30 */  
#!
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'convert(date,sysdatetime())')                    #! 'convert(date,sysutcdatetime())'
    #OF  ('POSTGRESQL')                                                         #! RA.2019.04.30
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'current_date')
    #OF  ('SQLANYWHERE')
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'current date')                                   #! 'current server date'
    #OF  ('MYSQL')
      #SET(%pResult         , TRUE)                                             #! RA.2019.07.04: NEEDS TESTING !!!
      #SET(%pFunctionReplace, '( current_date() )')                             #! RA.2019.07.04: Signals a default function [CURRENT_DATE]
    #OF  ('ORACLE')
      #SET(%pFunctionReplace, '')
    #OF  ('DB2')
      #SET(%pFunctionReplace, '')
    #ENDCASE                                                                    #! BACKEND EXCEPTIONS
  #OF  ('[TIME]')
    #CASE(%pBaseName)                                                           #! BACKEND EXCEPTIONS
    #OF  ('METABASE')
      #SET(%pFunctionReplace, '')
    #OF  ('MIMER')
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'current_time')
    #OF  ('MSSQL')
#!
#! RA.2020.03.34: https://docs.microsoft.com/en-us/sql/t-sql/functions/sysdatetime-transact-sql?view=sql-server-ver15 
#!
#! SELECT SYSDATETIME()  
#!    ,SYSDATETIMEOFFSET()  
#!    ,SYSUTCDATETIME()  
#!    ,CURRENT_TIMESTAMP  
#!    ,GETDATE()  
#!    ,GETUTCDATE(); 
#! 
#! /* Returned:  
#! SYSDATETIME()      2007-04-30 13:10:02.0474381  
#! SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00  
#! SYSUTCDATETIME()   2007-04-30 20:10:02.0474381  
#! CURRENT_TIMESTAMP  2007-04-30 13:10:02.047  
#! GETDATE()          2007-04-30 13:10:02.047  
#! GETUTCDATE()       2007-04-30 20:10:02.047  
#! */
#!
#! SELECT CONVERT (time, SYSDATETIME())  
#!    ,CONVERT (time, SYSDATETIMEOFFSET())  
#!    ,CONVERT (time, SYSUTCDATETIME())  
#!    ,CONVERT (time, CURRENT_TIMESTAMP)  
#!    ,CONVERT (time, GETDATE())  
#!    ,CONVERT (time, GETUTCDATE());  
#!  
#! /* Returned  
#! SYSDATETIME()      13:18:45.3490361  
#! SYSDATETIMEOFFSET()13:18:45.3490361  
#! SYSUTCDATETIME()   20:18:45.3490361  
#! CURRENT_TIMESTAMP  13:18:45.3470000  
#! GETDATE()          13:18:45.3470000  
#! GETUTCDATE()       20:18:45.3470000  
#! */
#!
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'convert(time,sysdatetime())')                    #! 'convert(time,sysutcdatetime())'
    #OF  ('POSTGRESQL')                                                         #! RA.2019.04.30
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'current_time')
    #OF  ('SQLANYWHERE')
      #SET(%pResult         , TRUE)
      #SET(%pFunctionReplace, 'current time')                                   #! 'current server time'
    #OF  ('MYSQL')
      #SET(%pResult         , TRUE)                                             #! RA.2019.07.04: NEEDS TESTING !!!
      #SET(%pFunctionReplace, '( current_time() )')                             #! RA.2019.07.04: Signals a default function [CURRENT_TIME]
    #OF  ('ORACLE')
      #SET(%pFunctionReplace, '')
    #OF  ('DB2')
      #SET(%pFunctionReplace, '')
    #ENDCASE                                                                    #! BACKEND EXCEPTIONS
  #!
  #OF  ('[STAMP]')
    #CASE(%pBaseName)                                                           #! BACKEND EXCEPTIONS
    #OF  ('METABASE')
      #SET(%pFunctionReplace, '')
    #OF  ('MIMER')
      #SET(%pFunctionReplace, '')
    #OF  ('MSSQL')
      #SET(%pFunctionReplace, '')
    #! RA.2019.07.24: PostgreSQL: Casting needed to let the table create (me TIMESTAMPTZ DEFAULT current_timestamp::TIMESTAMPTZ)
    #!                   OPTIONS: TIMESTAMPTZ, TIMESTAMP (Any numeric is okay: BIGINT DEFAULT current_timestamp)
    #! The current_timestamp is for any time we update the record, including insert which is why we default and cast.
    #! For the server, delete time stamps we leave it to null (0), since those will be used when needed.
    #! FUN: SELECT current_timestamp; SELECT current_timestamp AT TIME ZONE 'UTC'; [See what each returns] 
    #OF  ('POSTGRESQL')
      #SET(%pResult         , TRUE)
      #IF(UPPER(%NFieldOpt2) = 'TIMESTAMPTZ')
        #SET(%pFunctionReplace, 'current_timestamp::' & %NFieldOpt2)            #! RA.2019.07.24: Added
      #ELSE
        #SET(%pFunctionReplace, 'current_timestamp')                            #! RA.2019.07.23: Added
      #ENDIF
    #OF  ('SQLANYWHERE')
#! ALTER FUNCTION "hdata"."sf_GetElapsedTimeUTC"()
#! returns real
#! not deterministic
#! begin
#!   declare "ans" real;
#!   set "ans" = (select "DATEDIFF"("millisecond","DATETIME"('1970-01-01 00:00:00.000'),current utc timestamp));
#!   return "ans"
#! end      
      #SET(%pFunctionReplace, '')
    #OF  ('MYSQL')
      #SET(%pFunctionReplace, '')
    #OF  ('ORACLE')
      #SET(%pFunctionReplace, '')
    #OF  ('DB2')
      #SET(%pFunctionReplace, '')
    #ENDCASE                                                                    #! BACKEND EXCEPTIONS
  #!
  #ENDCASE
  #!
  #! RA.2019.07.24: [GUIDPKEY], [GUID]
  #CASE(SUB(UPPER(%NFieldOpt3),1,5))
  #OF  ('[GUID')
    #CASE(%pBaseName)                                                           #! BACKEND EXCEPTIONS
    #OF  ('METABASE')
      #SET(%pFunctionReplace, '')
    #OF  ('MIMER')
      #SET(%pFunctionReplace, '')
    #OF  ('MSSQL')
      #SET(%pFunctionReplace, '')
    #! RA.2018.05.06: PostgreSQL has several options. DO WE IMPLEMEMT AT THIS TIME???
    #OF  ('POSTGRESQL')
#!
#! UUID type
#! Universally Unique Identifies (UUID) is a 128-bit quantity which is generated by an algorithm. 
#! It is very unlikely that the same identifier will be generated by another person in the world using the same algorithm. 
#! That's why for the distributed systems, these identifiers are an ideal choice as it offers uniqueness within a single database. 
#! A UUID is written as a group of UPPER-case hexadecimal digits, with various groups separated by hyphens.
#!
#! PostgreSQL has a native UUID data type which consumes 16 bytes of storage.. UUID is an ideal Data type for primary keys.
#!
#!
#! The data type uuid stores Universally Unique Identifiers (UUID) as defined by RFC 4122, ISO/IEC 9834-8:2005, and related standards. 
#! (Some systems refer to this data type as a globally unique identifier, or GUID, instead.) This identifier is a 128-bit quantity that 
#! is generated by an algorithm chosen to make it very unlikely that the same identifier will be generated by anyone else in the known 
#! universe using the same algorithm. Therefore, for distributed systems, these identifiers provide a better uniqueness guarantee than 
#! sequence generators, which are only unique within a single database.
#! 
#! A UUID is written as a sequence of UPPER-case hexadecimal digits, in several groups separated by hyphens, specifically a group of 8 digits 
#! followed by three groups of 4 digits followed by a group of 12 digits, for a total of 32 digits representing the 128 bits. 
#! An example of a UUID in this standard form is:
#! a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
#!
#! PostgreSQL also accepts the following alternative forms for input: use of upper-case digits, the standard format surrounded by braces, 
#! omitting some or all hyphens, adding a hyphen after any group of four digits. Examples are:
#! 
#! A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
#! {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
#! a0eebc999c0b4ef8bb6d6bb9bd380a11
#! a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
#! {a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}
#!
#! Output is always in the standard form.
#! 
#! PostgreSQL provides storage and comparison functions for UUIDs, but the core database does not include any function for generating UUIDs, 
#! because no single algorithm is well suited for every application. The uuid-ossp module provides functions that implement several standard algorithms. 
#! Alternatively, UUIDs could be generated by client applications or other libraries invoked through a server-side function.
#!
#! CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
#! Then:
#! 
#! SELECT uuid_generate_v4();
#! Note also that, once you installed the extension, PostgreSQL has an actual binary uuid type, with a length of 16 bytes. 
#! Working with the binary type is much faster than working with the text equivalent and it takes up less space. 
#! If you do need the string version, you can simply cast it to text:
#! 
#! SELECT uuid_generate_v4()::text;
    #!
    #! #OF  ('UUID')
    #!   #SET(%NFieldTSQL, 'UUID')
    #!   #SET(%NFieldTDef, 'UUID')                                   #! RA.2008.08.19
    #! #OF ('UUID1')
    #!   #SET(%NFieldTSQL, 'UUID DEFAULT uuid_generate_v1()')
    #!   #SET(%NFieldTDef, 'UUID')                                   #! RA.2008.08.19
    #! #OF ('UUID2')
    #!   #SET(%NFieldTSQL, 'UUID DEFAULT uuid_generate_v1mc()')
    #!   #SET(%NFieldTDef, 'UUID')                                   #! RA.2008.08.19
    #! #OF ('UUID3')
    #!   #SET(%NFieldTSQL, 'UUID DEFAULT uuid_generate_v4()')
    #!   #SET(%NFieldTDef, 'UUID')                                   #! RA.2008.08.19
    #!
      #!
      #! RA.2019.07.20: IF primary key (PKEY), THEN go and generate the function.
      #! IF NOT primary key, THEN is a link (FKEY) to something else.
      #!
#!---- %%optGenerateGUID = %optGenerateGUID ----
      #SET(%pResult             , TRUE)                                         #! RA.2019.07.04: NEEDS TESTING !!!
      #CASE(UPPER(%NFieldOpt3))
      #OF  ('[GUIDPKEY]')
        #IF(%optGenerateGUID)                                                   #! RA.2019.07.19: Added
          #SET(%pFunctionReplace, 'uuid_generate_v4()')                         #! RA.2019.07.04: Signals a default function
        #ELSE
          #SET(%pFunctionReplace, '<39><39>')                                   #! RA.2019.07.19: If '', TRIGGER generates GUID (StringTheory ???) 
        #ENDIF
      #OF  ('[GUID]')
          #SET(%pFunctionReplace, '<39><39>')                                   #! RA.2019.07.19: If '', SET by relationship 
      #ENDCASE
    #OF  ('SQLANYWHERE')
#! newid
#! Description
#! Generates human-readable, globally unique IDs (GUIDs) in two different formats, based on arguments you provide. 
#! The length of the human-readable format of the GUID value is either 32 bytes (with no dashes) or 36 bytes (with dashes).
#! 
#! Syntax
#! newid([optionflag])
#! Parameters
#! option flag
#! 0, or no value ? the GUID generated is human-readable (varchar), but does not include dashes. 
#!                  This argument, which is the default, is useful for converting values into varbinary.
#!
#! -1 ? the GUID generated is human-readable (varchar) and includes dashes.
#!
#! -0x0 ? returns the GUID as a varbinary.
#! 
#! Any other value for newid returns NULL.
#! 
#! Examples
#! Example 1
#! Creates a table with varchar columns 32 bytes long, then uses newid with no arguments with the insert statement:
#! 
#! create table t (UUID varchar(32))
#! go
#! insert into t values (newid())
#! insert into t values (newid())
#! go
#! select * from t
#! UUID
#! --------------------------------
#! f81d4fae7dec11d0a76500a0c91e6bf6 
#! 7cd5b7769df75cefe040800208254639
#! Example 2
#! Produces a GUID that includes dashes:
#! 
#! select newid(1)
#! ------------------------------------
#! b59462af-a55b-469d-a79f-1d6c3c1e19e3
#! Example 3
#! Creates a default that converts the GUID format without dashes to a varbinary(16) column:
#! 
#! create table t (UUID_VC varchar(32), UUID varbinary(16))
#! go
#! create default default_guid
#! as
#! strtobin(newid())
#! go
#! sp_bindefault default_guid, "t.UUID"
#! go
#! insert t (UUID_VC) values (newid())
#! go
#! Example 4
#! Returns a new GUID of type varbinary for every row that is returned from the query:
#! 
#! select newid(0x0) from sysobjects
      #SET(%pFunctionReplace, '')
    #OF  ('MYSQL')
#!
#! MySQL UUID vs. Auto-Increment INT as primary key
#!
#! Pros
#! Using UUID for a primary key  brings the following advantages:
#!
#! UUID values are unique across tables, databases, and even servers that allow you to merge rows from different databases or distribute databases across servers.
#! UUID values do not expose the information about your data so they are safer to use in a URL. For example, if a customer with id 10 accesses his account
#!      via http://www.example.com/customers/10/ URL, it is easy to guess that there is a customer 11, 12, etc., and this could be a target for an attack.
#! UUID values can be generated anywhere that avoid a round trip to the database server. It also simplifies logic in the application. For example, 
#!      to insert data into a parent table and child tables, you have to insert into the parent table first, get generated id and then insert data into the child tables.
#!      By using UUID, you can generate the primary key value of the parent table up front and insert rows into both parent and child tables at the same time within a transaction.
#!
#! Cons
#! Besides the advantages, UUID values also come with some disadvantages:
#!
#! Storing UUID values (16-bytes) takes more storage than integers (4-bytes) or even big integers(8-bytes).
#! Debugging seems to be  more difficult, imagine the expression WHERE id = 'df3b7cb7-6a95-11e7-8846-b05adad3f0ae' instead of WHERE id = 10
#! Using UUID values may cause performance issues due to their size and not being ordered.
#!
#! MySQL UUID solution
#! In MySQL, you can store UUID values in a compact format (BINARY) and display them in human-readable format (VARCHAR) with help of the following functions:
#!
#! UUID_TO_BIN
#! BIN_TO_UUID
#! IS_UUID
#!
#! Notice that UUID_TO_BIN(), BIN_TO_UUID(), and IS_UUID() functions are only available in MySQL 8.0 or later.
#! The UUID_TO_BIN() function converts a UUID from a human-readable format (VARCHAR) into a compact format (BINARY) format for storing
#! and the BIN_TO_UUID() function converts UUID from the compact format (BINARY)to human-readable format (VARCHAR) for displaying.
#!
#! The IS_UUID() function returns 1 if the argument is a valid string-format UUID. If the argument is not valid string format UUID, the IS_UUID function returns 0. 
#! In case the argument is NULL, the IS_UUID() function returns NULL.
#!
#! The following are the valid string-format UUID in MySQL:
#!
#! 1) aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
#! 2) aaaaaaaabbbbccccddddeeeeeeeeeeee
#! 3) {aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee}
#!
#! MySQL UUID example
#! Let?s take a look at an example of using UUID as the primary key.
#! 
#! The following statement creates a new table named customers:
#!
#! CREATE TABLE customers (
#!     id BINARY(16) PRIMARY KEY,
#!     name VARCHAR(255)
#! );
#! To insert UUID values into the id column, you use UUID() and UUID_TO_BIN() functions as follows:
#! 
#! INSERT INTO customers(id, name)
#! VALUES(UUID_TO_BIN(UUID()),'John Doe'),
#!       (UUID_TO_BIN(UUID()),'Will Smith'),
#!       (UUID_TO_BIN(UUID()),'Mary Jane');
#!
#! To query data from a UUID column, you use BIN_TO_UUID() function to convert binary format to human-readable format:
#!
#! SELECT 
#!    BIN_TO_UUID(id) id, 
#!    name
#! FROM
#!    customers;   
#!
      #SET(%pResult             , TRUE)                                         #! RA.2019.07.04: NEEDS TESTING !!!
      #CASE(UPPER(%NFieldOpt3))
      #OF  ('[GUIDPKEY]')
        #IF(%optGenerateGUID)                                                   #! RA.2019.07.19: Added
          #SET(%pFunctionReplace, '( UUID_TO_BIN(UUID()) )')                    #! RA.2019.07.04: Signals a default function
        #ELSE
          #SET(%pFunctionReplace, '<39><39>')                                   #! RA.2019.07.19: If '', TRIGGER generates GUID (StringTheory ???) 
        #ENDIF
      #OF  ('[GUID]')
          #SET(%pFunctionReplace, '<39><39>')                                   #! RA.2019.07.19: If '', SET by relationship 
      #ENDCASE
      #!SET(%pResult         , TRUE)                                            #! RA.2019.07.04: NEEDS TESTING !!!
      #!SET(%pFunctionReplace, '( UUID_TO_BIN(UUID()) )')                       #! RA.2019.07.04: Signals a default function
#!
    #OF  ('ORACLE')
      #SET(%pFunctionReplace, '')
    #OF  ('DB2')
      #SET(%pFunctionReplace, '')
    #ENDCASE                                                                    #! BACKEND EXCEPTIONS
  #ENDCASE
#IF(%TraceOption)
---- #GROUP(%%IsInitialFunctionReplaceSQL,"%pResult","%pFunctionReplace","%pBaseName","%pFieldType","%pFieldInitial","%NFieldOpt3","%NFieldOpt2")
----
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! RA.2019.11.21: This has to be inside a field loop for all fields to get something.
#! This will give you an alternative if there is nothing added to the external name attributes.
#! Results can be also used to replace the name external attributes in the dictionary.
#! Used as comments in a few places where I generate structures from the dictionary. 
#GROUP(%BuildFieldType,*%pTheField)                                             #! RA.2019.11.29: Added
#IF(NOT VAREXISTS(%TheFieldType))
  #DECLARE(%TheFieldType)
#ENDIF
#!
#CASE(UPPER(%FieldType))
#OF  ('DECIMAL')
#OROF('PDECIMAL')
  #SET(%pTheField , %FieldType & '(' & %FieldMemoSize & ',' & %FieldPlaces & ')')
#OF  ('STRING')
#OROF('MEMO')
#OROF('BLOB')
#OROF('CSTRING')
#OROF('PSTRING')
#OROF('USTRING')
  #SET(%pTheField , %FieldType & '(' & %FieldMemoSize & ')')
#ELSE
  #SET(%pTheField , %FieldType)
#ENDCASE
#SET(%pTheField, UPPER(%pTheField))
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#GROUP(%BuildAttributesFieldNAME,*%pTheName)                                    #! RA.2019.11.21: Added
#IF(NOT VAREXISTS(%TheName))
  #DECLARE(%TheName)
#ENDIF
#IF(NOT VAREXISTS(%TheFieldType))
  #DECLARE(%TheFieldType)
#ENDIF
#IF(NOT VAREXISTS(%TheFieldName))
  #DECLARE(%TheFieldName)
#ENDIF
#!
#! RA.2022.04.10: Remove sorounding spaces on both sides of the vertical bar 
  #SET(%TheName     , '')
  #SET(%TheName     , %TheName & ',NAME(<39>')
  #CASE(UPPER(%FieldID))
  #OF  ('TIMESTAMP')
    #SET(%TheName   , %TheName & 'ts')
  #OF  ('SERVERTIMESTAMP')
    #SET(%TheName   , %TheName & 'sts')
  #OF  ('DELETETIMESTAMP')
  #OROF('DELETEDTIMESTAMP')
    #SET(%TheName   , %TheName & 'dts')
  #ELSE
    #SET(%TheName   , %TheName & %FieldID)
  #ENDCASE
#!
  #!CASE(UPPER(%FieldType))
  #!OF  ('DECIMAL')
  #!OROF('PDECIMAL')
    #!SET(%TheFieldType , %FieldType & '(' & %FieldMemoSize & ',' & %FieldPlaces & ')')
  #!OF  ('STRING')
  #!OROF('MEMO')
  #!OROF('BLOB')
  #!OROF('CSTRING')
  #!OROF('PSTRING')
  #!OROF('USTRING')
    #!SET(%TheFieldType , %FieldType & '(' & %FieldMemoSize & ')')
  #!ELSE
    #!SET(%TheFieldType , %FieldType)
  #!ENDCASE
  #CALL(%BuildFieldType,%TheFieldType)
  #SET(%TheName     , %TheName & '|' & %TheFieldType)
#!
  #SET(%TheName     , %TheName & '|' & UPPER(%FieldDisplayPicture))
#!
#! RA.2021.04.10: Remove the ALT-& marker from text. Add back if needed.
#! RA.2020.05.06: Checkboxes do not have a 'PROMPT'. So fake a 'PROMPT' to allow for editing.
#! RA.2020.02.05: Add the field display prompt that way we can set the field edit prompt.
  #FOR(%FieldScreenControl)
    #IF(INSTRING('CHECK(',%FieldScreenControl,1,1) > 0)                 #! RA.2020.05.06: Added for checkboxes
      #SET(%TheFieldName, EXTRACT(%FieldScreenControl,'CHECK',1))
      #CALL(%ReplaceSubString,%TheFieldName,'&','')                     #! RA.2021.04.10: Remove the ALT-& from text.
      #!SET(%TheFieldName, 'PROMPT(' & %TheFieldName & ')')
      #SET(%TheFieldName, 'PR(' & %TheFieldName & ')')                  #! RA.2022.04.10: PROMPT -> PR
    #ENDIF
    #IF(INSTRING('PROMPT(',%FieldScreenControl,1,1) > 0)
      #SET(%TheFieldName, EXTRACT(%FieldScreenControl,'PROMPT',1))
      #CALL(%ReplaceSubString,%TheFieldName,'&','')                     #! RA.2021.04.10: Remove the ALT-& from text.
      #SET(%TheFieldName, 'PR(' & %TheFieldName & ')')                  #! RA.2022.04.10: PROMPT -> PR
    #ENDIF
  #ENDFOR
  #CALL(%ReplaceSubString,%TheFieldName,'&','')                         #! RA.2021.04.10: Remove the ALT-& from text.
  #CALL(%ReplaceSubString,%TheFieldName,'''','''''')
  #SET(%TheName , %TheName & '|' & %TheFieldName)
#! RA.2021.11.20.SAT: Add 'COLUMN' header to the extended name attribute
  #!SET(%TheFieldName, 'COLUMN(''''' & %FieldHeader &  ''''')')
  #SET(%TheFieldName, 'CO(''''' & %FieldHeader &  ''''')')              #! RA.2022.04.10: COLUMN -> CO
  #SET(%TheName , %TheName & '|' & %TheFieldName)
#!  
#! RA.2022.02.19.SAT: Move 'Salt' to the back. Let's keep the first 5 consistant.
  #CASE(UPPER(%FieldID))
  #OF  ('GUID')
    #SET(%TheName   , %TheName & '|Salt')
  #ENDCASE
#!
#!  #SET(%TheName   , %TheName & '|' & EXTRACT(%FieldQuickOptions,'TAB'))
#!
#! RA.2019.11.21: This contains the original name external attributes in the dictionary.
#! There might be a need to parse what currently exists there in the old external name attributes  
#! and add what does not exist into the new. Not needed at this time. But food for future thought.
#! 
  #SET(%TheFieldName, %FieldName)
#!
  #! RA.2020.02.05: Leave case intact here.
  #!SET(%TheName    , LOWER(%TheName) & '<39>)')
  #SET(%TheName     , %TheName & '<39>)')
#! [???] Add name string display for debugging
#! ---- %TheFieldName
#! ---- %TheName
#SET(%pTheName      , %TheName)
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! BUILD QUALIFIED FIELDS (Audit GUID consistentancy)
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!RA.2019.07.06: Pass %BaseName as a parameter
#GROUP(%BuildQualifiedFieldsGUID,%pBaseName)                                    #! RA.2019.07.06: Added
#!
#!FIX(%File, %NFilesName)
#FOR(%Field)                                                                    #! VALID FIELD: FOR
  #!-----------------------!
  #! Check the OPTIONS now !
  #!-----------------------!
  #INSERT(%GetFieldOptions)                                                     #! RASQL(,,,) for Fields
  #!--------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a field is processed. !
  #!--------------------------------------------------------------!
  #IF(SUB(%NFieldOpt1,1,1)='N')                                                 #! Ignore this field
    #CYCLE
  #ENDIF
  #!------------------------------------------------!
  #! Begin removal of types that are not processed. !
  #!------------------------------------------------!
  #IF(%FieldType='GROUP')
    #CYCLE
  #ENDIF
  #!
  #IF(%FieldType='END')
    #CYCLE
  #ENDIF
  #!----------------------------!
  #! We pass FIELD requirements !
  #!----------------------------!
  #!-- FieldStatement: %FieldID [%FieldName] %FieldUserOptions
  #! RA.2019.11.20: Table Count EncryptionVersion (MyTable, JFiles, CryptoNite)
  #! RA.2019.07.31: Think I will stay consistant and use TS, STS, and DTS accross the field names and externals.
  #! RA.2019.07.25: Rework this area to catch both ways that this can be matched. (Looked at SYNC template)
  #! RA.2019.07.06: Make sure field name is there and field option is there.
  #IF (UPPER(%FieldID) = 'ENCRYPTIONVERSION'      AND INSTRING('READONLY',UPPER(%FieldName),1,1) = 0)
    #SET(%NFilesCountEncrypt      , %NFilesCountEncrypt + 1)
  #ENDIF
  #IF (UPPER(%FieldID) = 'GUID'                   AND INSTRING('READONLY',UPPER(%FieldName),1,1) = 0)
    #SET(%NFilesCountGUID         , %NFilesCountGUID + 1)
  #ENDIF
  #IF (UPPER(%FieldID) = 'TIMESTAMP'              AND INSTRING('READONLY',UPPER(%FieldName),1,1) = 0)
    #SET(%NFilesCountTS           , %NFilesCountTS + 1)
  #ENDIF
  #IF (UPPER(%FieldID) = 'SERVERTIMESTAMP'        AND INSTRING('READONLY',UPPER(%FieldName),1,1) = 0)
    #SET(%NFilesCountSTS          , %NFilesCountSTS + 1)
  #ENDIF
  #IF (UPPER(%FieldID) = 'DELETETIMESTAMP'      AND INSTRING('READONLY',UPPER(%FieldName),1,1) = 0)
      #SET(%NFilesCountDTS        , %NFilesCountDTS + 1)
  #ENDIF
  #IF (UPPER(%FieldID) = 'DELETEDTIMESTAMP'     AND INSTRING('READONLY',UPPER(%FieldName),1,1) = 0)
      #SET(%NFilesCountDTS        , %NFilesCountDTS + 1)
  #ENDIF
  #IF (UPPER(%FieldID) = 'TS'                   AND INSTRING('READONLY',UPPER(%FieldName),1,1) = 0)
    #SET(%NFilesCountTS           , %NFilesCountTS + 1)
  #ENDIF
  #IF (UPPER(%FieldID) = 'STS'                  AND INSTRING('READONLY',UPPER(%FieldName),1,1) = 0)
    #SET(%NFilesCountSTS          , %NFilesCountSTS + 1)
  #ENDIF
  #IF (UPPER(%FieldID) = 'DTS'                  AND INSTRING('READONLY',UPPER(%FieldName),1,1) = 0)
      #SET(%NFilesCountDTS  , %NFilesCountDTS + 1)
  #ENDIF
  #! RA.2019.11.20: Table Option EncryptionVersion (MyTable, JFiles, CryptoNite) [Not used]  
  #! RA.2019.07.25: Count this a bit differently to check (Verify in %FieldUserOptions)
  #IF(EXTRACT(UPPER(%FieldUserOptions),'ENCRYPTIONVERSION'))
    #SET(%NFilesOptionEncrypt     , %NFilesOptionEncrypt + 1)
  #ENDIF
  #IF(EXTRACT(UPPER(%FieldUserOptions),'GUID'))
    #SET(%NFilesOptionGUID        , %NFilesOptionGUID + 1)
  #ENDIF
  #IF(EXTRACT(UPPER(%FieldUserOptions),'TIMESTAMP'))
    #SET(%NFilesOptionTS          , %NFilesOptionTS + 1)
  #ENDIF
  #IF(EXTRACT(UPPER(%FieldUserOptions),'SERVERTIMESTAMP'))
    #SET(%NFilesOptionSTS         , %NFilesOptionSTS + 1)
  #ENDIF
  #IF(EXTRACT(UPPER(%FieldUserOptions),'DELETETIMESTAMP'))
    #SET(%NFilesOptionDTS         , %NFilesOptionDTS + 1)
  #ENDIF
  #IF(EXTRACT(UPPER(%FieldUserOptions),'DELETEDTIMESTAMP'))
    #SET(%NFilesOptionDTS         , %NFilesOptionDTS + 1)
  #ENDIF
#ENDFOR
#!-----------------------------------------------------------------------------
#!-----------------------------------------------------------------------------
#GROUP(%CalculatePosition,*%FldFrom,*%FldTo,*%FldSize,%pFieldType,%pFieldMemoSize)
#! %%FldFrom=%FldFrom, %%FldTo=%FldTo, %%FldSize=%FldSize, %%pFieldType=%pFieldType, %%pFieldMemoSize=%pFieldMemoSize 
  #SET(%FldSize, %pFieldMemoSize)
#CASE(%pFieldType)
#OF  ('CSTRING')
  #SET(%FldSize, %pFieldMemoSize - 1)
#ENDCASE
  #SET(%FldTo  , %FldFrom + %FldSize)
  #!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! BUILD QUALIFIED KEYS (Audit GUID consistentancy)
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!RA.2019.07.26: Pass %BaseName as a parameter
#GROUP(%BuildQualifiedKeysGUID,%pBaseName)                                      #! RA.2019.07.26: Added
#IF(NOT VAREXISTS(%NKeyFieldSize))
  #DECLARE(%NKeyFieldSize)
#ENDIF
#IF(NOT VAREXISTS(%NKeyFieldEQ))
  #DECLARE(%NKeyFieldEQ)
#ENDIF
#!
#!FIX(%File, %NFilesName)
#FOR(%Key)                                                                      #! VALID Key: FOR
  #!-----------------------!
  #! Check the OPTIONS now !
  #!-----------------------!
  #INSERT(%GetKeyOptions)                                                       #! RASQL(,,,) for Keys
  #!------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a key is processed. !
  #!------------------------------------------------------------!
  #IF(SUB(%NKeyOpt1,1,1)='N')                                                   #! Ignore this key
    #CYCLE
  #ENDIF
  #!--------------------------!
  #! We pass KEY requirements !
  #!--------------------------!
  #!-- KeyStatement: %KeyID [%KeyName] %KeyUserOptions
  #IF (INSTRING('_PKEY',UPPER(%KeyID),1,1))
    #SET(%NFilesKeyGUID           , %NFilesKeyGUID + 1)
    #IF(%KeyPrimary)
      #FOR(%KeyField)
        #SET(%KeyFieldID,SUB(%KeyField, INSTRING(':',%KeyField,1,1) + 1, LEN(%KeyField) - INSTRING(':',%KeyField,1,1)))
      #SET(%KeyFieldID,UPPER(%KeyFieldID))
      #SET(%NKeyFieldSize, LEN(%KeyFieldID))
      #SET(%NKeyFieldEQ, (UPPER(%KeyFieldID) = 'GUID'))
#!          --0 %@N03@(%NKeyFieldSize): %NKeyFieldEQ %[20]KeyFieldID %[30]KeyField %NFilesFieldGUID
        #IF(UPPER(%KeyFieldID) = 'GUID')
          #SET(%NFilesFieldGUID   , %NFilesFieldGUID + 1)
        #ENDIF
#!          --1 %@N03@(%NKeyFieldSize): %NKeyFieldEQ %[20]KeyFieldID %[30]KeyField %NFilesFieldGUID
      #ENDFOR
    #ENDIF
  #ENDIF
  #IF (UPPER(%KeyID) = 'TIMESTAMPKEY')
    #SET(%NFilesKeyTS             , %NFilesKeyTS + 1)
    #FOR(%KeyField)
      #SET(%KeyFieldID,SUB(%KeyField, INSTRING(':',%KeyField,1,1) + 1, LEN(%KeyField) - INSTRING(':',%KeyField,1,1)))
      #SET(%KeyFieldID,UPPER(%KeyFieldID))
      #SET(%NKeyFieldSize, LEN(%KeyFieldID))
      #SET(%NKeyFieldEQ, (UPPER(%KeyFieldID) = 'TIMESTAMP'))
#!          --0 %@N03@(%NKeyFieldSize): %NKeyFieldEQ %[20]KeyFieldID %[30]KeyField %NFilesFieldTS
      #IF(UPPER(%KeyFieldID) = 'TIMESTAMP')
        #SET(%NFilesFieldTS       , %NFilesFieldTS + 1)
      #ENDIF
      #! RA.2019.07.31: Think I will stay consistant and use TS, STS, and DTS accross the field names and externals.
      #IF(UPPER(%KeyFieldID) = 'TS')
        #SET(%NFilesFieldTS       , %NFilesFieldTS + 1)
      #ENDIF
#!          --1 %@N03@(%NKeyFieldSize): %NKeyFieldEQ %[20]KeyFieldID %[30]KeyField %NFilesFieldTS
    #ENDFOR
  #ENDIF
  #IF (UPPER(%KeyID) = 'SERVERTIMESTAMPKEY')
    #SET(%NFilesKeySTS            , %NFilesKeySTS + 1)
    #FOR(%KeyField)
      #SET(%KeyFieldID,SUB(%KeyField, INSTRING(':',%KeyField,1,1) + 1, LEN(%KeyField) - INSTRING(':',%KeyField,1,1)))
      #SET(%KeyFieldID,UPPER(%KeyFieldID))
      #SET(%NKeyFieldSize, LEN(%KeyFieldID))
      #SET(%NKeyFieldEQ, (UPPER(%KeyFieldID) = 'SERVERTIMESTAMP'))
#!          --0 %@N03@(%NKeyFieldSize): %NKeyFieldEQ %[20]KeyFieldID %[30]KeyField %NFilesFieldSTS
      #IF(UPPER(%KeyFieldID) = 'SERVERTIMESTAMP')
        #SET(%NFilesFieldSTS      , %NFilesFieldSTS + 1)
      #ENDIF
      #! RA.2019.07.31: Think I will stay consistant and use TS, STS, and DTS accross the field names and externals.
      #IF(UPPER(%KeyFieldID) = 'STS')
        #SET(%NFilesFieldSTS      , %NFilesFieldSTS + 1)
      #ENDIF
#!          --1 %@N03@(%NKeyFieldSize): %NKeyFieldEQ %[20]KeyFieldID %[30]KeyField %NFilesFieldSTS
    #ENDFOR
  #ENDIF
  #IF (UPPER(%KeyID) = 'DELETETIMESTAMPKEY' OR UPPER(%KeyID) = 'DELETEDTIMESTAMPKEY')
    #SET(%NFilesKeyDTS            , %NFilesKeyDTS + 1)
    #FOR(%KeyField)
      #SET(%KeyFieldID,SUB(%KeyField, INSTRING(':',%KeyField,1,1) + 1, LEN(%KeyField) - INSTRING(':',%KeyField,1,1)))
      #SET(%KeyFieldID,UPPER(%KeyFieldID))
      #SET(%NKeyFieldSize, LEN(%KeyFieldID))
      #SET(%NKeyFieldEQ, (UPPER(%KeyFieldID) = 'DELETETIMESTAMP' OR UPPER(%KeyFieldID) = 'DELETEDTIMESTAMP'))
#!          --0 %@N03@(%NKeyFieldSize): %NKeyFieldEQ %[20]KeyFieldID %[30]KeyField %NFilesFieldDTS
      #IF(UPPER(%KeyFieldID) = 'DELETETIMESTAMP' OR UPPER(%KeyFieldID) = 'DELETEDTIMESTAMP')
        #SET(%NFilesFieldDTS      , %NFilesFieldDTS + 1)
      #ENDIF
      #! RA.2019.07.31: Think I will stay consistant and use TS, STS, and DTS accross the field names and externals.
      #IF(UPPER(%KeyFieldID) = 'DTS')
        #SET(%NFilesFieldDTS      , %NFilesFieldDTS + 1)
      #ENDIF
#!          --1 %@N03@(%NKeyFieldSize): %NKeyFieldEQ %[20]KeyFieldID %[30]KeyField %NFilesFieldDTS
    #ENDFOR
  #ENDIF
#ENDFOR
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! RA.2020.05.19.SUN: Removed the passed parameters %pDecLength and %pDecDigits. 
#! RA.2020.05.17.SUN: HOW, WHEN: Does this need to handle arrays? 
#! RA.2020.05.17.SUN: Will need more testing. Better if there is a bit extra space.
#! RA.2020.05.13.WED: Now calls the #GROUP that calculates length and decimals.
#! RA.2020.05.12.TUE: Decimal and digits should be already set when we come here.
#!---------------------------------------------------------------------
#! RA.2020.05.19.SUN: Used as replacement for the dictionary calculated list field display width. [???]
#! RA.2020.05.12.TUE: Used for the Report Control horizontal field widths. May have other uses. [???]
#!---------------------------------------------------------------------
#!GROUP(%CalcDspSize,*%pDspSize,%pFieldFormatWidth,%pFieldMemoSize,%pFieldType,%pHeaderLength,%pFieldPicture,%pDecLength,%pDecDigits,%pFieldID,%pFieldHeader)
#GROUP(%CalcDspSize,*%pDspSize,%pFieldFormatWidth,%pFieldMemoSize,%pFieldType,%pHeaderLength,%pFieldPicture,%pFieldID,%pFieldHeader)
#IF(NOT VAREXISTS(%zLength))
  #DECLARE(%zFixit10)
  #DECLARE(%zLength)
  #DECLARE(%zDigits)
  #DECLARE(%zLenDec)
  #DECLARE(%zDspSize)
  #DECLARE(%zAdjusts)
#ENDIF
#CASE(UPPER(%pFieldType))
#OF  ('CSTRING')
  #SET(%pFieldMemoSize , %pFieldMemoSize - 1)
#! --997-- %%pFieldMemoSize = %pFieldMemoSize
#ENDCASE
#SET(%zFixit10, 10)
#SET(%zAdjusts, CHOOSE((%pFieldMemoSize > %pHeaderLength),%pFieldMemoSize,%pHeaderLength))
#!SET(%zDspSize, %pFieldFormatWidth)
#SET(%zDspSize, (%zAdjusts * %zFixit10))
#CALL(%CalcLenDig,%zLength,%zDigits,%pFieldType,%pFieldPicture)
#! ANY, MEMO, BLOB, GROUP, BYTE, BOOL, BFLOAT4, BFLOAT8,
#! DATE, TIME, SHORT, USHORT, LONG, ULONG, SIGNED, UNSIGNED, 
#! REAL, SREAL, DECIMAL, PDECIMAL, STRING, PSTRING, ASTRING,
#! BSTRING, CSTRING, USTRING, WORD, DWORD, BOOLEAN. 
#CASE(%pFieldType)
#OF  ('STRING') 
#OROF('ASTRING')
#OROF('BSTRING')
#OROF('CSTRING')
#OROF('PSTRING')
#OROF('USTRING')
#OROF('MEMO')
#OROF('BLOB')
 #IF(%zAdjusts > 30)
    #SET(%zDspSize,   INT(30.0 * %zFixit10))
  #ENDIF
  #!
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zAdjusts))
  #ENDCASE
  #!
#OF  ('DATE')
#OROF('TIME')
  #SET(%zDspSize,     INT( 5.0 * %zFixit10))
#OF  ('SHORT')  
#OROF('USHORT')  
#OROF('WORD')  
  #!
  #CASE(%zLength)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zLength))
  #ELSE
    #SET(%zDspSize,   INT( 5.0 * %zFixit10))
  #ENDCASE
  #!
#OF  ('LONG')  
#OROF('ULONG')  
#OROF('DWORD')  
  #!
  #CASE(%zLength)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zLength))
  #ELSE
    #SET(%zDspSize,   INT( 6.0 * %zFixit10))
  #ENDCASE
  #!
#OF  ('REAL')  
#OROF('SREAL')  
#OROF('DECIMAL')  
#OROF('PDECIMAL')  
#OROF('BFLOAT4')  
#OROF('BFLOAT8')  
  #!
  #SET(%zAdjusts, %zLength + %zDigits)
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 1.0 * %zFixit10 * %zAdjusts))
  #ENDCASE
  #!
#OF  ('BYTE')  
#OROF('BOOL')  
#OROF('BOOLEAN')  
  #!
  #CASE(%zLength)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zLength))
  #ELSE
    #SET(%zDspSize,   INT( 1.0 * %zFixit10))
  #ENDCASE
  #!
#ENDCASE
#!
#IF(INSTRING('GUID',UPPER(%pFieldID),1,1) > 0)
  #SET(%zDspSize,     INT( 9.0 * %zFixit10))
#ENDIF
#!
#! --999-- %@N03@(%zDspSize)=( %@N03@(%pFieldFormatWidth) , %@N03@(%pFieldMemoSize) , %@S10@(%pFieldType) , %@N03@(%pHeaderLength) , %@S10@(%pFieldPicture) , %@N04@(%zLength) , %@N02@(%zDigits) , %@S40@(%pFieldID) , %@S20@(%pFieldHeader) )
#SET(%pDspSize, %zDspSize)
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! RA.2021.08.20.FRI: Need to handle masks: LONG=@P##-####P, LONG=@P###-##-####P [???]
#!
#! RA.2020.05.13.WED: Changed so it should handle all the Clarion types.
#! RA.2020.05.13.WED: Need to implement all the types as I get some testing fields.
#! RA.2020.05.12.TUE: Get the decimal length and digits from the mask.
#GROUP(%CalcLenDig,*%pLength,*%pDigits,%pFieldType,%pFieldPicture)
#IF(NOT VAREXISTS(%zLength))
  #DECLARE(%zLength)
  #DECLARE(%zDigits)
  #DECLARE(%zLenDec)
#ENDIF
#IF(NOT VAREXISTS(%zPos1))
  #DECLARE(%zPos1)
#ENDIF
#SET(%zLength, 0)
#SET(%zDigits, 0)
#!SET(%zLenDec, '')
#SET(%zLenDec,UPPER(%pFieldPicture))
#!
#! ANY, MEMO, BLOB, GROUP, BYTE, BOOL, BFLOAT4, BFLOAT8,
#! DATE, TIME, SHORT, USHORT, LONG, ULONG, SIGNED, UNSIGNED, 
#! REAL, SREAL, DECIMAL, PDECIMAL, STRING, PSTRING, ASTRING,
#! BSTRING, CSTRING, USTRING, WORD, DWORD, BOOLEAN. 
#!
#CASE(%pFieldType)
#OF  ('DECIMAL')
#OROF('PDECIMAL')
#OROF('DEC')
#OROF('NUMERIC')
#!
#OROF('SREAL')
#OROF('BFLOAT4')
#OROF('FLOAT')
#OROF('FLOAT4')
#!
#OROF('REAL')
#OROF('BFLOAT8')
#OROF('DOUBLE')
#OROF('FLOAT8')
#!
#OROF('SHORT')
#OROF('LONG')
  #CALL(%ReplaceSubString,%zLenDec,'-','')
  #CALL(%ReplaceSubString,%zLenDec,'_','')
  #CALL(%ReplaceSubString,%zLenDec,'$','')
  #CALL(%ReplaceSubString,%zLenDec,'@','')
  #CALL(%ReplaceSubString,%zLenDec,'N','')
  #CALL(%ReplaceSubString,%zLenDec,'B','')
  #CALL(%ReplaceSubString,%zLenDec,'(','')
  #CALL(%ReplaceSubString,%zLenDec,')','')
  #SET(%zPos1,INSTRING('.',%zLenDec,1,1))
  #IF(%zPos1 > 0)
    #SET(%zLength,SUB(%zLenDec,1,%zPos1-1))
    #SET(%zDigits,SUB(%zLenDec,%zPos1+1,LEN(%zLenDec)))
  #ELSE 
    #SET(%zLength,%zLenDec)
    #SET(%zDigits,0)
  #ENDIF
#OF  ('STRING')
#OROF('CSTRING')
  #CALL(%ReplaceSubString,%zLenDec,'@','')
  #CALL(%ReplaceSubString,%zLenDec,'S','')
  #SET(%zLength,%zLenDec)
  #SET(%zDigits,0)
#OF  ('DATE')
  #SET(%zLength,8)
  #SET(%zDigits,0)
#OF  ('TIME')
  #SET(%zLength,6)
  #SET(%zDigits,0)
#ELSE
! ---- DATA TYPE UNKNOWN ----  
! ---- %%pFieldType=%@S10@(%pFieldType) %%pFieldPicture=%@S20@(%pFieldPicture) %%zLenDec=%@S20@(%zLenDec), %%zLength=%@N04@(%zLength), %%zDigits=%@N02@(%zDigits) %%pFieldType=%@S10@(%pFieldType)
#ENDCASE
#!
#! RA.2021.08.10.FRI: Check from @P, @K. Use the length of the mask itself.
#! -00- %%pFieldType=%@S10@(%pFieldType) %%pFieldPicture=%@S20@(%pFieldPicture) %%zLenDec=%@S20@(%zLenDec), %%zLength=%@N04@(%zLength), %%zDigits=%@N02@(%zDigits) %%pFieldType=%@S10@(%pFieldType)
#IF(%zLength=0 AND %zDigits=0)
  #SET(%zLenDec,SUB(UPPER(%pFieldPicture),1,2))
#! -01- %%pFieldType=%@S10@(%pFieldType) %%pFieldPicture=%@S20@(%pFieldPicture) %%zLenDec=%@S20@(%zLenDec), %%zLength=%@N04@(%zLength), %%zDigits=%@N02@(%zDigits) %%pFieldType=%@S10@(%pFieldType)
  #CASE(%zLenDec)
  #OF  ('@P')
  #OROF('@K')
    #SET(%zLength,LEN(%pFieldPicture))
    #SET(%zDigits,0)  
#! -02- %%pFieldType=%@S10@(%pFieldType) %%pFieldPicture=%@S20@(%pFieldPicture) %%zLenDec=%@S20@(%zLenDec), %%zLength=%@N04@(%zLength), %%zDigits=%@N02@(%zDigits) %%pFieldType=%@S10@(%pFieldType)
  #ELSE
! ---- MASK TYPE UNKNOWN ----  
! ---- %%pFieldType=%@S10@(%pFieldType) %%pFieldPicture=%@S20@(%pFieldPicture) %%zLenDec=%@S20@(%zLenDec), %%zLength=%@N04@(%zLength), %%zDigits=%@N02@(%zDigits) %%pFieldType=%@S10@(%pFieldType)
  #ENDCASE
#ENDIF
#!
#IF(%zLength=0 AND %zDigits=0)
! -03- %%pFieldType=%@S10@(%pFieldType) %%pFieldPicture=%@S20@(%pFieldPicture) %%zLenDec=%@S20@(%zLenDec), %%zLength=%@N04@(%zLength), %%zDigits=%@N02@(%zDigits) %%pFieldType=%@S10@(%pFieldType)
#ENDIF
#!
#SET(%pLength, %zLength)
#SET(%pDigits, %zDigits)
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! RA.2020.05.21.THU: Create internal table of what fields are in which tab.
#! A '~' is applied to the front of fields that are not shown to the user.
#! IOW: They are for programmers and system usage. They sort to the end.
#!-------------------------------------------------------------------------------------------------!
#!                                    ----- PURPOSE -----                                          !
#! The List Box Formatter (IMHO) has for a long time been a time comsuming IDE editing task.       !
#! No matter how well you edit the dictionary (DCT) the resuling LIST control has to be edited.    !
#! The LIST control complain is, the short fields are too short, and the long fields are too long. !
#! This is the attempt at making it a bit easier at what the widths should be for LESS editing.    !
#! This handles the list field widths for: 1) Clarion, 2) Noyantis.                                !
#! While this by no means is perfect and will required more adjustments as use cases are found,    !
#! it produces a generated list that has a bit better spacing (IMHO), than the IDE generated one.  !
#! It DOES NOT eliminate editing, it should just reduce the editing that has to be done.           !
#! IF you find a better calculation to do this, THEN just add the calculation here.                !
#!-------------------------------------------------------------------------------------------------!
#GROUP(%d2c2aTabsFields)
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#PREPARE
#IF(NOT VAREXISTS(%d2c2aTabsFields))
#DECLARE(%d2c2aWork)
#DECLARE(%d2c2aTabsFields),MULTI,UNIQUE
#DECLARE(%d2c2aTab       ,%d2c2aTabsFields)     #! TAB name where this field is
#DECLARE(%d2c2aNum       ,%d2c2aTabsFields)     #! Number of the field (in the GROUP structure)
#DECLARE(%d2c2aSeq       ,%d2c2aTabsFields)     #! Display #??# of the field (recall style byte)
#DECLARE(%d2c2aFld       ,%d2c2aTabsFields)     #! Field name with prefix
#DECLARE(%d2c2aDSP       ,%d2c2aTabsFields)     #! Clarion DCT value
#DECLARE(%d2c2aType      ,%d2c2aTabsFields)     #! Field Type
#DECLARE(%d2c2aFMS       ,%d2c2aTabsFields)     #! Field Memo Size (DCT)
#DECLARE(%d2c2aDHL       ,%d2c2aTabsFields)     #! Length of the column header
#DECLARE(%d2c2aFDP       ,%d2c2aTabsFields)     #! Field display picture
#DECLARE(%d2c2aLen       ,%d2c2aTabsFields)     #! Length of display picture
#DECLARE(%d2c2aDig       ,%d2c2aTabsFields)     #! Digits of display picture
#!DECLARE(%d2c2aDSL1     ,%d2c2aTabsFields)     #! Length of the sort header (RA.2021.09.07.MON: Added Clarion)
#!DECLARE(%d2c2aDSL2     ,%d2c2aTabsFields)     #! Length of the sort header (RA.2021.09.07.MON: Added Noyantis)
#DECLARE(%d2c2aCalc1     ,%d2c2aTabsFields)     #! Calculation 1: Clarion
#DECLARE(%d2c2aCalc2     ,%d2c2aTabsFields)     #! Calculation 2: Noyantis
#!                                              #! Calculation 3: <next list field width calculation>
#END
#ENDPREPARE
#!
#FREE(%d2c2aTabsFields)
#SET(%NumberFields          , 1)
#SET(%CountFields           , 1)
#FOR(%Field)
  #CASE(%FieldType)
  #OF  ('GROUP')
  #OROF('END')
    #CYCLE
  #ENDCASE
  #INSERT(%UseQualifiedFieldsD2C)
  #IF(SUB(%NFieldOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #CASE(UPPER(EXTRACT(%FieldQuickOptions,'TAB',1)))
  #OF  ('INTERNAL')
  #OROF('WORK')
    #SET(%d2c2aWork, '~' & EXTRACT(%FieldQuickOptions,'TAB',1) & '-' & FORMAT(%CountFields,@N03) & '-' & %FieldID)
  #ELSE 
    #SET(%d2c2aWork,       EXTRACT(%FieldQuickOptions,'TAB',1) & '-' & FORMAT(%CountFields,@N03) & '-' & %FieldID) 
  #ENDCASE
#!
  #ADD(%d2c2aTabsFields     , %d2c2aWork)
  #SET(%d2c2aTab            , EXTRACT(%FieldQuickOptions,'TAB',1))          #! TAB name where this field is
  #SET(%d2c2aNum            , %NumberFields)                                #! Number of the field (in the GROUP structure)
  #SET(%d2c2aSeq            , %CountFields)                                 #! Display #??# of the field (recall style long)
  #SET(%d2c2aFld            , %Field)                                       #! Field name with prefix
  #SET(%d2c2aDSP            , %FieldFormatWidth)                            #! Clarion DCT value
  #SET(%d2c2aFMS            , %FieldMemoSize)                               #! Field Memo Size (DCT)
  #SET(%d2c2aType           , %FieldType)                                   #! Field Type
  #SET(%d2c2aDHL            , LEN(%FieldHeader))                            #! Length of the column header
  #SET(%d2c2aFDP            , %FieldDisplayPicture)                         #! Display Width: DCT original
  #CALL(%CalcLenDig         , %d2c2aLen, %d2c2aDig, %d2c2aType, %d2c2aFDP)  #! Calculate width length and decimal digits
  #!SET(%d2c2aDSL1          , 30)                                           #! Length of the sort header (RA.2021.09.07.MON: Added Clarion)
  #!SET(%d2c2aDSL2          , 30)                                           #! Length of the sort header (RA.2021.09.07.MON: Added Noyantis)
  #SET(%d2c2aCalc1          , %d2c2aDSP)                                    #! Display Width: Clarion
  #SET(%d2c2aCalc2          , %d2c2aDSP)                                    #! Display Width: Noyantis
#!
#! RA.2021.09.29.WED: Add pSortSize parameter for the additional size for the sort constant (Sort size constant inside #GROUP)
  #!CALL(%CalcWidthClarion2  , %d2c2aCalc1, %d2c2aDSL1, %d2c2aDSP, %d2c2aFMS, %d2c2aType, %d2c2aDHL, %d2c2aFDP, %Field, %FieldHeader)
  #!CALL(%CalcWidthNoyantis2 , %d2c2aCalc2, %d2c2aDSL2, %d2c2aDSP, %d2c2aFMS, %d2c2aType, %d2c2aDHL, %d2c2aFDP, %Field, %FieldHeader)
  #CALL(%CalcWidthClarion   , %d2c2aCalc1, %d2c2aDSP, %d2c2aFMS, %d2c2aType, %d2c2aDHL, %d2c2aFDP, %Field, %FieldHeader)
  #CALL(%CalcWidthNoyantis  , %d2c2aCalc2, %d2c2aDSP, %d2c2aFMS, %d2c2aType, %d2c2aDHL, %d2c2aFDP, %Field, %FieldHeader)
#!
  #SET(%NumberFields        , %NumberFields + 1)                            #! Field number since this can sort
  #SET(%CountFields         , %CountFields + 2)                             #! There is a style LONG marker in between fields
#ENDFOR
#!---------!
#! TESTING !
#!---------!
#FOR(%d2c2aTabsFields)
#! %%d2c2aTabsFields = %[50]d2c2aTabsFields ( %[10]d2c2aTab, %@N03@(%d2c2aNum), %@N03@(%d2c2aSeq), %[50]d2c2aFld, %@N04@(%d2c2aDSP), %@N04@(%d2c2aCalc1), %@N04@(%d2c2aCalc2) )
#ENDFOR
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! RA.2021.09.29.WED: Backed off the additional parameter for the sort constant. Constraint for building Clarion LIST control.
#! RA.2021.09.29.WED: Add pSortSize parameter for the additional size for the sort constant.
#! RA.2020.05.28: NOTE to SELF: Consider taking out the %pFieldMemoSize and just using the %pFieldPicture by itself.
#!GROUP(%CalcWidthClarion2,*%pDspSize, *%pSortSize,  %pFieldFormatWidth,%pFieldMemoSize,%pFieldType,%pHeaderLength,%pFieldPicture,%pFieldID,%pFieldHeader)
#GROUP(%CalcWidthClarion,*%pDspSize,%pFieldFormatWidth,%pFieldMemoSize,%pFieldType,%pHeaderLength,%pFieldPicture,%pFieldID,%pFieldHeader)
#IF(NOT VAREXISTS(%zLength))
  #DECLARE(%zFixit04)
  #DECLARE(%zLength)
  #DECLARE(%zDigits)
  #DECLARE(%zLenDec)
  #DECLARE(%zDspSize)
  #DECLARE(%zAdjusts)
  #DECLARE(%zHdrSize)
  #DECLARE(%zFldSize)
  #DECLARE(%zSortSize)
  #DECLARE(%zResSizeHdr)
  #DECLARE(%zResSizeFld)
  #DECLARE(%zResSize)
  #DECLARE(%zResCase)
#ENDIF
#SET(%zFixit04  , 04)
#SET(%zSortSize , 03)                         #! RA.2021.09.29.WED: HARD CODED!
#SET(%zSortSize , %zSortSize * %zFixit04)     #! RA.2021.09.29.WED: Calculate!
#!
#CASE(UPPER(%pFieldType))
#OF  ('CSTRING')
  #SET(%pFieldMemoSize , %pFieldMemoSize - 1)
#! --995-- %%pFieldMemoSize = %pFieldMemoSize
#ENDCASE
#IF(%pHeaderLength > %pFieldMemoSize)
  #SET(%pHeaderLength , %pHeaderLength + 2)
#! --997-- CLA: %pHeaderLength >= %pFieldMemoSize [%pFieldID , %pFieldHeader (%pHeaderLength)]
#ENDIF
#!
#SET(%zAdjusts, CHOOSE((%pFieldMemoSize > %pHeaderLength),%pFieldMemoSize,%pHeaderLength))
#!SET(%zDspSize, %pFieldFormatWidth)
#! --002--- (%%pFieldMemoSize=%pFieldMemoSize > %%pHeaderLength=%pHeaderLength) = %%zAdjusts = %zAdjusts
#SET(%zDspSize, (%zAdjusts * %zFixit04))
#CALL(%CalcLenDig,%zLength,%zDigits,%pFieldType,%pFieldPicture)
#!
#SET(%zResSize  , %zDspSize)                  #! RA.2021.10.02.SAT: Calculate!
#SET(%zHdrSize  , LEN(%pFieldHeader))         #! RA.2021.09.29.WED: Calculate!
#SET(%zFldSize  , LEN(%pFieldPicture))        #! RA.2021.09.29.WED: Calculate!
#SET(%zFldSize  , %zLength)                   #! RA.2021.09.29.WED: Calculate!
#SET(%zHdrSize  , %zHdrSize  * %zFixit04)     #! RA.2021.09.29.WED: Calculate!
#SET(%zFldSize  , %zFldSize  * %zFixit04)     #! RA.2021.09.29.WED: Calculate!
#!
#! ANY, MEMO, BLOB, GROUP, BYTE, BOOL, BFLOAT4, BFLOAT8,
#! DATE, TIME, SHORT, USHORT, LONG, ULONG, SIGNED, UNSIGNED, 
#! REAL, SREAL, DECIMAL, PDECIMAL, STRING, PSTRING, ASTRING,
#! BSTRING, CSTRING, USTRING, WORD, DWORD, BOOLEAN. 
#CASE(%pFieldType)
#OF  ('STRING') 
#OROF('ASTRING')
#OROF('BSTRING')
#OROF('CSTRING')
#OROF('PSTRING')
#OROF('USTRING')
#OROF('MEMO')
#OROF('BLOB')
  #!
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 2.5 * %zFixit04 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 1.0 * %zFixit04 * %zAdjusts))
  #ENDCASE
  #!
  #IF(%zAdjusts > 30)
    #SET(%zDspSize,   INT(30.0 * %zFixit04))
  #ENDIF
  #!
#OF  ('DATE')
#OROF('TIME')
  #SET(%zDspSize,     INT(11.0 * %zFixit04))
#OF  ('SHORT')  
#OROF('USHORT')  
#OROF('WORD')  
  #!
  #IF(%zLength = %pHeaderLength)
    #SET(%zAdjusts, %zLength)
  #ENDIF
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 2.5 * %zFixit04 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 1.0 * %zFixit04 * %zAdjusts))
  #ENDCASE
  #!
#OF  ('LONG')  
#OROF('ULONG')  
#OROF('DWORD')  
  #!
  #IF(%zLength = %pHeaderLength)
    #SET(%zAdjusts, %zLength)
  #ENDIF
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 2.5 * %zFixit04 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 1.0 * %zFixit04 * %zAdjusts))
  #ENDCASE
  #!
#OF  ('REAL')  
#OROF('SREAL')  
#OROF('DECIMAL')  
#OROF('PDECIMAL')  
#OROF('BFLOAT4')  
#OROF('BFLOAT8')  
  #!
  #SET(%zAdjusts, %zLength + %zDigits)
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 2.5 * %zFixit04 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 1.0 * %zFixit04 * %zAdjusts))
  #ENDCASE
  #!
#OF  ('BYTE')  
#OROF('BOOL')  
#OROF('BOOLEAN')  
  #!
  #IF(%zLength = %pHeaderLength)
    #SET(%zAdjusts, %zLength)
  #ENDIF
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 2.5 * %zFixit04 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 1.0 * %zFixit04 * %zAdjusts))
  #ENDCASE
  #!
#ENDCASE
#!
#IF(INSTRING('GUID',UPPER(%pFieldID),1,1) > 0)
  #SET(%zDspSize,     INT(22.0 * %zFixit04))
#ENDIF
#!
#! RA.2021.010.10.SUN: While the calculations also happen for Clarion they do to get used at this time.
#! Clarion used the sort key and is normally not adjusted as to the order of the fields and the size of the fields.
#! -------
#! --111--  %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N03@(%zHdrSize)-%@N03@(%zFldSize)=( %@N03@(%pFieldFormatWidth) , %@N03@(%pFieldMemoSize) , %@S10@(%pFieldType) , %@N03@(%pHeaderLength) , %@S20@(%pFieldPicture) , %@N04@(%zLength) , %@N02@(%zDigits) , %@S40@(%pFieldID) , %@S20@(%pFieldHeader) )
#SET(%zResSizeHdr , %zHdrSize + %zSortSize)
#SET(%zResSizeFld , %zFldSize + %zSortSize)
#! --111--A %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N04@(%zResSizeHdr)-%@N04@(%zHdrSize)-%@N04@(%zResSizeFld)-%@N04@(%zFldSize)
#!+++ RA.2021.10.07.THU: Adjust the header to accomodate for the sort literal. (^,v)
#!+++ Add sort size to the current display size. We have calculated a correcr size for the field vs header.
#SET(%zResCase    , 'A')
#!SET(%zResSize   , %zDspSize + %zSortSize)         
#! --111--B %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N04@(%zResSizeHdr)-%@N04@(%zHdrSize)-%@N04@(%zResSizeFld)-%@N04@(%zFldSize)
#IF(%zResSizeHdr  <= %zFldSize)
  #SET(%zResCase  , 'B')       
#ENDIF
#IF(%zResSize     <= %zResSizeHdr)
  #SET(%zResCase  , 'A')       
#ENDIF
#!
#CASE(%zResCase)
#OF('A')
  #SET(%zResSize  , %zDspSize + %zSortSize)
#OF('B')
  #SET(%zResSize  , %zFldSize)  
#ENDCASE
#! --111--C %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N04@(%zResSizeHdr)-%@N04@(%zHdrSize)-%@N04@(%zResSizeFld)-%@N04@(%zFldSize)
#!SET(%zDspSize    , %zResSize)   #! RA.2021.10.10.SUN: DO NOT ADJUST CLARION.
#! --111--  %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N03@(%zHdrSize)-%@N03@(%zFldSize)=( %@N03@(%pFieldFormatWidth) , %@N03@(%pFieldMemoSize) , %@S10@(%pFieldType) , %@N03@(%pHeaderLength) , %@S20@(%pFieldPicture) , %@N04@(%zLength) , %@N02@(%zDigits) , %@S40@(%pFieldID) , %@S20@(%pFieldHeader) )
#! -------
#SET(%pDspSize  , %zDspSize)
#!---------------------------------------------------------------------
#!--------------------------------------------------------------------- 
#! RA.2021.09.29.WED: Backed off the additional parameter for the sort constant. Constraint for building Clarion LIST control.
#! RA.2021.09.29.WED: Add pSortSize parameter for the additional size for the sort constant.
#! RA.2020.05.28: NOTE to SELF: Consider taking out the %pFieldMemoSize and just using the %pFieldPicture by itself.
#!GROUP(%CalcWidthNoyantis2,*%pDspSize, *%pSortSize,  %pFieldFormatWidth,%pFieldMemoSize,%pFieldType,%pHeaderLength,%pFieldPicture,%pFieldID,%pFieldHeader)
#GROUP(%CalcWidthNoyantis,*%pDspSize,%pFieldFormatWidth,%pFieldMemoSize,%pFieldType,%pHeaderLength,%pFieldPicture,%pFieldID,%pFieldHeader)
#IF(NOT VAREXISTS(%zLength))
  #DECLARE(%zFixit10)
  #DECLARE(%zLength)
  #DECLARE(%zDigits)
  #DECLARE(%zLenDec)
  #DECLARE(%zDspSize)
  #DECLARE(%zAdjusts)
  #DECLARE(%zHdrSize)
  #DECLARE(%zFldSize)
  #DECLARE(%zSortSize)
  #DECLARE(%zResSizeHdr)
  #DECLARE(%zResSizeFld)
  #DECLARE(%zResSize)
  #DECLARE(%zResCase)
#ENDIF
#SET(%zFixit10  , 10)
#SET(%zSortSize , 03)                         #! RA.2021.09.29.WED: HARD CODED!
#SET(%zSortSize , %zSortSize * %zFixit10)     #! RA.2021.09.29.WED: Calculate!
#!
#CASE(UPPER(%pFieldType))
#OF  ('CSTRING')
  #SET(%pFieldMemoSize , %pFieldMemoSize - 1)
#! --996-- %%pFieldMemoSize = %pFieldMemoSize
#ENDCASE
#IF(%pHeaderLength > %pFieldMemoSize)
  #SET(%pHeaderLength , %pHeaderLength + 1)
#! --997-- NYS: %pHeaderLength >= %pFieldMemoSize [%pFieldID , %pFieldHeader (%pHeaderLength)]
#ENDIF
#!
#SET(%zAdjusts, CHOOSE((%pFieldMemoSize > %pHeaderLength),%pFieldMemoSize,%pHeaderLength))
#SET(%zDspSize, (%zAdjusts * %zFixit10))
#CALL(%CalcLenDig,%zLength,%zDigits,%pFieldType,%pFieldPicture)
#!
#SET(%zResSize  , %zDspSize)                  #! RA.2021.10.02.SAT: Calculate!
#SET(%zHdrSize  , LEN(%pFieldHeader))         #! RA.2021.09.29.WED: Calculate!
#SET(%zFldSize  , LEN(%pFieldPicture))        #! RA.2021.09.29.WED: Calculate! 
#SET(%zFldSize  , %zLength)                   #! RA.2021.09.29.WED: Calculate!
#SET(%zHdrSize  , %zHdrSize  * %zFixit10)     #! RA.2021.09.29.WED: Calculate!
#SET(%zFldSize  , %zFldSize  * %zFixit10)     #! RA.2021.09.29.WED: Calculate!
#!
#! ANY, MEMO, BLOB, GROUP, BYTE, BOOL, BFLOAT4, BFLOAT8,
#! DATE, TIME, SHORT, USHORT, LONG, ULONG, SIGNED, UNSIGNED, 
#! REAL, SREAL, DECIMAL, PDECIMAL, STRING, PSTRING, ASTRING,
#! BSTRING, CSTRING, USTRING, WORD, DWORD, BOOLEAN. 
#CASE(%pFieldType)
#OF  ('STRING') 
#OROF('ASTRING')
#OROF('BSTRING')
#OROF('CSTRING')
#OROF('PSTRING')
#OROF('USTRING')
#OROF('MEMO')
#OROF('BLOB')
  #!
  #CASE(%zAdjusts)
  #OF  (1)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zAdjusts))
  #OF  (2)
    #SET(%zDspSize,   INT( 1.2 * %zFixit10 * %zAdjusts))
  #OF  (3)
    #SET(%zDspSize,   INT( 1.1 * %zFixit10 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 1.0 * %zFixit10 * %zAdjusts))
  #ENDCASE
  #!
  #IF(%zAdjusts > 20)
    #SET(%zDspSize,   INT(20.0 * %zFixit10))
  #ENDIF
  #!
#OF  ('DATE')
#OROF('TIME')
  #SET(%zDspSize,     INT( 7.0 * %zFixit10))
#OF  ('SHORT')  
#OROF('USHORT')  
#OROF('WORD')  
  #!
  #IF(%zLength = %pHeaderLength)
    #SET(%zAdjusts, %zLength)
  #ENDIF
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 0.7 * %zFixit10 * %zAdjusts))
  #ENDCASE
  #!
#OF  ('LONG')  
#OROF('ULONG')  
#OROF('DWORD')  
  #!
  #IF(%zLength = %pHeaderLength)
    #SET(%zAdjusts, %zLength)
  #ENDIF
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 0.7 * %zFixit10 * %zAdjusts))
  #ENDCASE
  #!
#OF  ('REAL')  
#OROF('SREAL')  
#OROF('DECIMAL')  
#OROF('PDECIMAL')  
#OROF('BFLOAT4')  
#OROF('BFLOAT8')  
  #!
  #SET(%zAdjusts, %zLength + %zDigits)
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 0.7 * %zFixit10 * %zAdjusts))
  #ENDCASE
  #!
#OF  ('BYTE')  
#OROF('BOOL')  
#OROF('BOOLEAN')  
  #!
  #IF(%zLength = %pHeaderLength)
    #SET(%zAdjusts, %zLength)
  #ENDIF
  #CASE(%zAdjusts)
  #OF  (1)
  #OROF(2)
  #OROF(3)
    #SET(%zDspSize,   INT( 1.5 * %zFixit10 * %zAdjusts))
  #ELSE
    #SET(%zDspSize,   INT( 1.0 * %zFixit10 * %zAdjusts))
  #ENDCASE
  #!
#ENDCASE
#!
#IF(INSTRING('GUID',UPPER(%pFieldID),1,1) > 0)
  #SET(%zDspSize,     INT(14.0 * %zFixit10))
#ENDIF
#!
#! -------
#! --222--  %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N03@(%zHdrSize)-%@N03@(%zFldSize)=( %@N03@(%pFieldFormatWidth) , %@N03@(%pFieldMemoSize) , %@S10@(%pFieldType) , %@N03@(%pHeaderLength) , %@S20@(%pFieldPicture) , %@N04@(%zLength) , %@N02@(%zDigits) , %@S40@(%pFieldID) , %@S20@(%pFieldHeader) )
#SET(%zResSizeHdr , %zHdrSize + %zSortSize)
#SET(%zResSizeFld , %zFldSize + %zSortSize)
#! --222--A %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N04@(%zResSizeHdr)-%@N04@(%zHdrSize)-%@N04@(%zResSizeFld)-%@N04@(%zFldSize)
#!+++ RA.2021.10.07.THU: Adjust the header to accomodate for the sort literal. (^,v)
#!+++ Add sort size to the current display size. We have calculated a correct size for the field vs header.
#SET(%zResCase    , 'A')
#!SET(%zResSize   , %zDspSize + %zSortSize)         
#! --222--B %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N04@(%zResSizeHdr)-%@N04@(%zHdrSize)-%@N04@(%zResSizeFld)-%@N04@(%zFldSize)
#IF(%zResSizeHdr  <= %zFldSize)
  #SET(%zResCase  , 'B')       
#ENDIF
#IF(%zResSize     <= %zResSizeHdr)
  #SET(%zResCase  , 'A')       
#ENDIF
#!
#CASE(%zResCase)
#OF('A')
  #SET(%zResSize  , %zDspSize + %zSortSize)
#OF('B')
  #SET(%zResSize  , %zFldSize)  
#ENDCASE
#! --222--C %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N04@(%zResSizeHdr)-%@N04@(%zHdrSize)-%@N04@(%zResSizeFld)-%@N04@(%zFldSize)
#SET(%zDspSize    , %zResSize)
#! --222--  %@N03@(%zDspSize)-%@N03@(%zResSize)-%@N03@(%zSortSize)-%@N03@(%zHdrSize)-%@N03@(%zFldSize)=( %@N03@(%pFieldFormatWidth) , %@N03@(%pFieldMemoSize) , %@S10@(%pFieldType) , %@N03@(%pHeaderLength) , %@S20@(%pFieldPicture) , %@N04@(%zLength) , %@N02@(%zDigits) , %@S40@(%pFieldID) , %@S20@(%pFieldHeader) )
#! -------
#SET(%pDspSize    , %zDspSize )
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! RA.2020.05.22.FRI: Implemented. See the commented items below.
#! RA.2020.05.21.THU: This needs generalization by passing parameters.
#! 
#!SET(%NextLine, '?' & %FilePrefix & '_ListQ')               #! pScreenID
#!SET(%WorkLine, %FilePrefix & '_oListClass')                #! pObjectID 
#!SET(%MoreLine, 'ListQ')                                    #! pQueueID
#!CALL(%ScreenListForClarion,%NextLine,%WorkLine,%MoreLine)
#!
#GROUP(%ScreenListForClarion,%pScreenID,%pObjectID,%pQueueuID)
#SET(%IndexFields,0)
#SET(%CountFields,1)
#FOR(%d2c2aTabsFields)
  #FIX(%Field, %d2c2aFld)
  #CASE(%FieldType)
  #OF  ('GROUP')
  #OROF('END')
    #CYCLE
  #ENDCASE
  #INSERT(%UseQualifiedFieldsD2C)
  #IF(SUB(%NFieldOpt1,1,1)='N')                                                   #! Ignore this field
    #CYCLE
  #ENDIF
  #SET(%IndexFields,%IndexFields+1)
  #SET(%BuildPart1, '')
  #IF(%IndexFields=1)
!-------------------------------------------------------------------------------------------------!
! This LIST structure is created to replace the one generated by the ABC templates.               !
! Save your generated window structure first, then cut and paste replacing your LIST structure.   !
!-------------------------------------------------------------------------------------------------!
#! ! LIST,AT(4,40,400,40),USE(?%FilePrefix_oList),HVSCROLL, |
! LIST,AT(4,40,400,40),USE(%pScreenID),HVSCROLL, |
    #SET(%BuildPart1, 'FORMAT(')
  #ENDIF
  #! RA.2020.05.21.THU: The fields in the GROUP get build in record order. They get a diffennt display order.
  #! Make sure the sequence number #???# points to the correct field. You grab the '%d2c2aSeq' to set it up.
  #!SET(%NextWork, 0)
  #!SET(%NextWork, %d2c2aCalc1)
  #!CALL(%CalcDspSize, %NextWork, %FieldFormatWidth, %FieldMemoSize, %FieldType, LEN(%FieldHeader), %FieldDisplayPicture, %Field, %FieldHeader) 
  #SET(%BuildPart2, %d2c2aCalc1 & %FieldJustType & '(' & %FieldJustIndent & ')|MY~' & %FieldHeader & '~' & %FieldPicture & '@#' & %d2c2aSeq & '#')   
  #!SET(%BuildPart2, %FieldFormatWidth & %FieldJustType & '(' & %FieldJustIndent & ')|MY~' & %FieldHeader & '~' & %FieldPicture & '@#' & %CountFields & '#')   
  #SET(%BuildPart1, %BuildPart1 & '''' & %BuildPart2 & '''')
  #IF(%IndexFields=ITEMS(%Field))
    #!SET(%NextWork, %FilePrefix & '_oListClass.ListQ')
    #SET(%NextWork, %pObjectID & '.' & %pQueueuID)
    #! RA.2020.05.31.SUN: Remove IMM flag from LIST box. 
    #!SET(%BuildPart1, %BuildPart1 & '),FROM(' & %NextWork & '),IMM, |')
    #SET(%BuildPart1, %BuildPart1 & '),FROM(' & %NextWork & '), |')
  #ELSE
    #SET(%BuildPart1, %BuildPart1 & ' & |')
  #ENDIF
!   %BuildPart1
  #SET(%CountFields,%CountFields+2) 
#ENDFOR
#!
#SET(%IndexFields,0)
#SET(%CountFields,1)
#FOR(%d2c2aTabsFields)
  #FIX(%Field, %d2c2aFld)
  #CASE(%FieldType)
  #OF  ('GROUP')
  #OROF('END')
    #CYCLE
  #ENDCASE
  #INSERT(%UseQualifiedFieldsD2C)
  #IF(SUB(%NFieldOpt1,1,1)='N')                                                   #! Ignore this field
    #CYCLE
  #ENDIF
  #SET(%IndexFields,%IndexFields+1)
  #SET(%BuildPart1, '')
  #IF(%IndexFields=1)
    #SET(%BuildPart1, '#FIELDS(')
  #ENDIF
  #SET(%BuildPart1, %BuildPart1 & %NextWork & '.' & %FieldID)
  #IF(%IndexFields=ITEMS(%Field))
    #SET(%BuildPart1, %BuildPart1 & ')')
  #ELSE
    #SET(%BuildPart1, %BuildPart1 & ', |')
  #ENDIF
!   %BuildPart1
  #SET(%CountFields,%CountFields+2) 
#ENDFOR
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! RA.2020.07.19.SUN: #GROUP(%GetFieldType,*%ThisType,%pType,%pMemo,%pPlaces)
#GROUP(%GetFieldType,*%ThisType,%pType,%pMemo,%pPlaces)
#CASE(%pType)
#OF  ('DECIMAL')
#OROF('PDECIMAL')
  #SET(%ThisType , %pType & '(' & %pMemo & ',' & %pPlaces & ')')
#OF  ('STRING')
#OROF('MEMO')
#OROF('BLOB')
#OROF('PSTRING')
#OROF('USTRING')
  #SET(%ThisType , %pType & '(' & %pMemo & ')')
#OF  ('CSTRING')
  #SET(%ThisType , %pType & '(' & %pMemo-1 & ')')
#ELSE
  #SET(%ThisType , %pType)
#ENDCASE
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------