Formulas

Hello John,
formula cannot be searched. A workaround is to use mass data updates and write the value permanently into some field in the related table. For future entries set a trigger to perform this operation.
Best regards, Alex

Hello. I have a question about assigning behaviour to a button. I need a value in a certain field to increase as I press the button. So let's say it starts from zero and then clicking the button that value goes "1", then pressing it again it goes "2" and so on.
How can I arrange the formula?
Thanks for your help.
Great app by the way!

- Gianluca

Hi Gsechi,
pls. try
MyField := MyField + 1
Regards,
Frank

Thanks for your help. It works perfectly!

My only reference is using Filemaker. When I create a "formula" field in Ninox I can only return numbers. FM allowed the choice of creating a calculation in either a Number or Text format.

Basically, I am trying to generate a string of text, based on dates/numbers using the creation date of the file. Nov 2, 2016 would be output as 2016_1102_01 and ascending numbers ...02...03...4 The numbering sequence (based on the date) starts over each day, plus an ascending number at the end.

Easy stuff with FM, but I am trying to understand the syntax used to create calculations that result in a string of text characters (resulting from a alpha-numerical calculation) with Ninox.

Hello,
with formulas you can also return text.

To create a subsequent number for invoices for example, the best method id to use a trigger on table level "on create". Each time new record is created the trigger will be executed and insert the invoice number into a text field for example.

The following example creates an invoice number with year month and day. Invoice are counted upwards for each day.

let y := year(Date);
let m := month(Date);
let d := day(Date);
let c := cnt(select Invoice_Table where year(Date) = y and month(Date) = m);
InvoiceNo := y + format(m, "00") + format(d, "00") + format(c, "00")

Note: Date is the name of the date field; Invoice_Table is the name of the table with the invoices; InvoiceNo is the name of the text field with the invoice number.

If there are question you can also drop a line to support@ninoxdb.de

Best regards, Alex

Hi Frank,
Can we build on Gsechi's post as follows.
Suppose we want to make 10 buttons number 0-9 ( like a touch tone phone )
Getting the number 0-9 in field is pretty straight forward....
MyField := the button number goes here
Works great for numbers 0-9, but how do I get the number 10-99 or even 100-999 using the same 10 buttons?
The app & support is the best !
Regards
Jon

Hi Jon, this works if Myfield is a text field and you like to record a phone number for example:

MyField:= MyField +1
MyField:= MyField +2

Best regards, Alex

Hi Alex,
Could you collaborate please?
The goal is to have 10 buttons numbered 0-9
If you press button 123 we want to put the number 123 in myfield

Best
Jon

Hi Jon,
Create ten buttons from 1 - 10. On each button you put a trigger.
for Button 1: MyField:= MyField +1
for Button 2: MyField:= MyField +2
and so on. This works if MyField is a text field.
If you have questions you can also drop a line to support@ninoxdb.de
Best regards, Alex

Hi Alex,
Thank you very much, my apologies, I was looking at this to logically & mathematically.
Thank you again for tolerating my antiquated ways of thinking.

Also, is there a way for command a backspace or delete on a formula? Problem now is when 124 is entered and 123 was supposed to be entered, can we make a button to go back 1 space deleting the 4 so a 3 can be entered?
Regards
Jon

Hi Jon, undoing the last will work with this formula:
MyField := substring(MyField, 0, length(MyField) - 1)
Best regards, Alex

HELP - I have 11 number fields in the same record. After each field receives an entry I wish to calculated field to work out the 1st highest entry and the 2nd highest entry as each entry is made. In excel there is a function called LARGE. for example Large(A1:A11,1) returns the largest number in the cells A1 to A11 inclusive. Large(A1:A11,2) returns the 2nd largest number in the same range. I wish to replicate this in Ninox......

Hello Brett,
to get the highest values from the fields works with formula:
1st=max(A1, A2, A3)
The second will be more difficult with if / else statements.
Best regards, Alexander

So, I have a field "SerialNumber" and a field "Year" The 4th digit of the serial number is a letter, and the letter signifies the year.
Example: if the serial number is 24YG3A06498E, the 4th digit from the left is G so this tells us the year is 2005.Serial number 24YH6A4365w the H tells us the year is 2006.
Is there a solution which will populate the " year " field automatically with the correct year based on the 4th digit of the number placed into the "SerialNumber" field?

Regards
Jon

Hi Jon,
the field "Year" as f(x) formula

let YY := "ABCDEFGHIJKLMNOPRSTUFH";
let XX := substr(SerialNumber, 3, 1);
let ZZ := index(YY, XX);
1999 + ZZ

regards
Leo

Hello Leo,
I cut n paste the code in and get,
Error Unexpected symbol at line 1, column 9
I used the paste clip board and even just tried to cut n paste just the first line.
Need I type it out ?

Thanks
Jon

Hello Jon,
These are the quotation marks in the first line. Please delete this and put your own quotes with Mac / iPad

Leo

Here the database,

https://www.dropbox.com/s/8flxpumduav48yc/Serial.ninox?dl=0

Leo

Hi Leo,
Kudos to you,the team,and Ninox.Your solution to the problem now works perfectly.I have made a not about the error behavior with the quotation marks when using Mac/iPad.After 20 years of PC's, Windoz and MS Access, we switched to Mac and never looked back.
Thanks again for all your help, database is growing, 3500 records still working like a charm.

Regards
Jon

Not = Note if someone can edit the post

So, I am attempting to use a color box and set the color based on a calculation.
Example: if field "year" is between 200 and 2005 I want the color box to be red,if year is between 2006 and 2010 I'd like the color box to be blue, ect etc.
How would I achieve this?
App is still working great with over 1000 records and climbing.
Thanks for any help or examples

Regards
Jon

Hello Jon,

bei "year" trigger after update

'color box':=if year >=2000 and year=2006 and year<=2005 then"rgb(0,63,255)" else "black"

You can find the RGB codes by inserting a formula field

text ('color box')

Leo

Oops

‘color box’ := if year >= 2000 and year = 2006 and year <= 2010 then
"rgb(0,63,255)"
else
"black"