How to group by Custom Date Field Two Tables away

[NiteshD_]

Hi,

I have created a proof approval report within which I need to group the results by a custom date field.

I have been sucessfull in pulling the date field "Internal Due Date" from both the Project custom form and then advised to pull it from the Document Custom Form.

The data is coming in and showing on the report.

I now want to group by this column; below is the grouping Text Mode I am trying to use, but it just cannot seem to pull the data.

group.0.linkedname=approver

group.0.namekey=view.relatedcolumn

group.0.namekeyargkey.0=approver

group.0.namekeyargkey.1=name

group.0.valuefield=approver:name

group.0.valueformat=string

group.1.displayname=Project Internal Due Date

group.1.linkedname=documentVersion

group.1.namekey=view.relatedcolumn

group.1.namekeyargkey.0=document:DE:EACG Project Internal Due Doc

group.1.valuefield=documentVersion:document:DE:EACG Project Internal Due Doc

group.1.valueformat=fullAtDate

textmode=true

(ignore group.0.; this works fine)

Below is the text mode I am using to pull the custom field date into the report itself (just as an FYI)

displayname=Project Internal Due Date

querysort=documentVersion:document:DE:EACG Project Internal Due Doc

textmode=true

valuefield=documentVersion:document:DE:EACG Project Internal Due Doc

valueformat=fullAtDate

Can someone please advise on the grouping; I am stumped and not sure how to get around it. Please help you genius people out there!

Nitesh

[NicholeVargas]

@Nitesh D'Souza‚ If you were to swap your valuefield line to a valueexpression, that should do the trick. With custom fields, the DE: can oftentimes mess up a grouping because it's thinking it's referencing another field/table.

group.0.linkedname=approver

group.0.namekey=view.relatedcolumn

group.0.namekeyargkey.0=approver

group.0.namekeyargkey.1=name

group.0.valuefield=approver:name

group.0.valueformat=string

group.1.displayname=Project Internal Due Date

group.1.linkedname=documentVersion

group.1.namekey=view.relatedcolumn

group.1.namekeyargkey.0=document:DE:EACG Project Internal Due Doc

group.1.valueexpression={documentVersion}.{document}.{DE:EACG Project Internal Due Doc}

group.1.valueformat=fullAtDate

textmode=true

[JamesM-MERGE]

Hi @NicholeVargas, I have a similar situation and was wondering if you could assist?

Column in report:
displayname=Proof Designation
linkedname=document
namekey=view.relatedcolumn
namekeyargkey.0=document
namekeyargkey.1=taskID
querysort=document:task:DE:Proof Designation
textmode=true
valuefield=document:task:DE:Proof Designation
valueformat=HTML

I'm trying to group by this custom field but the grouping isn't pulling a value.

Grouping:
group.0.displayname=Proof Designation
group.0.linkedname.0=document
group.0.linkedname.1=task
group.0.namekey.0=view.relatedcolumn
group.0.namekey.1=view.relatedcolumn
group.0.namekeyargkey.0=task:DE:Proof Designation
group.0.valueexpression={document}:{task}:{DE:Proof Designation}
group.0.valueformat=HTML
textmode=true

[NicholeVargas]

 Based on your text mode column, it looks as if you are running a Document Version report instead of a Proof Approval report, correct? If that's the case, you can use the text mode below for your report grouping. Rather than using a colon to separate fields like you would in a valuefield line, for a valuexpression you use a period. 

group.0.valueformat=HTML
group.0.valueexpression={document}.{task}.{DE:Proof Designation}

group.0.displayname=Proof Designation

Let me know if you have any questions! 

[JamesM-MERGE]

Thank you for your help @NicholeVargas , worked like a charm!