Trying to make a unique serial from combination of other fields

[ClassAttire]

Please help. I'm a newbie, but I'm hopeful that what I want to do here is possible. : /

I have several fields for my products (size, color, design, etc.), each with from 12 to 30 values each. I would like to assign a number to each value in each field, then, based on the combination of these fields, produce a unique number (ITEM CODE) for each product.

Here is an example of what I would like to do:

SIZE Field

-small (assign number "1")

-medium (assign number "2")

-large (assign number "3")

COLOR field

-red (assign number "01")

-blue (assign number "02")

-yellow (assign number "03")

-etc...

DESIGN field

-square (assign number "01")

-triangle (assign number "02")

-star (assign number "03")

-etc...

Based on the selection for each field, I would like to enter data into the ITEM CODE field. For example, a small blue triangle would be 10202 (1 + 02 + 02)

Is this something that can be done?

Thank you for your help!

Chris

[RodSierra]

I'd recommend from experience you not build a code in this manner.

Instead set your product code to a simple auto entered serial number. When starting a new system with one of our customers we usually start the sequence at 10000. That number is an auto enter serial number and is truly random with regards to the product specifications. Also set at that time is a 4 letter prefix that allows for quick identification of the products key attributes.

You can then display the attribute information via calculated text fields to contantinate a description any way you want.

This insures you have a unique code for the product, that is also useable throughout the entire process. Having a short product code makes life easier all the way thru the system, allows for quick data entry and searches, and reduces errors. One example would be

BDLS-56799 Lt Green Small Button Down Long Sleeve Bulk Pack or also displays as

BDLS56799LGSBP

The only entry is the 5 digit code which is unique for that product, the description strings are then done via calculated text fields as required.

[ClassAttire]

Thanks. Why would you recommend I not build a code in this manner?

If I was to build the code as you recommend, how would I do so? I guess I basically want to know the calculation I would use for this. I'm not a pro at scripts...yet. : )

Also, once these serials are set, I would like to be able to then enter values into several fields and return the serial number that correlates to the combination of values in the fields.

For example, the calculated serials at issue would be records in my PRODUCTS table. I would then like to use another table, called ORDER FORM, to retrieve the serial via a lookup, I think.

So, for this example, I have three pop-up menus: SIZE, COLOR, and DESIGN. There is a set value list for each field. I would like to select a value from each field, coming up with a unique combination, say "Small Blue Triangle". I also have a field called ITEM CODE. Based on the combination of the three fields (SIZE, COLOR,DESIGN), I would like the serial to enter in the ITEM CODE field.

Thank you for your help! And for replying so quickly. This is a great forum for newbies.

Chris

[RodSierra]

Chris, I'm not very good at taking newbies thru this, and probably should not have started this with you so if I drop off due to other commitments, or my inability to explain in simple terms the details, I apologise in advance. You should think again about your product code as being an arbitrary number in the form of an auto enter serial number, that you can then use to form a product code by using other fields in your product table to form a pseudo or visible product description that gives more information about the product, but yet allows simple entry via a limited length numeric field. Sometimes my mouth gets in front of what I can explain clearly here in beginner terms so I'll try and shut up and leave the details to those valued folks here that are more capable of taking you thru this, or perhaps they have another way of dealing with your needs. Hope the little I've provided helps more than confuses.

[ClassAttire]

No worries Rod. Thanks for your help.

[David Jondreau]

The prohibition is against using such codes for realationships. Using them to look up the real key is fine. You can do that lookup using relationships or a find.

This will give you a sense of the calculation you may need:

Let([

size = table::size ;

size = Case ( size = "small" ; 1 ; size = "medium" ; 2 ; size = "large" ; 3 ; 0 ) ;

color = table::color ;

color = Case ( color = "red" ; "01" ; color = "blue" ; "02" ; color = "yellow" ; "03" ; "00" )

design = table::design ;

design = Case ( design = "square" ; "01" ; design = "triangle" ; "02" ; design = "star" ; "03" ; "00" ) ;

result = size & color & design

];

result

)

[ClassAttire]

Thanks David. That's what I was looking for. I had a feeling it was the case script.

Any idea of how to do the second part of what Im trying to accomplish? (below)

Also, once these serials are set, I would like to be able to then enter values into several fields and return the serial number that correlates to the combination of values in the fields.

For example, the calculated serials at issue would be records in my PRODUCTS table. I would then like to use another table, called ORDER FORM, to retrieve the serial via a lookup, I think.

So, for this example, I have three pop-up menus: SIZE, COLOR, and DESIGN. There is a set value list for each field. I would like to select a value from each field, coming up with a unique combination, say "Small Blue Triangle". I also have a field called ITEM CODE. Based on the combination of the three fields (SIZE, COLOR,DESIGN), I would like the serial to enter in the ITEM CODE field.

Thanks again!

[David Jondreau]

You can do it in two ways, with a relationship or with scripts. I prefer scripts, but a lookup/auto-enter calc is good too.

You'll want a script that will:

check to see if any of the three fields are empty, if so exit

If not, store their values in variables

go to a products layout

find on those fields with those variables

store the serial in a variable

go to your original layout

set your item code field to the variable

[ClassAttire]

Okay. I'm not great with scripts. Okay, I'm not great with anything in FM yet, but how would I do this with an auto-enter? I've got a conditional value list in a portal. I've got all of the conditions working. Just need to get the Item Code to auto-enter once all of the fields match up.

[David Jondreau]

I think doing it by script is easier. But:

Create a relationship between Orders and Products based on the three pop-up menus in Orders and the three corresponding fields in Products.

Set the Item Code field in Orders to auto-enter calc = Products::Item Code based on this relationship.