Data Field Types

Ninox supports several data types for the columns / fields of a table. There are four different kinds:

  • Data fields are filled in with data.
  • Formulas are used for automatically calculated results.
  • UI elements help with structuring the forms.
  • Table references allow you to relate tables to each other.

Data Fields

General options:

  • Required determines, if this field must be filled in. If there is no value entered, Ninox shows a warning.
  • Default value defines a value that is filled in automatically by Ninox, anytime you create a new record.
  • Width gives you the option to adjust the field width as shown in the form.

Additional options:

  • Trigger after update defines a trigger.
  • Display field only, if allows to hide a field in the form under certain conditions. This condition is defined as a formula.
    Set the formula to ‘No’, in order to hide the field permanently.
  • Global unique ID is generated automatically by Ninox. It is of internal use only. Although you are allowed to modify it, you should only do so for good reasons.

Text

Text fields are among the most important data types in Ninox. Use them to enter single-line or multi-line texts. Sometimes text fields even are the best way to record numeric values: this is true for numeric codes such as postal codes, item numbers and telephone numbers. (A code might start with a ‘0’ which a number field does not allow.)

Options:

  • Minimum length determines the minimum number of characters that must be entered in this field. If this limit is undercut, Ninox will give a warning.
  • Maximum length determines the maximum number of characters that can be entered in this field. If this limit is exceeded, Ninox will give a warning.

Text (formatted)

As opposed to normal text fields, the field type text (formatted) supports the input of longer texts and especially their formatting. This way you can use bold, italics and underlined multiline texts as well as headers and bullet points.

Text (formatted) is suited well to record descriptions and additional information. You shouldn’t use text (formatted) as a substitute for normal text fields, though, because there are some limitations compared to normal text fields:

  • The sorting order might be affected by text formatting.
  • There are no options for validation available.
  • Is not included in summaries or previews, for example in search results.

Therefore, use text (formatted) for fields like ‘Comments’, ‘Notes’, ‘Remarks’. In the case of fields like ‘Name’, ‘Title’, ‘Category’ we recommend that you use the normal text field.

Number

Number fields record number values and support their formatted output, for example as an amount in a currency.

Options:

  • Minimum determines the minimum value. If this limit is undercut, Ninox will give a warning.
  • Maximum determines the maximum value. If this value is exceeded, Ninox will give a warning.
  • Number format determines how the number will be displayed. Note: The format ’23 %’ adds a percent sign to the number. However, the number is not recognized as a percentage when doing calculations, so that you might have to divide by 100 to get the result you are looking for.

Yes / No

Yes / No-Fields have three states: Empty, Yes and No. They are shown in the form as a simple drop-down menu. If you want to avoid the state ‘Empty’, you should define the field as ‘required’ and choose a default value.

Choice

Using choice fields you can create self-defined value lists which appear as drop-down menus in the form. Instead of entering a word or number, you pick one of those values. This field type is suited well to record a state, for example ‘open’, ‘done’, ‘deleted’. In addition, you can set a background color and/or an icon for each value. Both are also displayed in table views helping you to keep track easily, for example, of the states of your to-dos.

If you re-name a value later, all data records will be updated accordingly.

You can change the order of the values using drag & drop, which will affect the order of values in dropdown menus.

Date

Date fields record the date, but no time. They are suited for birthdays, whole-day events, invoice dates etc.

Options:

  • Show in calendar defines, if the data records of this table are displayed in the calendar.
  • With color determines which color is used to display the data records. Use this option to be able to distinguish different entries quickly.
  • Anniversary Makes it possible to repeat an event each year, especially birthdays. Choose ‘Yes’ and the entry is displayed on that day independently of the year.

Time

Time fields record a time which is unrelated to any date.

Date + Time

Use Date + Time to record a time specification with date and hour in the same field.

Time Interval

A Time interval is the time between two points in time. A Time interval is the most precise method to measure time, because several days, hours and minutes are supported. If you want to register worked hours, for example, you could use Time interval to record the idle time.

If the time you want to measure is below 24 hours, it might make sense to ‘misapply’ an hour field, because the hour field is more user-friendly.

Appointment

Appointment fields record two points in time – start and end – in the same field. Also, appointments are shown in the week view. If your database includes a table with a date field, you can create new appointments directly in the calendar.

Image

Image fields let you insert photos from your camera or other images from your library into your records.

URL

With URL fields you can enter an internet address and call the web browser to open that address.

Email

Email fields let you enter email addresses and support sending emails directly
from Ninox.

Options:

  • Subject: you may enter a subject line that will be automatically added when hitting the email button on that email field. It even allows for embedding calculated values with formulas.
  • Body: as with subject, you may enter a default email body.

105 Gedanken zu „Data Field Types

  1. Hi Gang,

    One of the Ninox features I found most disappointing with was the built-in support for parsing dates entered via text. When entering a lot of records, being strict about the local date format (e.g., “Jun 2, 1961″) or using the calendar UI makes for a fairly cumbersome (even error-prone) experience. I decided to pair each Date field with a second Text field that I could write my own parsing code for.

    For example, if you have a Date field named DOB, and then you add a second Text field named DOB_Entry, putting the code near the end of this post into the latter’s Trigger after update (see Table > Edit Fields…, and then look in the More options area of the DOB_Entry field) should allow DOB_Entry to parse dates in a variety of common text formats (e.g., “1961-06-02″, “02.06.1961″, “6/2/61″, etc.), guess at the appropriate century or year, and then assign the DOB field to the resulting date.

    Note that you should probably also add a Trigger after update to the DOB field that updates the DOB_Entry field whenever the DOB field is changed directly:

    DOB_Entry := month(DOB) + “/” + day(DOB) + “/” + year(DOB)

    This code could be extended to handle Date / Time fields, but I didn’t need that support.

    A version that performed error checking and displayed good user feedback could easily be rolled into Ninox itself.

    Enjoy,

    - Chris

    let field1 := replacex(DOB_Entry, “([0-9]+)[./-]([0-9]+)(?:[./-]([0-9]+))?”, “”, “$1″);
    let field2 := replacex(DOB_Entry, “([0-9]+)[./-]([0-9]+)(?:[./-]([0-9]+))?”, “”, “$2″);
    let field3 := replacex(DOB_Entry, “([0-9]+)[./-]([0-9]+)(?:[./-]([0-9]+))?”, “”, “$3″);
    let year_string := field1;
    let month_string := field2;
    let day_string := field3;
    if contains(DOB_Entry, “/”) then
    (month_string := field1;
    (day_string := field2);
    (year_string := field3))
    else
    if contains(DOB_Entry, “.”) then
    (day_string := field1;
    (month_string := field2);
    (year_string := field3))
    else
    if length(field3) = 0 then
    (month_string := field1;
    (day_string := field2);
    (year_string := “”))
    else
    void;
    (month_string := lpad(month_string, 2, “0″));
    (day_string := lpad(day_string, 2, “0″));
    if length(year_string) < 3 then
    (let current_year := year(today());
    let current_century_string := text(floor(current_year / 100));
    year_string := if length(year_string) < 2 then
    text(current_year)
    else
    current_century_string + year_string;
    if age(date(number(year_string), number(month_string), number(day_string))) < -1 then
    (year_string := text(number(year_string) – 100))
    else
    void)
    else
    void;
    DOB := date(number(year_string), number(month_string), number(day_string));
    DOB_Entry := if contains(DOB_Entry, "/") then
    month_string + "/" + day_string + "/" + year_string
    else
    if contains(DOB_Entry, ".") then
    day_string + "." + month_string + "." + year_string
    else
    year_string + "-" + month_string + "-" + day_string

    1. Hi Gang,

      [Updated to use embedded HTML to avoid WordPress garbling my code.]

      One of the Ninox features I found most disappointing with was the built-in support for parsing dates entered via text. When entering a lot of records, being strict about the local date format (e.g., “Jun 2, 1961″) or using the calendar UI makes for a fairly cumbersome (even error-prone) experience. I decided to pair each Date field with a second Text field that I could write my own parsing code for.

      For example, if you have a Date field named DOB, and then you add a second Text field named DOB_Entry, putting the code near the end of this post into the latter’s Trigger after update (see Table > Edit Fields…, and then look in the More options area of the DOB_Entry field) should allow DOB_Entry to parse dates in a variety of common text formats (e.g., “1961-06-02″, “02.06.1961″, “6/2/61″, etc.), guess at the appropriate century or year, and then assign the DOB field to the resulting date.

      Note that you should probably also add a Trigger after update to the DOB field that updates the DOB_Entry field whenever the DOB field is changed directly:

      DOB_Entry := month(DOB) + “/” + day(DOB) + “/” + year(DOB)

      This code could be extended to handle Date / Time fields, but I didn’t need that support.

      A version that performed error checking and displayed good user feedback could easily be rolled into Ninox itself.

      Enjoy,

      - Chris

      let field1 := replacex(DOB_Entry, “([0-9]+)[./-]([0-9]+)(?:[./-]([0-9]+))?”, “”, “$1″);
      let field2 := replacex(DOB_Entry, “([0-9]+)[./-]([0-9]+)(?:[./-]([0-9]+))?”, “”, “$2″);
      let field3 := replacex(DOB_Entry, “([0-9]+)[./-]([0-9]+)(?:[./-]([0-9]+))?”, “”, “$3″);
      let year_string := field1;
      let month_string := field2;
      let day_string := field3;
      if contains(DOB_Entry, “/”) then
      (month_string := field1;
      (day_string := field2);
      (year_string := field3))
      else
      if contains(DOB_Entry, “.”) then
      (day_string := field1;
      (month_string := field2);
      (year_string := field3))
      else
      if length(field3) = 0 then
      (month_string := field1;
      (day_string := field2);
      (year_string := “”))
      else
      void;
      (month_string := lpad(month_string, 2, “0″));
      (day_string := lpad(day_string, 2, “0″));
      if length(year_string) < 3 then
      (let current_year := year(today());
      let current_century_string := text(floor(current_year / 100));
      year_string := if length(year_string) < 2 then
      text(current_year)
      else
      current_century_string + year_string;
      if age(date(number(year_string), number(month_string), number(day_string))) < -1 then
      (year_string := text(number(year_string) – 100))
      else
      void)
      else
      void;
      DOB := date(number(year_string), number(month_string), number(day_string));
      DOB_Entry := if contains(DOB_Entry, "/") then
      month_string + "/" + day_string + "/" + year_string
      else
      if contains(DOB_Entry, ".") then
      day_string + "." + month_string + "." + year_string
      else
      year_string + "-" + month_string + "-" + day_string

      1. Hi Gang,

        [Updated to use embedded HTML (that's actually supported) to avoid WordPress garbling my code. Somebody at Ninox should delete my earlier posts.]

        One of the Ninox features I found most disappointing with was the built-in support for parsing dates entered via text. When entering a lot of records, being strict about the local date format (e.g., “Jun 2, 1961″) or using the calendar UI makes for a fairly cumbersome (even error-prone) experience. I decided to pair each Date field with a second Text field that I could write my own parsing code for.

        For example, if you have a Date field named DOB, and then you add a second Text field named DOB_Entry, putting the code near the end of this post into the latter’s Trigger after update (see Table > Edit Fields…, and then look in the More options area of the DOB_Entry field) should allow DOB_Entry to parse dates in a variety of common text formats (e.g., “1961-06-02″, “02.06.1961″, “6/2/61″, etc.), guess at the appropriate century or year, and then assign the DOB field to the resulting date.

        Note that you should probably also add a Trigger after update to the DOB field that updates the DOB_Entry field whenever the DOB field is changed directly:

        DOB_Entry := month(DOB) + “/” + day(DOB) + “/” + year(DOB)

        This code could be extended to handle Date / Time fields, but I didn’t need that support.

        A version that performed error checking and displayed good user feedback could easily be rolled into Ninox itself.

        Enjoy,

        - Chris


        let field1 := replacex(DOB_Entry, "([0-9]+)[./-]([0-9]+)(?:[./-]([0-9]+))?", "", "$1");
        let field2 := replacex(DOB_Entry, "([0-9]+)[./-]([0-9]+)(?:[./-]([0-9]+))?", "", "$2");
        let field3 := replacex(DOB_Entry, "([0-9]+)[./-]([0-9]+)(?:[./-]([0-9]+))?", "", "$3");
        let year_string := field1;
        let month_string := field2;
        let day_string := field3;
        if contains(DOB_Entry, "/") then
        (month_string := field1;
        (day_string := field2);
        (year_string := field3))
        else
        if contains(DOB_Entry, ".") then
        (day_string := field1;
        (month_string := field2);
        (year_string := field3))
        else
        if length(field3) = 0 then
        (month_string := field1;
        (day_string := field2);
        (year_string := ""))
        else
        void;
        (month_string := lpad(month_string, 2, "0"));
        (day_string := lpad(day_string, 2, "0"));
        if length(year_string) < 3 then
        (let current_year := year(today());
        let current_century_string := text(floor(current_year / 100));
        year_string := if length(year_string) < 2 then
        text(current_year)
        else
        current_century_string + year_string;
        if age(date(number(year_string), number(month_string), number(day_string))) < -1 then
        (year_string := text(number(year_string) - 100))
        else
        void)
        else
        void;
        DOB := date(number(year_string), number(month_string), number(day_string));
        DOB_Entry := if contains(DOB_Entry, "/") then
        month_string + "/" + day_string + "/" + year_string
        else
        if contains(DOB_Entry, ".") then
        day_string + "." + month_string + "." + year_string
        else
        year_string + "-" + month_string + "-" + day_string

        1. Chris… How did you figure out the syntax of this code is there a resource somewhere… I’ve looked through manuals but it’s basic and doesn’t explain much on the coding side of things.. Does Ninox use a syntax from another database product, don’t recognize it. Use MS SQL and SAS as work need basic DB app for home thought this would suffice but struggling with it…

  2. Pingback: Bolide
  3. Pingback: jommakan.my
  4. Pingback: Sexo
  5. Pingback: Bandar Judi
  6. Pingback: adme

Leave a Reply