Hi,

Wondering if anyone has any idea how to do this; I have a simple database in which each record can have one of 2 or 3 possible entries from a validation list in several fields. For example one field entry could be "chocolate", "vanilla" or "strawberry", and another could be "cone" or "dish" and another could be "cash" or "credit".

I am trying to make a unique code for each possible combination that would auto-enter, based on the values in the other fields. So someone ordering chocolate in a cone and paying cash would be "1", and someone ordering vanilla in a cone with cash would be a "2" etc...

I am hoping this can be done by calculation, but at the moment, the only way I can think of doing it involves a script. Any thoughts welcome. Thanks,

Marcel

Hi Marcel,

I have attached an example of how you could do this. You should take into account that you want for instance more than one icecream flavours per order and that you can easily add new payment methods or packing material without having to redefine your calc. every single time. If not it´s ok, but with databasing it´s just like marriage...be prepared for the worst :

So In the example I already did that for you. Hopefully you can adjust it to your system. Good luck.

valuecode.zip

Edited October 20, 200520 yr by Guest

One very simple method would be:

Case (

Flavor = "chocolate"; 100 ;

Flavor = "vanilla" ; 200 ;

Flavor = "strawberry"; 300 )

+

Case (

Packaging = "cone"; 10 ;

Packaging = "dish" ; 20 )

+

Case (

Payment = "cash"; 1 ;

Payment = "credit" ; 2 )

The codes here not contiguous, so another packaging or payment method (up to 10) can be introduced in the future. There is also a flag if one of the fields is empty.

If you need contiguous codes, you could use something like:

4 * Case (

Flavor = "chocolate"; 0 ;

Flavor = "vanilla" ; 1 ;

Flavor = "strawberry"; 2 )

+

2 * Case (

Packaging = "cone"; 0 ;

Packaging = "dish" ; 1 )

+

Case (

Payment = "cash"; 0 ;

Payment = "credit" ; 1 )

Note that here adding a packaging or payment method would make all previously generated codes invalid.

4 * Case ( 

Flavor = "chocolate"; 0 ; 

Flavor = "vanilla" ; 1 ;

Flavor = "strawberry"; 2 )

+

2 * Case ( 

Packaging = "cone"; 0 ; 

Packaging = "dish" ; 1 )

+

Case ( 

Payment = "cash"; 0 ; 

Payment = "credit" ; 1 )

Indeed, but what if we arrange it to live in a cut up portal, where each portalrow points at it's own valuelist, the position in each valuelist gives where in the numberbase and number of items in each valuelist gives the numberbase in itself. It would be very easy to maintain by adding or removing items from the valuelists as far as getting the various combinations.

--sd

That is exactly what I meant with my examplefile:

ordercode explained:

3= 3 flavours

1= chocolate

7= banana

10= melon

1= cone

2= credit

valuecode.zip

Edited October 20, 200520 yr by Guest

Wow, thank you for the flurry of responses. I have it working! Many thanks.

Marcel