Discussions

 View Only
  • 1.  Filter report to show unrelated records

    Posted 16 days ago

    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
    ------------------------------


  • 2.  RE: Filter report to show unrelated records

    Posted 16 days 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
    ------------------------------



  • 3.  RE: Filter report to show unrelated records

    Posted 16 days 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
    ------------------------------



  • 4.  RE: Filter report to show unrelated records

    Posted 16 days 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
    ------------------------------



  • 5.  RE: Filter report to show unrelated records

    Posted 16 days ago
    I don't know where the data source is for the record set, but if you could coax it into a format like

    123 OR 23345 OR abd23 OR etc

    ie separated by OR in upper case, then you can paste that string into a filter box.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 6.  RE: Filter report to show unrelated records

    Posted 15 days ago

    Mark is correct in his analysis of the issue.  Yes, I need to the ability to paste a set of project numbers and pull up a report to view and typically grid edit just those records.  The table in question is my nightmare that I inherited from the original poor design of the app.  There are over 700 editable fields and the record count typically hovers around 150k.  If the original design was better, I would work with Mark's idea, however in this case I would need to create another monstrous table.  

    Thanks for the suggestions!



    ------------------------------
    Paul Peterson
    ------------------------------



  • 7.  RE: Filter report to show unrelated records

    Posted 15 days ago

    Paul,

    I think you may be misunderstanding Marks idea. In his idea, you only need to create 1 record in another table. I do similar things to this all the time, and have a table I simply call, Global Helper Table.

    You create 1 record in your helper table, and that is where you paste the search terms (in a multi line text field), as a vertical list.

    You look that up to your projects table (the relationship is simply to a formula numeric field, where the formula is 1, or whatever the record ID in your helper table).

    Now you have your search terms on your Project, and you can use a formula checkbox to see if the project name is in your search terms (I think this may be where the confusion is, Mark used the word styles, I think in your case it should have said projects).



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



  • 8.  RE: Filter report to show unrelated records

    Posted 15 days ago
    If you haven't used the User Focus table (something Mark taught me, and perhaps developed himself??), that is a technique in which every user can have their own search terms, versus only having 1 record where users would share it and keep deleting and re-entering search terms.

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



  • 9.  RE: Filter report to show unrelated records

    Posted 15 days ago
    Thanks, will look into that

    ------------------------------
    Paul Peterson
    ------------------------------