Custom Grouping by Name

[SamanthaIsin]

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...

[Doug_Den_Hoed__AtAppStore · Accepted solution]

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

[SamanthaIsin]

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

[SamanthaIsin]

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?

[Doug_Den_Hoed__AtAppStore]

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

[SamanthaIsin]

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.

[SarahNa]

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:

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

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

Hope that helps!

[SamanthaIsin]

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.

[SarahNa]

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

[SamanthaIsin]

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

[Doug_Den_Hoed__AtAppStore]

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

[SarahNa]

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=

[Doug_Den_Hoed__AtAppStore]

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

[kynabaker16]

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!

[SamanthaIsin]

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

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

[SarahNa]

Aha! I think I got it!

group.0.name=

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

group.0.valueformat=string

textmode=true

[SarahNa]

*Correction*

group.0.name=

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

group.0.valueformat=string

textmode=true

[Doug_Den_Hoed__AtAppStore]

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

[SamanthaIsin]

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.