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
-
In the MEMBERS table where you have the formula:
if Table2.Expires < today() then
styled("EXPIRED", "red")
else
styled("Current", "")
enddid 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.
-
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.
-
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.
-
You mention that you have a formula: last(Table2.Expires) , but that is not what you are referencing in the if-then-else statement.
-
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
-
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.
-
I did as you suggested and it now works perfectly. Thank you!
Content aside
- 6 yrs agoLast active
- 7Replies
- 2981Views