0

Help with creating an automated 'Invoice Status' based on date fields.

I have a 'status' field which is generated by a formula as follows, but not quite working:

 

let nw := today();

let dd := 'Date Due';

let po := 'Paid on';

let rm:= 'Reminder Date';

if po = null then

styled("Due", "orange", "")

else

if dd > date(nw) then

styled("Overdue", "red", "")

else

styled("Paid", "green", "")

end

end

 

I would ike the field to change to "paid" when i enter a date in to the 'Paid on' field, however if the field is empty i would like it to either state that the invoice is "issued" if the date is before reminder date, "Due" if between the reminder date and due date or "overdue" if passed the due date.

Any help would be much appreciated, i've filtered through many enquiries on the forum but cant seem to find a resolution.

Terry P

5 replies

null
    • Terry_Prokopchuk
    • 4 yrs ago
    • Reported - view

    Hello, any help on this would be greatly appreciated. Thank you.

    • Nick
    • 4 yrs ago
    • Reported - view

    Try this

    ---

    let td := today();
    if 'Paid On' != null then
    styled("Paid", "green", "")
    else
    if 'Paid On' = null and 'Reminder Date' <= td and 'Date Due' >= td then
    styled("Due", "orange", "")
    else
    if 'Paid On' = null and td > 'Date Due' then
    styled("Overdue", "red", "")
    end
    end
    end

    ---

    • Terry_Prokopchuk
    • 4 yrs ago
    • Reported - view

    Thank you Nick, 

    That has been really helpfull and sorted my issue, i had to add another scenario as the 'status' was left balnk if between the issue and reminder date, thats again. for anyone with the same issue here is the adapted formula i used. Thanks again Nick, superstar!

    let td := today();
    if 'Paid on' != null then
    styled("Paid", "green", "")
    else
    if 'Paid on' = null and 'Reminder Date' <= td and 'Date Due' >= td then
    styled("Due Soon", "orange", "")
    else
    if 'Paid on' = null and 'Reminder Date' >= td and 'Invoice Date' <= td then
    styled("Issued", "yellow", "")
    else
    if 'Paid on' = null and td > 'Date Due' then
    styled("Overdue", "red", "")
    end
    end
    end
    end

    • Nick
    • 4 yrs ago
    • Reported - view

    👍

    • Terry_Prokopchuk
    • 4 yrs ago
    • Reported - view

    Hi Nick, 

    Dont know if you can help with another 'sticky point' i have but you've been really knowledgeable and helpfull with my other issues. the post has been unanswered to date:

    https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/i-would-like-to-create-a-sum-formula-based-on-yesno-fields...-5e877092d8024a489eb18f76

Login to reply

Content aside

  • 4 yrs agoLast active
  • 5Replies
  • 859Views