Skip to main content
Question

Search bar - increase character limit past 250?

I am often searching in my app among thousands of records to provide coworkers with product info. These searches are based on a list (either csv or text). There is unfortunately no way to filter, since the request-for-info lists arrive without context.  

My product codes are 10 characters. I've been able to use the "OR" function to string together around 23 codes but I can't pass the 250 character limit. This doesn't help me when I'm trying to locate info on 200-500 codes. 

I've resorted to downloading spreadsheets and VLOOKUP-ing. Is there some way, or an existing add-on that allows a solution to this "cut and paste" search challenge? 

Thanks in advance. 
    This topic has been closed for replies.

    Is your search target a single field, or perhaps just a few fields?  I imagine so.

    My suggestion is to create a single record in a new table called Search. Add just 1 record ot that table.  It will be record ID#1.  

    Make 1 field as a large text field data entry field.

    On the search target table put a formula numeric field with a formula of 1.

    make a relationship to the details table and lookup the search target text.

    Make a report where the [search target text lookup] contains the search target field in the details table.

    Use that report as a report link field report on your search target single record.

    So you should be able to just edit the search record, save,  and see the hits on the embedded report.
    Trying to set this up. First problem for me might be not understanding data entry. 

    -  Make 1 field as a large text field data entry field.
    So the Search table will have 1 field only, and it will be Text?


    - On the search target table put a formula numeric field with a formula of 1.
    I can make the formula field, but how do I make the value = 1?

    - Make a relationship to the details table and lookup the search target text.
    So the table I wish to search is the Parent and the newly created Search table is the Child? What should the reference field be? I can enter the Lookup field as the one field I created in the Search table. 

    - Make a report where the [search target text lookup] contains the search target field in the details table.
    I don't understand this. Does this mean I should make a Table Report with the Lookup Search field and the field I wish to search in as columns? 

    Use that report as a report link field report on your search target single record.
    This report should show up on the Search table records or the table I wish to search in? 

    So you should be able to just edit the search record, save,  and see the hits on the embedded report.
    I would paste the string of values into the one text field here? Would they be separated by "OR"? 

    I appreciate the help. 
    Yes the Search table will have only 1 large text field and only 1 record.  It will be [Record ID#]. It looks like you called that field [ID#1] 

    To make a field that calculates to 1 the formula is 1.  Just a 1 will calculate to 1.  1 is the [Record ID#] of the one and only Search record. call this [Link to Search table (=1)]

    On the relationship, the Search table is the Parent.  The child table is you table that will be searches with all the records.  When you make the relationship, do not use the field that QuickBase offers to you.  Use the  field you just created [Link to Search table (=1)].

    Lookup the Search text field [ID#1] down to the details table.  call it [Search text for ID#1]

    Make a table report where the filter is

    where  [Search text for ID#1] contains [ID#1]  (assuming that your search target field is [ID#1]

    Run the report and let me know that you get the expected hits.  The report columns would be the [ID#1] field and any other fields you want, but not that [Search text for ID#1] field.

    There should be is  OR in the data you enter in the search box.  But ideally it would be good to separate the search text elements by a semicolon ;

    Post back if you get it working that far and we will continue.
    Did what you said and achieved a lot of success! 

    After some testing, here are my results: 
    • Using this method, I am able to search for up to 100 items
    • Searching for 101+ items results in the message "There are too many criteria in your query"
    • It only works with "OR" separating the search terms
    • When I try with semi-colons, I get no results
    If you have any suggestions to further optimize this, please let me know! I

    If not, thank you very much for the helpful solution. 
    Oh, also - I wasn't sure if there was a difference between a "text" field and a "data entry" field. I am entering the search terms in a text field. If there is another type of field for data entry, perhaps that would increase my results. 

    That said, the face that it was exactly 100 made me think it was by Quickbase's design. 
    Can you post a screen shot of your report filter.  I believe that you can get past that limit and that you are not filtering the report as i intended.
    Here's a link to a screen shot - https://drive.google.com/file/d/0Bxrtv4klVbV6Y2M5ZU5uU3VuTk0/view?usp=sharing
    I meant, can you tell me how the filter in the report is configured.
    I'm still trying to understand the filter on your report.

    I'm looking to see if it reads like 

    Where Search field - ID#1 Contains ID#1

    in my suggested solution, the search terms are a semi colon delimiter list, without the ORs.
    I have updated my filter and am much closer to success!

    My report works now, with semi-colon delimited values. It also supports 500+ search terms.

    The only catch is: it does not display when embedded. If I enter the search terms and navigate to the report on the target table, I get the results I need. If I try to view the report embedded on the search form, it says no records are found. I've checked, and they are both the same report (or so it seems). 

    If you have a suggestion for getting the embedded report to display, I'd love to hear it. If not, this is a pretty fantastic method for searching hundreds of items and I hope others find it. 

    THANK YOU
    OK, good progress then.

    Can you tell me the setup for the report link field?  What is on the left and what is on the right.
    I took a look at this and - success! 

    I had it set up to match values from [Text ID#1, aka search terms] to the values in [product code field, aka target field]

    I corrected it to match [Text ID#1] to the field [Master Table: Search Table - Text ID#1] 

    This was your initial suggestion. I had assumed this was a suggestion for the table relationship and was confused. Now I understand this is the field relationship between two already related tables. 

    I am now able to search for hundreds of items at a time in a user friendly way .Thank you very much for the help. This will save me time and drive user adoption. 
    I should clarify - I have a table called "Master Table." It is the child in this relationship, which is a little confusing. 
    So all good on that embedded report now?
    Yes, challenge solved. Thanks
    Terms & ConditionsAccessibility statement