Forum Discussion

CharlotteM's avatar
CharlotteM
Qrew Assistant Captain
8 years ago

Need form with one parent and two children, from one of the children

We have three tables:

Companies (parent)
  -- Companies can be either client companies, or vendor companies
Opportunities (children of Companies)
Vendors (children of Companies) which are.... Companies as well.
  
The Vendors table has two fields that are keys into the Company table - one is the Client company, and one is the Vendor.

So for example, we might have a Company "Acme" with two vendors, "ABC Bank" and "XYZ Bank."    This is three records in the Company table, and two records in the Vendor table.

This all works fine, except that now, I need a form from Opportunities that shows
> information from Opportunities (OK, it's there)
> information from Company (OK, using lookup fields to get it)
> a list of the vendors for the Company that is related (1:1) as the parent to the opportunity.

How can I get this list of vendors?  I have it on the Company form in a tab, but I need it on the Opportunity form.  I've tried several variations of table relationships, but can't find one that works.
App builders
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Hi Charlotte.


    There�s an easy solution here but first I have to tell you a story.


    When you go to McDonald�s even though when you order a hamburger and a drink they will always always always try to sell you the fries, it is in fact legally possible to go into McDonald�s and just order fries.


    Similarly, when you make a relationship, on the left-hand side Quick Base automatically gives you a Report Link field and a button to Add Child.


    But is also completely legal to create a report link field between any two tables, even tables that are not even on the same application, and not have a relationship between. Another words you can just order the fries you don�t have to have the full meal deal relationship


    So go ahead and create a report link field in the Opportunities Table. When you create that field it only wants two things. It wants a value in a field of the record you�re sitting on to match that same value in any field in any application in your whole realm


    So on the left side, I think the field to select is [Related Company]


    On the right side navigate back to your app and the field Record ID on the company table.


    Then I think the last thing to do is to make sure that you are only listing Vendor Companies.


    So make a report of all Vendor companies. Presumably the way to do that is to create a filter for the report where related vendor is greater than zero.


    Then go back to your opportunities form where you have the report link and in form properties you can highlight that report link field and choose a report to use to display those trail records right on the form and you will choose the report all vendors.

    Now, personally whenever I use an embedded report on the form I give it a name such as


    Used on opportunities form


    And then I only show that report to no Roles at all. That is because I want to have a dedicated report for the embedded report on the form and I don�t want anybody else messing around with it.