Adding Table Fields

Important consideration when designing your database table structure!

Database table structure can directly impact your Grid Table Report. When creating your grid table reports, be reminded that query fields are NOT re-usable. Hence, once they are mapped to grid table columns, they will disappear from the query field list. In a grid table report, you have the option to include embedded Hyperlinks, Add Button, Edit Button and Delete button and they each require unique or index field to work, so if youre planning to have all these datatypes in your grid report, you must create index field for each.

Note: Editing a table and its fields properties is not supported. Changing a table name or its field is as easy as deleting the table or its field and re-create with the correct name and properties.

Figure 1: Adding a Primary Key Field

The first field you will be adding to your new table is the Primary Key Field. Primary Key field ensure each record in the database table will be unique. Note, only the following data types can be used as Primary Key fields:

  • RANDOM ALPHA-NUMERIC - System Random Generated Numbers.
  • NUMBERS(0-9) - Support Auto-Numbering or Manual Assignment.
  • ALPHA-NUMERIC(A-Z,0-9) - Support Manual Assignment ONLY.
  • Figure 2: RANDOM ALPHA-NUMERIC Primary Key Field

    Of the three (3) data types above, RANDOM ALPHA-NUMERIC is the most preferred data type to use as a Primary Key. It is preferred for it's security as it is randomly generated thus, is difficult to guess what's the next record ID will be.

    Figure 3: NUMBERS(0-9) Primary Key Field

    With data type NUMBERS(0-9), you have the option to choose Auto Numbering if you want the system to number each record in your table or Manual Assignment. If Manual Assignment is used, your Data Entry users must ensure to keep each record unique and this can be a hassle. Most often Auto Numbering is preferred unless you have a special case that you will need manual assignment.

    Figure 4: ALPHA-NUMERIC(A-Z,0-9) Primary Key Field

    When using ALPHA-NUMERIC(A-Z,0-9) data type as a Primary Key, Data Entry User must ensure each entry is unique or will result in conflict keys in your table. Usually, this is the least preferred data type of the (3) to use as Primary Key unless you have a special requirement that requires manual assignment of key values. To use ALPHA-NUMERIC (A-Z,0-9) data type, you must know before hand the maximum length the key value can be so you can assigned enough data length to the field. The maximum length for ALPHA-NUMERIC is 255 characters.

    Figure 5: Adding Foreign Key Fields (Optional)

    A foreign key (or collection of foreign keys) defined in a table refers to the primary keys in the other tables. To add a foreign key, select a primary key from the listed tables to use as a foreign key. Repeat adding if you want multiple foreign keys in your table. Notice, only created tables (written to database) primary keys will available for selection.

    Figure 6: Adding the Rest of the table Fields

    Once you added a Primary Key field to your table, you can continue to add the rest of your table fields. Apparutas Database Table support the following field Data Types to handle all of your data storage requirements:

  • RANDOM ALPHA-NUMERIC - System Random Generated Numbers.
  • NUMBERS(0-9)
  • DECIMAL NUMBERS(0.00)
  • ALPHA-NUMERIC(A-Z,0-9)
  • TEXT(PARAGRAPH)
  • IMAGE - for storing uploaded image data.
  • FILE-WRITE - for writing Form Text Area data as Text or HTML file.
  • FILE-UPLOAD - for storing uploaded Text or Document file data.
  • USER-ID - for storing the Logged In User Identification.
  • TIMESTAMP - for storing User TimeStamp.
  • Figure 7: RANDOM ALPHA-NUMERIC Data Type field

    RANDOM ALPHA-NUMERIC data type is the system randomly generated alpha-numeric. You might ask why and in what situation will I need this type of field apart from it's role as a Primary Key? Database Table fields are NOT re-usable, meaning they won't be available within the same module once they are mapped to a form element or a grid table column. So if youre creating an app where you will need to include hyperlinks, add,edit and delete buttons, each will need its own unique field and the Primary Key alone is insufficient so you will need to create unique field for each of the elements or components.
    Example, in your Grid Table, you may want include special fields like add, edit and delete options and each need a unique field since fields are not re-usable in Grid Tables.

    Figure 8: NUMBERS(0-9) Data Type field

    NUMBERS(0-9) data type is strictly a numeric field. It is use for storing numeric data (0-9). However, you must know before hand the largest number your data can be so you can assigned enough digit length to the field. The maximum length for NUMBERS is 9 digits.

    Figure 9: DECIMAL NUMBERS(0.00) Data Type field

    DECIMAL NUMBERS(0.00) data type handles numbers with trailing decimal values like currencies and other numbers that deals with decimals values. However, you must know before hand the largest number your data can be so you can assigned enough digit length to the field. The maximum length for DECIMAL NUMBERS is 9 digits and the maximum decimal precision is 4.

    Figure 10: ALPHA-NUMERIC(A-Z,0-9) Data Type field

    ALPHA-NUMERIC(A-Z,0-9) data type is the most flexible of all data types. If youre not picky about the kind of data going be stored in this field, it is always safe to use this data type. However, you must know before hand the maximum length your data can be so you can assigned enough data length to the field. The maximum length for ALPHA-NUMERIC is 255 characters.

    Figure 11: TEXT(PARAGRAPH) Data Type field

    TEXT(PARAGRAPH) data type handles data more than 255 characters long. TEXT data type is useful if you're developing Apps like blogs,forum,news etc.. where you will need store paragraphs of text. Unlike other fields, with TEXT you do not need to set any data length.

    Figure 12: IMAGE Data Type field

    IMAGE data type as it's name depicts stores the randomly generated uploaded image or picture name.

    Figure 13: FILE-WRITE Data Type field

    FILE-WRITE data type stores the randomly generated file name to enable writing of TextArea content posted by a FORM as Text or HTML file instead of inserting into a database table.

    Figure 14: FILE-UPLOAD Data Type field

    FILE-UPLOAD data type stores the randomly generated uploaded Ascii or document file name.

    Figure 15: USER-ID Data Type field

    USER-ID data type as it's name depicts stores the logged in user session ID information. It has a fixed data length of 25 alpha-numeric characters.

    Figure 16: TIMESTAMP Data Type field

    TIMESTAMP data type as it's name depicts stores Client date and time information. It has a fixed data length of 25 alpha-numeric characters.