Filter with calculation field
I won't explain all my app in order to remain clear enough but I have one issue :
I've created a Deals app and one of the deals' field is Activity. This field is calculate with the other fields and even if it's not a "category" field, it always returns Active or Inactive.
Now, I want to create a view filtered by this field. For example, a view on which only the Inactive deals are shown. But the problem is that I don't find the "Activity" category in the list of filters and it doesn't seem that I can create views filtering by "Calculation" fields.
So I have 2 questions:
- Is it possible to filter views by calculated field?
- if it's possible, how can I do that?
Filters and views can only be built upon contacts, categories, relationships, dates and tags, not upon calculation fields - at least not with strings. To my knowledge there are two roads:
- create a new calculation field with the same logic, but not with a text output but a number output (you can't convert the existing calc field, that's why you need to make a new one). I usually do it like that: 0 if inactive, 1 if active. Then you can filter for items where this calc field has a value greater than 1 or smaller than one. Since you only have two states (active and inactive) this could do it for you. You can't however make a view with sub-views "inactive" and "active", you can only make one view for active items and one for inactive.
- create a category field with "active" and "inactive". Then, create a GlobiFlow where you use your calculation field (string or number, doesn't matter) as filter for the action "update item" and let GF set the respective status with the category field. Actually you might need two flows, one upon "create item" and one upon "update item".
You might think that road 2 is just perfect. But there's one possible obstacle: if the calculation field is the only thing that changes, this won't trigger a GlobiFlow. Changes you make in related apps leading to a change in your calculation field will do just that but not fire a trigger. In that case you'd need a third GlobiFlow in the related app which, upon editing the field influencing your calculation, triggers the flow updating your category.
apps can't be filtered by text - and your calculation field is text (type). But it's possible to filter views by calculation fields if they are number type or date type.
In your case I recommend to add another calculation field e.g. labeled as "Activity Filter" (can be Always hidden) and enter:
@Activity == "Active" ? 0 : 1
Alternate: enter the same code as in the other calculation field but with output 0/1 instead of Active/Inactive.
That calculation field will be number type (in the field settings -the little triangle on the left side of the brick - you can define the decimals, set it to 0). And now you can create a view "Inactive" by selecting the field "Activity Filter" in the filter dropdown list and entering 1 in the filter field "from" (or From 0 To 0 to filter the Actives).
Please sign in to leave a comment.