Forum Discussion

SalesInfo's avatar
SalesInfo
Qrew Assistant Captain
7 years ago

Item Relationship for adding bulk items in a table that is linked to many other tables

I would like to create a new record that contains the information from multiple tables. 

I have 4 tables
1. MASTER - which contains all basic information. This table (parent) is linked to both Amazon (child) and our website (child) in a relationship. The fields under each buyer will pop up depends on the choice that We choose (either Amazon or GXI or both). 

2. Amazon - this table has a list of all Amazon items
All the fields are from parent table (MASTER). This table (parent) is linked to the Promotion table (child). When the link button is clicked, form #3 on Promotion is open which has the fields for Amazon pricing. There are three different pricing (regular, special, custom promo) so this form is to choose the pricing type for the this product. 
 
3. Our website (GXI) - this table has a list of all GXI items
All the fields are from parent table (MASTER). This table (parent) is linked to the Promotion table (child). When the link button is clicked, form #4 on Promotion is open which has the fields for GXI pricing. There are three different pricing (regular, special, custom promo) so this form is to choose the pricing type for the this product. There are two different pricing (regular and special promo) so this form is to choose the pricing type for the this product. 

FYI, regular and special are filled out from MASTER. The custom option will let the user enter a custom pricing

4. Promotion
As I said in #2 and #3, this table is to create a promotion (child) for each product from either Amazon (form #3) or GXI (form #4). ***What I want to do is that for form #2, which is default form when I click "New Promotion" on a main table, I want to add a bulk promotion based on the buyer (either Amazon or GXI) I choose.
On this form, I created the first field, "Buyer" which I have to choose between Amazon and GXI

MY QUESTION/PROBLEM
- For Example, when I choose Amazon, I want a field pop up such as model#, which looks up all the model# from Amazon table. I choose the model# that I want and then the other fields from Amazon table that associates with this model# will automatically pop up. Then I choose the pricing type so It works basically the same as form #3. I want that there is a button "Add another item" and again a field model# pops up and do the same thing. this is a bulk promotion form so it is faster and easier when I want to run a promotion for multiple items at the same time instead of going to each item. The most important is that I want each of these promotions will work exactly as when I add a promotion individually. 

Is there a way to get this done? 

Thanks in advance!


Account and realm management
APIs and custom code
NotificationsEmail
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    It sounds like you are asking for a way to more quickly add children to a parent record.  I do have a Quick Select method I can implement for you.  Contact me directly if you would like a quick demo and then it usually takes under 90 minutes to get it working on a Go To Meeting.

    The technique is 100% native with no scripting, but it is too complicated to explain to you on his forum unless you already have a good familiarity with URL formula buttons.

    QuickBaseCoach.com
    • SalesInfo's avatar
      SalesInfo
      Qrew Assistant Captain
      7 years ago
      QuickBaseCoach App Dev./Training,

      I've been interacting with Quickbase for awhile so I kinda know what to do by looking at the steps people provided. I have tried multiple ways to set this up but it is still not working as what I want. Can you please guide me on how to get this done?

      thank you!
    • SalesInfo's avatar
      SalesInfo
      Qrew Assistant Captain
      7 years ago
      QuickBaseCoach App Dev./Training,

      I have read through your solution in the linked but have not started on my app yet. 

      Do I necessary have to create a new table "Focus Product"? 

      Can you please make any adjustment on your below solution based on my table so I can have a better understanding?

      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.  


      Thank you!