1 2 Previous Next 19 Replies Latest reply on Sep 20, 2013 6:39 AM by philmodjunk

# One more question - Calculating Expiration Dates

### Title

One more question - Calculating Expiration Dates

### Post

So here is what I am doing now:

Max ( Individual Contributions::ContributionDate) + 365

will give me the expiration date but its not working correctly how would I calculate using the code above for each individual record because each person has a different expiration date.  Each record has an ID so I think I need to include that in the code which is at the same level of ContributionDate

• ###### 1. Re: One more question - Calculating Expiration Dates

You'll need to explain that a big further.

Am I correct that you have two tables linked in a relationship, one of which is Individual Contributions? How is that relationship set up?

And how does your calculation "not work"? What results do you get and what results do you want?

I will also note that adding 365 to a date will not necessarily give you a date for the same month and day of the next year (leap years make for 366 days).

• ###### 2. Re: One more question - Calculating Expiration Dates

Individual Contrib. (Layout)

Data in the layout

Record ID: 405

ContributionDate: 9/24/2013

ContribAmt: 124

ContributionID: 1484

Basically the code I was looking looked at the whole set of data rather than each individual

• ###### 3. Re: One more question - Calculating Expiration Dates

Sorry, but that does not answer my question and Max ( Individual Contributions::ContributionDate) does not look at every record, it either refers to a single record or a set of related records--depending on the context in which it is used. And the "set of related records" will be controlled by the relationship specified between your tables.

• ###### 4. Re: One more question - Calculating Expiration Dates

Am I correct that you have two tables linked in a relationship, one of which is Individual Contributions? How is that relationship set up?

>In Individual Contributions its a layout with a record of each contribution made by each individual

And how does your calculation "not work"? What results do you get and what results do you want?

>It should calculate the expiration date based on the most recent contribution made

• ###### 6. Re: One more question - Calculating Expiration Dates

Layouts and tables are two very different things. How your layout is set up tells me little about your tables and relationships.

The next issue, as I mentioned in my last post is "context". In what context are you using this calculation? In a script step? A calculation field?

If in a script, what layout is current at the time the script is performed? What Tutorial: What are Table Occurrences? is named in Layout Setup | Show Records From for that layout?

If in a calculation field, in what table is that calculation field defined?

And if you put it in as an auto-entered calculation for a date field in Contact Management, this isn't likely to work as it won't automatically update when records are added, deleted or changed in the Individual Contributions table.

If you define Max ( Individual Contributions::ContributionDate) + 365 as a calculation field in Contact Management with Date selected as the result type, it would compute the date that is 365 days in the future from the Individual Contributions Record with the most recent date that is also linked via Record ID to a given record in Contact Management.

• ###### 7. Re: One more question - Calculating Expiration Dates

Yeah I am only using layouts.

• ###### 8. Re: One more question - Calculating Expiration Dates

Expires is the calculated field: Max ( Individual Contributions::ContributionDate) + 365

• ###### 10. Re: One more question - Calculating Expiration Dates

So within the Record ID I need to find the most recent date to calculate the expiration with. How would I do that?

• ###### 11. Re: One more question - Calculating Expiration Dates

To repeat, context is the key. Please note that I described defining the calcluation in a field in Contact Management, not Individual Contributions.

Defined in Indivdual Contributions, the Max function, like any other Aggregate function used with this syntax, is only accessing the value of ContributionDate for the current record in the table. It doesn't "See" any other data from any other records in this context with this syntax.

If the contribution date in the current record is 9/19/2013, then Max ( Individual Contributions::contribution date) evaluates as "take the maximum value of 9/19/2013" and thus returns 9/19/2013 and then your calculation adds 365 days to it.

I'd define this calculation like this:

First define the calculation field in Contact Management, select Date as the return type and "Contact Management" as the "Context Table".

Then I'd enter this calculation:

Let ( D = Max ( Individual contributions::Contributions ) ; Date ( Month ( D ) ; Day ( D ) ; Year ( D ) + 1 ) )

And If I were to specify a SORTED relationship between Contact Management and Individual Contributions, I could sort the records in Individual Contributions in descending order by contribution date and then I could simplify this to:

Let ( D = Individual contributions::Contributions ; Date ( Month ( D ) ; Day ( D ) ; Year ( D ) + 1 ) )

But evaluating this from the context of Contact Management, not Individual contributions is the key to getting the correct result.

• ###### 12. Re: One more question - Calculating Expiration Dates

Sweet works like a charm thanks again !

• ###### 13. Re: One more question - Calculating Expiration Dates

One more thing: It wont take 9/1/2013..9/31/2013 as input in the expiration field

• ###### 14. Re: One more question - Calculating Expiration Dates

says I cant index the result...

1 2 Previous Next