Allow Both Record Picker And Manual Entry For Relationship Reference Field

  • 1
  • 2
  • Question
  • Updated 3 months ago
  • Answered
I have a form with a reference field that opens up a record picker (search/selection) box.  However users want to be able to simply manually enter a value without having to go through the process of opening a dialog box, searching for that value, and then selecting it.  

That search/select feature should still be available for those who are not sure of which value to enter, but those who do know would like to be able to essentially skip those steps and simply enter in the value into the box.

Is there some way to accomplish this?  Normal multiple-choice dropdown lists have this capability (where you can just type and it auto-fills a valid value as you type), but I cannot seem to find something similar for a reference field. I've tried using the "Use a record picker instead of a drop down menu" property, but it behaves as a record picker regardless of that setting (probably because there are so many records).
Photo of Jonathan Klein

Jonathan Klein

  • 182 Points 100 badge 2x thumb

Posted 3 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
So here's the trick ...

You have have your existing relationship, leave it in place, but do not base any lookup fields on that relationships.  It will be used for the "usual" drop down box / record picker data entry method.method.

Make a new data entry field called [Direct Entry]

Also make a new formula field called [Related Parent (final)].

The formula will be something like

IF([related parent regular method] > 0, [related parent regular method], [Direct Entry])

Now, make a new relationship to the parent based on [Related Parent (final)].

So then the use can use the drop down, or the direct entry and then the lookup will be off that formula result of those two fields.  All your lookups need to come off the relationship using [Related Parent (final)].




Photo of Jonathan Klein

Jonathan Klein

  • 182 Points 100 badge 2x thumb
That's an interesting workaround, though I'm not sure if it will completely solve what I'm looking for.  If I do it this way, wouldn't the [Direct Entry] field then not have any kind of validation to it since it is not connected to the parent records?  What I'm aiming for is essentially the same thing as the multiple-choice dropdown where while you have the ability to manually enter a value, its still limited to the items in the multiple-choice list.  Though I suppose I could possibly add some kind of form rule or something to validate the field....

Either way, wouldn't this require two fields on the form? One for manual entry ([Direct Entry]) and another for the record-picker ([Related Parent Regular Method]).  I'm building the app for users who are not the most tech-savvy to begin with and I imagine having two fields for the same input value will most likely confuse them.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Do you ave more than 1,000 choices in your dropdown list?  If so then you will be forced to use that pop up record picker search box.

But you are right, there is not a feature to dynamically offer choices when you have more than 1,000 records.

The only other off the wall idea if you prefer the drop down list, but have more than 1,000 choices is to contrive a way to limit the drop down to under 1,000 records via a conditional drop down.  if you had any interest in that, i can describe further.  With a drop down if you type quickly enough, it will position you to the, for example 

az's if you type az , so you would be dynamically positioned to closer to your choice depending on how many characters you type..
Photo of Jonathan Klein

Jonathan Klein

  • 182 Points 100 badge 2x thumb
Yeah there are thousands of records involved, so its forced into record-picker mode.

I'll have to see whether they are okay with this setup or if they'd rather stick with record-picker-only, but I think I'm going to try your original suggestion with a little twist. 

Here's what I'm thinking:
The [data entry] and [record-picker] fields will be placed side by side on the form within the same section by themselves to highlight the fact they go together.  Then using form rules I'll make it so that when the [record-picker] is used and an item is selected it will populate the [data entry] field then immediately clear itself.  So the result regardless of how the value was entered will always be a populated [data entry] field, an empty [record-picker] field, and populated lookup fields (assuming a valid entry was entered).  

Conceptually it's sort of like how date fields have that little calendar button within them that you can click on to bring up a selection popup and then once something is selected it populates the field's entry portion.  Ideally I'd have more control over the form and turn the [record-picker] field into a little box or something and place it flush against the [data entry] field to better mimic that, but I suppose this will have to do.  Hopefully that made sense.

Then for validation I'd just have a rule that looks at a resulting lookup field and if there's no data in the lookup field then I can assume that an invalid value was entered and prevent the save from occurring.

A bit messier than I'd like, but we'll see what they go for.  Thanks for the suggestion.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Sounds like your fleshed out solution will provide a decent user experience in the end and better than what they have now.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
The coming features may help.  They are in "early access beta testing" with selected apps on request.

https://www.quickbase.com/blog/august-2018-release-notes

Type-ahead search picker

We’re working on a type-ahead search for records that will reduce time and frustration for those with large datasets. With our current record picker, you have to scroll through up to 1,000 records to find the one you want. With our new record picker, you can search by typing to narrow down your results.

Because our new record picker doesn’t support conditional drop-downs, we’re looking for early access participants that don’t have conditional drop-downs. We’re also looking for individual apps rather than realms to test out this feature.