0

Automatically updating a field

I have a table with 2 subtables.  The main table contains MEMBERS of an organization.  One of the subtables (DUES) contains their membership dues payments and the date paid.  Members  usually renew annually, but may renew earlier or later.

I want to know when a member expires so, in the DUES subtable, I calculate a one-year anniversary, "Expires": date(year(Date) + 1, month(Date), day(Date))  This works fine.

I want the expiration date to show up in the MEMBERS table too, so there I have this formula:  last(Table2.Expires)  which takes the one one year anniversary of the last payment made by the member.  This also works fine.

I want to be able to see this clearly, so I then added the formula in MEMBERS

if Table2.Expires < today() then
styled("EXPIRED", "red")
else
styled("Current", "")
end

When I entered a member with an already expired membership, the filed returns "EXPIRED" in a red background. Perfect!

But, the first member expired two days ago and the field still says "Current" and it did not turn red.  I thought this would happen automatically but it did not.  Why?  And how can I fix this?

Thanks!

7 replies

null
    • Sean
    • 5 yrs ago
    • Reported - view

    In the MEMBERS table where you have the formula:

     

    if Table2.Expires < today() then
    styled("EXPIRED", "red")
    else
    styled("Current", "")
    end

     

    did you use last()? I tested this using time() instead of today() and if there are multiple records for a member and you don't use last() it will test negative. Also, I'm pretty sure formulas will not calculate unless there is a trigger like database open, create or update, or if manually triggered by a button element with code or the refresh trigger in the upper left hand corner of a table view.

    • snaprovmembership
    • 5 yrs ago
    • Reported - view

    Slowwagon, Thanks for the response. "expires" is calculated using last(), so everything is working properly except that the "Current?" field does not update when the date changes and today becomes > expires.  I guess I am asking how to make this trigger.

    The refresh trigger (if that is the little circle with an arrow in the upper left) does not do anything.

    • Sean
    • 5 yrs ago
    • Reported - view

    Is the "Current?" field the formula field that has:

     

    if Table2.Expires < today() then
    styled("EXPIRED", "red")
    else
    styled("Current", "")
    end

     

    ? last() is not being used there.

    • Sean
    • 5 yrs ago
    • Reported - view

    You mention that you have a formula:  last(Table2.Expires) , but that is not what you are referencing in the if-then-else statement.

    • snaprovmembership
    • 5 yrs ago
    • Reported - view

    I guess I'm not being clear, but "Current?" in the top level table takes "expires" from table2"  "Expires" in table 2 is generated from the one year anniversary of the last contribution.  All of that works correctly.  The only problem is that "Current?" does not update when it happens that Table2.Expires < today

    • Sean
    • 5 yrs ago
    • Reported - view

    In your original post you say:

     

    "Expires": date(year(Date) + 1, month(Date), day(Date))

     

    You did not show that you have used last() for this field. If that is the case and you have multiple records and you use:

     

    if Table2.Expires < today() then

    instead of 

    if last(Table2.Expires) < today() then

     

    the condition will test negative even though the calculation in Table2 is correct.

    • snaprovmembership
    • 5 yrs ago
    • Reported - view

    I did as you suggested and it now works perfectly.  Thank you!

Login to reply

Content aside

  • 5 yrs agoLast active
  • 7Replies
  • 2971Views