0

Help Please

I’m trying to set up a very simple database to help me with a two-weekly task.

Firstly, I need my first field to contain a consecutive number for each entry. So my first record will be 0001 and the next will automatically be 0002 and so on. How do I achieve this?

Secondly, another field contains today’s date and the field after should automatically contain the date two weeks from today’s date (the expiry date). How do I achieve this?

Thirdly and finally, I want the records which have expired to be highlighted in green but for records still within their expiry date to be highlighted in red. Or, if this isn’t possible, a way for expired entries to be hidden? How do I achieve this?

Apologies for all of the questions but I’ve just discovered Ninox and it seems to be just what I’m looking for, but I can’t seem to figure it out properly.

Thanks in advance for any help. 

8 replies

null
    • blackie
    • 6 yrs ago
    • Reported - view

    Use styled() to set the color of text and the background. You can use if statements or a switch case to set the text color based on the experation date.

    styled(text, bgColor, color, icon)

    styled(text, colour, icon) - creates styled text elements. The icon is placed to the left of the text. Use "" for none, and the colour is the objects background
    |  styled("Attention", "red", "warn")

     

    To create the experation date add 14 to the date.     Date + 14

     

    for a unique auto incremented index, see this thread:

    https://ninoxdb.de/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/data-field-types-5ab8fe445fe2b42b7dd39f67?post=5ab8fe5e5fe2b42b7dd3a7fe&page=5

    Birger Hansen
    Wednesday, April 26, 2017 12:24 PM
    This is possible.
    1. with a trigger "onCreate" on table level with this formua:
    –––
    uniqueNumber := max((select Table1).uniqueNumber) + 1
    –––
    or - if you just want to check if the number is unique after update put this formula to the "onAfterUpdate" trigger of the field:
    –––
    let myTest := 'Auto increment Number';
    if cnt(select Table1[uniqueNumber = myTest])>1 then "not unique" else myTest
    –––
    Birger - Ninox Support

    • Nick
    • 6 yrs ago
    • Reported - view

    For expiry date create formula field:

    --

    YourDateField +15

    --

     

    For highlighted fields I use something similar for invoices:

    --

    let dd := Date + Customer.Terms;
    let pd := 'Pay Date';
    if pd = null then
    styled("Not Paid", "orange", "")
    else
    if pd > date(dd) then
    styled("Overdue", "red", "")
    else
    styled("Paid", "green", "")
    end
    end

    --

    Just change Fields and colors.

     

    Nick

    • Alan.1
    • 6 yrs ago
    • Reported - view

    Thanks for such a detailed response!

     

    Where do I place these formulas when editing fields? I can’t seem to get them working...

    • Alan.1
    • 6 yrs ago
    • Reported - view

    I can’t for the life of me figure out how to implement any of these formulas so that they work 😞

    • Nick
    • 6 yrs ago
    • Reported - view

    Create new Formula field and:

    Screen Shot 2018-09-22 at 19.06.30

     

    Nick

    • Alan.1
    • 6 yrs ago
    • Reported - view

    Thanks, but where is that a screenshot from? I’m using the iPhone app...

    • blackie
    • 6 yrs ago
    • Reported - view

    Edit fields 

    new field

    formula 

    • Alan.1
    • 6 yrs ago
    • Reported - view

    Thank you.

    I’ve got the expiry date working but I can’t get the sequential numbers working. I definitely need to play around with this quite a lot..!

Content aside

  • 6 yrs agoLast active
  • 8Replies
  • 3813Views