Expand my Community achievements bar.

Trying to report on a Custom Field, but the reporting function will not allow me to group by the custom field selection. Can someone please assist me?

Avatar

Level 2
 
Topics

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

5 Replies

Avatar

Employee

Hi Shayla!

In order to group by a field, that field has to be a 1:1 value in the database, which means you'll need to create a calculated field that references your other custom field, then you can group by the calculated field.

I hope this helps!

Thanks,

Avatar

Level 2

Thank you Dustin for you response. By any chance can you or someone assist me on doing this? I am new to WF and have taught myself a lot but I don't know how to create calculated fields.

Avatar

Employee

Hi Shayla,

Here's our help article that goes over the process:

Link

Short story:

Say you have a checkbox field called Toppings. Your goal is to find out who wants what toppings on their ice cream.

Because you can have more than one choice selected, you can't group by it, as that's a many to one relationship (many choices to 1 field, or 1:n in database terms.)

So you create a calculated field, and call it Toppings Aggregated.

The calculation for the calculated field will be the name of the field you want to turn into a 1:1 relationship.

In this case, the calculation is just 'Toppings'. (Without single quotes.)

https://drop.workfront.com/p9uGlzBN

So now you have 2 fields on a custom form, a checkbox field and a calculated field.

You now add the custom form to a request queue where people can submit their favorite ice creams.

John Smith chooses Choc Chips and Sprinkles.

Jane Doe chooses Reeses Pieces and Sprinkles.

Jack Johnson chooses Sprinkles and Oreo Crumbs.

You now have 3 issues, each with a custom form showing all the above.

You create a new report, you add a grouping, and you can now group by the field Topping Aggregate.

Because no 2 people selected the exact same results, you'll have 3 groupings.

https://drop.workfront.com/7KuLvxjA

Now let's say Jack Johnson decides he wants Choc Chips instead of Oreo Crumbs. Now he has Choc Chips and Sprinkles.

You go back to the report and have 2 groupings, with "Choc Chips and Sprinkles" having 2 rows underneath it, one for John Smith and one for Jack Johnson.

This is because even though the Toppings field is multi-select, the same 2 toppings are selected on 2 different issues, and thus the calculated field shows the 2 toppings as 1 value. (Choc Chips, Sprinkles)

https://drop.workfront.com/WnuJdA6Q

I hope this helps make sense!

Thanks,

Avatar

Level 2

Dustin - Thank you for the update. I added a calculated field and this is the Calculation I added. DATEDIFF(Entry Date,Requested Contract Signing Date)

This is not showing me the number of day between the Entry Date and the Requested Contract Signing Date.

What am I doing wrong?

I have read other feeds, but I am not coding savvy to figure this out.

Avatar

Employee

The calculation looks like it should be right to me.

I'd bulk update a few of the objects that have that custom form on it, select "Recalculate Custom Expressions" and that should populate the data you're looking for.