Forum Discussion

BobJones's avatar
BobJones
Qrew Member
11 months ago

Filtering table with a list of mulitple values

I have a field in my table that is of a randomly generated number for each record. I need to update another field of multiple records that are on a list of numbers that is created by someone else and sent to me that matches the randomly generated values on that table. This table has thousands of records so scrolling to find them all takes forever.

Is there a way to filter and show only the records I need to update by copy-pasting or importing the list of numbers I receive into a filter or formula. As of right now the only way is to type in the number in the search filter for that field and then update.



------------------------------
Bob Jones
------------------------------

4 Replies

  • Bob,

    There is several ways to tackle this.  You are already doing the brute force method.  The most elegant is to write some code that would ingest the file and then update the records.

    There is a third way with Pipelines.  I would create another Quickbase application call it Vendor Data or something like that.   You do not even need any relationships.   Here is the structure

    You need three steps in your Pipeline.  It starts when you import records to the Vendor Data Table

    The Search Step finds the matching record by Random Number

    Then it updates the records in the Existing Table.

    Reach out if this does not make sense.



    ------------------------------
    Don Larson
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      I have two other ideas.

      The first leverage is the fact that when you are searching in any filter box on a QuickBase report or any kind of search if you separate the search terms with OR in uppercase then you can search for things like  

      cats OR dogs OR fish

      One low tech solution is to get hold of these numbers and use excel to calculate a string which is those numbers separated by  OR

      The other approach is to create a special table with exactly one record in it called search filter. It will be record ID number one. You make a big text box to paste in your search target numbers. Then because this is record ID when you make a relationship back to your detail records based on the reference field of formula of one. Then look up those search terms down into the details table.  

      Can you create a formula that calculates to true if the search terms master lookup random number Contains the random number on the record.

      These approaches both assumed that the number of digits in your random number and the provided random numbers have the same number of digits otherwise you could get false positive hits I think.  



      ------------------------------
      Mark Shnier (Your Quickbase Coach)
      mark.shnier@gmail.com
      ------------------------------
      • BobJones's avatar
        BobJones
        Qrew Member

        That OR search macro seems promising. I will get more info from accounting to see if that approach will work. Thanks for the response.

        The purpose of the filtering changed slighty which I put in my other response, but I do not think that changes the approaches you mentioned. Thanks for the response.



        ------------------------------
        Bob Jones
        ------------------------------
    • BobJones's avatar
      BobJones
      Qrew Member

      Okay thanks for the quick response. After speaking to the accounting department, the task is now much simpler than before.

      All I need to do now is to simply filter by the numbers I receive and print out each record, there is no need to update any values or any data. I just need a report that is filtered by the list of numbers I get

      How does this change the pipeline or approach. 



      ------------------------------
      Bob Jones
      ------------------------------