Building reference numbers for different selections from defined list

[Excheltenham]

Hi there,
Sorry folks not sure even where to start on this. At work I have 12 sites , and I need a work code for each variable so i can identify jobs.. I.e. If supplier goes to site 1,2,3 I need one code, or he goes to sites 6,8,4,9 i need another. I need enough options to cover all variables with a separate reference for each distinct choice

Any ideas , please

 

[Gingertrees]

Welcome to the board
We'll need a little more info to help you, though. I think you might need to have ID Ranges established for suppliers, and another for worksites, like the following examples.

Please provide some more details, and we'll go from there.
Suppliers example:

SupID	SupName	SupLocation
AX01	Acme Widgets	Auburn Hills, MI
AV12	Northwind Industries	Baltimore, MD

<tbody>
</tbody>

Worksites example:

SiteID	SiteDesc
INITECH01	Detroit, MI location
INITECH02	Flint, MI location

<tbody>
</tbody>

 

[Excheltenham]

Thank you so much for your help and advice , and ok here's some more flesh on the bone.

There are 12 stores that get deliveries and each delivery may to any of the stores. to create a work order i want reference code which is constamt and unique to the stores delivered to.

let's say 12 stores include .. Fitzroy, Brunswick, Richmond, Toorak , Hawthorn etc

So if the delivery goes to only Fitzroy . it's code 00001 ,
If delivery goes to Brunswick & Hawthorn it's code 00065
If delivery goes to Toorak, Richmond & Fitzroy it's code 00147

So I need a way to create unique and fixed identifiers for all possible scenarios.

Again , thanks in advance

 

[Gingertrees]

Ah, making me dig through math skills I haven't used in a few years... If any delivery could go to any combination of the 12 stores, as low as 1 and up to 12... You would need 12 + 66 + 220 + 792 + ... codes. While I could do the other 12Cx calculations and line up that many codes for you, that's not the most efficient way to develop this.

WHY: Say you've got work order A, with delivery codes for 3 pallats of widgets going to Code ID2002. Say this represents going to Fitzroy, Brunswick, and Toorak. This code is tied to other data - store location(s) for each of the three, payment info for each of the three, etc. Anytime code 2,002 is called, the system needs to rifle through thousands of records to get the data to say that this delivery goes to Fitzroy, Fitzroy address, Brunswick, Brunswick address, and Toorak, Toorak address, etc..

Instead, it would be much more efficient to have delivery slots available to be filled with any number of up to the 12 stores. Then the system only has 12 records to go through at each instance - in this case, 12 * 3 = 36 records checked. Fewer = faster.

Not to mention the fact that work order A going to FITZ01, BRUN02, and TOOR04 is much more readable to workers filling and delivering this order.

 

[Excheltenham]

Thanks so much for that, but I would see the calculation as a great starting point. Could you please asssit with that please ? Thanks

 

[MickG]

Try this example of Store combinations.
NB:- Change the 12 store names in colum "A" and re-run the code.
https://app.box.com/s/c8e1l2kwprz1d76ua1zsy5aw231mv0ha

 

[Gingertrees]

As you can see from Mick's (surprisingly detailed) example, every order will need to cycle through thousands of possibilities.

 

[Excheltenham]

Wow Mick that is fantastic ! It solves part a, and yes Ginger take on board ramifications if this ever becomes much more than a labelling excercise. Thank you both !