If you have parent records with LOTS of children add a search box to the form to make finding the 'right' record easy!

It can be frustrating (and eye-straining) to sift through page after page of information looking for a specific record. Adding search functionality on a form to quickly search & filter a report of child records is easier than it sounds.

In this example, I use a LEGO data set to search the minifig part category for parts that match descriptions like "princess" or "batman". All it takes is a few new fields and an adjustment to one report.

Steps to add search to the form:

1. Add a "Search Term" text field to the parent table/form
2. Add a "Search" Formula-URL field to the parent table/form
3. Update the "Search" field's formula to reload the current form (see Resources below for details) NOTE: double-check that the table's advanced settings have the box "Save parent record automatically..." is checked
4. In the relationship between the parent & child, add a lookup for the "Search Term" field
5. On the parent table, update the report used to display the child records to include a filter for the field containing the value in the "Parent - Search Term" field (this is the lookup field that was just added)
6. Test your search!

If you do not want to display an empty report when no search has been done, follow these additional steps:

1. Add a "Search Match" Formula-Text field to the child table
2. Update the formula in the "Search Match" to the search term if present or the field that's being searched (see Resources below for details)
3. Update the parent form's embedded report filter to use the "Search Match" field in place of the lookup field
   
   

RESOURCES:

Get the Action Button Toolbox referenced in this tutorial

Formula #1 - Search

If(IsNull([Record ID#]),"",URLRoot() & "db/" & Dbid() & "?a=er&rid=" & [Record ID#])

Formula #2 - Match Term

If([part_cat_id - Search Term]="",[Name],[part_cat_id - Search Term])

Feeling like a Junkie?  Subscribe Now!