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!!)
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!
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
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.
Expected a valid expression after the "&"
urlroot() & "db/" & [_DBID_MSO_ASSOCIATES] & ?a=q&qid=qid=11&nv=1&v0=" & ToText([Enterprise ID])