0

Formulas

Hello,

When attempting to link entries together from two different tables via the cross reference function, Is there a way to get data entries to show up in the search results of "Find: X" when searching a specific word/name associated with a formula field?

- John
62replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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
    Like
  • 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
    Like
  • Hi Gsechi,
    pls. try
    MyField := MyField + 1
    Regards,
    Frank
    Like
  • Thanks for your help. It works perfectly!
    Like
  • 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.
    Like
  • 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
    Like
    • jonfusa
    • jonfusa
    • 6 yrs ago
    • Reported - view
    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
    Like
  • 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
    Like
    • jonfusa
    • jonfusa
    • 6 yrs ago
    • Reported - view
    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
    Like
  • 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
    Like
    • jonfusa
    • jonfusa
    • 6 yrs ago
    • Reported - view
    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.
    Like
    • jonfusa
    • jonfusa
    • 6 yrs ago
    • Reported - view
    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
    Like
  • Hi Jon, undoing the last will work with this formula:
    MyField := substring(MyField, 0, length(MyField) - 1)
    Best regards, Alex
    Like
    • brett
    • brett
    • 6 yrs ago
    • Reported - view
    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......
    Like
  • 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
    Like
    • jonfusa
    • jonfusa
    • 6 yrs ago
    • Reported - view
    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
    Like
  • 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
    Like
    • jonfusa
    • jonfusa
    • 6 yrs ago
    • Reported - view
    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
    Like
  • Hello Jon,
    These are the quotation marks in the first line. Please delete this and put your own quotes with Mac / iPad

    Leo
    Like
    • jonfusa
    • jonfusa
    • 6 yrs ago
    • Reported - view
    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
    Like
    • jonfusa
    • jonfusa
    • 6 yrs ago
    • Reported - view
    Not = Note if someone can edit the post
    Like
    • jonfusa
    • jonfusa
    • 5 yrs ago
    • Reported - view
    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
    Like
  • 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
    Like
  • Oops

    ‘color box’ := if year >= 2000 and year = 2006 and year <= 2010 then
    "rgb(0,63,255)"
    else
    "black"
    Like
Like Follow
  • 4 yrs agoLast active
  • 62Replies
  • 20764Views