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

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.

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.

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}});

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?

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.

That's cool. Thanks!

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.

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.

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.

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