limiting a field list based on a variable

[#1]

Hi,

I have a field called MonthName which is self explainatory.

I also have a variable called vCurrentSeason. There are 2 options here, summer or winter. Summer season goes from August to January (this is a retail season in Australia if you are concerned its a cold time of year for summer in your area) and winter is February to July.

When Summer is entered into the variable vCurrentSeason via an input box, I want to limit the number of options visible in the field MonthName to only include August, September, October, November, December, January.

This will avoid users selecting months outside the summer period.

Anyway of doing this in the field selection box?

Thanks

David

[swuehl]

Hi David,

in your list box on general tab, you could use an expression as field, something like:

=if(vCurrentSeason='Summer' and match(MonthName,'August','September','October','November','December','January'),MonthName,

if(vCurrentSeason='Winter' and match(MonthName,'February','March','April','May','June','July'),MonthName))

It would probably be easier if you add a SeasonFlag as a field to your data model, but above should do the job for the moment.

Hope this helps,

Stefan

[#3]

Hi,

Hope this file helps you..

Regards,

Lavanya

[#4]

Hi,

I have it working but it feels like a bit of a hacked together job..... as a Management Accountant and not a developer I think its not too bad

I took the flag idea from swiehl and created 2 extra columns in my period list, e.g. SummerMonths & WinterMonths

Period Name, Season, SummerMonths, WinterMonths

May-11,           Winter,      NA,                Winter

Jun-11,            Winter,      NA,                Winter

Jul-11,             Summer,   Summer,         NA

Aug-11,           Summer,   Summer,         NA

I then used a formula that inserted the SummerMonths into the multibox if the current season is set to summer or WinterMonths is the current season is set to winter and excludes the months marked as NA as below.

=if(vFiscalCalendar = 'Season',if(left(vCurrentSeason,1)='S' and SummerMonths<>'NA',SummerMonths,if(left(vCurrentSeason,1)='W' and WinterMonths<>'NA',WinterMonths)),MonthName)

I have 3 buttons which run macros to change all the data by Season, Calendar or Financial year which creates the value for vFiscalCalendar. If the Season button isn't select then the field MonthName is presented instead which lists all months. The different order of months between Fiscal (Jul-Jun) and Calendar

is handled by a sort formula.

Thanks for the help it, let me know if you see any efficiencies that can be gained

Thanks

David