Summary Field Through 2 Tables Related in Different Directions

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

Want a summary field link which links to records in another table.

Associates < Assignments > Clients < Trust Roles

(However, an Associate would only have 1 Assignment relating to any 1 Client)

Embedded Assignments table for each Associate should have a numeric summary field which links to the Trust Role records.

I related Assignments < Trust Roles, but the standard summary field for all records is showing as 0 (whereas the lookup summary field brought through the Client table is showing the number of Trust Roles, but clicking on link goes to Client records instead of Trust Roles).

I have a feeling this is because the relationship between them goes in two directions, so I'm trying using an =1 method, with a numeric summary field that's value is 1, but I don't think I'm doing it right.

Thank you!

Photo of Amber


  • 590 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
You will need to make your own hyperlink field.  It will show the # of Trust Roles that the client has, but it will actually be a hyperlink URL to run a report.

You will need to make an <ask the user> report on Trust Roles for Related Client Clients = <ask the user>

The formula for the hyperlink will be 

var text Words = ToText([Client - # of Trust Roles]);

var text URL = urlroot() & "db/" & [_DBID_Trust_Roles] & "?a=q&qid=99&nv=1&v0=" & Totext([Related Client]);

"<a href=" & $URL & ">" & $Words & "</a>"

you will need to replace the 99 with the qid of the <ask the user> report.

the URL will run  that report which has 1 <ask the user> question (ie &nv=1  Number of variables = 1) and the n it will supply the answer to the zeroth question.  (&v0= ....). for some reaosn it has to start numbering the answers at zero.