Forum Discussion

PaulPeterson1's avatar
PaulPeterson1
Qrew Assistant Captain
3 years ago

Filter report to show unrelated records

I am not sure if you have tried to filter a report to show a specific set of records that may not have a common relationship other than having a value in the same column.  In my company, we frequently need to edit a set of specific records.  This is core functionality in the system that is being replaced by Quickbase, but I haven't been able to find a good way to do in in Quickbase, until now.  

Here's the use case:

I need to filter a report to display a specific step of projects, but there isn't a clear way to filter and only see the orders needed.

The approach I used starts with an Excel worksheet to quickly format the data.  The raw data is pasted into column A and this formula is in column B,

=IF(ISBLANK(A2), "", CONCAT(CHAR(34),A2,CHAR(34),IF(ISBLANK(A3), ");", ", ")))

The output from column B will be pasted into the Report Formula.

Then I edit the report where I want the data to appear and create a formula checkbox report formula.  I start off by typing

var text records = List(";", 

Then paste the output from column B.  for example
"AAA1234",
"BBB1235",
"CCC1236",
"AAA1235",
"BBB1236",
"CCC1237",
"AAA1236",
"BBB1237",
"CCC1238",
"AAA1237",
"BBB1238",
"CCC1239",
"AAA1238",
"BBB1239",
"CCC1240");

Then add a contains function to select the desired records.

Contains($records, [Project Number])

Finally, I add a report filter to filter on the report formula where the value for the record returned by the report filter is yes, in this example I named the formula InList


This was my solution to this issue, I would love to hear if there is an easier way to accomplish the same results.



------------------------------
Paul Peterson
------------------------------
reports and charts

8 Replies

Sort By
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    3 years ago
    I'm a bit confused on the ask. Is it that you want to filter records in a table where a certain field isn't blank?

    ------------------------------
    Mike Tamoush
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      3 years ago
      @Paul Peterson

      I believe you are saying that you have a list of records, say a list of Record ID#s or "Project numbers and you want to be able to copy and paste them form somewhere else and see a report of just those projects.

      If you are in a low volume environment where users will not get tangled up with each other here is a simple technique.

      Make a table with a single record. It will be Record ID# =1.  Block anyone from adding or deleting records.  Make a field there to paste in a set of "records", just in a vertical list.

      Make a relationship to the target table based on a reference field with a formula of 1 (to match Record ID# =1) 

      Lookup the search terms.

      Them make a  Checkbox formula field

      Trim([Styles for Search])<>""
      and
      Contains([Styles for Search], [Style #])

      Make a report link field on the single record search table (actually the relationship will make the report link field) 

      On the embedded report, filter where that formula checkbox is true.

      If you have a high volume of activity and the users ned to be independent of each other, then there is a user focus technique to keep the users separate.

      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------
      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander
        3 years ago

        I like Marks solution, but perhaps another low tech solution if you want to be able to just keep changing the search string is to create a dummy record somewhere that has a simple multi line field and a formula.

        MultiLine Text field you you paste your list. The formula spits out a search string and is simply something that does a search and replace for line breaks on your list, and puts in " OR ". (So it spits out aaa OR bbb OR ccc OR ddd).

        Copy and paste that into your search on the report. This of course is not going to set your report and keep it there. I was thinking more if everyone had their own list.

        Again, I like Marks solution a lot better, and mine would really be messy with say 100 records, but I think works. But Marks combined with a user focus table could really be slick.



        ------------------------------
        Mike Tamoush
        ------------------------------