Discussions

 View Only
Expand all | Collapse all

How to create a view or maybe report combine the items from 2 tables?

  • 1.  How to create a view or maybe report combine the items from 2 tables?

    Posted 06-09-2014 20:20

    Hi All,

    I have 2 tables that have some of the same fields:

    - Sales (invoice #, customer name, grand total and other fields)

    - Services (invoice #, customer name, grand total and other fields)

    I want to create a table report that list both Sales and Services items that has the same fields... basically an invoice listing. The 2 tables are not related other than sharing the same Customer table.

    Is there a way to display that report? Thanks!!



  • 2.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 06-09-2014 21:26
    A better idea will be to find a way to combine those two tables into one table and have a field which identifies the invoice as a sale or a service as you cannot combine records from two tables on one report.


  • 3.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 06-10-2014 01:30
    Yes. That was considered but Sales and Services are very different. Many different fields. They start with services first then add sales. I need a way to create new table by making a VIEW or do a SQL join.


  • 4.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 06-10-2014 05:22
    In SQL you would use a UNION to combine two result sets.

    You can easily accomplish this in QuickBase using script. In the example below I perform two different queries on Tutorials table of the QuickBase Support Center application and join the results into one data representation:


    Pastie Database

    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=288


    Notes:

    (1) the fact that I made two queries on the same table is immaterial. You could just as easily have made two queries of two different tables so long as they returned compatible field structures so they can be joined together.

    (2) Beyond joining the two queries together you probably need to sort or group the joined results prior to rendering a HTML table of the results. Underscore's _.groupBy() and _.sortBy() methods can manipulate the joined data to suit your needs.

    (3) You can use Underscore's _.template () method or Mustache's Mustache.render() method to merge the data with the report template to generate a HTML report. Both Underscore and Mustache are included within QuickBase's pages.

    (4) QuickBase's API_GenResultTable when called with the parameter &jsa=1 returns a JavaScript array named qdb_data. My code merely concatenates each copy of the response qdb_data into the variable data as each query returns a response.

    (5) Use an apptoken in your version of the code (the Support Center Application does not need one).

    var apptoken = "your apptoken";

    $.ajaxSetup({data: {apptoken: apptoken}});



  • 5.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 09-25-2014 12:54
    Hello dandiebolt,

    I've incorporated your "concatenate data" script to successfully retrieve data from three separate child tables and have it combined in one data set.  I can see that the final data set does contain a 2 x n array of data.  My biggest hurdle now is writing the syntax to display this data as an HTML table.  I've searched high and low on how ot do this and need some help.  Would you be able to enlighten me on how to 'convert' the javascript array into an HTML table?


  • 6.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 09-25-2014 15:50
    I think you mean n x 2 array of data. You would use Mustache or another templating engine to convert the data array / object into HTML. This code takes a small set of data and renders it to HTML:

    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=342
    It would be more common to start from an object rather than an array of data. The Mustache template is easier to write when working with object properties rather than array indices.


  • 7.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 09-25-2014 17:23
    That's cool. Thanks!


  • 8.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 09-26-2014 06:04
    Thanks dandiebolt.  Your insights and suggestions are top shelf.

    In this case, to display the javascript array, I actually implemented an idea from the discussion on this page:  http://stackoverflow.com/questions/12178792/how-to-display-array-values-inside-table-tag

    I was intrigued by the mustache.render solution but my ideal solution was to avoid another link to a code site.  My app is running through a firewall with plenty of group policies to step through and most users are on IE11.  It was a headache getting jquery as a trusted site across the board and the idea of adding mustache was not appealing.

    Thanks for everything.


  • 9.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 09-26-2014 10:21
    Those SO answers are not that great in the sense it is encouraging you to use low level DOM manipulation to create a simple table. I think it is important to know JavaScript at that level but I would never do so unless I was creating a library. Libraries like jQuery, Mustache and Underscore allow you to work at a much higher level and all of these libraries are used by QuickBase. I don't understand firewall issue as you can run your code from user defined pages hosted within the QuickBase domain.


  • 10.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 09-27-2014 04:27
    Thanks dandiebolt.  I may pursue this.  I never knew you could save the library as a quickbase page hosted within the quickbase domain.  I just tried that and it worked great.


  • 11.  RE: How to create a view or maybe report combine the items from 2 tables?

    Posted 06-27-2019 15:28
    Hi - I have a similar requirement. I just need to know where to place the code found in: 

    Pastie Database


    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=288