Looking for a formula to match deadline to duration field in a different database

Get Help
1

Hi there,

I am looking for a formula of which I’m not sure it’s possible. I have tasks and tasks have deadlines. But tasks also belong to a certain period. For example this week / this month / next week / etc.

For those periods I have a seperate database. Each entity in this database represents a period. And for each period there is a start date and, an end date and a duration field. These our automatically updated at the beginning of a new period.

I want an automation that looks at the deadline of the task to see which periods this task belongs to. In other words, does the deadline of the task fall in the running time of a specific period. If so, link this period / these periodes.

Is there a formule that would make this possible?

Thanks!

2

I assume this Formula should exist within the Tasks DB.

The Formula will filter the Periods DB; so the first requirement is that the Tasks DB must have a bona-fide Relation to Periods DB (otherwise the Formula cannot reference the Periods DB at all).

So first we create a new “Periods Ref” Reference field in Tasks (many-to-many). This collection can be empty and always hidden if not needed for anything else, but its existence allows us to reference (and filter) the Periods DB within a Tasks Formula field.

To get the filtered collection of Periods, define the new Formula field in Tasks like this:

[Periods Ref].Filter(
  [Period Date].Start() <= [This Task].[Due Date]  and
  [Period Date].End()   >= [This Task].[Due Date]
)
3 Likes
3

Thanks @Matt_Blais , this did the job! Didn’t know the <= and >= function yet.

In our cause the automation / formula should not be in the tasks database. We have a third database. It’s a Planning automation database where (for example) an entity gets linked when the deadline changes. We have more automations for just planning and by linking a third database it becomes less overwhelming but also easier to prefend the automation from ending up in a loop.

You can compare it to how templating works.

Thank you so much for your help!