How to create a formula url button that links to a report related the parent but only pulls in related address, not record ID

  • 0
  • 2
  • Question
  • Updated 11 months ago
  • Answered
I have 3 tables: Loans, Draws, Trades. Loans is the parent to Draws, Draws the parent to Trades. In Trades I have several reports created depending upon the Draw # (each includes different sets of fields). What I want to be able to do is create a formula url button that the user can click on and it will pull up the related report, depending upon the Draw #, but relating to the Loan Property Address and not the Loan Record ID. The Loan Record ID is the key field and needs to remain the key field. Right now I have I have this formula:

If([Draw #]=1,URLRoot()  & "db/" & [_DBID_TABLE_1]& "?a=q&qid=138&nv=1&v0=" & [Loan - Property - Address: Street 1] ,
[Draw #]=2,URLRoot()  & "db/" & [_DBID_TABLE_1]& "?a=q&qid=139&nv=1&v0=" & [Loan - Property - Address: Street 1],
null)

But it is still pulling in the Trades for ALL addresses, not just the related address on that Draw. It's pulling in the correct reports, just not relating to the current Draw Property Address. Any suggestions?
Photo of Dawn Rene

Dawn Rene

  • 526 Points 500 badge 2x thumb

Posted 11 months ago

  • 0
  • 2
Are those two respective reports (138 and 139)  set to be <ask the user> type reports against that field?

Photo of Dawn Rene

Dawn Rene

  • 526 Points 500 badge 2x thumb
No they are not because I also use them for a Print Draw Request url formula button that works well. I just did not want to build more reports. :)
When  you use this in a URL

a=q&qid=138&nv=1&v0=" & [Loan - Property - Address: Street 1] ,

what that means in plain English is that you are running report # 138 and there are going to be 1 questions to be answered and the answer to the first question (which happens to be called number 0) is the value in the field 

[Loan - Property - Address: Street 1]
Photo of Dawn Rene

Dawn Rene

  • 526 Points 500 badge 2x thumb
Okay, I get that. Is there any other way to link it to the Loan property address or do I need to make the property address the key field? If so, I'll have to test so I don't screw up the rest of what I have set up! :)
I'm just saying that you need to make a report that asks a question and then you can have the url formula answer the question.  You do not need to change the key field.  It is just a button to run a report.
Photo of Dawn Rene

Dawn Rene

  • 526 Points 500 badge 2x thumb
Okay, thanks much!