Report Link Filter possible?

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

I am trying to create a link on a grandchild record to the grandparent's child, including a filter on that information but cannot figure out the filter part. Here are the specifics:

1. Opportunity table has a child table Documents

2. Opportunity table has a grandchild table Line Items

3. Line Items and Documents are not related

I want to show a listing of a specific Document type that is associated with the Line item (because of the shared Opportunity table). I was able to create the Report Link with Opportunity Name linking the Line Item and Documents table. But I cannot figure out how to filter out only a specific document type on this report, so that the Report link will land on the filtered document report.

*Please note, I need to include this field on a REPORT not a form (if only it was that easy!!)

Thanks!

Photo of Leanne

Leanne

  • 262 Points 250 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,696 Points 50k badge 2x thumb
I can help but need more information.  When you say "filter out only a specific document type on this report".. Is that a "hard coded" filter, e.g. Document type = "A", or does it vary according to the data.
Photo of Leanne

Leanne

  • 262 Points 250 badge 2x thumb
Document Type is a multiple choice drop down menu - so it is hard coded.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,696 Points 50k badge 2x thumb
My question is "how do I know which document type you want the filter to show".  Also which record type (Opportunity, Documents, Line items) is holding this report link field.
Photo of Leanne

Leanne

  • 262 Points 250 badge 2x thumb
I want to show a subset of Documents related to each record on the Line Items table. There are many Line Items within each Opportunity. There are also many Documents within each Opportunity.

The filter would pull all Documents with a type "Assets" when the report link is clicked. I currently have the Report link in the Line Item table using the Opportunity Name (pulled down into the Line Items table) and the Opportunity Name pulled down on the Document's table. The general report link works fine, it shows a report of all documents associated with the line items' opportunity.

Hope this additional information helps, thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,696 Points 50k badge 2x thumb
I think I now understand your issue, though i don't think that you ever started it like this. 


You are probably aware that a Report Link field can be configured in Form Properties (on a form) to use a particular report.  So you can make a report with a filter of where Document Type = Asset and change the Form Properties to use that Report instead of what you are using now which might just be the record picker fields or a "List All" type of unfiltered report.  So on a Form the answer is easy - just make a report with the filter you want and set the form to use that report.

But now you are saying, "OK, but I also need it to work as a link off a report, not a form".  I don think that you can configure a link on a form.



So plan B can be this.

Make an <ask the user> report which will give you the perfect report if only the questions were answered correctly.  Observe the URL you see after you have answered the questions. 




It will look like this

https://mycompany.gesco.quickbase.com/db/xxxxxx?a=q&qid=104&nv=1&v0=IVO



Then make a URL formula field to replicate that result


urlroot() & "db/" & [_DBID_YOUR _TABLE_MAME] & "?a=q&qid=XX&nv=1&v0=" & ToText(Related Opportunity])

The table name  comes from the Advanced Properties  of the target tale that has the report.
The XX ois thr query ID# which you observe opn the report list or when you run the report.
The &nv=1 is telling QuickBase that this <ask the user> report will have 1 questions (ie Number of Variables =1)
The &V0= is leading quickbase to the answer to variable # 0 (don't ask my why but the first question is called # 0 and if there was a second question it would be v1.

So, the idea is the report run and rather than stopping to ask you a question, it answers its own quesiton.  In this case I'm assuming that the correct answer to the question is [Related Opportunity] which would hold the Record ID# of the Opportunity.
Photo of Wendy

Wendy

  • 0 Points
your example link is broken
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,696 Points 50k badge 2x thumb
Is there a question here?
Photo of Phil

Phil

  • 0 Points
I make the necessary changes and get this result

Expected a valid expression after the "&"


urlroot() & "db/" & [_DBID_MSO_ASSOCIATES] &   ?a=q&qid=qid=11&nv=1&v0=" & ToText([Enterprise ID])
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,696 Points 50k badge 2x thumb
It needs a " before the ?


urlroot() & "db/" & [_DBID_MSO_ASSOCIATES] &   "?a=q&qid=qid=11&nv=1&v0=" & ToText([Enterprise ID])