ULTIMATE DATABASE

You can make a list of data that can be displayed as a google chart using the {GOOGLE_CHART_BTN()} command (GOOGLE_CHART_BTN). Firstly we need a list of items.

So to start with you create a new list, the example will be called Models but it can be called anything, for part 1 we want a title, so select Character, for part two select None and for part 3 select XML. Then in the Other Options select Colour Selector. Click save.

Now go to your System links/XML section and create two XML Fields called Cost and Size, with tag names cost and size respectively, set the data type to Base Currency for the Cost and Integer for the Size and make the Data Entry Method an input box. Click save and link them to your Models list.

Create a few items on the list and give each one a different colour. Now we need to make a custom list template, this is where the main part of the google charts comes in.

For the list template write this code:

<div class="container">
	<div class="row py-1 noprint">
		<!-- List navigation menu -->
		<div class="col-12 list-edit-element">{LIST_NAV(^FP^PT^PP Page ^CP of ^TP <span class="d-none d-lg-inline-block">(Rows ^FN to ^LN of ^TN)</span> ^NP^NT^LP <span class="d-none d-md-inline-block">^[^Q2^Q5^QA^]</span><span class="float-right">^ET ^NI</span> ^RE ^RD)}</div>
	</div>
	
	<!-- Start of the table -->
   <div class="table-responsive">
		<!-- The id is how we reference the table to collect the data from. The data sets correspond with google chart options, width, height and title, the pie hole is for donut charts, this is the measurement of the hole in the center. -->
		<table class="table table-bordered table-sm" id="chart_data" data-chart-width="" data-chart-height="400" data-chart-title="Tests" data-chart-piehole="0.5">
			<thead>
				<tr>
					<th>tests </th>
					<th>size </th>
					<th>cost </th>
				</tr>
			</thead>
			<tbody>
				<tr>
					<!-- here the data-chart-color is how we reference the custom colors for our pie chart. -->
					<td data-chart-color="{OBJ_COLOR}" style="background-color:{OBJ_COLOR};color:{OBJ_COLOR(,#000000,80)}">{OBJ_DATA(0, C1:1)}</td>
					<td>{OBJ_DATA(0, size)}</td>
					<td>{OBJ_DATA(0, cost)}</td>
				</tr>
			</tbody>
		</table>
	</div>
</div>

For my example I'm going to make it so that the table displays on the left and the chart on the right, to do this add this code to the page you want the chart on:

<div class="row">
	<!-- this is the div where the table will be displayed -->
	<div class="col-12 col-xl-6">
		{LIST(#:1464, ,,, #:1471, 0, 20)}
	</div>
	<div class="col-12 col-xl-6">
		<!-- this is the id of the div we will tell the system to use when it draws the chart -->
		<div  id="chart_here">
		</div>
	</div>
</div>

Except of course you will want to change the list command to display your list and list template meaning you will need different id's to mine.

Now its time to add the actual chart buttons, so put in the following lines of code:

	{GOOGLE_CHART_BTN(#chart_data, #chart_here, PieChart)}
	{GOOGLE_CHART_BTN(#chart_data, #chart_here, BarChart)}

Put them underneath the list command in the page html, NOT the list template like so:

<div class="col-12 col-xl-6">
	{LIST(#:1464, ,,, #:1471, 0, 20)}
	{GOOGLE_CHART_BTN(#chart_data, #chart_here, PieChart)}
	{GOOGLE_CHART_BTN(#chart_data, #chart_here, BarChart)}
</div>

Now click save and you should have working pie and bar charts.

But what if you want to display the data for the cost on the pie chart instead of the size?

well we can do that by adding the noChart class.

Add the class to the th tag of the column you want to skip, it will now only display the data from the other column(s). Like so:

<thead>
	<tr>
		<th>tests </th>
		<th class="noChart">size </th>
		<th>cost </th>
	</tr>
</thead>

Now, some more things you should know:

If you don't set custom colors, or set the colors to white (#ffffff) it will set the colors to the google defaults. Which aren't bad if color isn't important for the presentation of your data.

If you don't tell the command what chart you want to use it will default to a bar chart, this is the horizontal version, the vertical version is the column chart (ColumnChart).

If you don't set a pie hole size and use the donut chart (DonutChart) then it will default to 0.3.

If you don't set a custom height it will use the default height, this is quite small and causes issues with the donut chart's default pie hole size.

If you dont want the symbols on the buttons you can add a fourth option to the command that is the button text, for example:

	{GOOGLE_CHARTS_BTN(#chart_data, #chart_here, PieChart, Pie)}

If you want to customise the colors for charts like Bar and Line charts that don't use the item colors you can add a color element to the th tag of each data set, for example:

<thead>
	<tr>
		<th>tests </th>
		<th data-chart-color="#F5B2B2">size </th>
		<th data-chart-color="#47C6B5">cost </th>
	</tr>
</thead>

If you update the data on the table you just need to click the button for the graph again, the data will update without refreshing the page.