Learn more
 

In this article

Related articles

Displaying Data from Related Tables Using Angular with the API Server


Angular is an updated framework for dynamic Web apps, built upon and expanding principles of Angular JS. The CData API Server lets you generate a REST APIs for 80+ data sources, including both on-premises and cloud-based databases. This article will walk through setting up the CData API Server to create an OData-based REST API for QuickBooks Online data and creating a simple single-page application (SPA) that has live access to QuickBooks Online data. The SPA will dynamically build and populate an HTML tables based on related QuickBooks Online tables (i.e.: Invoices and Invoice Line Items). While the article steps through most of the code, you can download the sample Angular project to see the full source code and test the functionality for yourself.

Setting Up the API Server

If you have not already done so, you will need to download the CData API Server. Once you have installed the API Server, you will need to run the application, configure the application to connect to your data (the instructions in this article are for the included sample database), and configure the application to create a REST API for any tables you wish to access in your SPA.

Enable CORS

If the Angular Web app and API Server are on different domains, then Angular will generate cross-domain requests. This means that CORS (cross-origin resource sharing) must be enabled on any servers queried by Angular Web apps. We can enable CORS for the API Server by navigating to the Server tab in of the SETTINGS page of the API Server. You will need to adjust the following settings:

  • Click the checkbox to "Enable cross-origin resource sharing (CORS)".
  • Either click the checkbox to "Allow all domains without '*'" or specify the domain(s) that are allowed to connect in Access-Control-Allow-Origin.
  • Set Access-Control-Allow-Methods to "GET,PUT,POST,OPTIONS".
  • Set Access-Control-Allow-Headers to "authorization".
  • Click Save Changes.

Connect to QuickBooks Online

After you deploy, provide authentication values and other connection properties needed to connect to QuickBooks Online by clicking Settings -> Connections and adding a new connection in the API Server administration console.

QuickBooks Online uses the OAuth authentication standard. OAuth requires the authenticating user to log in through the browser. To authenticate using OAuth, you can use the embedded OAuthClientId, OAuthClientSecret, and CallbackURL or you can obtain your own by registering an app with Intuit. You will also need to specify the CompanyId.

See the Getting Started chapter of the help documentation for a guide to using OAuth.

Configure a User

Next, create a user to access your database data through the API Server. You can add and configure users on the Users tab of the SETTINGS page. Since we are only creating a simple SPA for viewing data, we will create a user that has read-only access. Click Add, give the user a name, select GET for the Privileges, and click Save Changes.

As you can see in the screenshots, we already had a user configured with read and write access. For this article, we will access the API Server with the read-only user, using the associated authtoken.

Accessing Tables

Having created a user, we are ready to enable access to the database tables. To enable tables, click the Add Resources button on the Resources tab of the SETTINGS page. Select the data connection you wish to access and click Next. With the connection selected, you can begin enabling resources by clicking on a table name and clicking Next. You will need to add resources one table at a time. In this example, we enabled all of the tables.

Sample URLs for the REST API

Having configured a connection to the database, created a user, and added resources to the API Server, we now have an easily-accessible REST API based on the OData protocol for those resources. Below, you will see a list of tables and the URLs to access them. For information on accessing the tables, you can navigate to the API page for the API Server. For the URLs, you will need the address and port of the API Server. Since we are working with Angular, we will append the @json parameter to the end of URLs that do not return JSON data by default.

Table URL
Entity (table) List http://address:port/api.rsc/
Metadata for table QBO_Invoices http://address:port/api.rsc/QBO_Invoices/$metadata?@json
QBO_Invoices data http://address:port/api.rsc/QBO_Invoices

As with standard OData feeds, if you wish to limit the fields returned, you can add a $select parameter to the query, along with other standard URL parameters, such as $filter, $orderby, $skip, and $top.

Building a Single Page Application

With the API Server setup completed, we are ready to build our SPA. We will walk through the source files for the SPA contained in the .zip file, making note of any relevant sections of code as we go along. Several of the source files are based loosely on the Angular tutorial from angular.io.

src/index.html

This is the home page of our SPA and the source code mainly consists of script elements to import the necessary Angular libraries.

src/main.ts

This TypeScript file bootstraps the App.

src/app/app.module.ts

This TypeScript file creates a class, including definitions of the Components and Services, that imports the necessary modules to create and run our SPA.

src/app/app-routing.module.ts

This TypeScript file defines the routes and paths used to navigate through the content of our SPA.

src/app/app.component.css

This file creates CSS rulesets to modify the h1 and h2 elements in our Web App.

src/app/app.component.ts

This TypeScript file creates a Component for our SPA and defines the template. Though this App is simple, it could easily be expanded to included multiple routings and components.

src/app/dashboard.component.css

This file creates CSS rulesets to modify the table, th, and td elements in our HTML.

src/app/dashboard.component.html

This file defines the layout for the dashboard components of our SPA. The template consists of drop-downs to select a table and the related table, drop-downs to indicate which fields are the foreign keys for the tables, an HTML table to display the parent table data and another HTML table to display the child table data. Different sections are enabled/disabled based on criteria in *ngIf directives and the menus and tables are built dynamically based on the results of calls to the API Server, using the *ngFor directive to loop through the returned data.

All of the calls to the API Server and assignment of values to variables are made in the DashboardComponent and AppService classes.

<div style='float:left' class="table_select">
  <label>Select a Table</label>
  <br>
  <select [(ngModel)]="selectedTable" (change)="tableChanged()">
    <option *ngFor="let sel_table of tableNames" [value]="sel_table">{{sel_table}}</option>
  </select>
  <br>
  <div *ngIf="selectedTable">
    <label>Select the Key for [{{selectedTable}}]</label>
    <br>
    <select [(ngModel)]="tableKey">
      <option *ngFor="let sel_column of tableColumns" [value]="sel_column">{{sel_column}}</option>
    </select>
    <br>
  </div>
</div>
<div class="subtable_select" *ngIf="selectedTable">
  <label>Select a SubTable</label>
  <br>
  <select [(ngModel)]="selectedSubTable" (change)="subTableChanged()">
    <option *ngFor="let sel_table of tableNames" [value]="sel_table">{{sel_table}}</option>
  </select>
  <br>
  <div *ngIf="selectedSubTable">
    <label>Select the Key for [{{selectedSubTable}}]</label>
    <br>
    <select *ngIf="selectedSubTable" [(ngModel)]="subTableKey">
      <option *ngFor="let sel_column of subTableColumns" [value]="sel_column">{{sel_column}}</option>
    </select>
    <br>
  </div>
</div>
<div *ngIf="selectedTable && tableKey && selectedSubTable && subTableKey && tableData?.length > 0" class="data_retrieve">
  <br>
  <h2>Click an Entry from [{{selectedTable}}] to Expand the [{{selectedSubTable}}] Entities</h2>
  <table>
    <tr>
      <th *ngFor="let column of tableColumns">{{ column }}</th>
    </tr>
    <tr style='cursor:pointer' *ngFor="let row of tableData" (click)="rowClicked(row[tableKey])">
      <td *ngFor="let column of tableColumns">{{ row[column] }}</td>
    </tr>
  </table>
</div>
<div *ngIf="selectedSubTable && subTableColumns && subTableData?.length > 0">
  <br>
  <hr>
  <h2>Data from [{{selectedSubTable}}]</h2>
  <table>
    <tr>
      <th *ngFor="let column of subTableColumns">{{ column }}</th>
    </tr>
    <tr *ngFor="let row of subTableData">
      <td align=center *ngFor="let column of subTableColumns">{{ row[column] }}</td>
    </tr>
  </table>
</div>

src/app/app.service.ts

This TypeScript file builds the service for retrieving data from the API Server. In it, we have functions for retrieving the list of tables, retrieving the list of columns for a specific table, and retrieving data from a table. We also have a class that represents the metadata of a table as returned by the API Server.

API_Table

The metadata returned by the API Server for a table includes the table's name, kind, and URL. We only use the name field, but pass the entire object in the event that we need the other information if we decide to build upon our SPA.

export class API_Table {
  name: string;
  kind: string;
  url: string;
}

constructor()

In the constructor, we create a private instance of the Http class and set the Authorization HTTP header based on the user/authtoken credentials for the user we created earlier. We then include this header in our HTTP requests. 

constructor(private http: Http) {
  this.headers.append('Authorization', 'Basic ' + btoa(this.userName+":"+this.authToken));
}

getTables()

This function returns a list of the tables. The list is retrieved from the API Server by making an HTTP GET request, including the Authorization header, to the base URL for the API Server: http://localhost:8153/api.rsc

getTables(): Promise&lt;API_Table[]&gt; {
  return this.http.get(this.baseUrl, {headers: this.headers})
    .toPromise()
    .then(response => response.json().value )
    .catch(this.handleError);
}

getColumns()

This function returns a list of columns for the table specified by tableName. Since the $metadata endpoint returns XML formatted data by default, we pass the @json parameter in the URL to ensure that we get JSON data back from the API Server. Once we have the JSON data, we can drill down to retrieve the list of column names.

getColumns(tableName: string): Promise&lt;string[]&gt; {
  return this.http.get(`${this.baseUrl}/${tableName}/$metadata?@json`, {headers: this.headers})
    .toPromise()
    .then(response => response = response.json().items[0]["odata:cname"] )
    .catch(this.handleError);
}

getTableDataByColumns(tableName:string, columnList: string)

This function returns the rows of data for the specified table and columns. We pass the tableName in the URL and then pass the list of columns (a comma-separated string) as the value of the $select URL parameter. If there are no specific columns requested, we send do not use the $select URL parameter and request all of the columns.

getTableDataByColumns(tableName:string, columnList: string): Promise<Object[]> {
  if (columnList) {
    return this.http.get(`${this.baseUrl}/${tableName}/?$select=${columnList}`, {headers: this.headers})
      .toPromise()
      .then(response => response = response.json().value )
      .catch(this.handleError);
  } else {
    return this.http.get(`${this.baseUrl}/${tableName}/`, {headers: this.headers})
      .toPromise()
      .then(response => response = response.json().value )
      .catch(this.handleError);
  }
}

getAllTableDataById(tableName:string, idColumn:string, idValue:string)

This function returns the rows of data for the specified table based on the specified ID column and value. We pass the tableName in the URL and then use the ID column and value to request data related to a specific entry in the main table.

getAllTableDataById(tableName:string, idColumn:string, idValue:string): Promise<Object[]> {
return this.http.get(`${this.baseUrl}/${tableName}(${idColumn}='${idValue}')`, {headers: this.headers})
    .toPromise()
    .then(response => response = JSON.parse('[' + response['_body'] + ']'))
    .catch(this.handleError);
}

src/app/dashboard.component.ts

In this TypeScript file, we have defined the functions that react to the events in the SPA; within these functions, we call the functions from the AppService and use the results to populate the various elements of the SPA. These functions are fairly straightforward, assigning values to the different variables as necessary.

ngOnInit()

In this function, we call the getTables function from our AppService. Since getTables returns the raw data objects from our API Server table query, we need to push only the name field from each result into the array of available tables and not push the entire object.

ngOnInit(): void {
    this.appService
      .getTables()
      .then( tables => { 
        for (let tableObj of tables) { 
          this.tableNames.push( tableObj.name ) 
        }
      });
  }

tableChanged()

This function is called whenever the user selects a table from the drop-down menu in the SPA. The function makes a call to the API Server to retrieve the list of columns for the given table, which populates another drop-down menu. The function also retrieves the data for the selected table, which is used to populate an HTML table.

tableChanged(): void {    
    this.appService
      .getColumns(this.selectedTable)
      .then( columns => this.tableColumns = columns.sort() );
    this.appService
      .getTableDataByColumns(this.selectedTable, this.tableColumns)
      .then( data => this.tableData = data );
  }

subTableChanged()

This function is called whenever the user selects a related table from the drop-down menu. The function makes a call to the API Server to retrieve the list of columns for the given table, which populates another drop-down menu.

subTableChanged(): void {    
    this.appService
      .getColumns(this.selectedSubTable)
      .then( columns => this.subTableColumns = columns.sort() );    
  }

rowClicked(keyValue: string)

This function is called whenever a row of data from the main table is clicked. It captures the ID value for the row (based on the selected column for the main table) and makes a call to the API Server to retrieve the data from the related table based on the select ID. The resulting data is then used to populate the HTML table.

rowClicked(keyValue: string): void {
    columnList = this.selectedColumns.join(',');
    this.appService
      .getTableData( this.selectedTable, columnList )
      .then( data => this.tableData = data );
  }

Running the Single Page Application

With our connection to data configured and the source files for the SPA reviewed, we are now ready to run the Single Page Application. You will need to have node.js and npm installed on your machine in order to run the SPA. Included in the sample download is a pre-configured package.json file. You can install the needed modules by running npm install from the command line at the root directory for the SPA. To start the SPA, simply run npm start in the same directory.

When the SPA launches, you will see the title and a drop down menu to select a table. The list of tables is retrieved from the API Server and includes all of the tables you added as resources when configuring the API Server.

With a table selected, the drop-down for columns appears, allowing you to select the key column which relates the table and sub table.

With the main table and key column selected, you can select the related sub table.

With a sub table selected, the drop-down for columns appears, allowing you to select the key column which relates the table and sub table.

Once the tables and columns are selected, the data from the main table will be displayed. You can click on a row in the HTML table to retrieve the related line items from the sub table that correspond with the entry you clicked.

Free Trial & More Information

Now that you have seen a basic example of connecting to your database data in dynamic Web pages, visit our API Server page to read more information about the API Server and download the API Server. Start building dynamic Web pages using live data from on-premises and cloud-base databases, applications, and services, like QuickBooks Online! As always, our world-class Support Team is ready to answer any questions you may have.