Expand my Community achievements bar.

SOLVED

Custom Grouping by Name

Avatar

Level 8

I've looked all over the community but can't find an answer for this...I believe it will require text mode.

We need too be able to group by... Project >> Name >> Contains >> XYZ. We use a naming pattern system-wide that helps us stay organized and search for objects easier, but I would like to utilize it even further. In the following screenshot you can see how all the project names start with the same 2 components (LIA_Oct20) and then are further sorted by a name, section title, and other details.

I want to be able to group by the numbers. For example, a grouping for LIA_Oct20_1; LIA_Oct20_2; LIA_Oct20_3; LIA_Oct20_4; ect.

Is this possible? It makes sense because I can filter by what is contained in the name...0694X00000As4PBQAZ.png

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi Samantha,

I'd suggest you add a calculated custom parameter (e.g. "Sorting Prefix") to all such Projects that strips the first part of your well defined naming convention either using LEFT(name,10) if your pattern is indeed always 10 characters, or using an (advanced) formula that dynamically finds the prefix such as LEFT(name,3+SEARCH(name,"_1_")+SEARCH(name,"_2_")+...)

Either way, once you have that Sorting Prefix plucked off, you can then Group (or filter, or sort, or chart, etc.)

Regards,

Doug

View solution in original post

18 Replies

Avatar

Correct answer by
Community Advisor

Hi Samantha,

I'd suggest you add a calculated custom parameter (e.g. "Sorting Prefix") to all such Projects that strips the first part of your well defined naming convention either using LEFT(name,10) if your pattern is indeed always 10 characters, or using an (advanced) formula that dynamically finds the prefix such as LEFT(name,3+SEARCH(name,"_1_")+SEARCH(name,"_2_")+...)

Either way, once you have that Sorting Prefix plucked off, you can then Group (or filter, or sort, or chart, etc.)

Regards,

Doug

That sounds complicated and perfect. I'll try it! Thanks!

So @Doug Den Hoed‚, I got this all figured out (albeit in a slightly different way), but I'd like to apply the same grouping to a document report.

I have a custom field on all my projects that allows me to section them out, but how do I apply this to documents? I don't see how to group by anything other than the project ID using the standard options. Any thoughts?

Avatar

Community Advisor

Hi Samantha,

Hmm...on a list of Documents (in WF Classic, at least), when I create a new Grouping, the Project level custom parameters are available for me to choose from the built in graphical designer, but if that's not available in your case (and/or so you have another arrow in your tech quiver), whenever you see an ID that is close to what you need, you can also:

  • choose that ID (e.g. projectID, in your case)
  • change to textmode
  • replace every occurrence of that ID with something on the related object by dropping the ID and instead adding a colon and the desired built in field (e.g. project:name, project:status, etc.) or custom data parameter (e.g. project:Sorting Prefix, etc.)

Regards,

Doug

Maybe it's because I'm on a document version report not a document report. And it turns out Project ID is only available in a view, not a grouping. So, now I'm stuck.

Avatar

Level 7

Hi Samantha,

I would try something like this - create a calculated field on the custom form for your projects (if you don't have one, add one to all of your projects and the template you use) with this:

0694X00000AsDZtQAN.png

On your report or view, group by the Project number custom field (I just threw a few in as examples):

0694X00000AsDZyQAN.pngIf you don't want to see the text "Project Number", go to the groupings, switch to text mode and change the following line:

group.0.namekey=Project Number

To:

group.0.name=

Then you will just see:

0694X00000AsDawQAF.pngHope that helps!

Avatar

Level 8

This is essentially what I ended up doing! Thank you! Now the question is how to group by the same thing on a document version report.

Avatar

Level 7

Just to clarify, are these the project names or the document names we are looking at?

Avatar

Level 8

Project names, but we have documents in each of these projects that need to be sorted in the same way.

Avatar

Community Advisor

Hi Samantha,

I've confirmed that the "other arrow in your tech quiver" I mentioned above also works on a Document Version report. Here's the textmode, if you'd prefer to just jump to the punchline:

valuefield=document:project:name

querysort=document:project:name

valueformat=HTML

displayname=

linkedname=document

namekey=view.relatedcolumn

namekeyargkey.0=document

namekeyargkey.1=project:name

Regards,

Doug

Avatar

Level 7

Doug you got to this just before me!

One change I would make is

textmode=true

group.0.valuefield=document:project:name <--instead of name, add your custom field here!

group.0.valueformat=string

group.0.name=

Avatar

Community Advisor

Excellent point, Sarah -- thanks for the assist!

@Kyna Baker - inactive‚ everyone's so helpful today! My excuse is that it's snowing in Calgary, so I'm cozied up by a fire (and apparently, I admit) procrastinating a bit by answering. Meh. Nice day for it.

Regards,

Doug

Avatar

Employee

I am loving the helpfulness! :) Maybe we're all feeling a little Friday-distractedness... (so many "nesses")...

Thank you all for working through this - I love seeing the expanded use of grouping, I'm thinking about where I could use this too!

Avatar

Level 8

@Sarah Nau‚ and @Doug Den Hoed‚ I'm sorry to be a bother, but I am so stuck.

Here is the text mode I'm using for the Custom Grouping on my Document Version report:

group.0.linkedname=document

group.0.name=

group.0.namekey=view.relatedcolumn

group.0.namekeyargkey.0=document

group.0.namekeyargkey.1=project:name

group.0.querysort=document:project:name

group.0.valuefield=document:project:LIA Section

group.0.valueformat=string

textmode=true

And here's what I'm getting:

0694X00000AsEN5QAN.png

I'm totally new to text mode. So, what did I screw up on?

Avatar

Level 7

Aha! I think I got it!

group.0.name=

group.0.valuefield={document}.{project}.{DE:LIA Section}

group.0.valueformat=string

textmode=true

Avatar

Level 7

*Correction*

group.0.name=

group.0.valueexpression={document}.{project}.{DE:LIA Section}

group.0.valueformat=string

textmode=true

Avatar

Community Advisor

Hi Sarah,

I just tried this grouping, and believe it's returning the desired results, as below (for project name):

group.0.linkedname=direct

group.0.namekey=document:project:name

group.0.valuefield=document:project:name

group.0.valueformat=string

textmode=true

HOWEVER...

Because custom data is stored on a custom form, that adds one more "hop" into the equation, which at first I thought mean it would be too far to reach (3 being the limit), but, on a hunch, I tried switching from valuefield (hitting it "directly") to valueexpression (hitting it "via code"), and to my pleasant surprise, got it working (in my case, Hot Sheet Job Number is a Project level custom parameter, which you could replace with "yours"):

group.0.name=Hot Sheet Job Number

group.0.valueexpression={document}.{project}.{DE:Hot Sheet Job Number}

group.0.valueformat=string

textmode=true

Thanks for the tricky challenge, and enjoy!

Regards,

Doug

0694X00000AsEWWQA3.png

0694X00000AsEdcQAF.png

BLESS the both of you @Doug Den Hoed‚ and @Sarah Nau‚ This worked!!!! And now I know how to replicate it. Thank you so so so much.