Add a "contains" search feature within a drop-down list linked with another table.

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

Hi,

I’m looking to improve the search process within a drop-down list that is sourced via a relationship with another table.

Below is a detailed explanation of my current set-up: 

I have a table for Individuals listing their contact information. One of the fields is for the company an individual works for, and is linked via a relationship (text look-up I believe it is called) to another table called Companies.

My list of companies is close to 1,000 already and will continue to grow. I have encountered problems when trying to check if a company already exists in my list before creating a new entry. For example, I searched for the company “Seven Seas Ventures” but was not able to find it. Later, I noticed that the company had been entered as “7 Seas Ventures.” I am only able to search by typing the exact name of a company though.

Basically, I would like to have the ability to search for a company wherein “the user enters any number of words or partial words and the search will find hits for a ‘contains search’ on all those terms” (Mark Shnier posted this in another answer to a QB user).

I have two other instances where this would be helpful to have in my database as well – I have another table called Meetings which has two fields; one pulling names of individuals and the other names of meeting managers. The “contains search” feature would be extremely helpful for those as well.

Thanks,

Carter

Photo of Carter

Carter

  • 0 Points

Posted 4 years ago

  • 0
  • 1
I have a way to do this using native QuickBase, but the process is to complicated to explain a step by step method.  Basically I would make a URL formula button to store the Contact record ID# in a User table.  Then present a report of all Companies.  The user would get full use out of the native filter (typing) or the dynamic filers at the side and then click a button to select that company.  The user would then be landed back on the Contact record with the company selected.

So one click to initiate, then one click to select after any searching and filtering.  Contact me off line via he info in my profile if you like for further details.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
We have a presentation submitted to Mas Que Nada User Group Conference being held in Brazil that introduces a new technique named after that old war hound General Record Picker. What the General Record Picker (GRP for short) technique does is create a miniature popup form that substitutes for QuickBase's record picker interface. As you will recall, QuickBase uses one of two native record picker interfaces to select a single parent record and relate it to a child record. If the number of parent records is less than 1000 a dropdown list is used to display candiate records and if the number of parent records is 1000 or more a record picker window is used. You can see these two native interfaces midway in this documentation page:

http://www.quickbase.com/user-assistance/creating_a_relationship.html

The General Record Picker technique does away with these two native interfaces and allows you to create your own custom interface that allows you to search for parent records using whatever form processing logic and elements you choose (including text inputs, radio buttons, checkboxes, selections etc). In your case there would be a single text field that solicits the terms you want to search for and the matching parent records containing any of those terms would be dynamically listed below. Once a user selects and confirms a record in the dynamically generated list the reference value is inserted into the [Related Parent] field of the child table and the process is complete. In a nutshell the General Record Picker technique generalizes the process of presenting a custom form to the user where arbitrary parameters can be entered that will be turned into a query of some type. Those records matching the query are displayed below and once one record is selected the [Related Parent] field is updated.

Applying the General Record Picker technique to your situation there would be one text box to collect the terms:


<input type="text" id="QBU_terms" name="QBU_terms">


and a user might type into this <input> the following:


Michigan Iowa Alaska  


The form processing script would use code similar to this to generate a URL that queried the table using API_DoQuery:


var dbid = "somedbid";
var fid = 6;
var clist = "6.7.8";
var slist = "6";

var search = $("#QBU_terms").val();
var terms = search.match(/\S+/g);
var query = "{" + fid + ".CT." + terms.join("}OR{" + fid + ".CT.") + "}";
var url = dbid + "?act=API_DoQuery" + "&query=" + query + "&clist=" + clist + "&slist=" + slist;

console.log(url);


Here is the URL that the above form processing would generate:


somedbid?act=API_DoQuery&query={6.CT.Michigan}OR{6.CT.Iowa}OR{6.CT.Alaska}&clist=6.7.8&slist=6


These records and fields are listed in the dynamically generated list and a user is invited to select one record to complete the record picking process.

I attached a "conceptual" screenshot below:

Mas Que Nada!
Photo of Carter

Carter

  • 0 Points
Thank you very much! This is really helpful.
Photo of Carter

Carter

  • 0 Points
Thanks so much for your help!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
I am going to work up a full demo of this technique as I had already started one and have been discussing it with various clients and developers.
Is this a similar concept to the Autocomplete solution that Softtech Experts do?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
>Is this a similar concept to the Autocomplete solution that Softtech Experts do?

I don''t know about that particular implementation but what I am talking about is a completely general approach (hence the name General Record Picker technique) to record picking which would subsumes the lone feature of autocomplete as a special case. What I have created is a technology to popup up a miniature form that solicits various inputs from the user and allows them to select on parent from the list of matching parents that meet the criteria in the miniature popup form. In fact I just worked up an working demo that I am sharing with a few select people but you are happy to look at the pdf I created to help understand the process. If you want more info feel free to contact me via the info in my profile.
http://softtechexperts.com/autocomplete.html

This is the autocomplete for quickbase. It works really well but my thinking is that it should be native to quickbase and not something that every user of an app has to register to use.

Just looking at the PDF it appears to be similar.!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
> ... it appears to be similar.!

I doubt there is any similarity as my approach is extremely general - although I have not seen that feature in action. Although I am displaying a General Record PickerTM form with seven inputs this large number of inputs was used only for testing some features of the code. More naturally you would only have a few input controls on the popup form to base the query of potential parents on . In the original question the user would have just one input control that would serve as a way of listing keywords any one of which could match a parent record and be displayed for possible selection. 
Also I a better name: General Record PickerTM. Anyway back to my marathon viewing of old SCTV episodes - hopeful I will come up with some catchy names so I can create more QuickBase techniques. Down the memory hold: 
http://tvtropes.org/pmwiki/pmwiki.php/Series/SCTV
https://www.youtube.com/watch?v=8Kd3-m7yCF4