Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 14, 2021 02:23 AM
I have a projects table where I link multiple records from a resources table. The linked records (resources) have different attributes (e.g. aaa, bbb, ccc, ddd) that come from a “single-select” field.
My challenge now is to find projects where resources of a special attribute (e.g. bbb) are linked.
Is there a function/formula field I could use to color records in my view?
I’m not the full Expert on automation or formulas, but I think of a formula that goes to the link of the record, looks for the attribute, and then makes it possible to color or maybe write a “true/false” to another field and I color/filter/group for that.
Currently the only solution I could find is to separate the resource-link field into a "link “bbb” resources here (links based on a filtered view) and all other resources on another field. Thus I see in a coulum quickly if a resource with the attribute “bbb” is linked. I don’t like this approach as I want to have all resources linked to a project in one field. It would be enough to color my projects in a view if a resource with attribute “bbb” is linked or not.
Solved! Go to Solution.
Sep 14, 2021 07:26 AM
TBH this use case sounds simple enough that someone might have already written what you needed if you at least shared a screenshot, aaa-bbb abstractions are just another obstacle to the mind after a long day. :grinning_face_with_sweat:
See, an image is worth a thousand words, and I have a couple of them for you:
My crude recreation of your base description amid some other random base, the key fields are the linked resources and the rolloup listing their props:
There are many ways to fine-tune its output, but here’s a super-simple one:
You can then filter the results by thos which include a comma, which automatically means it contains more than one of your selected match:
And/or you could use that logic as a basis for a standalone view filter:
Hopefully this proves illuminating but let me know if I misunderstood or you’re still having issues.
Sep 14, 2021 06:16 AM
Either a rollup field or a formula field (or two) could easily accomplish this.
Sep 14, 2021 06:36 AM
Thanks for the hint. I tried the rollup already, but I kind of failed on first attempt.
But I’ll give it another try.
On the formula I must admit I’m totally inexperienced with writing the formula. I’d definitely need help here. Do you have a starting point for me to tinker around?
Sep 14, 2021 07:26 AM
TBH this use case sounds simple enough that someone might have already written what you needed if you at least shared a screenshot, aaa-bbb abstractions are just another obstacle to the mind after a long day. :grinning_face_with_sweat:
See, an image is worth a thousand words, and I have a couple of them for you:
My crude recreation of your base description amid some other random base, the key fields are the linked resources and the rolloup listing their props:
There are many ways to fine-tune its output, but here’s a super-simple one:
You can then filter the results by thos which include a comma, which automatically means it contains more than one of your selected match:
And/or you could use that logic as a basis for a standalone view filter:
Hopefully this proves illuminating but let me know if I misunderstood or you’re still having issues.
Sep 14, 2021 09:10 AM
Oh this is great. Amazing!
Worked like a charm. I even used ARRAYUNIQUE(values) on the aggregation of the rollup to have resources only once in there. This works great and I can live with that for now.
Nevertheless let’s level up one more complication as I’m challenging myself with this:
I do have on projects two fields where I assign these resources. One column called “resource assigned” the other one called “resource backup”. Now I want to do the coloring based on when NONE of the two fields has any resource of prop “bbb”.
Can you follow? It’s like a rollup looking in two fields on a record. I guess I would need to summarize first and then do the lookup/rollup, right? Would that be easier to do with a formula or how would you approach this?
My current approach is to do two rollups and combine them it on the coloring conditions. Do you see another approach to the problem?
Sep 14, 2021 09:17 AM
Thinking about the aggregation made me realize that you can link a resource only once in a link field, so there is no need for ARRAYUNIQUE, right? Whatever… works.
Sep 14, 2021 09:40 AM
Another rolloup array or lookup reference would allow you to do so, so would a custom view and dozens of formula combinations. Personally, I’d probably just go with regex here as direct, no-nonsense queries are what it’s good for:
So, see what this does:
REGEX_MATCH(
{ResourceAssignedColumnName}
& "" & {ResourceBackupColumnName},"(bbb)")
It should return a 1 if there’s a bbb somewhere in there and 0 otherwise.
And note that you can replace the “(bbb)” part with another field reference in the vein of “(” & DesiredFieldName & “)”, depending on whether you’re going for full auto or are simply building a dashboard of sorts and would prefer to have some agency without redoing the formulae every now and then.
Sep 15, 2021 07:16 AM
I tried a bit and found that it works when the resource name has a “bbb” in it. But as it’s an attribute to the linked record I’m not sure how I can tell the Regex that it should look into the details of a record to match against.
Example: The linked resource name is “Ford Mustang”, but I want to match against the property “e-powered” of the Ford Mustang.
Sep 15, 2021 07:26 AM
Here you can see my resources. The crucial field is “Powered”. I’m looking for electric powered cars.
here you can see my projects where I assign a primary and a backup resource.
As long as I match against parts of the resource name (here “VW”) it works.
How can I match against the powered by “electric” property of my resources?
Sep 15, 2021 05:38 PM
If I got that right, can’t you just add that condition over here:
If not, duplicate the field, just switch the conditions.