# Filter on a Collection

**URL:** <https://community.fibery.io/t/filter-on-a-collection/3411>\
**Category:** Misc\
**Created:** [October 18, 2022, 1:07pm UTC](https://community.fibery.io/t/filter-on-a-collection/3411 "2022-10-18T13:07:18Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![thumDer](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/thumder/32/4998_2.png) [@thumDer](https://community.fibery.io/u/thumDer)\
**Post date:** [October 18, 2022, 1:07pm UTC](https://community.fibery.io/t/filter-on-a-collection/3411/1 "2022-10-18T13:07:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 18, 2022, 2:29pm UTC](https://community.fibery.io/t/filter-on-a-collection/3411/2 "2022-10-18T14:29:54Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![thumDer](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/thumder/32/4998_2.png) [@thumDer](https://community.fibery.io/u/thumDer)\
**Post date:** [October 19, 2022, 8:25pm UTC](https://community.fibery.io/t/filter-on-a-collection/3411/3 "2022-10-19T20:25:39Z")

</div>

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’ 😃

 ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/1/10194985aa44fc8c7be95005bfa93e9b962baf04.png)

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 19, 2022, 8:37pm UTC](https://community.fibery.io/t/filter-on-a-collection/3411/4 "2022-10-19T20:37:31Z")

</div>

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` ?

---

<div class="post-metadata">

**Author:** ![thumDer](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/thumder/32/4998_2.png) [@thumDer](https://community.fibery.io/u/thumDer)\
**Post date:** [October 19, 2022, 9:35pm UTC](https://community.fibery.io/t/filter-on-a-collection/3411/5 "2022-10-19T21:35:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 19, 2022, 10:15pm UTC](https://community.fibery.io/t/filter-on-a-collection/3411/6 "2022-10-19T22:15:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![thumDer](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/thumder/32/4998_2.png) [@thumDer](https://community.fibery.io/u/thumDer)\
**Post date:** [October 22, 2022, 12:25pm UTC](https://community.fibery.io/t/filter-on-a-collection/3411/7 "2022-10-22T12:25:50Z")

</div>

Thanks!  
It is like this:

| one | many |
| --- | --- |
| Containers | Acquisitions |
| Currencies | Containers |
| Currencies | Rates |

---

<div class="post-metadata">

**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\
**Post date:** [October 22, 2022, 1:13pm UTC](https://community.fibery.io/t/filter-on-a-collection/3411/8 "2022-10-22T13:13:25Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![antoniokov](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/antoniokov/32/201_2.png) [@antoniokov](https://community.fibery.io/u/antoniokov)\
**Post date:** [January 3, 2023, 9:32am UTC](https://community.fibery.io/t/filter-on-a-collection/3411/9 "2023-01-03T09:32:23Z")

</div>

> [@thumDer](#):
>
> When using `.Filter()` on a Relation Field in a Formula is there a way, to validate against another Field of my current database?

Now the answer is “yes”: [December 29, 2022 / SOC 2 Type II compliance, [This ...] in Formulas](https://community.fibery.io/t/december-29-2022-soc-2-type-ii-compliance-this-in-formulas/3751).

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.