Select year with radio button to calculate statistic

[RacingLists]

Hello: assistance is requested with this simple calculation.

I want to be able to select the year of interest with a radio button and to make a calculation based on the data in that year. Yearly data for one record is lumpped together and not in annual tables (individuals in rows and up to 15 years data in columns). Is there a simple calculation to accomplish this without having to start over and manipulate the base data file?

The file is attached. Thanks!

changeyear.zip

[sbg2]

You're not giving us enough information to determine exactly what you are trying to calculate. For starters try changing the stats1result field to a calculation field, select Unstored after clicking options button and use the calculation:

Case (

Select_Year = "2000"; Data_2000;

Select_Year = "2001"; Data_2001;

Select_Year = "2002"; Data_2002;

// If no valid year selected default to:

0)

This will return the value in the data_2000...2003 field dependant on which Year radio button is selected. See if that is a push in the right direction.

[LaRetta]

Hi RacingLists,

Your structure is the problem. But then (at least now you know that). Sometimes we're stuck with old solutions and can't change the structure immediately but do it as soon as possible, okay? In the meantime, it is possible and it doesn't have to be hard-coded in the calc. Again, you haven't given us the requirements. Calculation for Stats1_Result (number):

GetField ( "Data_" & Select_Year )

This calc can be indexed (as could sbg2's) or you can even use standard number with Auto-Enter (Replace). If this is multi-user, you should make Select_Year global. If you make it global you MUST make Stats1_Result standard number with Auto-Enter (Replace) if you need to index it. But this will give you the base depending upon radio button selected. What are the criteria for the evaluation and we can slide that into the formula?

UPDATE: Here's an example. I'd like to help you eliminate hard-coding anything but it depends upon your needs. This simply takes the value in the selected field and multiplies it. Your calc can be just about anything you wish.

GetField ( "Data_" & Select_Year ) * 10

If your needs are more complex, we'll probably want Let() to keep the logic straight. If there is no pattern to the yearly increments, you'll need to either group the values (if rate changes on even years, for instance), use Choose() which is somewhat limited or maybe Case() which will be hard-coding. And you can also turn that Custom Value List into a dynamic one which will expand as your years expand. But then ... you'll be having to open Define Fields and adding a field each year anyway so we can't make the whole thing dynamic (as we could if structured correctly). DRAT! You see (one of the reasons) flat-file structure is so clunky?

LaRetta :wink2:

[RacingLists]

Thanks sbg2 and LaRetta for the help. The script works fine and I attached the end result ('changeyear.zip' probably not worth opening for you guys).

changeyear.zip