Discussions

Expand all | Collapse all

Buttons: how to revise the standard "Add XX" button to default to data from a related fourth table

  • 1.  Buttons: how to revise the standard "Add XX" button to default to data from a related fourth table

    Posted 06-30-2021 16:07

    By default there's an "Add XX" button to add data to a second table related to an item in my first table. I'd like to take that one step further to make an "Add" button that pulls the data from table 2 based on it related data in other tables. 

    I have 4 tables that are related.  I'll use the QuickBase favorite apartment/rental example since I think that's easier to follow that my actual table names which relate to a QA inspection process:

     

    Table 1: Tenants

    Table 2: Rental Agreements

    Table 3: Type of Rental Agreement

    Table 4: Buildings

     

    Tenants can have many Rental Agreements – one-to-many relationship

    Buildings can have many Tenants – one-to-many relationship

    Buildings can have many Rental Agreements – one-to-many relationship

    Buildings can have many Types of Rental Agreements – one-to-many relationship

     

    Note on Types of Rental Agreements: There are only three types (annual, month-to-month, 5yr), but have different specifics depending on the related Building, that's why this is a separate table and not just a multiple choice field.  For example Building A's month-to-month lease does not allow dogs, but Building B's month-to-month lease does.  Also note that a Building may have more than one "annual" type rental agreement, but only one that is "current" which is determined by a checkbox field.

     

    I have a Tenants table and I want to add a Rental agreement.  There's a standard "Add Rental Agreement" button.  I've used dynamic rules to pull the related Building info into the Rental Agreement form.  I have a conditional field to choose the Type of Agreement.  This works fine.

     

    However, I would like instead to have an "Add Annual Rental Agreement" button that would automatically set the Type of Agreement to the "Annual" type for the related building?  The reason is not just that I don't want to have to pick when I open the form, but because I only want user to be able to select this type of agreement when adding a new Tenant.  Later, other types of agreements may be added.

     

    Is that something I can code into the button somehow?  Do I need to pull down information from the Type of Rental Agreement to the Tenants table?  Is there something I can do with form rules or separate forms that would make this possible? 

     

    The standard "Add Rental Agreement" button code looks like this:

     

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

     

    Below is what I tried, but it didn't work.  Field 38 is my rental agreement type. I wasn't sure if I could just add text there like that, but anyway, it also doesn't consider there may be multiple "annual" types, and I need it to look at the one with the check box set to "current."

     

    URLRoot() & "db/" & [_DBID_RENTAL_AGREEMENTS] & "?a=API_GenAddRecordForm&_fid_9=" & URLEncode ([Record ID#])& "&z=" & "?a=API_GenAddRecordForm&_fid_38=Annual" & Rurl()

     

    Any recommendations on how to approach this or specific code to add to the button are greatly appreciated.



    ------------------------------
    Kim Gardner
    ------------------------------


  • 2.  RE: Buttons: how to revise the standard "Add XX" button to default to data from a related fourth table

    Posted 06-30-2021 16:15
    I am making some guesses an assumption see her so you can correct me if they were incorrect..

    The key field of the Rental Agreement Types parent table is probably [Record ID#].  That means that you are probably populating the field called [Related Rental Type].  you will need to see which record that you want chose for that Annual choice.

    Let's say that the record you want to choose is Record ID# of 5, so that is what needs to go into the field for [Related Rental Type].  Check if that is correct field for the reference field of the relationship.


    URLRoot() & "db/" & [_DBID_RENTAL_AGREEMENTS] & "?a=API_GenAddRecordForm"
    & "&_fid_
    9=" & URLEncode ([Record ID#])
    & "&_fid_
    38=5"
    & "&z=" & Rurl()


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



  • 3.  RE: Buttons: how to revise the standard "Add XX" button to default to data from a related fourth table

    Posted 06-30-2021 17:10
    Hi Mark,

    Thanks for the suggestion.  I think that would work if my one-to-many relationship was set up in the other direction, that is if Rental Agreement Types had many Buildings.  In that case I would only have 3 records in my Rental Agreement Types table, and I could pick the record ID related to "Annual"

    However, the relationship I have is: Buildings have many Rental Agreement Types

    The data looks more like this:
    Building A
    • Rental Type "Annual" with conditions XYZ, current  - > Record ID 1
    • Rental Type "Month to month" with conditions ABC, current - > Record ID 2
    • Rental Type "Annual with conditions XY, not current - > Record ID 3
    Building B
    • Rental Type "Annual" with conditions DEF, current - > Record ID 4
    • Rental Type "Month to month" with conditions CGY, current -> Record ID 5
     So it's not enough to look at the "key/record ID" field because Buildings have many Rental Agreement Types rather than Rental Agreement Types having many Buildings.

    The form already knows the related Building, so there should be some way for it to know the related Type of Rental Agreement for the conditions of "Annual" and "Current" - but I'm starting from the Tenant table creating a record on the related Rental Agreement table, so they're more distantly connected.

    ------------------------------
    Kim Gardner
    ------------------------------



  • 4.  RE: Buttons: how to revise the standard "Add XX" button to default to data from a related fourth table

    Posted 06-30-2021 17:31
    OK, so 1 Building has many Rental Types and we need for the Building record to know what the Record ID# is for its Current Annual Rental Agreement.

    No problem,

    Make a Summary of the Maximum Record ID# of the Rental Agreements subject to the filter that it is Current and the Type is Annual.  Call that [Record ID# of Current Annual Rental Agreement].  Pass that down to Tenants as that is where your Add Rental Agreement button lives. 

    URLRoot() & "db/" & [_DBID_RENTAL_AGREEMENTS] & "?a=API_GenAddRecordForm"
    & "&_fid_
    9=" & URLEncode ([Record ID#])
    & "&_fid_
    38=" & [Record ID# of Current Annual Rental Agreement]
    & "&z=" & Rurl()




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



  • 5.  RE: Buttons: how to revise the standard "Add XX" button to default to data from a related fourth table

    Posted 06-30-2021 19:13
    Thanks Mark! I think I've got it working correctly now.  I would not have thought about using the summary field for that.  This will be helpful to replicate on other forms I've made.  I really appreciate the insight.

    ------------------------------
    Kim Gardner
    ------------------------------