Problem W/ Script... Please Help...
Good Morning Everyone. Hopefully this is simple to do, but the more i try the more i get confuse. Im trying to create a script that will do the following, but first let me tell you how i have this database so far.
I have a Art Request Table, and a Products Table link via "ar id" field (auto serial # is the Art Request table)
I also have a products Table, and a category table. (no links)
Products Table contain Category Table contain
Products name Category Name
Products code Category Code
The process that i have so far is this.
1-create art request
2-art request is routed to Art Director
3-Art director decides if a "New Product" has to be created
or if a "Revision" to an existing product needs to be revised.
If art director clicks on "New Product" i need the script to do the following (THIS IS WHERE IM REALLY STUCK)
***A NEW PRODUCT CONSIST OF CBM1000-A**)
first 2 letters is the product line code
the following letter is the Category code
numerical value based on Product LIne and Category combined.
so we have different product lines and categorys, so i need the database to know the last number it was created for that specific combination of Product LIne and category.
a-new window will come up asking me to select product line and category Example (CB=product line code) and then i select M for the Category code. so we have CBM, at this point i need the script to search the entire Products Table or another one i don't know to see what the last Product Number that was created for this combination. for example if the last onewas CBM0934-A,the this new one will be CBM0935-A. THE "A" represents the first Product Number Version for this Number.
**IF art director clicks "REVISION" then what i need the script to do is the follwing.
a-again new window selects PRoduct line and Category, BUT after selecting product Line and category i need a field to show me all Product Numbers that match the product line and category. So i would see a list of 50 or more product numbers with different Versions. SO for example
if i i have a list of 5 product Number.
and i select # 4 (CBM0349-B) then i want my script to create a new revision of that product. so it would create CBM0349-D.
SO the version is based on the alphabet.
I KNOW THIS IS A LOT OF INFO, BUT THE MORE THE BETTER I THINK.
Please let me know if you have any ideas how to do this...
THANKS FOR YOUR TIME..