Complicated Key Naming Convention

Ben_Ellis-Gowla · 02-16-2021 01:53 PM

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

Steve

First read and understand the risks described here:

Then consider the approach presented here:

perissf

I completely agree with @Steve 's post about risks of using sequential numeric identifiers.
Another approach would be to let AppSheet assign unique IDs in the standard way (through autocomputed UNIQUEID() values). And, for the naming, use a virtual column that COUNTs the existing Shipment related records of each type, with timestamp prior to the considered one, and assigns a name equal to that COUNT + 1

Ben_Ellis-Gowla

Ok I get that it might not be a good idea to use it as a key.

The problem still remains though. I don’t know how to out together the formula for something like perissfs solution

perissf

Can you give us more detail about the Tables and references? Don’t need all the columns, only the references among them.
I.e.
Shipments table contains column KEY and column Freight Method that references table Fright Method.
And so on…

Ben_Ellis-Gowla