# Use collection in if formula

**URL:** <https://community.fibery.io/t/use-collection-in-if-formula/4764>\
**Category:** Get Help\
**Created:** [July 10, 2023, 6:08am UTC](https://community.fibery.io/t/use-collection-in-if-formula/4764 "2023-07-10T06:08:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\
**Post date:** [July 10, 2023, 6:08am UTC](https://community.fibery.io/t/use-collection-in-if-formula/4764/1 "2023-07-10T06:08:50Z")

</div>

I am curious as to why I cannot use a collection in an if formula.

Example. In 1 database, I have 3 fields.

- If field 1 is set and field 2 is empty, then field 3 is set with entities that meet the criteria
- If field 1 and 2 are set, then field 3 is set with entities that meet the criteria
- If field 1 and 2 are empty, then field 3 should also be empty.

But the response I get is that I cannot use a collection in an if formula. I would like to understand why this is not possible. And if there is no logical explanation and if this should be developmentally possible, I would be very happy. Now I need 3 automations where otherwise I could have 1.

---

<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:** [July 10, 2023, 10:56am UTC](https://community.fibery.io/t/use-collection-in-if-formula/4764/2 "2023-07-10T10:56:04Z")

</div>

I don’t know the exact technical reasons why it’s not possible, but normally you can workaround it as follows:

Imagine you want

If (x=y, tasks.filter(effort \> 10), tasks.filter(effort \<= 10))

You can instead write

tasks.filter( (effort \> 10 and x=y) or (effort \<= 10 and x != y) )

---

<div class="post-metadata">

**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\
**Post date:** [July 10, 2023, 11:29am UTC](https://community.fibery.io/t/use-collection-in-if-formula/4764/3 "2023-07-10T11:29:00Z")

</div>

Mmm I kinda believe that the automations we have are a bit to complex to combine. But curious (and stubborn) enough to give

Automation 1 → Update all periodes and planning if ‘Date range’ is updated.

- Clear planning
- Formula for ‘all periodes’ is  
Periodes.Filter(  
[Step 1 Doelen].Looptijd.Start() \<= [End date] and  
[Step 1 Doelen].Looptijd.End() \>= [Start date] and  
(Type.Name = “Maand” or Type.Name = “Kwartaal” or Type.Name = “Jaar”)  
)

Automation 2 → Update date range and all periods if ‘planning’ is updated

- Clear date range
- Formula for ‘all periodes’ is  
Periodes.Filter(  
Least(  
[Step 1 Doelen].Looptijd.Start(),  
[Step 1 Doelen].Planning.[Start date]  
) \<= [End date] and  
Greatest(  
[Step 1 Doelen].Looptijd.End(),  
[Step 1 Doelen].Planning.[End date]  
) \>= [Start date] and  
If(  
[Step 1 Doelen].Planning.Type.Name = “Jaar”,  
Type.Name = “Jaar”,  
If(  
[Step 1 Doelen].Planning.Type.Name = “Kwartaal”,  
Type.Name = “Jaar” or Type.Name = “Kwartaal”,  
Type.Name = “Jaar” or Type.Name = “Kwartaal” or Type.Name = “Maand”  
) ) )

Automation 3 → Empty ‘all periods’

- Empty ‘All periods’ when date range is empty AND planning is empty.

So it’s a bit complexer than the solution you have given. I believe it should be possible with your workaround, but can’t see how.

---

<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:** [July 10, 2023, 1:35pm UTC](https://community.fibery.io/t/use-collection-in-if-formula/4764/4 "2023-07-10T13:35:16Z")

</div>

I misunderstood from your first post that this

> [@Marloes](#):
>
> If field 1 is set and field 2 is empty

meant ‘If field 1 is not empty and field 2 is empty’ rather than ‘If field 1 is updated and field 2 is empty’

I don’t think it’s possible to combine the automations into a single one since there is no way for a formula to know which field was updated to trigger the automation.

---

<div class="post-metadata">

**Author:** ![Marloes](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/marloes/32/5926_2.png) [@Marloes](https://community.fibery.io/u/Marloes)\
**Post date:** [July 10, 2023, 6:01pm UTC](https://community.fibery.io/t/use-collection-in-if-formula/4764/5 "2023-07-10T18:01:08Z")

</div>

Thank you @Chr1sG.