Jump to content
Sign in to follow this  

Value list for account numbers that change every year

Recommended Posts


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


Share this post

Link to post
Share on other sites

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.


Share this post

Link to post
Share on other sites

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
Sign in to follow this  


Important Information

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