I’d like to create a list of numbers that are unqiue. In Airtable I would use the ArrayUnique formula.
In my case I have a list of contacts with a one to many relationship to phone calls.
I’d like to be able to determine which contacts were contacted each year and then extract other information from the contact records (such as birthdate, shirt size, etc).
My initial thought is to create a formula field on the contact record that looks up the year of each linked phone call and then returns a deduped/unique list of the years.
Any ideas about how I can pull this off?
It might be easiest to create a ‘helper’ database for the set of possible years, and then auto-relate these to the phonecalls, i.e. by matching the Year’s name against a new formula field in the phone call db: ToText(Year(DateField))
Then you can just use a lookup in the Contacts db to get all the possible Years linked to its phonecalls.
It also has the advantage that you can do some useful queries from the Year database.
Oh, just to be clear this thread is about returning results in a field. I am not looking to remove duplicate entities.
I’m curious to know if there is any workaround for not having a “unique” or “arrayunique” formula that would take a collection field and output a list/array that only has unique values in another field.
Well, Fibery doesn’t really have the concept of arrays, except as entities in a database.
So there is no such thing as the ‘unique values in a collection field’ since all entries in a collection field are, per definition, unique.
Of course, they might have identical values in one of more of their fields (which seems to be the case you’re interested in).
If the field by which uniqueness is determined is a select field, or a collection field, then the lookup method described above will work.
Otherwise, it’s not possible, since there is no valid datatype for storing the result.
So for example, if it is a number field, and you want all the possible unique values of that number field, the result would be a collection (array) of numbers (which is a primitive datatype) but there is no way to store an array of primitives.
I hope that makes sense.
It’s not meant to be an attempt to undermine/disagree with what is a clearly-defined need, just an explanation of why it is not currently possible.
Yes, this has been a bit of a thorn in my side as of late. It’s hard to work around the lack of simple arrays. Looks like I need to put in a feature request
You have contacts [C], each of which can have multiple phone calls [PC], and each [PC] has a year in which the phone call was made [Y].
Now you want to see in a separate field, a list of all years in which at least one phone call was made?
So if you have a contact which has had 12 phone calls, 10 in the year 2019, 1 in year 2021, and 1 in year 2022, you want to see a field like this: “2019, 2021, 2022”
Is my understanding correct?
Edit: [Brackets] indicate a database/type (as opposed to just a field)
Well the Array field feature doesn’t exist yet to my understanding so it’d be great to have it.
For my use case, I’ve created a Year database and it is linked to phone calls. I can create a unique array of years contacted by lookup years from the Phone Calls database.
