Use collection in if formula

Get Help
1

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.

2

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

3

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.

4

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
5

Thank you @Chr1sG.