0

Data Field Types

Hi, I'm trying to use this Db for the first time on a mini-Ipad, I'm trying to use the data field "location" but I cannot find any instruction on the on line help.
I put the field in a form, when I click on it, a map comes out, find an address, click on save thinking that it will appear on the field, but nothing happened.
I added a text field, wrote on it an address, edit the location field adding aa command "location=address" but again nothing happens. Can someone please help me to understand? Thanks

90 replies

null
    • Birger_H
    • 6 yrs ago
    • Reported - view
    Hello!
    Get the selected index number of a choice field like this:
    –––
    number('Name of my Choice field')
    –––

    Get the displayed name of a choice field like this:
    –––
    text('Name of my Choice field')
    –––

    Birger - Ninox Support
    • flynblu
    • 6 yrs ago
    • Reported - view
    Is there a way to enter a list of multiple items for "add value" in the "Choice" field instead of 1 item at a time? I have a list of many items and really don't want to have to type them in one by one.
    • Birger_H
    • 6 yrs ago
    • Reported - view
    Hello,
    there is no way to automatically (bulk) add choice to choice fields.

    Birger - Ninox Support
    • qgoforth_triadunlimited
    • 6 yrs ago
    • Reported - view
    Auto Number Field with Input Mask

    Below is formula code that can be used to create an Auto Number Field with an Input Mask (e.g., AG00001, AG00002, … , AG00015). The number generation is built using the respective table’s standard ‘Id’ field so there isn’t any coding required to check for unique values.

    Note, each Input Mask formula code will have limitations based on the number of records or ‘Id’ values within the respective table so this should be accounted for. If a limit is hit and causes issues, updates to the formula code can be made retroactively and will update all records.

    Input Mask Requirements:

    Prefix = “AG”
    Number = “#####”

    Formula Code:

    if number(Id) < 10 then
    "AG0000" + Id
    else
    if number(Id) < 100 then
    "AG000" + Id
    else
    if number(Id) < 1000 then
    "AG00" + Id
    else
    if number(Id) < 10000 then
    "AG0" + Id
    else
    "AG" + Id
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view
    Hi qgoforth,

    You can also use the following formula:

    "AG" + lpad(text(Id), 5, "0")

    regards
    Leo
    • tedandrachel
    • 6 yrs ago
    • Reported - view
    Hey - I also wanted to do this. A way round which I found which you may find useful, is to use a "table reference" field rather than a "choice" field. You can create a table with all the values that you want available by importing data to a new table from an xl file (saved as a .csv document). This has worked well for me with a long list of choices that I wanted for one field. Birger - please correct me if I am wrong or if there is a big disadvantage to doing it this way.
    • tedandrachel
    • 6 yrs ago
    • Reported - view
    Hi,

    How do i make another field visible only if a yes/no checkbox is ticked?

    Thanks
    • Birger_H
    • 6 yrs ago
    • Reported - view
    Here is a small example. In the property "Visible if..." of the field you want to hide give the name of the Yes/No field.


    Birger - Ninox Support
    • Birger_H
    • 6 yrs ago
    • Reported - view
    This is how I would do it, yes. Birger
    • qgoforth_triadunlimited
    • 6 yrs ago
    • Reported - view
    Much better and more efficient! Thanks!
    • qgoforth_triadunlimited
    • 6 yrs ago
    • Reported - view
    How to copy and create multiple records from multiple tables and mirror record relationships?

    I have 3 tables with parent-child relationships: A > B > C (A is one-to-many to B, and B is one-to-many to C). I want to create a formula (or macro type function) that can copy the EXISTING records below from each table and create NEW records below (mirroring the previous tables records relationships).

    EXISTING
    A:
    ID 1

    B:
    ID 1 (A ID = 1)
    ID 2 (A ID = 1)

    C:
    ID 1 (B ID =1)
    ID 2 (B ID = 1)
    ID 3 (B ID = 2)
    ID 4 (B ID = 2)

    NEW
    A:
    ID 2

    B:
    ID 3 (A ID = 2)
    ID 4 (A ID = 2)

    C:
    ID 5 (B ID =3)
    ID 6 (B ID = 3)
    ID 7 (B ID = 4)
    ID 8 (B ID = 4)

    Is this possible with Ninox???

    Thanks in advance for any/all help!
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view
    Hi,
    you can write a trigger on create by Table A

    let AN := Id;
    for II in range(0, 2) do
    let BB := (create B);
    BB.(A := AN);
    (let BN := BB.Id;
    for JJ in range(0, 2) do
    let CC := (create C);
    CC.(B := BN)
    end
    )
    end

    Regards
    Leo
    • qgoforth_triadunlimited
    • 6 yrs ago
    • Reported - view
    Thanks Leo. As a next step, I wanted to see if we can use field values affiliated with tables A, B, and C to populate within the newly created records for each respective table.

    A better way to clarify this is, I have a library of Tests (table A) for Students (table D, not introduced in my previous post), and each Test has Sections (table B), and each Section has Questions (table C). Therefore table A will also have a field called 'A Name' (or Test Name), and table B will also have a field called 'B Name' (or Section Name), and table C will also have a field called 'C Name' (or Question Name).

    I want to be able to use a library of Tests and quickly populate new records in tables A, B, and C that are linked to a Student (table D).

    Is this possible?
    • Leonid_Semik
    • 6 yrs ago
    • Reported - view
    It is very difficult to describe as text. I have created a test database for you.

    https://www.dropbox.com/s/dz82xtnmhqiwtrv/STUDENT.ninox?dl=0

    There is an additional table "RESULTS" with links to students and questions. Also, I have inserted a TESTS FILE table. This is necessary for the fields "LOAD ....." in the table STUDENTS to have no plus sign. You can either add a complete test or a complete section or single question in the STUDENTS table

    regards
    Leo
    • qgoforth_triadunlimited
    • 6 yrs ago
    • Reported - view
    Thanks again Leo! I'll need some time to digest this and understand how to apply it to my scenario.
    • Birger_H
    • 6 yrs ago
    • Reported - view
    This is an interesting one - thanks for sharing :-)

    Birger
    • Schmed
    • 6 yrs ago
    • Reported - view
    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
    • Schmed
    • 6 yrs ago
    • Reported - view
    Note: You'll want to replace all of the left/right curly double quotes that WordPress substituted in the code above with simple double quotes (ASCII 34).
    • Schmed
    • 6 yrs ago
    • Reported - view
    You'll also have to correct the indention of each line. Perhaps it would be better to attach an example DB, but I don't know how to do that.
    • Schmed
    • 6 yrs ago
    • Reported - view
    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
    • Schmed
    • 6 yrs ago
    • Reported - view
    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
    • Schmed
    • 6 yrs ago
    • Reported - view
    I give up.
    • gb_eh
    • 6 yrs ago
    • Reported - view
    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...
    • donnydave
    • 6 yrs ago
    • Reported - view
    Hi,
    Is it possible to format a number with a fixed number of decimal places ?
    eg. 24.50000
    • Birger_H
    • 6 yrs ago
    • Reported - view
    You can specify the number of decimal places in the properties of any number field.

    Birger - Ninox Support

Content aside

  • 5 yrs agoLast active
  • 90Replies
  • 30278Views