Dynamic Filter on Lookup

[JBJ]

Hi guys,

On my [Revenue] table, I'm trying to compute the expected revenue that I'll get from a client. The client processes payments twice a month after the work sessions are completed. We have struck a deal that spans multiple payment periods.

To this end, on my [Revenue] table, I'm trying to set a dynamic filter on a lookup of the `Sessions` field, which is itself a lookup on the [Deals] table. I want the `Sessions` field on the [Revenue] table to display only the sessions – hourly sessions or weekly sprints – that ended after the `Work Period Start Date` and before the `Work Period End Date`. This would then allow me to sum the compensation due for each session within this work period with a rollup.

The thing is, it seems I can't set up a dynamic filter on a lookup.

Do you guys confirm?

If so, which workaround would you suggest?

Thanks for your help.

[Mike_AutomaticN]

Hey @JBJ! Please let me get back later today to try to understand what is it that you are trying to do LOL.
In the meantime, I can for sure confirm that you cannot dynamically set up a dynamic filter on a lookup.

Mike,  Consultant @ Automatic Nation

[JBJ]

Hey @Mike_AutomaticN,

Thanks for offering your help. I solved my problem in a dirty way.

Take care.

[Karlstens]

@JBJ- what workaround did you settle with? I believe I have a similar issue, that I suspect only an Automation Script can resolve.

I've tried a few different rollup/lookup methods, but I too always lack a dynamic filter to execute in my rollups/lookups which would then allow the reduced sum data to return.

[JBJ]

@Karlstens I ended up linking [Revenue Items] directly to both [Deals] and [Client Sessions]. And to avoid any data inconsistency, I added a dynamic filter to the {Client Sessions} linked record field on [Revenue Items} to make it possible to add only client sessions matching their associated deal.

Horrible for people coming from SQL, but effective in Airtable.