# Use collection in if formula

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.

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) )

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.

I misunderstood from your first post that this

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.

1 Like

Thank you @Chr1sG.