Formula Field: Use Autonum/Consecutive numbering ONLY when value is not unique

[Jessica_Carey]

Hello! (tl;dr below)

I have an Signage Inventory base. One table is “Designs” with each record being one design with the proofs and text attached. Another table is “Main Building” where each record is a PHYSICAL sign stored in the main building with a “Design” field that links to the design table.

I’d like the primary key for the Location to be a concatenation of the “Name” field (primary key) from designs & dimensions & [#]; BUT i’d only like the number to increase when it needs a unique value. Right now I’m using autonum but that’s of course numbering by record.

tl;dr: Is there a way to change this:
[Design|Name] & “_” & [Design|Dimensions] & “_” & [Autonum]

(which gets me this:)
NO-climb_11x17_1
NO-climb_11x17_2
NO-touch_11x17_3
NO-touch_11x17_4
NO-touch_9x12_5

So that it will give me this:
NO-climb_11x17_1
NO-climb_11x17_2
NO-touch_11x17_1
NO-touch_11x17_2
NO-touch_9x12_1

Thanks for any help!

[HammerOz]

Hi there

Wouldn’t you create an auto number in the designs table and use a lookup field in your building table. Build this field in designs. The location id might then be a location name / auto number & the design linked field or a lookup of other items from designs. That will solve your auto number issue.