Discussions

Expand all | Collapse all

Need Formula Field to always navigate to report of child records

  • 1.  Need Formula Field to always navigate to report of child records

    Posted 02-05-2020 16:01
      |   view attached
    See attached Excel file for explanation of what I am looking for but in short, I am looking to replace a report link field with a formula field that will always take me to the report of child records where is 1 or 100 of them.

    ------------------------------
    Chris Swirtz
    ------------------------------

    Attachment(s)

    xlsx
    Explaination of need.xlsx   815K 1 version


  • 2.  RE: Need Formula Field to always navigate to report of child records

    Posted 02-05-2020 16:38
    You can make a URL formula button to run any report.
    A report link field is just a built in feature to run a report with one extra filter - that being how the Report Link field is configured, typically but not necessarily where the Record ID# field on the parent matches the field [Related Parent]] on the child record.

    So, first step is to make your perfect report with an <ask the user>  for [Related Parent]
    Run the report, answer the question and observe the URL.

    Now make a formula URL to mimic that URL

    URLRoot() & "db/" & [_DBID_of the child table found in advanced properties] & "?a=q&qid=99&nv=1&v0=" & [Record ID#]

    So translated that says:
    Go to my Realm and then to the child table and run report #99 which has 1 question and the answer to the zeroth question (don't ask me why but computers sometimes like to start to count from zero) is [Record ID#] of the parent record.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Need Formula Field to always navigate to report of child records

    Posted 02-05-2020 16:47
    so if the DBID of the child record is bp9s6xp7v how would that get entered into this formula? Every way I add it I get a syntax error, but I'm sure I'm doing it wrong.


    ------------------------------
    Chris Swirtz
    ------------------------------



  • 4.  RE: Need Formula Field to always navigate to report of child records

    Posted 02-05-2020 16:50
    look at the advance properties tab for the child table.  ASt the bottom it will give the table alias like

    _DBID_CHILDREN

    so you just put that in the square brackets like below including the leading underscore

    [_DBID_CHILDREN]

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Need Formula Field to always navigate to report of child records

    Posted 02-05-2020 17:19
    This worked perfectly, thank you!

    ------------------------------
    Chris Swirtz
    ------------------------------



  • 6.  RE: Need Formula Field to always navigate to report of child records

    Posted 02-07-2020 11:46
    Mark,

    Is it safe to assume that this same style formula could be utilized to take a user to a specific form instead of a report?

    For instance, if a user is in the Workstation table and clicks  "Add Peripheral" it takes them to one form, but if they are in the Assessments table and click "Add Peripheral" it takes them to a different form?

    ------------------------------
    Chris Swirtz
    ------------------------------



  • 7.  RE: Need Formula Field to always navigate to report of child records

    Posted 02-07-2020 14:50
    OK, different question,  but there is a parameter in the API_GenAddRecordForm API to specify a form.

    you add &dfid=99 

    to specify say form # 99

    But let me go on record, that I really try to avoid having multiple forms as it always gets confusing on how when a user visits a record to have the system know what form to use.  I prefer form rules whenever possible. 




    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Need Formula Field to always navigate to report of child records

    Posted 02-07-2020 15:39
    I wish you could tell my boss that. She is insisting that we use different forms depending on what stage of the process we are and what info is being entered. So like if you are adding "inventory" to the table, the form would show certain fields. But then if you are adding an "Assessment" to the same table, you would need to utilize different fields and the inventory ones would be hidden. I'm not sure how to do that via form rules either. ..

    Can you give me an example of where I would add that piece of code into the formula? The current formula that creates a child "Peripheral" from the "Workstation" Parent table looks like this:

    URLRoot() & "db/" & [_DBID_PERIPHERALS] & "?a=API_GenAddRecordForm&_fid_15=" & URLEncode ([Record ID#])& "&z=" & Rurl()



    ------------------------------
    Chris Swirtz
    ------------------------------



  • 9.  RE: Need Formula Field to always navigate to report of child records

    Posted 02-07-2020 18:31
    The boss will not care how you get a look of different forms as long as the form that the user sees is appropriate to the type of transaction, and it not cluttered up with extraneous fields.

    For example, if you have a Transaction Type of Inventory, then you use form rules to expose the appropriate Tabs, Sections or fields.

    If the Transaction Type is Assessment, then different Tabs, Sections and Field apply will show.

    Multiple forms in your situation is a recipe for disaster and frustration.  

    I've been doin' Quick Base since 2002, so that is like wow 17+ years.  I avoid multiple forms like the plague.  The only exception might be to have a different form for Mobile.  Far better to have a form with 20 Form Rules , than to have even two forms.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 10.  RE: Need Formula Field to always navigate to report of child records

    Posted 02-07-2020 18:33
    I hesitate to even tell you this as its such a dead end, but to directly answer your questiion ...

    URLRoot() & "db/" & [_DBID_PERIPHERALS] & "?a=API_GenAddRecordForm&dfid=99&_fid_15=" & URLEncode ([Record ID#])& "&z=" & Rurl()

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------