Hour Report Text Mode Filter Comparing Two Fields

[NickVa1]

WF Communi'tea!

Try as I might, I can't seem to get the following - what would appear to be a simple text mode filter - to work. Wondering if anyone knows where I'm going wrong?

• I have a custom calculated (Date) field on a project custom form called "Project Completed Date Stamp"
• I've created an Hour report, and in a text mode filter I want to write a statement for the following
  • Hour entry date is greater (newer) than the Project Completed Date Stamp field value

Here's what I have at present:

valueexpression={entryDate}>{Project}.{DE:project:Project Completed Date Stamp}

Result: error (whoops, something went wrong)

I've also tried this:

entryDate=DE:Project Completed Date Stamp

entryDate_Mod=gt

Result: error (whoops, something went wrong)

Any ideas where I might be going astray?

Thanks!

Nick

[NicholeVargas · Accepted solution]

@Nick Valeriote‚ This can be done, it just takes a few steps:

1. Create a new Hour Report
2. Go to the filters tab and select Hour > Entry Date > Greater Than > (Choose a random date)
3. Switch to Text Mode
4. Remove the date/time value that follows "entryDate=" and instead replace it with FIELD:ID. The new line should look like entryDate=FIELD:ID
5. Click Done
6. Switch back to Standard Mode (a pop up will appear, click Go Ahead)
7. A field dropdown menu will appear where you can search for your custom field, Project Completed Date Stamp
8. Your updated filter will be Hour Entry Date > Greater Than > Project Completed Date Stamp

If you have any questions, let me know!

[BillPe]

I tried your solution but the dropdown menu does not appear for me to select the second field

[NickVa1]

Hey Nichole!

This little method with FIELD:ID is great to know, beyond just this use case. Thanks!

However, I'm still getting an error when I run the report. Mind you, I did have to make one update to the project custom calculated field (Project Completed Date Stamp). I had to recreate it and update the Format field value as Date/Time. Prior to this, my calculation within this calculated field wasn't working when I specified Date as the Format.

I'm not sure if the report filter you suggested is having trouble comparing (greater than) the Hour Entry Date field (date format possible) against the Project Completed Date Stamp field (date/time format)? Date vs datetime formats.

Attached is a screenshot of how I have the filter set up, according to your instructions.

[NickVa1]

Hi Nichole....not pushing you, but checking in to see if you know where I'm falling short?

Thanks.

[NickVa1]

Hi Nichole...no worries on this, I think I got it. I reversed the field order in the filter, putting my custom field first, and it's working.

Thanks again for your help with this.

Nick

[NicholeVargas]

@Nick Valeriote‚ Apologies for not getting back to you sooner! I'm glad you were able to flip the order of the fields to get it working. If you need any other reporting help, just let me know!

[NickVa1]

Thanks, Nichole.

Don't open that can of worms ;). I could actually really use a hand with this, if you find time to take a peek.

I won't abuse your generosity.