Complicated Key Naming Convention

[Ben_Ellis-Gowla]

Hi -

I have a table with Work Orders in. Each Work Order can have multiple shipments linked to it in the “Shipments” table.

Each Shipment will have a “Freight Method”.

So for example:

Order 1, might have 3 Shipments linked to it (2 x “Air Freight” Shipments, and 1 x “Sea freight” Shipment).

What I would like to do is name those shipments by Freight Type, and then by an increment to do with the count of shipments attached to that order, with the same freight type.

So in this example above they would be named:

Order 1 - Air Freight - A
Order 1 - Air Freight - B
Order 1 - Sea Freight - A

So I need to get a formula which first selects, all of the existing shipments linked to the same order as the existing shipment, then count the number of values with the same Freight Value as the existing shipment, and then turn that into A,B,C format (if possible).

I will use that as the Key, and then I will need to setup another column I think which would just extract the second half of that (without the order number) for displaying in user views. having the full key name with Order number would be unnecessary. (e.g the key “Order 1 - Air Freight - A”, would be shown to user as “Air Freight - A”.

So I know the first bit of the Key, which is fairly straightforward:

=Shipments[Work Order ID REF] & " - " & Shipments[Freight Method ID REF] & " - " & ???

It’s the last bit where I select matching freight types for that order, and then count them, and then add a new incremented one in ABC convention. (if not possible, then numeric convention would be workable).

Please help!

Thanks,

Ben