Filter parent records within child record grid edit based on other parent selection

Question

Hi all, I've implemented solutions similar to this in the past (filtering child records based on grid edit overrides &amp; forms, etc), but I'm really struggling to figure this one out. Here's the scenario:

I've got a table "jobs", which is a child table of "venues". On the Jobs form, I need users to be able to select an on-site contact from a list filtered based on their selection of a grandparent "venue" record.&nbsp;

Currently, I've got a table "contacts" which is a child of "venues". I've created an intermediary relationship table "on-site contacts" which is a child of both "jobs" and of "contacts" to connect the two. I've tried adding a grid-edit child record report of "on-site contacts" to the jobs form, and attempted to filter it so that it only shows records in which "contact-related venue" is equal to "job-related venue", but with the grid edit report, job-related venue does not populate until the form is saved and the child record is officially created.&nbsp;

Does anyone have a solution to accomplish something similar? I suppose I could flip the relationship and try and make on-site contact a parent of jobs, but I'd like for users to be able to add multiple contacts as needed.&nbsp;

Thank you!&nbsp;

#Gridedit #Search #filters #Many-to-Many&nbsp;------------------------------Elena Larrabee------------------------------

elenalarrabee1 · Answer

I'm also trying to implement something very similar without much success in another area of my app. I've got a table "Inventory", a table "Sub-Inventory", and a merge table between them so as to give them a many-to-many relationship. (One inventory item can implicate the use of many sub inventory items, and one sub inventory item can be implicated in the use of many master inventory items).&nbsp;

"Inventory items" is a parent table to "Pull list items", which is in turn a parent to "Sub Pull List Items", which is a child of a separate inventory table "Sub-Inventory Items." See relationship diagram below:&nbsp;

What I'm trying to accomplish is a scenario in which a user can add additional Sub Pull List child records to a parent Pull List Item record, each with a parent Sub Inventory Item record. However, I don't want the user to have to scroll through every single Sub Inventory Item record; I want the possible parent Sub Inventory Item records to be filtered by matching the parent Inventory Item category (which is fed down to the Sub Pull List item child via its parent Pull List item).
The issue I'm running into is that I can't figure out a way to dynamically filter the report used for my grid edit override form on the "Sub Inventory Items" table by a value on a child record (the "Sub Pull List Item" record).
I've got a pipeline set up that automatically adds Sub Pull items to each Pull item and selects the appropriate parent Sub Inventory item based on the relationships defined between master and sub inventory in that merge table. But, I still need users to be able to add and edit additional sub pull items without having to search through every single sub inventory item every time.&nbsp;
Does anybody have any ideas on this? Or know when QB plans on implementing type-ahead search in grid edits so I can completely avoid this whole thing? Haha.
------------------------------Elena Larrabee------------------------------

markshnier__you · Answer

If I am correctly, understanding what you were trying to accomplish, then &nbsp;there is my favorite technique called User Focus which could make this work.
The user would launch off a button on the pull list items. The button would populate the User Focus record for the current user with the Focus master inventory record I'd ie, the one that the pull list is a child of.&nbsp;
That would put the master item in focus and hence all it's children Master Subs, via a lookup and then via a summary field would put all the sub inventory items in focus which &nbsp;belong to any Master Sub which is in focus.

Then the user would be landed in a grid edit form &nbsp;which is driven by a dedicated form to limit the drop down choices for Sub items which are in Focus.

If that approach sounds like it could work then we could either very slowly try to work through the steps to do this via this forum or else get on a consulting call for an hour and knock it off together.&nbsp;------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

elenalarrabee1 · Answer

Ah, amazing, thank you Mark! Knocking it out in an hour sounds great. I'll PM you to schedule?&nbsp;------------------------------Elena Larrabee------------------------------

gary1 · Answer

I was really hoping you'd hash it out here. I just made popcorn.------------------------------gary------------------------------

markshnier__you · Answer

I will post back next week after our meeting:)------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

Forum Discussion

Filter parent records within child record grid edit based on other parent selection