Filter on a Collection

When using .Filter() on a Relation Field in a Formula is there a way, to validate against another Field of my current database? Besides dynamic date variables like Today() I’ve only seen examples validating against static values.

Filter() does support dynamic values, but only values accessible in the database of the collection that is being filtered.

For example, if a Project database has the fields Name, Due date, Assignees and Tasks, and a Task database has Project, Name and State fields, when you create a formula in Projects which is Tasks.Filter( condition ) then condition can only access fields in the Task database.

But you can do Tasks.Filter(Name = Project.Name) for example.
Does this make sense?

I think I get it, but unfortunately my collection is not a relation, but a lookup, so it doesn’t seem to work.

I tried going through both the relation and the lookup, but i get a weird error, that ‘Cannot compare Date and Date’ :smiley:

What are the databases, how are they related, and what are the relevant fields?

Without knowing that, it’s a bit hard to guess the problem. It certainly looks a bit odd that you are using Containers.Dates.Sort(Date, true).First() since this would imply that Containers have Dates, and Dates have a Date field. Is that really the case?

Maybe you mean Containers.Sort(Date, true).First().Date ?

I have 4 Databases: Currencies, Containers, Acqusitions, and Rates. Currencies are related to Containers and Rates, Acqusitions are related to Containers.
In the Rates Database I can get the related Acqusitions through the Currencies Relation → Containers Lookup → Acquisitions Lookup. Here I want to Sum a field of all these Acquisitions which Date field is less than the current Rates record. If that makes sense. The above example was wrong, I fixed it but now I got a different error. The formula field I’m trying to create in the Rates Database is:
[Currency Containers Acqusitions].Filter(Date < Containers.Currencies.Rates.Sort(Date, true).First().Date)
and the error is:
Cannot create a formula with a collection field as a result and “First/Last” functions used inside.
The field [Currency Containers Acqusitions] is the relation’s lookup’s lookup.
If I add .Sum(Amount) at the end the error is gone, and the field can be finalized but no data is returned, but I guess that is somehow related to the error above. Even a .Count() ending is returning an empty value instead of 0.

So this doesn’t seem possible

Indeed, it is not possible, sorry.
The issue is that the Filter function needs to be calculated on ‘immediate’ values (i.e. ones that are determined without the use of further functions). This means it can’t use the Sort().First() combination.

If you tell me the nature of the various relations (one:one, one:many, many:many) I’ll see if I can figure out a workaround to get what you need.

1 Like

Thanks!
It is like this:

one many
Containers Acquisitions
Currencies Containers
Currencies Rates

I think it is possible to solve this using automations, but I’d need to know what the sequence of events is, i.e. in what order are Rates, Currencies, Containers and Acquisitions created/updated?

Now the answer is “yes”: December 29, 2022 / SOC 2 Type II compliance, [This ...] in Formulas.

I think you can achieve the desired result, perhaps with the use of an extra auxiliary Formula Field (we still can’t do aggregations within the Filter(...) function).

Please ping us via Intercom if you need assistance — @Chr1sG or I would be happy to jump on a screen-sharing call.