Forum Discussion

BlakeEstep's avatar
BlakeEstep
Qrew Trainee
5 years ago

View query record if single record otherwise view query list

I am creating some buttons in a Formula - Rich Text field. They will allow users to quickly jump from one table to the next using the table to table relationships we have set up.

Mainly what they will be seeing is a list of related records based on a query. For example, if I am in the Customers Table and I click on the Invoices button it will take me to the invoices table and I can see all the invoices related to that customer.
If there is only one invoice I would like the button to take me directly to that invoice record and not view it in list mode.

Is there a way to use the query and set up an if statement that would check the length of the query response and if there is only one then view that record.

Below is an example of the query code I am using for one of the buttons.

URLRoot() & "db/" & [_DBID_INVOICES] & "?a=q&query=" & "{'6'.TV.'" & [Record ID#] & "'}"​
Formulas and functions
Relationships
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    I presume that you are aware that if you use the native Report Link field, that will be the default behaviour that you want, and I assume though that you are trying though to have a fancier Rich text formula field to replicate the same behaviour.

    If you have a relationship you can have a summary field to count the # of children.​

    You can also have a summary field of the [Maximum Record ID#] of children.  of course, if these is only 1 child, then it will be the maximum.

    so then you just have an IF

    IF([# children] = 1,
    URLRoot() & "db/" & [_DBID_INVOICES] & "?a=dr&rid=" & [Maximum Record ID#],
    ​
    URLRoot() & "db/" & [_DBID_INVOICES] & "?a=q&query=" & "{'6'.TV.'" & [Record ID#] & "'}"​)

     you will want to suppress the button in form rules  or calculate to null if [# of children] = 0

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • BlakeEstep's avatar
      BlakeEstep
      Qrew Trainee
      5 years ago
      Thank you, this is exactly what I needed.
      I wasn't sure if there was a way to do the same thing but within the URL itself. I will implement this solution.
  • PeterMonahan1's avatar
    PeterMonahan1
    Qrew Member
    7 months ago

    For anyone who stumbles upon this, the way to do it with one URL is to add:

     &opts=disprec

    as a query parameter in the URL.