How to filter a Conditional Drop Down List to reduce results below 1000 for Grid Edit

I looked on here for a solution to a grid edit problem and didn't see anyone with a solution so I thought I would share a solution I find useful.

I have a form with an embedded report I want to be editable using Grid Edit. The embedded report has two fields which are a conditional pair, meaning Users select an option in the first column to filter options in the second. 

The problem happens when a User selects the second column drop down list and there are more than 1000 results, the <Browse Choices> option appears instead of a nice list. When the User clicks <Browse Choices> the pop up window uses the default report and conditional filters are lost.

While this in itself is a bug, we need a solution today. So after some thought here is our solution. 

On the second conditional table add a formula field with a data type that matches whatever the data type is for the matching field used in the conditional relationship (the second column). In the formula field apply filters until the list is below 1000 records, display the matching value of the first column so the conditional filter works. The result is the editable report on the form now displays a nice list and you can avoid the <Browse Choices> dialog.

Example: A Numeric formula field:

ToNumber(If([Closed] <> true and Date > today(), [Record ID#], ""))

This formula example says if the "Closed" field check box is not checked and the "Date" field is after today then display the "Record ID#" otherwise leave blank. The "Record ID#" is used in the Conditional relationship to find matching values. Then in the Conditional Relationship, change the second condition to match the formula field.

To verify the formula is working create a report that has the fields being compared and then look to see if there are values where the formula filter is met. If you don't see any values, the formula needs to be changed.

Finally open the form with the embedded report and click the second conditional drop down list. Users are presented with a nice list and not the <Browse Choices> option.

------------------------------
Jim Harrison
transparency = knowledge + understanding : The Scrum Dudes
------------------------------