Count with a dynamic condition on a date set in a field

[ErwanLP]

Hello everyone,

I have the following problem, which I haven't been able to solve for weeks. Basically, I want to do a Count with a dynamic condition on a date, which is not possible in the Count field settings.

I have three linked tables:

• 1 table A "Contracts" with insurance contracts. It has an anniversary date field.
• 1 table B "Vehicles" linked to table A for vehicles attached to insurance contracts. It includes two fields with a date of entry into the fleet, and a date of exit from the fleet.
• 1 table C "Annual periods" is linked to table A contracts. It contains the annual entry and exit periods created from the contract anniversary date.

What I want to do is obtain from table B "Vehicles" to table C "Annual periods" a count of the number of vehicles:

• 1 - on the entry date,
• 2 - on the period exit date.

The calculation of the count in table C from table B is normally quite straightforward: starting from (for example) the entry date, the count of the number of vehicles is :

(Date of entry into the fleet < date of entry into the period
AND
(Date of exit from fleet is zero OR Date of exit from fleet is > date of entry into period))

What do you think?

Many thanks in advance!

Erwan

[Alexey_Gusev]

Hi,
create lookup field in A from C - entry period. if I understand well, each contract has one such date.
create lookup of this field in B. Now in B you have all data to make a formula according to your condition.
IF(AND (IS_BEFORE(Date of entry into the fleet < date of entry into the period), ....  )  , 'Yes' , 'No')
note that you should compare dates with IS_BEFORE, IS_AFTER, not with < > 

Now create Count field in C, based on link to B, with built-in filter {Formula_field}='Yes'

[ErwanLP]

Hello Alexey,
Thank you very much for your reply. Indeed, counting works well with your solution but for a fixed date condition. However, I want a dynamic condition from the starting table.
In other words, I'd like to create a dynamic field in the table containing the formula (in the photo, a date), which sorts on the table where the data will be retrieved. Today, you can only set a fixed date.
By the way, I use "<" and ">" by reflex, and they work well instead of IS_BEFORE, and IS_AFTER.
So today, to obtain a table with annual values, I'm obliged to make as many columns as years, which isn't very clean!

[ErwanLP]

Hello everyone,
In fact there's no solution with Airtable, the function simply doesn't exist, which is what I thought.

I've gone about it in a more "agricultural" way, which isn't very clean but which works, using the only anniversary date that allows me to establish the annual periods:
- I've created as many columns as possible years in the history expected in the past: Current year, and 5 years in AR,
- For each of these columns, I've created a conditional formula for presence or absence in the park, based on the entry date and the exit date,
- From there, I include the 6 different counts (current year and 5 years in AR) in my table of contract periods,
- In other words, I have three 'populations' of insured masses: vehicles, buildings and insured equipment, which is a lot of columns per table, but also a multiple for the period table in which everything converges.

Not great, but better than nothing !

[ScottWorld]

You can hack your way into the ability to set dynamic conditions on dates, but it involves several workarounds that might not be worth it.

First, you have to convert all of your dates to UNIX timestamp numbers by creating formula fields that use the DATETIME_FORMAT function. The formula would be DATETIME_FORMAT({Your Date Field} 'X')

However, because dynamic filtering doesn’t support formula fields, you would then need to create automations that automatically copy and paste your formula fields into normal number fields.

Then, you can set dynamic conditions based on those number fields.

Might not be worth it to go through all of those steps, though.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

[Alexey_Gusev]

You are right, dynamic filtering is absent for Count, Lookup, Rollup built-in filters.
Except mentioned by @ScottWorld , that might be helpful.
But in many cases dynamic value that you want to set into filter, can be obtained as Rollup in other table. Then you can add lookup of this value back in the first table and create the formula in the way you can use static values in Count, Lookup, Rollup built-in filters. Formula does dynamic part with output "Yes" or "No", and then you can count all items with "Yes".
Of course, this needs more efforts and additional columns than if built-in filters were able to add dynamic values.