Problem Creating Script.. Don't know how create new/revision product

[Soccerfan1978]

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.

1-CBM0145-A

2-CBM0145-B

3-CBM0349-A

4-CBM0349-B

5-CBM0349-C

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..

 

[JeanW]

Indeed a lot of info.
But it's not enough.

Let's go step by step.

= which version of FM do you use. Possible techniques will be different.

>> 3-Art director decides if a "New Product" has to be created

How ? Is it a specific button, a given procedure?

To build your sequence number/letter you need some relationships. But before we can tackle that we need to 'prepare' your data first.

f.i. for your CBM1000-A. Are the different pieces of that ID in separate fields?
What if the range reaches the 'Z', does it go further as 'AA', 'AB', 'AC',.....?

Be prepared that your answers will generate more questions...