In reports, second filter dropdown choices based on first user selected filter

  • 0
  • 1
  • Question
  • Updated 3 weeks ago
  • Answered
Is it possible to do this in a report: have 2 dropdown filters (values from related tables), where the second dropdown values are filtered based on selection in the first dropdown? Essentially, conditional dropdown features in report filters, similar to that of forms.
Photo of Uma Harithsa

Uma Harithsa

  • 142 Points 100 badge 2x thumb

Posted 4 weeks ago

  • 0
  • 1
You sure can!

Open the related field properties of the second dropdown. There is a section for "Conditional values". You'll check to box and indicate that a selection in the first dropdown shows choices that match a value in the second dropdown.

-Sharon


Photo of Uma Harithsa

Uma Harithsa

  • 142 Points 100 badge 2x thumb

Hello Sharon. I do have that set-up and it works well in a form. However, in the report the second dropdown shows all the values even before the 1st dropdown has been selected. Whereas in the form, the second dropdown has no values until the first dropdown is selected. Any suggestions on how to fix this? Thanks ins advance.
It sounds like you're using Grid Edit. Can you describe the relationships in more detail? When I use these conditional settings with Grid Edit, I cannot choose the second dropdown until I make a selection in the first.

-Sharon
Photo of Uma Harithsa

Uma Harithsa

  • 142 Points 100 badge 2x thumb

It's a Table style report, not Grid Edit.  I have 3 tables: Building, Location and Workstations. Building to Location is one to many; Location to Workstations is one to many; Building to Workstations is one to many relationship. See attached picture for the Related Location field set-up. 
Thank you for the additional details. I think I understand now that you're trying to put conditional settings on the dynamic filters that show on the left of the report.

If that's what you're trying to do, I'm afraid they don't work that way. Those filters are based entirely on what shows in that field for the report. So, you are correct when you say that all the values for the second drop down show even if the first is not selected. That's because those values are already set and in the report.

If you want to set up a 'conditional' report here is what I suggest:

In the report filters set the field in the first drop down equal to <ask the user>, like in the image below.

This will require them to make a selection there first. Then when the report displays, the dynamic filters on the left will only include those based on the first filter they chose before the report displayed. 

Photo of Uma Harithsa

Uma Harithsa

  • 142 Points 100 badge 2x thumb

Thank you Sharon. That's what I did. Thanks for validating my approach!