Input Mask/Validation Rule

[ethan.geerdes]

So I have "launched" my application as a "beta" version in our office and the first person that tried to use it (my boss) wanted to be an original and try to "stress" test it. He has enough knowledge of stuff to get him into trouble but needs a true IT like myself to get him out. Well, a few errors later I told him "you know what? forget it. I'll go back to the drawing board and try to idiot proof it so you can't be an original."

So now that I have the back story laid out, I can tell you a little bit about my database.

I have a table called "Systems". This table consists of different names of things we use and there is a column named "System_Code" that has a 3 letter code for each system for example, lets say there is a system used for medical it's code would be MED.

Well, if there is a message or an email that comes out for the system we use, we input a ticket to assign it to our IT workforce to make sure it gets implemented

I have the input mask as follows:

>LLL"-FAM-"000\-0000;;

the LLL is supposed to be for the three letter code that goes with the advisory message (which is what the FAM stands for) and then the three 0's for the number of messages that have been sent for that system and the last four 0's are for the year.

How can I make sure to have the first 3 letters be for the system code from the system table?

Any help would be greatly appreciated.

 

[jdraw]

My first reaction is that you are trying to put too much intelligence into a code.
My suggestion would be to analyze the requirement a little more-

different names of things we use

--
make a list of those things, and a related list of the "whatever FAM is an example of". (Sounds like category or MajorGrouping...). Store these in tables and use the values in your business/application as needed.

Construct your messages using "code for NameOfThing" "CategoryThingBelongsTo"....
I would also suggest you make a list of message (info, error,criticality...) that you can use across Categories and/or Things as appropriate.
You can count messages via queries if and when needed.

Anyway, just a few thoughts for consideration.