Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 8307 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

I'm trying to devise a script/calculation that will look at 8 date fields (DO1, DO2 etc) and if the value is 1/1/02...12/31/02, a value in a corresponding number field (AO1, AO2 etc) will be added to a total figure. In other words, there are 8 quarterly payments scheduled over 24 months, but I want to get a total of just the payments that will fall in a certain date range. It seems as if it should be simple, but the only thought I have requires a script with 8 different subscripts.

Suggestions appreciated.

Chuck

Posted

Easy answer: calculated fields... wrong forum, oh well, here goes:

Make a calculated number that =

((Year(D01) = 2002) * A01) +

((Year(D02) = 2002) * A02) +

((Year(D03) = 2002) * A03) +

etc.

In other words, the first part of the expression yields a 1 (true) or 0 (false), then multiply by the field and add it up!

If you want to make it more flexible, replace "2002" with a number field, e.g. MyYear, or an expression that evaluates start and end dates. Think about it and if you still need help let me know.

Posted

"Hope that helped! If so, Rate Me!"

Fitch: you are a tart!!

Posted

Hey, that's great! Thank you. It works like a charm. I did run in to trouble when I tried setting up a more flexible field (YearToUse). The year has to be entered on every record I want to sum, which I can do quickly using the Replace function. It should probably be a Global Field. I haven't worked with Global Fields, but I guess now's a good time to start.

Thanks again,

Chuck

Posted

You're right, I should have specified global fields, that's definitely the way to go.

Like this:

((D01 great.gif globalStartDate and D01 less.gif globalEndDate) * A01) +

((D02 great.gif globalStartDate and D02 less.gif globalEndDate) * A02) +

((D03 great.gif globalStartDate and D03 less.gif globalEndDate) * A03) +

etc.

This topic is 8307 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.