Ugh! Someone created an HQ Material code that didn't follow our company code. If only we could enforce a code format of our choosing just like JC Jobs does. Today I'm going to show you how to enforce your code formats using F3 Field Properties, Regular Expressions (Regex) and ai.

The other day I was trying to figure out a regular expression to enforce a numbering system and had a hard time with it. It's a whole other language. I decided to google "regex generators" to see if I could find a decent one and I stumbled across a free ai regex generator. Now, if you like copilot or chatgpt then you'll love this gem of a website. Surf on over to https://workik.com/ai-powered-regex-generator and follow along by typing or copy/paste the parts that I've bolded. If you've never dipped your toes into ai, this is a great starter.

So, I wanted to enforce a special numbering system for Vista's HQ Materials. We use 3 digits - 5 uppercase alpha - 4 digits for year. We have troubles with our folks confusing upper case i's for ones and upper case o's for zeroes and would like to exclude them from the middle segment. I also don't want any third segments with a year of 2020 or less.

In the website I type in a sample code 123-parts-2022 and the ai explains to me what it thinks it sees and offers a regex code ^\d{3}-[a-zA-Z]+-\d{4}$ along with a nice explanation of everything it's doing.

Great, then I realize I used lower case alpha instead of upper case. I type The middle segment should be uppercase only and the ai very nicely tells me it has a modified regex code of ^\d{3}-[A-Z]+-\d{4}$ along with a lovely explanation.

Next I tell it I want to exclude the letters i and o in the middle segment and I get ^\d{3}-[A-HJ-NP-Z]+-\d{4}$

Sheesh. I would never have figured that out. I still need to do the last segment and tell it The last segment should only have numbers greater than 2020 and it gives me back a doozy, ^\d{3}-[A-HJ-NP-Z]+-(202[1-9]|20[3-9]\d|2[1-9]\d{2}|[3-9]\d{3})$

That was as non-nerdy of a conversation I've ever had with a computer...or one of the IT nerds...of which I proudly am. I head over to Vista's HQ Materials and press the F3 Field Properties key on the Material field, click the System Overrides tab and fill in the validation fields like so.

Bingo! I can now tear that sticky note off my monitor that reminded me how we like our HQ Material codes to look because Vista is now doing all the hard work. I hope you enjoy this tip and our other tips we publish on Tip-Tuesday. We love tweaking Vista to enable it to make your job easier. Feel free to reach out if you would like help on this topic, Vista, SQL, SSRS, PowerBI, or Crystal Reports related. It truly is our pleasure.