16
May
2022
Tutorial

Build a WooCommerce and Wordpress Sales Reporting Dashboard

Building an online store with WooCommerce is incredibly easy. It allows you to upload your products, set up stock and downloadable products, and track orders and customer data.

And it is all free.

It makes sense that a lot of people use it and love it. 

But getting reports out of it is hard sometimes. They have their default reporting tools, but they often don’t show all the information you need. And customizing it is incredibly complex.

A faster approach is to build a reporting dashboard using Appsmith. This allows you to create fully customized reports, and it’s easy to add new elements or edit the current ones.

Therefore, our goal for today is to make the WooCommerce and WordPress analytics even better. We will use Appsmith to read your database and build a sales reporting dashboard.

Along with the dashboard, we will explore many aspects of Appsmith and how to read external databases with it.

These are the things we are going to learn today:

  • Reading external databases
  • Preprocessing your data
  • Building your Appsmith dashboard
  • Working with different time spans in your widget's data
  • Reading WooCommerce data
  • Creating line charts, bar charts, map charts
  • Displaying stats box with our main KPIs
  • Tables and lists with products and customers rankings

Let’s get started!

You can check out the final app here. You can fork it and build your app using your database. 

Before we start building, let’s walk through the main features of the app.

The time selector

This button group allows you to select different timeframes in your queries. It allows you to check partial periods as well as completed periods quickly.

In addition, it is created in a way that you can easily extend it and add custom time selectors if you want (from X date to Y date).

The KPIS

The KPIs show you a quick overview of your primary metrics. You can use the ones we’ve created or create your metrics. 

Overview charts

These charts allow you to quickly see orders for the selected period and how they compare to previous periods. This is the hardest part to implement in coding, but we have some excellent tips to make it much more manageable.

The Map Chart

This map allows you to see your top performing countries, with labels on hover. It is quite an interesting element. It has some technical challenges as well, but it creates a useful visual element for online stores.

Products Ranking

The product ranking allows you to see which are your top-performing products quickly. This is vital information for store owners, and you can get many insights from it.

Customers Ranking

Likewise, the top customers list shows your best customers and how much they are spending. You can use this information to reward them or find other customers with the same profile.

Now let's get to putting the UI together, but for that we need to plan what our app will look like.

Create a Wireframe

Before coding your dashboard, you need a plan. There are many tools, from low-tech and reliable pen and paper to fancy design tools.

Another tool that might work well for you is Appsmith itself. You can use it as a prototyping tool. In this phase, you drag and drop the components you want to use on your page. Then you order them according to what makes the most sense for your decisions.

The wireframe is just like the final design in our demo, but with no data connections.

In addition to getting a feel of how your dashboard looks like, you can check two other points with your wireframe:

  • What kind of interactivity and functions do you need to create
  • What data source and in what format do you need

The first point is solved with some JS functions. The second one is solved with the DB connections.

WordPress and WooCommerce Data Structures

Now that you know what your app looks like, it's time to check your data structures. Since we are going to read data from WordPress directly, you have three options:

  • A direct database connection
  • REST API connections
  • Using a database that mirrors your main DB

The first option is the simplest. That's what we are going to do today. You can add your DB connection to Appsmith, and you are good to go.

The second option might work better depending on the data you want to load. If it is public data (which isn't the case for orders), this can be a quick solution, in particular, if you don't own the data source.

There are options to create custom API endpoints and authenticated requests. But this can get complex quite quickly. So make sure that this is the best option if you want to go for it.

The third option is an exciting one. In it, you can copy your database to another place. This allows you to pre-process your data if you want, reducing the stress on your main server. This might be a good option if you have too much data.

The downside is that you are always working with past data. Since you need to schedule this "mirroring" process, you are usually limited to doing it a couple of times per day.

Look-up Tables

You know the drill if you've worked with WordPress and WooCommerce before.

The data structures in WordPress are pretty hard to read, and WooCommerce makes it all the more complex.

This is where 'lookup tables' feature comes in.

They have been introduced in WooCommerce to solve performance issues. They are pre-processed versions of the most common WooCommerce queries you run, making it much, much faster. So, instead of checking the postmeta table, finding the post, then finding the user, you can look at the customer_lookup table, and it's all there.

There are many tables on your WordPress site, but we are going to use these in our demo:

You might have noticed that we have two additional tables in addition to the lookup tables. Let’s talk about them now.

Auxiliary Tables: Calendar and Times

It is common to plot all dates, even if you have no data in it when it comes to reporting. After all, you don’t want a chart that skips dates, giving you the false impression that there are only good days.

For this reason, we need to calculate all possible dates and times. You can do it with JS functions, but it can get messy. Therefore, it’s much easier to create a calendar table with all possible dates using a SQL function.

This allows you to select all dates and then join your desired data (revenue, for example). The same goes for the hous of the day. Instead of manually calculating them, get the table with times and LEFT JOIN your desired data.

Data Manipulation

Since we are dealing with pre-made tables, which we don’t control, we must double-check if all the data we need is there.

For example, if you have a KPI for sales goals, do you have this stored on your site? If not, you need to find a way to store it.

In our case, all our data is there. It’s just a matter of finding which tables to load where.

There are many approaches to this, but one that works well is to go through each component and describe what kind of data you need to populate it. From there, you can even list the tables involved.

Here is how you could do it for the demo dashboard:

KPIS

Revenue - Sum of total sales from wp_wc_order_stats

Orders - Count of rows from wp_wc_order_stats

Average Ticket - Revenue / Orders

Orders completed chart

Get dates from the calendar or times

Left Join sum of total sales grouped by order date or time from wp_wc_order_stats

Order Status Chart

Get the sum of total sales from wp_wc_order_stats grouped by status

Orders Map

Get sum of total sales from wp_wc_order_stats

Left join country wp_wc_customer_lookup

Group by country

Products ranking

Get the sum of product revenue and count of orders from wp_wc_order_product_lookup grouped by product_id

Customers ranking

Get sum of order total from wp_wc_order_stats

Left join name from wp_wc_customer_lookup 

Group by customer ID

As you can see, everything we need is there. Now it’s time to get our hands dirty.

Connecting the timeframe switchers

Let’s make the first component interactive. Drag and drop a button group to your page if you haven't already.

Next, add all options. You can create regular buttons (the main buttons) using the button type as simple, and you can create the “more” button using the menu type:

The menu type allows you to create sub-items for each menu item.

To mark a button as selected we are going to use the “disabled” property. You can set it as “true”, “false” or as the result of some JS code. This allows you to assign dynamic values to your buttons.

You can create a new JS object, then add this code in it:

  
currentButton: ( slug = "" ) => {   	    	 var ret = false;   	    	 if ( slug == appsmith.store.button ) {   		 ret = true;   	 }   	    	 return ret;    }, 

This code just checks if the “slug” passed is the current button (stored in the appsmith.store.button variable). If it is the current button, it returns true, so the button is disabled. If it isn’t the current button, it returns false.

While you are at it, you can use this code to assign values to this “button” variable:


setButton: ( slug ) => {   	 // possible values   	 // today, (X to date) wtd, mtd, ytd, yesterday, (last X) lw, lm, ly   	 storeValue( 'button', slug );   	    	 get_country_orders.run();   	 get_customers_ranking.run();   	 get_kpis.run();   	 get_linechart.run();   	 get_linechart_past.run();   	 get_orders_by_status.run();   	 get_product_ranking.run();    }

In this case, you could simply  use the storeValue function directly. But since we need to re-run all our queries, it’s better to pass the button action to a function, and in it we can do it all. Here we can save the button value and run each of our queries again, to update data when the value has changed.

Now you just need to edit each of your buttons and add the disabled value you want, along with the “onclick” function to save the button value:

Once you have this, you can play around and click your buttons; they should turn on and off automatically.

You need one more adjustment. When the page loads, there is no value assigned to this button. But we need it in all our actions. You can create a function to run on page load to assign a default value to it:

onLoad: async () => {   	    	 if ( appsmith.store.button == null ) {   		 storeValue('button', 'mtd');   	 }   	    	 return appsmith.store.button;    },

Since this is an async function, you can click on the gear button next to it and enable “Run function on page load”:

The time limit is ready; now we need to use it. In this case, we use this function to generate the “where” part of all our queries. This is quite handy since you need to call this function whenever you have a time limit.

timeframe: ( type = "date_created", date_convert = false ) => {   	    	 var time = appsmith.store.button, ret = [], dateCurrent = new Date(), dateMiddle = new Date(), datePast = new Date(), compare = "", offset = 0;   	    	    	 dateMiddle.setUTCHours(0, 0, 0, 0);   	 datePast.setUTCHours(0, 0, 0, 0);   	    	 //default time = mtd   	 time = ( time == null ) ? "mtd" : time;   	    	    	 // if time is one of the "incomplete" types   	 compare = ["today", "wtd", "mtd", "ytd"];   	    	 if ( compare.includes( time ) ) {   		 //current date (most recent records) is today (tomorrow just to load all items no exceptions)   		 dateCurrent.setDate( dateCurrent.getDate() + 1 );   	 } else {   		 // current date is the begining of the period (start of the day (yesterday), week, month, year)   		 dateCurrent.setUTCHours(0, 0, 0, 0);   		    		 //increase by one all further calculations   		 offset = 1;   		    		 //set day, week, month, year   		 switch (time) {   			 case 'lw':   				 dateCurrent.setDate( dateCurrent.getDate() - dateCurrent.getDay() - 1 );   				 break;   			 case 'lm':   				 dateCurrent.setDate( 0 );   				 break;   			 case 'ly':   				 dateCurrent.setMonth( 0 );   				 dateCurrent.setDate( 0 );   				 break;   		 }   	 }   	    	 if ( time == "today" || time == "yesterday" ) {   		 dateMiddle.setDate( dateMiddle.getDate() - offset );   		 datePast.setDate( datePast.getDate() - 1 - offset );   	 }   	    	 if ( time == "wtd" || time == "lw" ) {   		 dateMiddle.setDate( dateMiddle.getDate() - ( offset ) * 7 - dateMiddle.getDay() - 1 );   		 datePast.setDate( datePast.getDate() - ( 1 + offset ) * 7 - datePast.getDay() - 1  );   	 }   	    	 if ( time == "mtd" || time == "lm" ) {   		 dateMiddle.setDate( 0 );   		 dateMiddle.setMonth( dateMiddle.getMonth() - offset );   		    		 datePast.setMonth( datePast.getMonth() - 1 - offset );   		 datePast.setDate( 0 );   	 }   	    	 if ( time == "ytd" || time == "ly" ) {   		 dateMiddle.setYear( dateMiddle.getFullYear() - offset );   		 dateMiddle.setMonth( 0 );   		 dateMiddle.setDate( 0 );   		    		 datePast.setYear( datePast.getFullYear() - 1 - offset );   		 datePast.setMonth( 0 );   		 datePast.setDate( 0 );   	 }   	    	    	 type = ' `' + type + '` ';   	    	 dateCurrent = ' "' + dateCurrent.toISOString().replace('T', ' ') + '" ';   	 dateMiddle = ' "' + dateMiddle.toISOString().replace('T', ' ') + '" ';   	 datePast = ' "' + datePast.toISOString().replace('T', ' ') + '" ';   	    	 if ( date_convert == true ) {   		 dateCurrent = " DATE(" + dateCurrent + ") ";   		 dateMiddle = " DATE(" + dateMiddle + ") ";   		 datePast = " DATE(" + datePast + ") ";   	 }   	    	 ret.push( type + ' < ' + dateCurrent + ' AND  ' + type + ' >= ' + dateMiddle   );   	 ret.push( type + ' < ' + dateMiddle + ' AND  ' + type + ' >= ' + datePast   );   	    	 return ret;    },

Now you are ready to load dynamic data! Let’s go through each of your components.

Loading data for the KPIs

As discussed earlier, the KPIs load the sum of all sales in the current period. You can do it with this SQL query:

SELECT SUM(total_sales) as "revenue", COUNT(order_id) as "orders" FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}}

Notice how we use the timeframe function with the current period as an argument. 

This is the output for that query:

[  {	"revenue": 14539,	"orders": 200  }]

The revenue returns just a number though. Since we are using currency formatting in many different places, you can use this function in your JS object:

currencyFormat: (n) => {   	 n = ( n || 0 );   	    	 return Intl.NumberFormat('en-US' , {   			 style: "currency",   			 currency: "USD",   			    	 }).format(n);    }

Then, you need to replace the current value in your KPI boxes.

This is the revenue:

{{ utils.currencyFormat( get_kpis.data[0]["revenue"] ) }}This is the orders count:{{ get_kpis.data[0]["orders"] }}And this is the average ticket size:{{utils.currencyFormat((    get_kpis.data[0]["revenue"] / get_kpis.data[0]["orders"])    )}}

That’s it! Your KPIs are ready.

Create Orders Completed and Order Status Charts

Appsmith comes with some preset charts. But if you need custom charts, you can use any fusion chart you want.

You need to select the “custom chart” option and then pass the arguments as defined by fusion charts.

If you need multi-series data in a line format, you can use the msline chart. That’s what we use for the main sales chart.

This is what we use in the custom fusion chart field for that component:

{  "type": "msline",  "dataSource": {	"chart": {    	"caption": "",    	"subCaption": "",    	"xAxisName": "",    	"yAxisName": "",    	"numberPrefix": "$",   	 "labelStep": {{ (get_linechart_past.data.length / 7) }},    	"plotFillAlpha": "100",    	"theme": "fusion",   	 "lineColor": "03B365",   	 "anchorBgColor": "03B365"	},	"categories": [    	{        	"category":   		 {{ get_linechart_past.data }}    	}	],	"dataset": [    	{        	"seriesname": "Previous",   		 "color": "#000",   		 "anchorBgColor": "#000",‍        	"data": {{ get_linechart_past.data }}       	     	},    	{        	"seriesname": "Current",        	"data": {{ get_linechart.data }}    	}	]}}

The central aspect of this chart is that you need a setup, then your categories, and then your data series as arrays. In this case, we use the past data as the labels (since they have a complete series at all times), and we use current and past data in the datasets.

These datasets are created with 2 queries, but these queries just load a JS function. For the current data:

{{utils.queryBuild()[0]}}And for past data:{{utils.queryBuild()[1]}}In this function we make use of the time limits set in our functions and build our queries.queryBuild: () => {   	 // builds queries for the line chart   	 // return [current, past]   	 // utils.timeframe( type = "date_created", date_convert = false  );   	 var time = appsmith.store.button, ret = [], labels_table = "", labels_time_format = "", orders_time_format = "", labels_where = [ " 1 ", " 1 "], orders_where = utils.timeframe( ), query = "";   	    	 //default time = mtd   	 time = ( time == null ) ? "mtd" : time;   	    	 // select if we are plotting data from calendar_table (dates, weeks, months) or time_table (hours)   	 if ( time == "today" || time == "yesterday" ) {   		 labels_table = "time_table";   		    	 } else {   		 labels_table = "calendar_table";   		 labels_where = utils.timeframe( "dt", true  );   	 }   	 labels_table = ' `' + labels_table + '` ';   	    	 // select the column that is the time_format for the labels and orders   	 switch (time) {   			 case 'today':   			 case 'yesterday':   				 labels_time_format = ' value as "time_format", `24h` as "label" ';   				 orders_time_format = ' HOUR(date_created) as "time_format" ';   				 break;   			    			 case 'wtd':   			 case 'lw':   				 labels_time_format = ' `dt` as "time_format", `dayName` as "label"  ';   				 orders_time_format = ' DATE(date_created) as "time_format"  ';   		    				 break;   			    			 case 'mtd':   			 case 'lm':       				 labels_time_format = ' `dt` as "time_format", CONCAT(`d`, " ") as "label"  ';   				 orders_time_format = ' DATE(date_created) as "time_format"  ';   	    				 break;   			    			 case 'ytd':   			 case 'ly':   				 labels_time_format = ' `m` as "time_format", `monthName` as "label"  ';   				 orders_time_format = ' MONTH(date_created) as "time_format"  ';   			    				 break;   	 }   	    	 //build main query   	 query += " SELECT `tm`.`label`, `orders`.`value` FROM ";   	 // calendar select   	 query += "(";   		 query += ' SELECT *, ' + labels_time_format + ' FROM ' + labels_table + ' WHERE ' + labels_where[0] + ' GROUP BY `time_format`  ';   	 query += ") as tm ";   	 // orders select   	 query += " LEFT JOIN (";   		 query += 'SELECT *, ' + orders_time_format + ' , SUM(total_sales) as "value" FROM `wp_wc_order_stats` WHERE ' + orders_where[0] + ' GROUP BY `time_format`';   	 query += ") as orders ";   	 // end query   	 query += " ON `tm`.`time_format` = `orders`.`time_format` ";   	    	 ret.push(query);   	 //build past query   	 query = "";   	 query += " SELECT `tm`.`label`, `orders`.`value` FROM ";   	 // calendar select   	 query += "(";   		 query += ' SELECT *, ' + labels_time_format + ' FROM ' + labels_table + ' WHERE ' + labels_where[1] + ' GROUP BY `time_format`  ';   	 query += ") as tm ";   	 // orders select   	 query += " LEFT JOIN (";   		 query += 'SELECT *, ' + orders_time_format + ' , SUM(total_sales) as "value" FROM `wp_wc_order_stats` WHERE ' + orders_where[1] + ' GROUP BY `time_format`';   	 query += ") as orders ";   	 // end query   	 query += " ON `tm`.`time_format` = `orders`.`time_format` ";   	    	 ret.push(query);   	    	 return ret;    }

The entire function is quite long, but the entire logic can be summarized as follows:

Check if we load data from the calendar (dates) or the timetable

LEFT JOIN data from orders, grouped by the time restriction

Apply the time restriction in both queries

Return 2 queries (an array), one for the current selection and one for the past selection.

The orders by a status chart are created using a columns2d chart. 

You can define your options such as axis names, palette colors, and captions in it.

Then, you need to pass the charts data. This is the query to load orders by status:

SELECT status as "label", COUNT(order_id) as "value" FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}} GROUP BY status

And this is the result:

[  {	"label": "wc-completed",	"value": 174  },  {	"label": "wc-failed",	"value": 12  },  {	"label": "wc-processing",	"value": 14  }]

You can see that the query is crafted with the chart in mind. Therefore, we need to return the label and value comuns, as per the widget.

Sales map chart

The map charts have options that significantly affect how your data is presented. Check it out:

You can pick the map type from a list of values. Some maps expect country IDs (from the fusion charts table), and some expect continents labels. 

We call a function that does this mapping process for us to make things easier.

This is the SQL query to load data for the map charts:

SELECT SUM(`rev`.`revenue`) as "c_revenue", SUM(`rev`.`orders`) as "c_orders", `nm`.`country` as "country" FROM

( SELECT *, SUM(total_sales) as "revenue", count(order_id) as "orders"  FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}} GROUP BY customer_id ORDER BY `revenue` DESC LIMIT 20) as rev
LEFT JOIN
( SELECT * FROM `wp_wc_customer_lookup`  ) as nm

ON `rev`.`customer_id` = `nm`.`customer_id` GROUP BY `nm`.`country`

This might seem complex, but this is what we are doing:

SELECT SUM of all revenue, SUM of all orders count, Country codes FROM

Wp_wc_order_stats (where you get all orders, sum of revenue, orders count)

LEFT JOIN wp_wc_customer_lookup (where you get the country code for your customers/orders)

GROUP BY Country code

Therefore, we get data from Wp_wc_order_stats (revenue) then we load the wp_wc_customer_lookup to know which country that order is from. Next, we group the result by country.

But this query generates a result that isn’t what the fusion charts component expects. Here is an example:

[  {	"c_revenue": 759,	"c_orders": 5,	"country": "CA"  },  {	"c_revenue": 168,	"c_orders": 1,	"country": "GL"  },  {	"c_revenue": 7595,	"c_orders": 103,	"country": "MX"  },  {	"c_revenue": 1488,	"c_orders": 10,	"country": "US"  }]

Therefore, we need to translate this data into their format. We can do it with array mapping. We use these 2 functions for that:

mappedCountries: () => {   	 var countries = get_country_orders.data;   	    	 countries = countries.map(({ c_revenue, country}) => ({ "id": utils.mapCountry(country), "value": c_revenue }));   	    	 return countries;    },    mapCountry: ( code = "" ) => {   	 var ret = "001";   	    	 switch (code) {   		 case 'CA':   			 ret = '005'   			 break;   		 case 'MX':   			 ret = '016'   			 break;   		 case 'GL':   			 ret = '024'   			 break;   		 case 'US':   			 ret = "023";   			 break;   		 default:   			 ret = '001';   	 }   	    	 return ret;   	     },

You can modify these functions to add more options (countries, continents) and manipulate data in other ways (for example, plot order count instead of revenue).

Either way, now, when you call utils.mappedCountries, you get all countries in the exact format you need.

A final touch is just creating chart labels in your desired format. Here is an example:

[  {	"minValue": 1,	"maxValue": 200,	"code": "ECB365"  },  {	"minValue": 100,	"maxValue": 1000,	"code": "#61A4BC"  },  {	"minValue": 1000,	"maxValue": 10000,	"code": "#03B365"  }]

Products Ranking

The product ranking is just a query that loads all orders within the timeframe selected. Then it groups all these orders by product ID, summing the order values and counting the number of orders.

It seems simple enough, but what about the ranking?

You can do it with some JS code, but you can include it in your SQL query if you want.

Here is how you can do it:

SET @rownum = 0;SELECT @rownum:=(@rownum+1) as "rank", `rev`.`revenue`, `rev`.`orders`, `nm`.`title` FROM‍( SELECT *, SUM(product_net_revenue) as "revenue", count(order_id) as "orders" FROM `wp_wc_order_product_lookup` WHERE {{utils.timeframe()[0]}} GROUP BY product_id  ORDER BY `revenue`  DESC ) as revLEFT JOIN( SELECT post_title as "title", ID FROM `wp_posts` ) as nm‍ON `rev`.`product_id` = `nm`.`ID`

In this query, we set a variable (rownum), and for each row, your database adds one to it ( @rownum:=(@rownum+1) ).

The selection itself is similar to what we did before; the only adjustment is that we load the product name from the wp_posts table.

Then you can add this query to your table:

You can adjust the column names, and adjust the revenue to be set as:

{{utils.currencyFormat(currentRow.revenue)}}

This function converts the revenue value to currency.

Customers Ranking

The customers' ranking is very similar to the products ranking. The only difference is that we are using a list this time.

In this case, you can load the customer gravatar if you want (loading the gravatar image for their email). In our demo dashboard, we load placeholder images.

This is the query to load the customers ranking:

SET @rownum = 0;SELECT @rownum:=(@rownum+1) as "rank", `rev`.`revenue`, `rev`.`orders`, `nm`.`first_name` as "name" FROM‍( SELECT *, SUM(total_sales) as "revenue", count(order_id) as "orders"  FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}} GROUP BY customer_id ORDER BY `revenue` DESC LIMIT 20) as revLEFT JOIN( SELECT * FROM `wp_wc_customer_lookup`  ) as nm‍ON `rev`.`customer_id` = `nm`.`customer_id`

And you need to load its data in your list like this:

Similar to the table, you can reference the currentItem in your components to load data. For example, the revenue and orders field is set up like this:

Revenue: {{ utils.currencyFormat(currentItem.revenue) }} <br />Orders: {{ currentItem.orders }}

And that’s it! Your dashboard is ready.


Today we learned how to create a sales dashboard for WooCommerce sites. We discovered how to read data from WordPress and add it to different Appsmith widgets.

By the end of the day, you should be able to connect your WordPress site to Appsmith, creating your widgets. We didn’t cover some aspects of applications (such as security) for educational purposes, but this should be a great starting point.

We hope you’ve enjoyed it, and see you again next time!

Build a WooCommerce and Wordpress Sales Reporting Dashboard

Share this

Building an online store with WooCommerce is incredibly easy. It allows you to upload your products, set up stock and downloadable products, and track orders and customer data.

And it is all free.

It makes sense that a lot of people use it and love it. 

But getting reports out of it is hard sometimes. They have their default reporting tools, but they often don’t show all the information you need. And customizing it is incredibly complex.

A faster approach is to build a reporting dashboard using Appsmith. This allows you to create fully customized reports, and it’s easy to add new elements or edit the current ones.

Therefore, our goal for today is to make the WooCommerce and WordPress analytics even better. We will use Appsmith to read your database and build a sales reporting dashboard.

Along with the dashboard, we will explore many aspects of Appsmith and how to read external databases with it.

These are the things we are going to learn today:

  • Reading external databases
  • Preprocessing your data
  • Building your Appsmith dashboard
  • Working with different time spans in your widget's data
  • Reading WooCommerce data
  • Creating line charts, bar charts, map charts
  • Displaying stats box with our main KPIs
  • Tables and lists with products and customers rankings

Let’s get started!

You can check out the final app here. You can fork it and build your app using your database. 

Before we start building, let’s walk through the main features of the app.

The time selector

This button group allows you to select different timeframes in your queries. It allows you to check partial periods as well as completed periods quickly.

In addition, it is created in a way that you can easily extend it and add custom time selectors if you want (from X date to Y date).

The KPIS

The KPIs show you a quick overview of your primary metrics. You can use the ones we’ve created or create your metrics. 

Overview charts

These charts allow you to quickly see orders for the selected period and how they compare to previous periods. This is the hardest part to implement in coding, but we have some excellent tips to make it much more manageable.

The Map Chart

This map allows you to see your top performing countries, with labels on hover. It is quite an interesting element. It has some technical challenges as well, but it creates a useful visual element for online stores.

Products Ranking

The product ranking allows you to see which are your top-performing products quickly. This is vital information for store owners, and you can get many insights from it.

Customers Ranking

Likewise, the top customers list shows your best customers and how much they are spending. You can use this information to reward them or find other customers with the same profile.

Now let's get to putting the UI together, but for that we need to plan what our app will look like.

Create a Wireframe

Before coding your dashboard, you need a plan. There are many tools, from low-tech and reliable pen and paper to fancy design tools.

Another tool that might work well for you is Appsmith itself. You can use it as a prototyping tool. In this phase, you drag and drop the components you want to use on your page. Then you order them according to what makes the most sense for your decisions.

The wireframe is just like the final design in our demo, but with no data connections.

In addition to getting a feel of how your dashboard looks like, you can check two other points with your wireframe:

  • What kind of interactivity and functions do you need to create
  • What data source and in what format do you need

The first point is solved with some JS functions. The second one is solved with the DB connections.

WordPress and WooCommerce Data Structures

Now that you know what your app looks like, it's time to check your data structures. Since we are going to read data from WordPress directly, you have three options:

  • A direct database connection
  • REST API connections
  • Using a database that mirrors your main DB

The first option is the simplest. That's what we are going to do today. You can add your DB connection to Appsmith, and you are good to go.

The second option might work better depending on the data you want to load. If it is public data (which isn't the case for orders), this can be a quick solution, in particular, if you don't own the data source.

There are options to create custom API endpoints and authenticated requests. But this can get complex quite quickly. So make sure that this is the best option if you want to go for it.

The third option is an exciting one. In it, you can copy your database to another place. This allows you to pre-process your data if you want, reducing the stress on your main server. This might be a good option if you have too much data.

The downside is that you are always working with past data. Since you need to schedule this "mirroring" process, you are usually limited to doing it a couple of times per day.

Look-up Tables

You know the drill if you've worked with WordPress and WooCommerce before.

The data structures in WordPress are pretty hard to read, and WooCommerce makes it all the more complex.

This is where 'lookup tables' feature comes in.

They have been introduced in WooCommerce to solve performance issues. They are pre-processed versions of the most common WooCommerce queries you run, making it much, much faster. So, instead of checking the postmeta table, finding the post, then finding the user, you can look at the customer_lookup table, and it's all there.

There are many tables on your WordPress site, but we are going to use these in our demo:

You might have noticed that we have two additional tables in addition to the lookup tables. Let’s talk about them now.

Auxiliary Tables: Calendar and Times

It is common to plot all dates, even if you have no data in it when it comes to reporting. After all, you don’t want a chart that skips dates, giving you the false impression that there are only good days.

For this reason, we need to calculate all possible dates and times. You can do it with JS functions, but it can get messy. Therefore, it’s much easier to create a calendar table with all possible dates using a SQL function.

This allows you to select all dates and then join your desired data (revenue, for example). The same goes for the hous of the day. Instead of manually calculating them, get the table with times and LEFT JOIN your desired data.

Data Manipulation

Since we are dealing with pre-made tables, which we don’t control, we must double-check if all the data we need is there.

For example, if you have a KPI for sales goals, do you have this stored on your site? If not, you need to find a way to store it.

In our case, all our data is there. It’s just a matter of finding which tables to load where.

There are many approaches to this, but one that works well is to go through each component and describe what kind of data you need to populate it. From there, you can even list the tables involved.

Here is how you could do it for the demo dashboard:

KPIS

Revenue - Sum of total sales from wp_wc_order_stats

Orders - Count of rows from wp_wc_order_stats

Average Ticket - Revenue / Orders

Orders completed chart

Get dates from the calendar or times

Left Join sum of total sales grouped by order date or time from wp_wc_order_stats

Order Status Chart

Get the sum of total sales from wp_wc_order_stats grouped by status

Orders Map

Get sum of total sales from wp_wc_order_stats

Left join country wp_wc_customer_lookup

Group by country

Products ranking

Get the sum of product revenue and count of orders from wp_wc_order_product_lookup grouped by product_id

Customers ranking

Get sum of order total from wp_wc_order_stats

Left join name from wp_wc_customer_lookup 

Group by customer ID

As you can see, everything we need is there. Now it’s time to get our hands dirty.

Connecting the timeframe switchers

Let’s make the first component interactive. Drag and drop a button group to your page if you haven't already.

Next, add all options. You can create regular buttons (the main buttons) using the button type as simple, and you can create the “more” button using the menu type:

The menu type allows you to create sub-items for each menu item.

To mark a button as selected we are going to use the “disabled” property. You can set it as “true”, “false” or as the result of some JS code. This allows you to assign dynamic values to your buttons.

You can create a new JS object, then add this code in it:

  
currentButton: ( slug = "" ) => {   	    	 var ret = false;   	    	 if ( slug == appsmith.store.button ) {   		 ret = true;   	 }   	    	 return ret;    }, 

This code just checks if the “slug” passed is the current button (stored in the appsmith.store.button variable). If it is the current button, it returns true, so the button is disabled. If it isn’t the current button, it returns false.

While you are at it, you can use this code to assign values to this “button” variable:


setButton: ( slug ) => {   	 // possible values   	 // today, (X to date) wtd, mtd, ytd, yesterday, (last X) lw, lm, ly   	 storeValue( 'button', slug );   	    	 get_country_orders.run();   	 get_customers_ranking.run();   	 get_kpis.run();   	 get_linechart.run();   	 get_linechart_past.run();   	 get_orders_by_status.run();   	 get_product_ranking.run();    }

In this case, you could simply  use the storeValue function directly. But since we need to re-run all our queries, it’s better to pass the button action to a function, and in it we can do it all. Here we can save the button value and run each of our queries again, to update data when the value has changed.

Now you just need to edit each of your buttons and add the disabled value you want, along with the “onclick” function to save the button value:

Once you have this, you can play around and click your buttons; they should turn on and off automatically.

You need one more adjustment. When the page loads, there is no value assigned to this button. But we need it in all our actions. You can create a function to run on page load to assign a default value to it:

onLoad: async () => {   	    	 if ( appsmith.store.button == null ) {   		 storeValue('button', 'mtd');   	 }   	    	 return appsmith.store.button;    },

Since this is an async function, you can click on the gear button next to it and enable “Run function on page load”:

The time limit is ready; now we need to use it. In this case, we use this function to generate the “where” part of all our queries. This is quite handy since you need to call this function whenever you have a time limit.

timeframe: ( type = "date_created", date_convert = false ) => {   	    	 var time = appsmith.store.button, ret = [], dateCurrent = new Date(), dateMiddle = new Date(), datePast = new Date(), compare = "", offset = 0;   	    	    	 dateMiddle.setUTCHours(0, 0, 0, 0);   	 datePast.setUTCHours(0, 0, 0, 0);   	    	 //default time = mtd   	 time = ( time == null ) ? "mtd" : time;   	    	    	 // if time is one of the "incomplete" types   	 compare = ["today", "wtd", "mtd", "ytd"];   	    	 if ( compare.includes( time ) ) {   		 //current date (most recent records) is today (tomorrow just to load all items no exceptions)   		 dateCurrent.setDate( dateCurrent.getDate() + 1 );   	 } else {   		 // current date is the begining of the period (start of the day (yesterday), week, month, year)   		 dateCurrent.setUTCHours(0, 0, 0, 0);   		    		 //increase by one all further calculations   		 offset = 1;   		    		 //set day, week, month, year   		 switch (time) {   			 case 'lw':   				 dateCurrent.setDate( dateCurrent.getDate() - dateCurrent.getDay() - 1 );   				 break;   			 case 'lm':   				 dateCurrent.setDate( 0 );   				 break;   			 case 'ly':   				 dateCurrent.setMonth( 0 );   				 dateCurrent.setDate( 0 );   				 break;   		 }   	 }   	    	 if ( time == "today" || time == "yesterday" ) {   		 dateMiddle.setDate( dateMiddle.getDate() - offset );   		 datePast.setDate( datePast.getDate() - 1 - offset );   	 }   	    	 if ( time == "wtd" || time == "lw" ) {   		 dateMiddle.setDate( dateMiddle.getDate() - ( offset ) * 7 - dateMiddle.getDay() - 1 );   		 datePast.setDate( datePast.getDate() - ( 1 + offset ) * 7 - datePast.getDay() - 1  );   	 }   	    	 if ( time == "mtd" || time == "lm" ) {   		 dateMiddle.setDate( 0 );   		 dateMiddle.setMonth( dateMiddle.getMonth() - offset );   		    		 datePast.setMonth( datePast.getMonth() - 1 - offset );   		 datePast.setDate( 0 );   	 }   	    	 if ( time == "ytd" || time == "ly" ) {   		 dateMiddle.setYear( dateMiddle.getFullYear() - offset );   		 dateMiddle.setMonth( 0 );   		 dateMiddle.setDate( 0 );   		    		 datePast.setYear( datePast.getFullYear() - 1 - offset );   		 datePast.setMonth( 0 );   		 datePast.setDate( 0 );   	 }   	    	    	 type = ' `' + type + '` ';   	    	 dateCurrent = ' "' + dateCurrent.toISOString().replace('T', ' ') + '" ';   	 dateMiddle = ' "' + dateMiddle.toISOString().replace('T', ' ') + '" ';   	 datePast = ' "' + datePast.toISOString().replace('T', ' ') + '" ';   	    	 if ( date_convert == true ) {   		 dateCurrent = " DATE(" + dateCurrent + ") ";   		 dateMiddle = " DATE(" + dateMiddle + ") ";   		 datePast = " DATE(" + datePast + ") ";   	 }   	    	 ret.push( type + ' < ' + dateCurrent + ' AND  ' + type + ' >= ' + dateMiddle   );   	 ret.push( type + ' < ' + dateMiddle + ' AND  ' + type + ' >= ' + datePast   );   	    	 return ret;    },

Now you are ready to load dynamic data! Let’s go through each of your components.

Loading data for the KPIs

As discussed earlier, the KPIs load the sum of all sales in the current period. You can do it with this SQL query:

SELECT SUM(total_sales) as "revenue", COUNT(order_id) as "orders" FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}}

Notice how we use the timeframe function with the current period as an argument. 

This is the output for that query:

[  {	"revenue": 14539,	"orders": 200  }]

The revenue returns just a number though. Since we are using currency formatting in many different places, you can use this function in your JS object:

currencyFormat: (n) => {   	 n = ( n || 0 );   	    	 return Intl.NumberFormat('en-US' , {   			 style: "currency",   			 currency: "USD",   			    	 }).format(n);    }

Then, you need to replace the current value in your KPI boxes.

This is the revenue:

{{ utils.currencyFormat( get_kpis.data[0]["revenue"] ) }}This is the orders count:{{ get_kpis.data[0]["orders"] }}And this is the average ticket size:{{utils.currencyFormat((    get_kpis.data[0]["revenue"] / get_kpis.data[0]["orders"])    )}}

That’s it! Your KPIs are ready.

Create Orders Completed and Order Status Charts

Appsmith comes with some preset charts. But if you need custom charts, you can use any fusion chart you want.

You need to select the “custom chart” option and then pass the arguments as defined by fusion charts.

If you need multi-series data in a line format, you can use the msline chart. That’s what we use for the main sales chart.

This is what we use in the custom fusion chart field for that component:

{  "type": "msline",  "dataSource": {	"chart": {    	"caption": "",    	"subCaption": "",    	"xAxisName": "",    	"yAxisName": "",    	"numberPrefix": "$",   	 "labelStep": {{ (get_linechart_past.data.length / 7) }},    	"plotFillAlpha": "100",    	"theme": "fusion",   	 "lineColor": "03B365",   	 "anchorBgColor": "03B365"	},	"categories": [    	{        	"category":   		 {{ get_linechart_past.data }}    	}	],	"dataset": [    	{        	"seriesname": "Previous",   		 "color": "#000",   		 "anchorBgColor": "#000",‍        	"data": {{ get_linechart_past.data }}       	     	},    	{        	"seriesname": "Current",        	"data": {{ get_linechart.data }}    	}	]}}

The central aspect of this chart is that you need a setup, then your categories, and then your data series as arrays. In this case, we use the past data as the labels (since they have a complete series at all times), and we use current and past data in the datasets.

These datasets are created with 2 queries, but these queries just load a JS function. For the current data:

{{utils.queryBuild()[0]}}And for past data:{{utils.queryBuild()[1]}}In this function we make use of the time limits set in our functions and build our queries.queryBuild: () => {   	 // builds queries for the line chart   	 // return [current, past]   	 // utils.timeframe( type = "date_created", date_convert = false  );   	 var time = appsmith.store.button, ret = [], labels_table = "", labels_time_format = "", orders_time_format = "", labels_where = [ " 1 ", " 1 "], orders_where = utils.timeframe( ), query = "";   	    	 //default time = mtd   	 time = ( time == null ) ? "mtd" : time;   	    	 // select if we are plotting data from calendar_table (dates, weeks, months) or time_table (hours)   	 if ( time == "today" || time == "yesterday" ) {   		 labels_table = "time_table";   		    	 } else {   		 labels_table = "calendar_table";   		 labels_where = utils.timeframe( "dt", true  );   	 }   	 labels_table = ' `' + labels_table + '` ';   	    	 // select the column that is the time_format for the labels and orders   	 switch (time) {   			 case 'today':   			 case 'yesterday':   				 labels_time_format = ' value as "time_format", `24h` as "label" ';   				 orders_time_format = ' HOUR(date_created) as "time_format" ';   				 break;   			    			 case 'wtd':   			 case 'lw':   				 labels_time_format = ' `dt` as "time_format", `dayName` as "label"  ';   				 orders_time_format = ' DATE(date_created) as "time_format"  ';   		    				 break;   			    			 case 'mtd':   			 case 'lm':       				 labels_time_format = ' `dt` as "time_format", CONCAT(`d`, " ") as "label"  ';   				 orders_time_format = ' DATE(date_created) as "time_format"  ';   	    				 break;   			    			 case 'ytd':   			 case 'ly':   				 labels_time_format = ' `m` as "time_format", `monthName` as "label"  ';   				 orders_time_format = ' MONTH(date_created) as "time_format"  ';   			    				 break;   	 }   	    	 //build main query   	 query += " SELECT `tm`.`label`, `orders`.`value` FROM ";   	 // calendar select   	 query += "(";   		 query += ' SELECT *, ' + labels_time_format + ' FROM ' + labels_table + ' WHERE ' + labels_where[0] + ' GROUP BY `time_format`  ';   	 query += ") as tm ";   	 // orders select   	 query += " LEFT JOIN (";   		 query += 'SELECT *, ' + orders_time_format + ' , SUM(total_sales) as "value" FROM `wp_wc_order_stats` WHERE ' + orders_where[0] + ' GROUP BY `time_format`';   	 query += ") as orders ";   	 // end query   	 query += " ON `tm`.`time_format` = `orders`.`time_format` ";   	    	 ret.push(query);   	 //build past query   	 query = "";   	 query += " SELECT `tm`.`label`, `orders`.`value` FROM ";   	 // calendar select   	 query += "(";   		 query += ' SELECT *, ' + labels_time_format + ' FROM ' + labels_table + ' WHERE ' + labels_where[1] + ' GROUP BY `time_format`  ';   	 query += ") as tm ";   	 // orders select   	 query += " LEFT JOIN (";   		 query += 'SELECT *, ' + orders_time_format + ' , SUM(total_sales) as "value" FROM `wp_wc_order_stats` WHERE ' + orders_where[1] + ' GROUP BY `time_format`';   	 query += ") as orders ";   	 // end query   	 query += " ON `tm`.`time_format` = `orders`.`time_format` ";   	    	 ret.push(query);   	    	 return ret;    }

The entire function is quite long, but the entire logic can be summarized as follows:

Check if we load data from the calendar (dates) or the timetable

LEFT JOIN data from orders, grouped by the time restriction

Apply the time restriction in both queries

Return 2 queries (an array), one for the current selection and one for the past selection.

The orders by a status chart are created using a columns2d chart. 

You can define your options such as axis names, palette colors, and captions in it.

Then, you need to pass the charts data. This is the query to load orders by status:

SELECT status as "label", COUNT(order_id) as "value" FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}} GROUP BY status

And this is the result:

[  {	"label": "wc-completed",	"value": 174  },  {	"label": "wc-failed",	"value": 12  },  {	"label": "wc-processing",	"value": 14  }]

You can see that the query is crafted with the chart in mind. Therefore, we need to return the label and value comuns, as per the widget.

Sales map chart

The map charts have options that significantly affect how your data is presented. Check it out:

You can pick the map type from a list of values. Some maps expect country IDs (from the fusion charts table), and some expect continents labels. 

We call a function that does this mapping process for us to make things easier.

This is the SQL query to load data for the map charts:

SELECT SUM(`rev`.`revenue`) as "c_revenue", SUM(`rev`.`orders`) as "c_orders", `nm`.`country` as "country" FROM

( SELECT *, SUM(total_sales) as "revenue", count(order_id) as "orders"  FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}} GROUP BY customer_id ORDER BY `revenue` DESC LIMIT 20) as rev
LEFT JOIN
( SELECT * FROM `wp_wc_customer_lookup`  ) as nm

ON `rev`.`customer_id` = `nm`.`customer_id` GROUP BY `nm`.`country`

This might seem complex, but this is what we are doing:

SELECT SUM of all revenue, SUM of all orders count, Country codes FROM

Wp_wc_order_stats (where you get all orders, sum of revenue, orders count)

LEFT JOIN wp_wc_customer_lookup (where you get the country code for your customers/orders)

GROUP BY Country code

Therefore, we get data from Wp_wc_order_stats (revenue) then we load the wp_wc_customer_lookup to know which country that order is from. Next, we group the result by country.

But this query generates a result that isn’t what the fusion charts component expects. Here is an example:

[  {	"c_revenue": 759,	"c_orders": 5,	"country": "CA"  },  {	"c_revenue": 168,	"c_orders": 1,	"country": "GL"  },  {	"c_revenue": 7595,	"c_orders": 103,	"country": "MX"  },  {	"c_revenue": 1488,	"c_orders": 10,	"country": "US"  }]

Therefore, we need to translate this data into their format. We can do it with array mapping. We use these 2 functions for that:

mappedCountries: () => {   	 var countries = get_country_orders.data;   	    	 countries = countries.map(({ c_revenue, country}) => ({ "id": utils.mapCountry(country), "value": c_revenue }));   	    	 return countries;    },    mapCountry: ( code = "" ) => {   	 var ret = "001";   	    	 switch (code) {   		 case 'CA':   			 ret = '005'   			 break;   		 case 'MX':   			 ret = '016'   			 break;   		 case 'GL':   			 ret = '024'   			 break;   		 case 'US':   			 ret = "023";   			 break;   		 default:   			 ret = '001';   	 }   	    	 return ret;   	     },

You can modify these functions to add more options (countries, continents) and manipulate data in other ways (for example, plot order count instead of revenue).

Either way, now, when you call utils.mappedCountries, you get all countries in the exact format you need.

A final touch is just creating chart labels in your desired format. Here is an example:

[  {	"minValue": 1,	"maxValue": 200,	"code": "ECB365"  },  {	"minValue": 100,	"maxValue": 1000,	"code": "#61A4BC"  },  {	"minValue": 1000,	"maxValue": 10000,	"code": "#03B365"  }]

Products Ranking

The product ranking is just a query that loads all orders within the timeframe selected. Then it groups all these orders by product ID, summing the order values and counting the number of orders.

It seems simple enough, but what about the ranking?

You can do it with some JS code, but you can include it in your SQL query if you want.

Here is how you can do it:

SET @rownum = 0;SELECT @rownum:=(@rownum+1) as "rank", `rev`.`revenue`, `rev`.`orders`, `nm`.`title` FROM‍( SELECT *, SUM(product_net_revenue) as "revenue", count(order_id) as "orders" FROM `wp_wc_order_product_lookup` WHERE {{utils.timeframe()[0]}} GROUP BY product_id  ORDER BY `revenue`  DESC ) as revLEFT JOIN( SELECT post_title as "title", ID FROM `wp_posts` ) as nm‍ON `rev`.`product_id` = `nm`.`ID`

In this query, we set a variable (rownum), and for each row, your database adds one to it ( @rownum:=(@rownum+1) ).

The selection itself is similar to what we did before; the only adjustment is that we load the product name from the wp_posts table.

Then you can add this query to your table:

You can adjust the column names, and adjust the revenue to be set as:

{{utils.currencyFormat(currentRow.revenue)}}

This function converts the revenue value to currency.

Customers Ranking

The customers' ranking is very similar to the products ranking. The only difference is that we are using a list this time.

In this case, you can load the customer gravatar if you want (loading the gravatar image for their email). In our demo dashboard, we load placeholder images.

This is the query to load the customers ranking:

SET @rownum = 0;SELECT @rownum:=(@rownum+1) as "rank", `rev`.`revenue`, `rev`.`orders`, `nm`.`first_name` as "name" FROM‍( SELECT *, SUM(total_sales) as "revenue", count(order_id) as "orders"  FROM `wp_wc_order_stats` WHERE {{utils.timeframe()[0]}} GROUP BY customer_id ORDER BY `revenue` DESC LIMIT 20) as revLEFT JOIN( SELECT * FROM `wp_wc_customer_lookup`  ) as nm‍ON `rev`.`customer_id` = `nm`.`customer_id`

And you need to load its data in your list like this:

Similar to the table, you can reference the currentItem in your components to load data. For example, the revenue and orders field is set up like this:

Revenue: {{ utils.currencyFormat(currentItem.revenue) }} <br />Orders: {{ currentItem.orders }}

And that’s it! Your dashboard is ready.


Today we learned how to create a sales dashboard for WooCommerce sites. We discovered how to read data from WordPress and add it to different Appsmith widgets.

By the end of the day, you should be able to connect your WordPress site to Appsmith, creating your widgets. We didn’t cover some aspects of applications (such as security) for educational purposes, but this should be a great starting point.

We hope you’ve enjoyed it, and see you again next time!

What’s a Rich Text element?

The rich text element allows you to create and format headings, paragraphs, blockquotes, images, and video all in one place instead of having to add and format them individually. Just double-click and easily create content.

  • xvcmbmvkmnkmbknmbkmlnj
  • A rich text element can be used with static or dynamic content. For static content, just drop it

A rich text element can be used with static or dynamic content. For static content, just drop it into any page and begin editing. For dynamic content, add a rich text field to any collection and then connect a rich text element to that field in the settings panel. Voila!

  1. A rich text element can be used with static or dynamic content. For static content, just drop it into any page and begin editing. For dynamic content, add a rich text field to any collection and then connect a rich text element to that field in the settings panel. Voila!

How to customize formatting for each rich text

Headings, paragraphs, blockquotes, figures, images, and figure captions can all be styled after a class is added to the rich text element using the "When inside of" nested selector system.

ksnopirirfnb [aorewmb[oiewsn b[opebr
  1. then connect a rich text

dfbstjsrykmsry

Square
Try Appsmith
Debugging your apps in Appsmith with the Appsmith Debugger, part 2
27
September
2022
Product

Debugging your apps in Appsmith with the Appsmith Debugger, part 2

Debugging your apps in Appsmith with the Appsmith Debugger, part 2
Ayush Pahwa
0
 minutes ↗
#
product
#
errors
#
troubleshooting
#
debugger
Product
Meet the sidekicks, Logs and Inspect Entity

The first part of this teardown helped you see how the Error pane can save you hours in debugging and build better internal apps. In this one, let’s meet two seemingly innocuous features that can give you debugging super-powers when used right.

Logs

The Logs pane shows you everything logged by Appsmith and, like Errors, in lockstep with the sequence of code execution in your build. Borrowing from the experience of showing logs in general—in the browser console, from a shell, or on your favorite IDE—the Logs pane has four views for specific debugging use cases.

Post_5.jpg (1920×1080)

All Logs

This view shows you all logs timestamped by when we saw them in your Appsmith session. Updated a widget’s property? Wrote a new action to your GraphQL datasource? Ran a JS Object to concat two queries? It all gets logged, including the errors you see in the Errors pane, in a separate view called Error Logs. You will see how that can be useful in a GIF, pun intended.

The All Logs view can be a little overwhelming, though, and a bit of work when you have been at your build for a while. For easier tracking of relevant logs, use one of the three options below.

Post_6.gif (1440×810)

Errors Logs

Everything you learned about the Errors pane applies to this view, too, but there’s more to this view. Here's a likely scenario to show that.

State #1

You have a button to reload a table, presumably to refresh the data from your datasource.

Condition #1

You use the Button property, onClick, which runs the query to fetch the latest data into the table.

Scenario #1

Your query fails.

- On just the Error pane

  • You see just the error for the failing query. Although helpful, it doesn’t offer context for the before and after of the error.

- On the Error Logs pane under Logs

  1. You see logs for the Button click and the executed onClick event .
  2. Because the onClick property is binded to queries and JS Objects, you see the ones that are successfully executed and those that fail.
Error_Logs__Appsmith.gif (1440×810)

The triaging in our example above is especially useful when you have nested queries, several dependent bindings, and a more complex workflow overall.

Console Logs

console.log_in_the_Editor__Appsmith.jpg (1920×1080)

Just introduced in the Debugger, console methods in Appsmith help you see statements for just JS Objects and JavaScript bindings so much better than in the browser sub-window.

Set points in your code that you want to log statements at, view tabular data, or see groups for repeated errors.

System Logs

Post_7.jpg (1920×1080)

Automatically tracking all your interactions with Appsmith during build, System Logs show a helpful trail of activity by descending order of timestamp, especially useful when you want to go back in time or pivot from a point of failure to everything that led to it.

They show up for different situations and interactions for the type of entity you are working with.

With widgets, you see a log when you

  • Drag-and-drop a new widget on the canvas.
  • Update the widget’s properties
    Updating a property also updates all its dependent properties which then show up in System Logs.
Dependent_properties_updates_in_system_logs__Appsmith.gif (1280×720)
For example, when you update the tableData property, you also see its dependent properties like selectedRowIndex, filters, triggeredRowIndex, and so on.
  • Trigger events with an end-user action.
Trigger_events_with_an_end-user_action__Appsmith.gif (1280×720)
For example, when you are using an end-user action to store a value with storeValue or when you want a click-action to trigger an operation like an update or delete and are using onClick, you see them show up in System Logs.
  • Delete a widget from the canvas

With actions, you see them when you

  • Create a new datasource or a query
  • Update query properties like queryName, queryBody, queryConfiguration, and queryProperties.
  • Execute a query
Execute_a_query.gif (1440×810)
This can be either from query pane, running a plain REST API query, a JS Object, or via a widget’s bindings.
  • Delete a query

With JS Objects, you’ll see system logs when you

  • Create and update code inside JS Objects
  • Execute JS Objects
Execute_JS_Objects.gif (1440×810)

Just like errors, system logs are native to entities and have four parts to them.

Parts_of_a_system_log_line__Appsmith.jpg (1920×1080)

The timestamp

Logged as your entities are created, updated and deleted, these little breadcrumbs help you track back from when the error occurred to when it was last A-Okay.

Timestamped_logs_in_System_Logs.gif (1440×810)

The message

Useful during build, the message of the log answers two questions— what were you doing with an entity—creating it, updating it, deleting it—and what happened with your action—success or failure.

  • With widgets, outside of CRUD information, you also see event-specific info like onClick and showAlert linked to those widgets.
  • Queries and JS Objects are straightforward with start and end points that indicate if they were updated, ran, and failed.

The source

Like errors, a system log has two parts to its source—the entity’s name.the type of entity, e.g., SELECT1.TABLE1.WIDGET.

Redirect_from_an_Inspect_Entity_sub-window.gif (1440×810)
👌🏽 Appsmith Experience plug

Clicking the source from the logs takes you to the associated entity anywhere in Appsmith, be it a widget, a query, or a JS Object. Noice!

The response

This doesn’t always show, but when it does, it can be useful confirmation of a binding working, a query running successfully, or a JS Object executing completely.

  • For widgets, you see which properties are updated when you are configuring them and how.
    Say you’re updating the text widget’s background property and you don’t see it change on the canvas. Track the log to the response for a quick confirmation of that and troubleshoot the canvas next.
  • For queries, you’ll see two different logs—the start of a query run and the status of its execution.
    The first type of log will show you configuration details of the query—helpful to verify if the config matches the request.        

{
    "timeoutInMillisecond":10000
    "paginationType":"NONE"
    "encodeParamsToggle":true
    "body":"SELECT * FROM public."users" LIMIT 10;"
    "pluginSpecifiedTemplates":[
        0:{
            "value":true
        }
    ]
}

  • The second type will throw an error if the run fails. When the query runs successfully, it shows all the parameters that the query ran with and the time taken for the response.

{
	"response" : [...],
	"request" : {
		"actionId" "6321c1193668£71e£7caala2"
		"requestedAt" : 1663912830.640344
		"requestParams": {...}
}

  • With JS Objects, you see the response from the function as a JSON after an object is successfully run. This shows you how Appsmith handles the function while evaluating and running it and can be useful for spotting conflicts, undefined references, or troublesome variables.

Inspect Entity

Borrowing from a modern browser’s Inspect Element feature, Inspect Entity lets you see incoming and outgoing entities for any widget. These entities can be queries, JS Objects, or even other widgets.

Group_8480.png (1920×1080)
  • Incoming entities are those that fetch data from the datasource into the widget.
    For example, if the data on a table is populated by a Postgres query, you’ll see the query name under the Incoming entities column.
  • Outgoing entities are those that can specify the data that’s to be sent to the datasource in a typical CUD operation and then send it to your datasource.
    Say, a text widget is binded to a table's selectedRow property, you will see the text widget’s name under the Outgoing entities column.

The Inspect Entity pane lets you see dependencies for all your widgets on the canvas, especially useful if you have a medium-complex app with several widgets working off of each other. For example, when you have a parent widget or query that controls bindings on other dependent widgets---call them children widgets---, Inspect Entity can show you all those children when you click the parent and quickly take you to any one of them directly.

In combination with Errors, Logs and Inspect Entity round out the Debugger for several scenarios during build and save you hours in building an app end-users love. Try out the Debugger and let us know how you like it, what it's missing, and what we can improve. Our Discord is the best place for that feedback.

The Appsmith Debugger now supports Console methods
23
September
2022
Announcement

The Appsmith Debugger now supports Console methods

The Appsmith Debugger now supports Console methods
Rishabh Rathod
0
 minutes ↗
#
debugger
#
troubleshooting
#
console-methods
Announcement

For a while now, you have used and loved the Appsmith Debugger, nearly complete with a Error pane, system and error logs, and an entity inspector. We say nearly complete because it was missing one of the most popular debugging tools in a dev’s toolkit—console methods.

We are happy to announce the availability of console methods for both cloud users and self-hosters on v1.8.0.

“But, what is the Appsmith Debugger?”

Image_1.png (1920×1080)

Think of the Appsmith Debugger as a set of Chrome DevTools—like for Appsmith. It lives on the familiar 🐞 everywhere in Appsmith and

  • shows helpful error messages for bindings, queries, and variables
  • lets you inspect entity relationships
  • filters system and user logs

All of this is helpful when debugging unexpected API responses or app viewer experiences. Should you care to learn more, this post breaks down the debugger by each one of its features.

“Okay, and console methods are…”

Just one of the most popular ways of print debugging in modern browsers, console methods, exposed by the console API, are a set of functions that help you log the values of variables at set points in your code, messages, or even tabular data so you can investigate them in your browser’s debugging console.

Before today, you could use all supported browser console methods, but only in the browser’s dev tools sub-window. To any developer with their hands dirty with front-end code, the browser debugging subwindow is a necessary evil—a thousand lines of errors, messages, values, and steps that you would have to sift through. We are not going to say, “Looking for the literal needle in the haystack”, but you know you are thinking it.

“And the Appsmith Debugger has a console now?”

Yes! 🥳

So, instead of something like,

you now see,

Image_3.png (1920×1080)

Sweet? This gets sweeter.

Supported methods

  • log

Almost synonymous with console, the .log() method is one of the most popular ways to log a message or the values of variables defined in your Javascript.

It can also be used to show helpful messages or comments, say, the entry and exit points of functions.

Example


getUUID: () => {
		console.log("entry - getUUID function");
		let prefix;
		
		let d = new Date().getTime();
		console.log("new date created -", d);
		d += (parseInt(Math.random() * 100)).toString();
		console.log(d, "random number generated by getUUID")
		if (undefined === prefix) {
			prefix = 'uid-';
		}
		d = prefix + d;
		console.log("UUID created -", d);
		console.log("exit - getUUID function")
		return d;
	}

Result

Image_4.png (1920×1080)
  • error

the .error() method logs an error message to the Appsmith console, be it a a string like, “This is an error message” or the value of a function.

Say you've written a function and you suspect it’s returning an error., but you don’t know what kind. For unknown unknowns like this, `error` comes handy.

Example


checkTextWidget: () => {
		const element = Text1.text;
		if (element == "") {
			console.error("There is an error. The Text property is empty ");
		}
		return element;
	}

Result

Image_5.png (1920×1080)
  • warn

Jus as .error() aids error investigations, .warn() shows, well, warnings for known knowns. Some situations this can come in handy are,- When the evaluated value of binded data on a widget is not using the same datatype as the expected value- When widgets continue to use deprecated queries or functions- When the timezone used in a datetime functions doesn't match the browser’s

Example


selectDefaultValue: () => {
	 const defaultValue = Select1.selectedOptionValue;
		if (defaultValue == ""){
			console.warn("No values selected on Select1 widget ")
		}
		return defaultValue;
}

Result

Image_6.png (1920×1080)
  • table

table (.) just does what it says—logs a Table widget’s data in key-value pairs for rows as objects. While we support this in Appsmith, we are still working on a browser console-like table, especially as we make the Table feature-richer.

Example


table1DataFunc: () =>{
		const data = Table1.tableData;
		console.table(data)
}

Result

Image_7.png (1920×1080)

That’s it! You now have the power of the console right within in Appsmith. There are other useful views available under Logs and we'll talk about them in a follow-up to the Debugger teardown soon. Bookmark this page. Thank us later.

Debugging your app in Appsmith with the Appsmith Debugger, Part 1
20
September
2022
Product

Debugging your app in Appsmith with the Appsmith Debugger, Part 1

Debugging your app in Appsmith with the Appsmith Debugger, Part 1
Ayush Pahwa
0
 minutes ↗
#
product
#
errors
#
troubleshooting
#
debugger
Product

That title is a tongue twister, innit? Almost.

Here’s a meme that isn’t. It’s just the painful truth.

Debugging_is_like_being_lost_in_a_deser.jpg (749×500)

There is no perfect code, so you know debugging is inevitable, but it’s still a chore and is as crushing often times as the meme claims it is.

But, while debugging is inevitable, making it painful is optional, especially when you have the Appsmith Debugger. We have claimed we champion developer experience as many times as we could before without being brazen about it. We think. So, we thought some more and said, “Let’s prove the claim, too.”

“Wait, wait. What is the Appsmith Debugger?”

In 2021, we shipped the Appsmith Debugger, a set of Chrome DevelTools-like features that have helped you investigate and resolve errors in Appsmith.

We recorded a video for it in a series about the Debugger, talked about it in our docs, and referenced it enough times to make you groan about our obsession with errors. If this is the first you are hearing of it, get on our Discord so we can tell you some more about it.

Why we did this

Browser dev tools are as helpful as a magnet when looking for iron fillings in a pristine haystack. To the untrained eye, they can be downright criminal, too.

Browser_debugger.jpeg (960×506)
Source: Reddit

Sure, sure, they nest groups of errors and there are separate tabs for the console and the debugger, but meh! There’s a sea of error messages, system logs, console logs, and then there’s you swimming in it.

Before we shipped the debugger, you saw,

  • errors inside a widget's Property pane that floated on your canvas which probably already had several widgets
  • the Editor’s Response pane, which clubbed legit responses with errors

The Debugger solved several of those problems.

Post_8.jpg (1920×1080)

What’s the Debugger have

Available on app.appsmith.com and our self-hosted release images, it can be called by toggling the debug icon—the one that looks like a bug—on the bottom-right corner of your Appsmith screen or with CTRL/CMD +D.

Inside the Debugger, live three panes, Errors, Logs, and Inspect Entity, each with their own uses. In the first part of this two part post, we will break the Error pane down for you and see how it can save you hours over browser dev tools in debugging.

If you would much rather just learn about Logs and Inspect Entity, bookmark this post. We will link to Part 2 in five days. :-)

Errors

Borrowing from a browser’s dev tools sub-window but improving on it radically, the Error pane lists all errors that we see when you are building inside Appsmith. Familiar examples include syntax errors from JavaScript bindings, reference errors from queries, and datatype mismatch errors.

Errors in the pane are specific to an Appsmith entity. Translated from Appsmithlish, it means you see helpful error messages about a faulting widget, a rogue query, or a stubborn JS Object.

Untitled.gif (1440×810)
Example of a faulting widget and the error beaconing it
Untitled.gif (1440×810)
A JS Object error

  • These errors get logged to the pane in lockstep with the sequence of code execution in Appsmith.
  • The Error pane is the default view when working with widgets—most noticeable if you have the Debugger sub-window resized as in the pictures in purple—so you know what’s going wrong and where in real-time.
  • The Editor's Error Pane is smarter. It doesn’t automatically switch to the Error pane—Response is the default on this screen—when an error occurs. Instead, the Debug icon lights up in red with a numeric notification that’s like a running ticker for the number of errors the Debugger sees with your queries or JS Objects. Click it to open the Error pane.
  • Every error you see in the pane follows a template with a few helpful pieces of info to help you debug.
image_high.jpeg (1920×1080)
Numbers on this image correspond to bullets below. Images in sub-bullets below show what the sub-bullet talks about.

The timestamp

Logged as your code executes or a value evaluates with your widgets, these little breadcrumbs help you track back from when the error occurred to when last it was A-Okay.

The issue

Depending on the error type, you will see a couple different kinds of issues.

  1. With widgets, you’ll see the faulting widget property’s name. An example of this is the commonplace The value at tableData is invalid, occurring when the property tableData expects an Array<Object> datatype but you have an Array<List> instead.
  2. With queries, you see more specific errors, often specific to the datasource you are running your queries to, often indicated by status codes returned by your failing requests.
  3. With JS Objects, we straight-up level with you about the parseability of your functions. Parseability probably isn’t a word, but you know what we mean.

The source

This has two parts to it—the entity’s name.the type of entity the troublesome one is, e.g., SELECT1.WIDGET. As is obvious and has been to you, SELECT1 is the entity’s name and WIDGET is the entity type.

👌🏾 Appsmith Experience plug: Clicking the source takes you to the faulting entity anywhere in Appsmith, be it a widget, a query, or a JS Object. Noice!

The message

This is the most helpful part of the message, beginning with Error and ending with a helpful bit of text or a number.

  1. Because widgets bind to queries or JS Objects using JavaScript, quite a few errors you see are the same as familiar JavaScript errors like SyntaxError or TypeError. Some other errors show is not defined. This is when a variable, a query, or a JS Object isn’t defined, but you have specified it in the Property pane.
  2. In the Editor, these messages go a step further and call out the line number in the editor that has the faulting code. For example, Line 2: Unrecognized token '$'. This type of message has three parts to it.
Post_9.jpg (1920×1080)

1. The type: Error

2. The string: relation “public.user” does not exit

3. The line number: Position 15

😻 Appsmith Experience plug: Clicking the message will open our in-app docs finder and run a helpful search to show you top docs matching the error.

Response from queries or bindings

This doesn’t always show, but when it does, it can show you helpful responses with query params or evaluated values of data bindings.

Post_10.jpg (1920×1080)
  1. With widgets, you’ll see the evaluated value from the bindings.
  2. With queries, you’ll see the payload from the API you are querying.

“How does all of this help?”

Consider two situations we have painfully drawn for you.

State #1

You have several queries and widgets on your way to a complete build.

Condition #1

You have nested queries inside JS Objects. Meaning, these queries are binded to multiple widgets via JavaScript transformations and have dependent parameters with each other.

Scenario #1

A query fails and returns an error.

Without the Appsmith Debugger

You decide to sift through the browser dev tools sub-window, trying to locate the faulty query in something that looks like ↓.

The_browser_console.png (1920×1080)

When you find the first problem query, you’re hoping against hope this is your patient zero.

  1. If so, congratulations aren’t quite in order yet. You’re still going to have to surgery the query to see what went wrong where.
  2. With browser tools, may you be lucky and find a fix in the first hour.

Most times, though, Murphy’s Law applies.

  1. Meaning, you will need to find the last problem query.
  2. Repeat steps #1 and #2 with all the sub-steps in between

If you have a friend who’s on Appsmith, you hear them say, “Good morning. Do you have a ready app? No? Try the Debugger. 🙄”

With the Appsmith Debugger

You see all the errors from all the failed queries In the Error pane and nothing else to crowd your investigation.

  1. You quickly scan by the type of errors.
  2. Errors are listed in the sequence of query execution.
Post_11.jpg (1920×1080)
So you can simply scroll to the first failed query, and investigate further.

  1. The error message tells you what failed with the params in which line, neatly indented neatly for you.

Don’t remember the query’s name? Pfft! We got it. Click the error message, and go right to the error source.

Trouble troubleshooting? Click the error message and find super-relevant docs in Appsmith’s doc finder.

At the end of it, you save a whole night’s hair-pulls, wake up bright and fresh, sip your coffee, and wonder why some people still use browser dev tools. 🤔 Maybe you should refer them to us.

State #2

You have the data from a REST API and the table for your dashboard, but you have left the chart for the very end. You are sensible like that. Charts are tricky things in general.

Condition #2

You have to bind the chart widget from Fusion Charts or one of our defaults with a query that should output the format Array<{ x: string, y: number Required }> as input to the widget. This will need JavaScript transformations.

Scenario #2

You get a datatype mismatch error.

Without the Appsmith Debugger

You toggle around the floating EXPECTED STRUCTURE, EXPECTED STRUCTURE - EXAMPLE, and EVALUATED VALUE panes to understand the chart widget’s configuration.

You have a JS Object for the transformation, so you now switch back and forth between the canvas and the JS Editor for each possible fix in the JS code.

  1. By now, you have console.loged your way to the browser tools sub-window. Magnet, meet Iron Fillings In A Haystack.
  2. Forgot the change you made to the JS Object five tries ago? Yeah, well, no System Logs, so what can you do, right? Maybe note each change on Sublime or VS Code from this point on.

With the Appsmith Debugger

Post_12.jpg (1920×1080)

Right after you run the transformation, you see the floating-pane-that-we-don’t have-a-name-for-yet show you some red and the Error pane light up with all your errors, timestamped and sequenced by the order of code execution.

  1. You see the type of error and the evaluated value for the faulting entity. Stick to this without worrying about the unnamed floating pane.
  2. Your query has trouble getting a response from your datasource, so you see that error, but hey, you also see the binding failure of that same query with the widget.
  3. No hunting for the query or the widget you want to troubleshoot. One click from the Debugger and you are transported to the associated entity.
Debugger_with_click-actions__JS_Editor.jpg (1920×1080)

You see all the errors from the transformation in one pane with click-actions for each one of them.

Docs_finder_from_Response__Appsmith.gif (1440×810)

Error messages not enough? Click the error and choose, Browse code snippets, and voila! You now now search for the chart + the query right there and see some of our helpful docs.

Made it to here? Your life inside Appsmith is going to change.

Also, this is just part one of this two-part breakdown. What’s next?

https://media.giphy.com/media/3kIGmlW0lvpnmF3bGy/giphy.gif

Better than post-credits. A whole other movie featuring Logs and Inspect Entity. Meanwhile, here’s a few things you can do.

Until the next Debugger post, Appsmiths.

P.S.: We love you.