You need to sign in to do that
Don't have an account?
Red Earth 2
Increasing a field's value by X
Hello,
When I write a formula, for example, for a custom field Membership Expiration Date:
Membership_Expiration_Date__c + (Membership_Years_Added__c * 365)
It won't let me do it because it's referring to itself. How else can I add X years on to an existing date? I don't want to reset the date, I want to add years to the date already in the field.
Thank you and best wishes!
When I write a formula, for example, for a custom field Membership Expiration Date:
Membership_Expiration_Date__c + (Membership_Years_Added__c * 365)
It won't let me do it because it's referring to itself. How else can I add X years on to an existing date? I don't want to reset the date, I want to add years to the date already in the field.
Thank you and best wishes!
Membership Years = Membership Years + X
how do I increase it's current value by X instead of having to reset it to X?
Context - I have a FAAS form where I'm submitting quantity of 'years' purchased, so if someone purchases 2 years and is already a member, I'd like to increase their membership by 2 years. That's easy if they are a new contact, just add 2 years to today. But if they are already a contact, and their expiration date is March 20, 2015, I'd like to add 2 years to March 20, 2015, not 2 years from today. So either by adding X years to the existing expiration date, or add X to a membership years field (not reset, add to) that I could use in a formula to calculate the expiration date.
I think you're almost there. I would recommend storing the original expiration date as a date field. You would have the following fields:
1 - Membership_Expiration_Date_Original__c (Date)
2 - Membership_Expiration_Date_Current__c (Formula) --> Membership_Expiration_Date_Original__c + Membership_Total_Years_Added__c
3 - Membership_Total_Years_Added__c (Number, defaults to 0)
Any time a renewal happens, you can use a workflow rule to update Membership_Total_Years_Added__c. The value would be Membership_Total_Years_Added__c + X. So, assuming an original expiration date of 12/31/2014, the first time a renewal of 1 year happens, you would have:
Membership_Total_Years_Added__c = 0 + 1 = 1
Membership_Expiration_Date_Original__c + Membership_Total_Years_Added__c = 12/31/2014 + 1 = 12/31/2015
Let's say a second renewal occurred next year and it was for two years, you would have:
Membership_Total_Years_Added__c = 1 + 2 = 3
Membership_Expiration_Date_Original__c + Membership_Total_Years_Added__c = 12/31/2014 + 3 = 12/31/2017
Does that make sense?
Hope that helps...
Thanks,
Phil
http://amzn.com/1484200985
I now have these fields:
Membership_Years_Added_Recent = the number of years posted from a membership FAAS or edited on the contact record
Membership_Years_Added_Total = workflow to add Membership_Years_Added_Total__c + Membership_Years_Added_Recent__c
The workflow then also resets _Recent to 0, because the workflow triggers each time a contact is edited. I did this because I found that if it equaled 1, and then someone renewed for 1 year, it didn't consider it edited since 1 was the same, so did not do 1+1, it just stayed 1. Resetting to 0 after the calculation now makes let's me trigger the workflow anytime _Recent changes to > 0.
Membership_Expiration_Date = npo02__MembershipJoinDate__c + (Membership_Years_Added_Total__c * 365)
So this is all working beautifully IF and only if the Membership_Years_Added_Total = an integer. But when I created the number field, even when I set the default to 0, it still renders it as a blank value.
When it's a blank value, nothing executes in the workflow. I tried recreating the field as an auto-number and then converting it to a number, but it still showed as blank field. If I set it to 0 to start, everything works as it should.
Two questions:
* Is there a way to auto-update all existing entries to the number 1 so all existing contacts can renew accurately? (it won't trigger the workflow when the field is blank)
* Is there a way to force the number field to default to 1 even when there's a workflow associated with the field? I even tried using this formula:
1+ Membership_Years_Added_Total__c + Membership_Years_Added_Recent__c for the total field, but it doesn't even get that far if it's a blank field.
Thanks for any ideas!
Best wishes
---
We recommend to use "If" so if the field is null it just passes the value no addition, and an else statement where if it has a value adds them together.
So from
Membership_Years_Added_Total__c + Membership_Years_Added_Recent__c
we used
IF( ISNULL(Membership_Years_Added_Total__c) , Membership_Years_Added_Recent__c, Membership_Years_Added_Total__c + Membership_Years_Added_Recent__c)
---
Using the if statement took care of it without having to backfill the values of the field for existing members.
Best wishes!