I think I found a use case when existing formulas are not enough.
So I have the following structure:
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.