How do I create a button that adds children to a master table?

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

I have a Master table call "Products" and a Child table called "Items". One "Product" can have many "Items" associated to it.

On the form for a "Product" I have an embedded report that looks up that Product's "Items". 

What I would like to have on this form (the form for a "Product" on the master table) as well is a button that when clicked:

a) Pulls up a list of all children on the "Items" table

b) Allows me to select multiple items at once from that list

c) Associate the selected child items to the master product

This is to avoid my having to go into each child item and associate it 'upwards' to it's parent product.

I've read that this should be possible through the use of a URL formula, but I haven't been able to nail down how to get that to work. Does anyone know of a method to do this?

Photo of Bob

Bob

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
I can give you a demo of this quick select method in action, done using native QuickBase and just some formula URL buttons.  But the steps to set this up are beyond what I can explain here.    I can usually get these quick selects working in about 2 hours including the pre and post discussions.  Let me know if you would like a demo.  please contact me via the information in my profile.  I happen to be available now if that works for you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
Actually, maybe I can explain it here.  Have you used URL formulas before?
Photo of Bob

Bob

  • 0 Points
Hi! A simple explanation would be great if possible. I've only just started getting into URL formulas, so I only really know the basics (mostly taken from here: https://goo.gl/fpAHbV). Happy to learn/research more about them though!
Photo of Bob

Bob

  • 0 Points
So I was able to make a button that displays the type of form I need but unfortunately the functionality is lacking. The formula I have currently is:

URLRoot()&"db/"&"[TableID]"&"?a=recordPicker&fids=[FieldID]"&"showNewMaster=1&rpinstance=reffield"

Where [TableID] is the ID of the child table and [FieldID] is the field to be displayed in the record picker. I'm unsure what the last portion of the formula ("showNewMaster=1&rpinstance=reffield") means though, which may be why it's not working.

The main problem with it currently are:
1)  The "OK" button doesn't work (pressing the button does nothing); thus the items selected in the form are not related to the parent.
2) More minor, but the record picker displays in the same page rather than a smaller pop-up window. If there is a way to have it in a pop-up, then I think that would be more ideal.

Do you know how I might be able to address the above two problems? I feel like I'm pretty close.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
I will help you through thus but first I need to be sure if the objective.

Is yor goal to have a mater table of items and from those items you build Products. So 1 1 Product has Many Items, but also One Item is used in many products?  

So do you in fact have that middle join table built?

Please let me know your current tables and there relationships, and if aside from wanting to be able to select quicker it's working correctly?  From your original post, it sounds like you were missing the concept of that middle table for Product Items.
Photo of Bob

Bob

  • 0 Points
Sorry for not being clear before. In my tables, a product can have many items, but an item CANNOT have many products. It's a 1 to many relationship - not a many to many.

I have a pre-populated list of Items based on a .csv sync. What I'm looking to set up is a record picker that I can use to associate one or more Items to a single Product from within a Product's form without actually having to go into each Item and pick the intended Product from a drop-down list. The drop-down list and relationship between the two tables as a whole is working properly outside of wanting to be able to select the Items more efficiently.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
OK, here goes.  The goal is to select a "Focus Product" and then go to report of items which are not yet connected to a Product and be able to use full dynamic filters and the filer box to to get at the items  and then easily click click click to associate them with the Product.

I will make the assumption here that speed is the most important thing here, so after each item is selected, we don't want to disturb the Items report, as it may have been filtered, so we don't want it to be refreshed.  The user can then refresh the page when they choose.


I will also assume that this is intended to be a single user system for assigning items to products.  I have a slightly more complicated setup which would allow for multiple concurrent users.

So we will first make a table to hold the [Focus Product Record ID#].  So make a Table called Focus Product and make a field called [Record ID# of Focus Product].  Let assume it is fid 6.


Add 1 record to that table.  It will be record ID# of 1.



make a field on the items table called [Link to Focus Product] as a formula numeric field and make the formula a 1.

Make a relationship back to the Focus Product table using that field as the reference field on the right side of the relationship.  Lookup the value of the [Focus Product Record ID#] down to the items.  Great, now every item know the Focus Product Record ID#.

Also make a report of items which have not Parent Product, so where the field [Related Product] is blank.  Let say it is query ID# of 10.

Make a formula URL button on the Products table called [Set as Focus Product].


var text URLONE = urlroot() & "db/" & dbid() & "?act=API_EditRecord&rid=1&_fid_6=" & totext([Record ID#[);

var text URLTWO = urlroot & "db/" & [_DBID_ITEMS] & "?a=q&qid=10";

$URLONE

& "&rdr=" & urlencode($URLTWO)



OK, so that button should set the focus product and run the report of items not already associated with a Product

Next we need to make a URL formula field on the Items table to select items.  That button will simply edit the item to populate the field for [Related Product] and pop up a confirmation message to the user



var text URL = URLRoot() & "db/" & dbid() & "?act=API_EditRecord&rid=" & totext([record ID#])

& "&_fid_99=" & totext[Focus Product Record ID#]);  // replace 99 with the fid for the field [Related Product]

"javascript:" &
"$.get('" & 
$url & 
"',function(){" &
"$.jGrowl('This Item has been added to the Focus Product', {life: 5000, theme: 'jGrowl-green'});" &
"});" &
"void(0);"




You will probably want to also make a button to allow the user to navigate back to the Focus Product to see it with its embedded table of Items.  

 
Photo of Bob

Bob

  • 0 Points
Thanks a bunch for the answer! This will take a bit for me to go through, so I'll need to devote some time to it later this week. Thanks again - I'll respond back if I can get it to work.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
Ok, Le me know if you stuck anywhere and I will help you over the hump.
Photo of Bob

Bob

  • 0 Points
I got it working! I learned quite a bit with this one. Very clever solution  - thanks a bunch! I'm pretty confident I can port the sample over to the actual dataset without too much hassle now that I know how it works.

What it seems you did was to essentially make a placeholder product in the Products table that was used as a way to move the currently selected record number into the Items table so that it could be applied to the related product field. I would have never thought of nor known where to start with that.

You were right about wanting to make a button to let the user navigate back though. I was thinking of having this displayed in the notification bar at the top and just persistently hover there without timing out.

Do you know of a way for a button that goes back to the focus product and refreshes that page to be embedded into the javascript? Is the javascript that can be added limited in any way?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,168 Points 50k badge 2x thumb
Great that it working.

To add a button to go back to the focus product, you just add a field to the items table and show it as the last column on the report.

URLRoot & "db/" & [_DBID_PRODUCTS] & "?a=dr&rid=" & totext([Focus product Record ID#])