Data Field Types

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

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.

Hello,
there is no way to automatically (bulk) add choice to choice fields.

Birger - Ninox Support

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

Hi qgoforth,

You can also use the following formula:

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

regards
Leo

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.

Hi,

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

Thanks

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

This is how I would do it, yes. Birger

Much better and more efficient! Thanks!

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!

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

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?

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

Thanks again Leo! I'll need some time to digest this and understand how to apply it to my scenario.

This is an interesting one - thanks for sharing :-)

Birger

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

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).

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.

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

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


I give up.

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...

Hi,
Is it possible to format a number with a fixed number of decimal places ?
eg. 24.50000

You can specify the number of decimal places in the properties of any number field.

Birger - Ninox Support