Hour Report Text Mode Filter Comparing Two Fields | Community
Skip to main content
N
NickVa7
Level 9
February 11, 2021
Solved

Hour Report Text Mode Filter Comparing Two Fields

  • February 11, 2021
  • 5 replies
  • 1317 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by NicholeVargas

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

5 replies

NicholeVargas
Adobe Employee
NicholeVargasAdobe EmployeeAccepted solution
Adobe Employee
February 11, 2021

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

    B
    BillPe
    Level 3
    April 24, 2024

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

    N
    NickVa7Author
    Level 9
    February 11, 2021

    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.

    N
    NickVa7Author
    Level 9
    February 16, 2021

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

    Thanks.

    N
    NickVa7Author
    Level 9
    February 16, 2021

    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
      Adobe Employee
      NicholeVargasAdobe Employee
      Adobe Employee
      February 16, 2021

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

        N
        NickVa7Author
        Level 9
        February 16, 2021

        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.

        Terms & ConditionsAccessibility statement

        Loading footer...