Need our users to be able to filter a list of 2-50 records by pasting in a list of them, and quickly get to only the relevant records in QB that they want to update.

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

We have a large database with 100k+ records.  Need our users to be able to filter a list of 2-50 records by pasting in an excel list of them, and quickly get to only the relevant records in QB that they want to update.  This is a constant problem for our users to filter through the large lists left after dynamic filters are applied, and our technology partner says it can't be done in QB.  Is there a native feature we can use rather than develop elaborate custom add in features?

Photo of Erik

Erik

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
I think what you are saying is that you need to paste in 2-50 records from the Excel list consisting of a single column of data such as a list of names or account numbers and return a report with those records with a field matching some item on the list you pasted. Is that correct? If so you can accomplish this with a simple script. 

If you are pasting in multiple columns of Excel data you can still do this with a simple script but you just have to add some additional logic.

Please clarify and perhaps provide a sample of typical Excel data you would be pasting in to build the report on.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
I may have a native solution.

https://yqc.quickbase.com/db/bkm3jnw39


The idea is that you paste in the excel list into the search box you see with the Green button, and then save. The Matches will float to the top of the embedded report. 

That single field is the looked up into each detail record.

The Match checkbox formula field is simply

Contains([Excel copy and paste], [Postal FSA])
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
You had me there for a moment until I looked at the action: ?a=er

Pretty clever for native! I am going to have to release a new technique to keep the arms race going.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Hi praise indeed, coming from you.  

I think that after Erk tests, he will know if this is "good enough". For example, if the dental record is
abc
 And the excel cut and paste is

abcdef
ghijk

Then there will be a false hit because abc can be found in that string.

In a similar situation I had to bookend the target string like

*abcdef*
*ghijk*

 And then bookend the records by formula to result in
*abc*

To avoid false hits.  But Ekik's data may not lend itself to raise hits and he is just trying to get a "pretty good" search result, and not a perfect formula count, for example of valid records.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Erik,
If you get my solution working, I'd love to know the performance on a 100,000 record Tabke.
Photo of Erik

Erik

  • 0 Points
Thanks QB Community, I'm going to play with this today and see what we find.  

This will help, but I suspect this is going to require additional refinement because we have a lot of similarity in the record ID that can't be removed.  So it's going to need to be an exact match analogous to =vlookup(searchterm,table,column,FALSE) in excel.

Dandibolt - your assumption is correct.  A single column would be a huge improvement and is the application we need 99% of the time.  Multiple columns would be a bonus, but not required.

Thanks!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
This is easy to do then. I will look at it later as I am in a conference call now.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,044 Points 20k badge 2x thumb
E>A single column would be a huge improvement and is the application we need 99% of the time

D>This is easy to do then.

I don't know how you want to implement this from a navigational perspective but the essential code to take the values out of a <textarea> and form a query is as follows matching any of the values in the <textarea> is:

Markup:

<textarea id=QBU_Search>
alpha
beta
delta
gamma
</textarea>

Script:

var fid = 6;
var joinPrefix = "{" + fid + ".EX.'";
var joinSuffix = "'}";
var joinString = "'}OR{" fid + ".EX.'"
var query = joinPrefix + $("#QBU_Search").val().split("\n").join(joinString) + joinSuffix;
console.log(query);

//output:
//{6.EX.'alpha'}OR{6.EX.'beta'}OR{6.EX.'delta'}OR{6.EX.'gamma'}