5 Replies Latest reply on May 14, 2009 12:12 PM by philmodjunk

# Need help creating specific calculation/function for list/report layout set-up

### Title

Need help creating specific calculation/function for list/report layout set-up

### Post

I have a very specific problem/question:

I'm creating a list/report layout, grouped for subtotals and grand totals. I have a field in my database that is called "Life Remaining" and shows a number. Each record in the database is for a difference piece of equipment, and the Life Remaining field references how much life expectancy is remaining in that piece of equipment. I want to be able to sort my list/report into three groups: Life Remaining is equal to or less than One Year, Life Remaining is between One and Five Years, and Life Remaining is Greater than Five Years. Each of these groups would show a sub-total of the Estimated Cost of Replacement (another field in each record).

I know how to set up the list to create subtotals (I have done so in another layout to sort according to Priority Number), but I can't figure out what kind of calculation or function will give me an answer in a single field that can then be used to sort the list. I think what I want to do is have a field that has a function which would be something like (in layman terms):

Life Remaining equals which of the following: (Equal to or less than 0), (Between 0 and 5), and (Greater than 5)

This would give me a single field which then shows one of the following:

"< 0" (meaning Life Remaining is equal to or less than 0), "> 0, < 5" (meaning Life Remaining is between 0 and 5) or "> 5" (meaning Life Remaining is greater than 5) - or something similar.

I could then use this new field as my choice when I "Organize Records by Category" in the layout set-up. This would give me the subtotals I need.

If anyone has ANY hints or suggestions, or just knows how to create a function like that, I'd love your help! It's been a while since high school math and I'm just hitting a dead end. Let me know if you need more clarification.

Thanks so much!

• ###### 1. Re: Need help creating specific calculation/function for list/report layout set-up

Case (LifeRemaining < 0; 0; LifeRemaining > 0 AND LifeRemaining < 5 ; 1; LifeRemaining > 5; 2)

Sort it on that calculation field to group your records into the three groups you need. You can use any values you want instead of 0, 1, 2 as long as the values sort into the order you want in your report.

• ###### 2. Re: Need help creating specific calculation/function for list/report layout set-up

Hi mkenzie

something along this line may work:

Case(
Life Remaining ≤ 1 ; 1 ;
Life Remaining ≤ 5 ; 2 ;
3
)

• ###### 3. Re: Need help creating specific calculation/function for list/report layout set-up

Hi PhilModJunk,

Your calculation format worked great, thank you so much!! I knew what I needed, but not how to express it in a function.

Now, there's one more aspect that I've discovered I need to add to the calculation.

I need to exclude certain records from the list based on what it says in a certain field. Specifically, there is a field called "System State" and when it says "New", I don't want those records included in the list. How do I do this? Would I accomplish this by adding something to the Life Remaining Calculation, since it sorts the information, saying (in my layman terms) "When System State = New, do not include"? Or is there another way?

Meredith

• ###### 4. Re: Need help creating specific calculation/function for list/report layout set-up

Buon Giorno, Daniele! I had a reply from another forum member, but your idea was similar, so grazie!

Parlo un po'italiano, ma il vostro inglese e molto meglio :)

Meredith

• ###### 5. Re: Need help creating specific calculation/function for list/report layout set-up

If you want the record to be omitted from your report, omit it from the found set:

Enter find mode

Enter "New" in the System State field.

Select the "omit" option

Click Perform Find