Value list for account numbers that change every year

[ktalley]

Hello,

I'm adding in a Purchase Order module to my existing music department database and need some guidance.

Our department has 15 accounts from which purchases are made. The account numbers on these accounts are static except for 2 digits that change based upon the fiscal year (from July 1 to June 30). I would like to have a way when creating a PO to select from a drop-down list that shows accounts only for the current fiscal year. I will need to keep a record of the POs and their year-specific accounts from past years.

I can create a calc field that calculates the correct account number at any time of year for each account, but I don't know where best to store the account numbers for the list. Do the account numbers need to be in their own table? In the PO table? I have a preferences table with one record that I use to get lots of other information, but that won't give me a value list.

Thank you for your assistance

kt

[Fitch]

Sounds like the acct numbers should go in their own table for the purposes of this value list. Depending what you want to do, you could keep one table with all acct numbers, and another table with only the current numbers. Then your value list could use the "all values" option.

Another way to go is to make a fiscal year -- i.e., the 2-digit code -- field in your prefs table, and create a relationship from there to a single table with all acct numbers. Then your value list would use the "related values" option, starting from prefs.

HTH.