Support for map in formulas

I think I found a use case when existing formulas are not enough.
So I have the following structure:

Screen Shot 2021-01-01 at 1.15.57 pm

Each food has a specific food type(s) and what I’m trying to do is just to output the sum of amounts eaten by specific type for a specific date.
I was able to do so in the report. But struggling to do with formulas.
I tried to write the following formula on the food log date:

[Food Logs].Filter(MatchRegex([Food Type].Join(Name,","),"Veggie") = true).Sum([Adjusted Amount])

But that gives me no errors neither output anything.
I do believe that the problem is with using join within a filter. So a simple solution would be able to use the map function. Something like:

[Food Logs].Filter([Food Type].Map(Name).Filter("Veggie")).Sum([Adjusted Amount])

Not sure if the double filter will through it off. But I do find myself reaching a lot into transient relationships which is quite difficult without map support.

I do want to mention that I’m familiar with a workaround of creating formulas on the referenced type which is what I’m using right now. But it’s a workaround not a necessary fields on the type.

Hi there!

There is an issue in the editor, that allows to use “Join” inside “Filter”. It should not be possible, editor should show an error. That is why your formula is not working. Will fix it with the next release.

About “Map” function. No luck with this. We do not want to complicate formula implementation right now. Just technical stuff. Formulas use fibery api, and it does not support such expressions.

At the moment the best solution is to use intermediate fields, just as you did. I think we will add a possibility to hide fields on UI soon, so it will not blow your UI.

1 Like