Can I show only some field values in a lookup field?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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?

Thanks,
Elaine

Photo of Elaine

Elaine

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Yes, there is 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.
Photo of Elaine

Elaine

  • 0 Points
How do you make the new form look like the grid instead of record by record?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Sorry, but I'm not understanding your question.  You told me that users were using Grid Edit and hence your drop down was not working with the filters on the drop down for Client.

I suggested going to Forms Usage and setting that Report to use ther Grid edit form for Grid Edit.
Photo of Elaine

Elaine

  • 0 Points
My apologies - I don't think I am explaining well enough.  

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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
I think that I understand your situation.
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.
Photo of Elaine

Elaine

  • 0 Points
I have a test form and it is filtering the way I want it to.  I think the thing I'm not understanding is:  Then set a test report to use that form for Grid edit.    How do you set a report to use a form instead of looking at the table?
Photo of Elaine

Elaine

  • 0 Points
Oh - I think I just found the setting you are talking about here.