Example: I have two tables: Client and Timesheet. Client table is really only a lookup table for the client field inside Timesheet - and works fine. However, I now need it to show only records that have a status field = "active". I don't want to delete old records because they are attached to old timesheet records -- but I need old clients to not show up on the pulldown list.
I have fixed situations like this in the past by making the data entry form look up to a report that filters out "inactive" records. But... people sometimes use grid edit, and then all options appear -- not just the filtered list.
Is there a way?
You will need to make a new form, yes a form and i suggest that you call it Grid Edit Form. Put the correct fields on the form that you want to use in your embedded report. Set the form to use the correct drop down filtered report of Status = Active.
Then now that you have more than 1 form the forms settings will allow you to set which form gets used. Set the embedded report to use your new Grid edit form for Grid edit.
That should do it.
I suggested going to Forms Usage and setting that Report to use ther Grid edit form for Grid Edit.
Timesheet.Client is set to lookup a list from Client.Client (currently shows all client values). I now need the client input list to show only a subset of Client.Client values (only if client status = active).
I would have expected the solution to be something along the lines of creating a SQL query that only selects active records and using that query result to show the lookup values.
In another field, I made something like this work on our data input form. Did this by using the "When used for data entry:" and picking a report that filters out what I don't want to show. So I'm sure I could make the client field work the same way in this data input form.
Here's my problem: The method above relies on people using the data entry form (solution isn't programmed into the field definition). Sometimes my users use grid edit to edit many records at once -- and when you are in grid edit, the way I'm doing this doesn't matter -- it doesn't filter out the inactive drop-down options. I am hoping to solve this for all of the fields that I need to filter out inactive options for.
As far as I am aware, when you click open "grid edit" it opens a grid version of a report, not a form.
I hope I am explaining this well enough. I appreciate your patience.
What I am trying to tell you is that the way that you can Control how Grid Edit behaved is by setting up a dummy form. I suggest that you make a dummy form with the fields listed in the sequence that you want them to appear on the report, and do the usual filter on the drop down by selecting a report for the record picker field to use.
You can test this by setting up even a dummy form with just that one drop down field for now, to prove to yourself that the filter will work. Then set a test report to use that form for Grid edit. The form is just a convenient, if obscure, way to set the columns to use on Grid edit and it also allows for filtering the drop down choices.