Discussions

Two Tables - Jobs and Installs

I have a report in Jobs that I want filtered based on the value of "Location" in the Installs table. How can I accomplish this?

Hi Jason,

Are Jobs and Installs related tables? If so how are Jobs and Installs related?

If Jobs is the parent of Installs then you would need to add a lookup field from Jobs down to Installs that is the Location of the Job. Then you could build your report to filters off this new lookup field. I hope this suggestion is helpful but if this isn't your set up please let me know how your setup is different and ideally we can help you find a solution. 

I have created the relationship both ways.  I got the location field to appear in the report from the Jobs table, but no results.  Any idea what could be wrong?

If you want to have a 'conditional' dropdown on a table you need to have at least 3 tables involved.  It sounds like you might only have 2 (Jobs and Installs).  If you want conditional based on location, you will need to make a 3rd table of 'Locations' and have that be a parent to both the Jobs and Installs.

Hi Jason,

Matthew brings up a good point I read the contents of the question where it asked about filtering a report, but I didn't see the filter a dropdown as well in the question title. For that it would take a few more layers. If it was just about filtering the report itself having the lookup field should be enough to edit the report settings and add a filter to the report based upon it but if you are looking to instead create a conditional dropdown that does add extra layers to the set up. 

Not trying to do a 'conditional' dropdown.  Got that to work. Thanks! 

In my installs table, I have a location field that the user selects.  When the user selects a location, i have a second dropdown box, which is populated by a report from the Jobs table, which needs to know the selected location to filter the report correctly.

I hope this makes sense. The title may be the confusing part.

This is the essence  of a 'conditional' dropdown.  When one dropdown options are conditional based on some other dropdown.

If you want the Jobs dropdown to only show options that match that 'location' you will need a 'locations' table. 

using a multiple choice field for location will not give you a pre-filtered list.  

Ok. Maybe I'm confusing this.

JOBS table
Ready to Build report

INSTALLS table
Dropdown box #1 - "Location" (user selects a location)
Dropdown box #2 - Select "job to schedule" (this is being populated by the READY TO BUILD report in the Jobs table).  I need "Location" from the installs table to filter the report in the job table. 

I gotten the dropdown box to populate without the filter.

If I am making this harder than I should, please explain how you would do it.

Thank you for all of the help with this.

Ok. Maybe I'm confusing this.

JOBS table
Ready to Build report

INSTALLS table
Dropdown box #1 - "Location" (user selects a location)
Dropdown box #2 - Select "job to schedule" (this is being populated by the READY TO BUILD report in the Jobs table).  I need "Location" from the installs table to filter the report in the job table. 

I gotten the dropdown box to populate without the filter.

If I am making this harder than I should, please explain how you would do it.

Thank you for all of the help with this.

Hi Jason,

So you are looking to filter the report on the parent table by a value in the child table? You want to take the Location from the Installs table and pull it up in the Jobs table and filter the report that way?

Correct

Hi Jason,

What is usually easier to do in that instance is to pass down the data you are looking to report on to the child level and then build the report you are using there. So for example a report that passes down to installs that includes the relevant look up fields from the parent table of Jobs. Alternatively you could look into using Actions to pass information back up to the Parent table. The reason for this is information flows from the top down in a relationships, so from Parent to Child records. You can summarize back up numerical or date based data but for Text based data there isn't a summary option at this time. Yet if you pass all the needed info down to the child table you can recreate a report with the same data on the child table level since anything from the parent can be passed down. 

Thanks I will look into that.