'Require entries to be unique' also for an Auto Generated primary field
Thomas Barnekow
I would also love to see this and further suggest to extend this to formula fields as well.
My use case is that I am computing several kinds of IDs for records that are conceptually attached to a parent or container record (i.e., tree structures). Using an abstract example, an element is contained in a set. The set is identified by an ID like "21" or "32". The elements within the sets have component IDs (e.g., "100", "310") that must be unique within the respective set. The elements also have compound IDs, (e.g., "21-100", "21-310") that are computed from the set and element IDs. Those compound IDs must be unique within the table. Both constraints could be ensured by allowing unique constraints on formula fields. At least conceptually, this would be very similar to unique constraints on auto-generated titles.
Jon Darbyshire
Great to hear your perspective, Dino Kovačević! I have a few more questions for you:
- Can you provide a specific example of when you encountered this issue?
- What impact does this issue have on your workflow or productivity?
- Are there any specific fields you would like this feature to be applied to?
D
Dino Kovačević
Hey Jon Darbyshire, here are my answers:
- The specific example would be: The primary field's name is made up of values from two other fields from a record, as this best describes a record, hence Auto Generated option is the best choice.
If I use example from a client's case that would be the following: Client wants to track gift giving to their many business partners and they want to make sure that their employees (whoever goes to a business partner at that time) doesn't bring the same type of a gift that a business partner might have already received it previously. Employees, when selecting a gift to take to a business partner, are creating records in a table for tracking gift giving.
- The client would like to make this fool-proof so that they are sure their employees won't be creating new records with the same values without needing to manually input each time they are creating a new record.
- Yes, this would be applied to the Primary field.
Another solution for solving this challenge that would also be neat is if we could apply Spotlight function but without needing to manually input a value when creating a Spotlight.
N
Nik P
Hi Jon Darbyshire, reviving an old one here.
I also have a need for something similar. The use case is as follows:
We have a table for vendors and another table for invoices. On the invoices table, I would like to create an auto-generated primary field that is a built from another field for invoice # and a third field for a link to the vendors table. If the auto-generated primary field could enforce a unique constraint, this would effectively make the auto-generated field a unique compound key, and would effectively prevent entering duplicate invoices per vendor.
I have several other scenarios that would benefit from this pattern as well as I build out business systems, and the only workaround I've found so far is to create a formula for each invoice that checks the link to the vendors table for all invoices linked to the vendor, where the invoice number matches the current invoice number, and the count is greater than 1. This is a formula with an IF wrapping a COUNTIF and spanning effectively 3 tables of searching and joining, and marking duplicate records as "Duplicate" if any are found.
The approach I have now is not consistent in it's behavior, sometimes taking a long time to update and sometimes not updating at all. I've been playing with it and I'm also finding that sometimes it only updates one of the records and removes the "Duplicate" flag if I change the invoice number of one of the duplicate invoices.
All in all this would be a huge boost in productivity and ensuring data integrity across users in our company. I'm glad to discuss further if needed.