Help

Re: Color records linked with a record of "special kind"

Solved
Jump to Solution
1111 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Jan_Kiekeben
4 - Data Explorer
4 - Data Explorer

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.

1 Solution

Accepted Solutions

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:
image

There are many ways to fine-tune its output, but here’s a super-simple one:
image
You can then filter the results by thos which include a comma, which automatically means it contains more than one of your selected match:
image
And/or you could use that logic as a basis for a standalone view filter:
image

Hopefully this proves illuminating but let me know if I misunderstood or you’re still having issues.

See Solution in Thread

11 Replies 11

Either a rollup field or a formula field (or two) could easily accomplish this.

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?

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:
image

There are many ways to fine-tune its output, but here’s a super-simple one:
image
You can then filter the results by thos which include a comma, which automatically means it contains more than one of your selected match:
image
And/or you could use that logic as a basis for a standalone view filter:
image

Hopefully this proves illuminating but let me know if I misunderstood or you’re still having issues.

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?

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.

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.

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.

Bildschirmfoto 2021-09-15 um 16.23.56
Here you can see my resources. The crucial field is “Powered”. I’m looking for electric powered cars.

Bildschirmfoto 2021-09-15 um 16.24.04
here you can see my projects where I assign a primary and a backup resource.

Bildschirmfoto 2021-09-15 um 16.24.12
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?

If I got that right, can’t you just add that condition over here:

If not, duplicate the field, just switch the conditions.

Oh yes, I’ve had that solution already. This rollup works looking on one field, but not two.

Look here: This is the solution you propose. The rollup is looking into the Primary Resource and rolls up when there is a link with prop powered electric.

Bildschirmfoto 2021-09-16 um 09.35.44
Bildschirmfoto 2021-09-16 um 09.35.35

The challenge now is to look in Primary and Backup Resource at the same time for a prop electric linked record. Here is an example. Find the “VW ID3” in both resource links.

I’m wondering if there is a solution using a formula?

I tried the “REGEX solution”, as you can see in the column left to the rollup. I can only make it work with a REGEX matching for “VW” here, but not the prop of the resource “electric” which would make it work for all resources regardless of a naming convention.

The only hacky solution I was able to find is using a “naming convention” on resources and then use a REGEX to match against the name.

Here is how it works:

Make the resource identifier a naming convention “[description] - [powered]” in the resource table.
Bildschirmfoto 2021-09-16 um 09.52.27

Then match with the REGEX “electric” in the projects table.
Bildschirmfoto 2021-09-16 um 09.52.52

What I don’t like is, that this solution does not scale well to many props.
Is there a better solution that scales?