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

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!

Square
Try Appsmith
Introducing Version Control with Git
3
June
2022
Announcement

Introducing Version Control with Git

Introducing Version Control with Git
Visahavel Parthasarathy
0
 minutes ↗
#
announcement
#
community
#
developer
Announcement

Today we're excited to announce the launch of Version Control with Git, so that developers can collaborate and version control their applications. This has been one of the most requested features from our community.

While low-code frameworks like Appsmith save a developer's time via easier application management, faster plugging in of data sources, and effortless creation of UI, there are still aspects of a developer's usual workflow that aren't native to low-code platforms.

Version Control with Git, enables us to take a big step towards that.

Here's a short video on how you can use Version Control with Git:

Version Control with Git Highlights

Version Control with Git allows multiple developers to add their work in a git branch, raise a pull request for code reviews, integrate with CI/CD pipelines so that their changes go live when their pull requests are approved, and provide a commit history to go back to a previous version if something were to go wrong. You can now also test a new addition to your app on a separate branch without breaking your deployed version.

You can connect to any popular version control tool of your liking: Github, Gitlab, Bitbucket, AWS Code Commit and more.

Furthermore, if you're a large organization using isolated environments for your production and test apps, you can now ensure that your applications can only be promoted to production via your CI/CD processes.

Version Control For All

Appsmith's Community Edition users will be able to connect unlimited public repos and up to 3 private repos.

In contrast, low-code frameworks in the market today either don't offer this functionality as part of their open source or free edition (and instead offer a simple auto-save), or push it deep into their most expensive pricing plans (like Retool).

Head over to our Version Control with Git documentation on how to set up version control with your favorite version control tools and get the most out of it.

May Round-up: App Theming, New Copy Paste Experience, and Product Updates
2
June
2022
Monthly Round-up

May Round-up: App Theming, New Copy Paste Experience, and Product Updates

May Round-up: App Theming, New Copy Paste Experience, and Product Updates
Vihar Kurama
0
 minutes ↗
#
announcement
#
developer
#
community
Monthly Round-up

If you’ve followed Appsmith for a while, you know that we can do a lot in 30 days! I am here to give you all the deets; follow along!

Application Theming [Beta]

One of our most awaited features — App Theming — is now out! Style your pages and widgets using global controls. We've also made it easy to change the visual layout with a single click.

Note that while all apps support theming, changing the theme or customizing the theme may not update the app entirely for older apps. We’ve done this to prevent overriding any changes you may have already made while styling your widgets. (Note that, this is a beta feature at the moment, and we are going to be improving it in the coming weeks).


Theming automatically updates multiple properties, including fonts, colors, borders, shadows, etc. All these properties can be configured from the property pane globally. Read the complete documentation here.

Discard Changes/ Delete Branch on Version Control

Version Control with Git is well-loved by Appsmith users, especially when working with teams. We’ve enhanced this feature by adding another option other than ‘commit’ to make it more flexible. Now you can discard changes on the current branch and reload the earlier, committed application. Additionally, you can also delete the stale or unused branches.

Allowing Camera and Microphone on iFrame

Are you communicating to other websites from Appsmith? Our latest improvements will help you get the most out of it. All you have to do is use allow="camera; microphone" on the attributes to be able to access the camera/microphone on the iframe.

Making it easy for you on Windows using WSL

Have trouble setting up the Appsmith server on Windows? Don’t worry. We’ve worked on some documentation to help you contribute to Appsmith on Windows operating systems that use WSL; check it out here. Prerequisite MongoDB is included, along with detailed notes on building/running the code and how to set up your code editor.

Improvements

New Copy Paste Experience

Duplicating widgets on Appsmith is now easier with the new-copy-paste experience. Here’s a quick summary of how it works:

When the layout widget is selected:

  • If the mouse pointer is not on the layout widget, the widget is pasted inside the layout widget at the bottom of the canvas.
  • If the mouse pointer is on the layout widget, the pasted widget will be aligned top-left of the mouse pointer inside the layout widget.

If the non-layout widget is selected, no matter where the mouse pointer is, It is pasted below the non-layout widget, and the widgets are aligned horizontally.

If the mouse pointer is on a widget and no widgets are selected, widgets will be posted below and are horizontally aligned.

More Pages? View them all at once!

We’ve added a new option to resize the entity explorer page to view more pages at once. Just find the expander and extend it based on your comfort :)

Total Records and Page Count on Table Header

More love to the table widget; we’ve just added total record count and page count on the table header.

CleanShot 2022-05-25 at 15.34.35@2x.png
Better Way of Running JS Objects

We’ve revamped the way we run JS Objects from the JS Editor. Just put the cursor inside the function and hit the RUN button. The editor automatically identifies which function you’re working on; you can always use the shortcuts to make this easier!

Additionally, the page load functions can be configured from the Settings tab, just like how we do it from queries!

CleanShot 2022-05-25 at 15.40.46@2x.png
Added top-level isValid property to JSONForm

Currently, the JSONForm widget has an isValid property inside the fieldState for each field. However, there is no top-level property to check all fields at once. With this update, we added an isValid property which can be used to check if the current form is valid (passes all validation) or not.

CleanShot 2022-05-25 at 15.47.07@2x.png
Enhanced Map Widget

We’ve added a new property to the Map widget that lets us enable search location from Map without entering the coordinates. You can search directly on the map and display searched fields on other widgets without clicking on the Map widget.

CleanShot 2022-05-25 at 15.48.29@2x.png

Collaborations, Technical Content, Videos & Tutorials

Last month was hectic! We’ve published a new video on how you can generate PDFs from Appsmith using APITemplate; not just this, we’ve successfully hosted a couple of live sessions.

  • In this Video, Confidence, our Developer Advocate, show us how you can build a workflow to generate PDFs from Appsmith by consuming APIs from APITemplte.io.
  • Shams Mosowi from Rowy.io collaborated with us to demonstrate how we can build an expense management tool that employees can use to submit expenses for approval using Rowy as the backend.
  • Getting started with Version Control with Git on Appsmith? We’ve created a guide on how you can connect to a Git provider to version control Appsmith apps. Watch it here.

If you're curious about what we were up to, look no further and follow this link.

If you’re interested in using a database not listed on our website as an integration, please let us know about it by raising a PR on Github, and we will do our best to include it at the earliest.

Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

April Round-up: Multiple Widget Reflow, Readable URLs, more Product Updates
3
May
2022
Monthly Round-up

April Round-up: Multiple Widget Reflow, Readable URLs, more Product Updates

April Round-up: Multiple Widget Reflow, Readable URLs, more Product Updates
Vihar Kurama
0
 minutes ↗
#
announcement
#
community
Monthly Round-up

If you’ve followed Appsmith for a while, you know that we can do a lot in 30 days!

I am here to give you all the deets; follow along!

Introducing Multiple Widget Reflow

We’ve received a lot of positive feedback on our new drag and drop experience. Users dig being able to resize widgets and move them around. This provides them with the ultimate freedom to build applications however they want without barriers that might get in the way :P

We’ve made this even more fun and powerful by adding a multiple widget reflow feature. With this, users should be able to move widgets and resize them even when they are grouped.

This also makes the drag and drop experience more performant in avoiding UI lags while moving widgets.

Readable Application URLs

All of you have been waiting for a much-requested feature, and today, it’s finally here! The application URL now has your app name as a slug, making it easier for you to share and find your Appsmith applications. This is available immediately for all the old applications and, of course, the newly created ones. Here’s a sample app URL.


https://app.appsmith.com/app/april-round-up/home-6266f76700cba01cd0301e20

To upgrade your old app URLs, please navigate the updates section from the bottom bar and hit update. Here’s the recording of how you can do it:

Product Updates

New Response View Switchers

We've added a new response view switcher feature that lets users easily switch between response formats on the fly; you'll be able to view them in different formats (JSON, Table, RAW) based purely on ease of choice. This feature will be available on all action types (APIs/Queries on a datasource).

Added Button Variants in Table Column Type

It’s super easy to set a column type to a button on Appsmith - just navigate to column properties and update the type to Button. To make your table button more extraordinary, we’ve added the variant property taking inspiration from our button widget; with this, you can update table button types to different variants available.

CleanShot 2022-04-26 at 01.05.56@2x.png
Smart Substitution Feature on Firestore Plugin

We’ve added a smart substitution feature to Firestore plugin methods (to dynamically perform type conversions on field values in a request body). The smart substitution will work the same way for REST API requests.

Currently, this feature only gets applied to the body section of Create, Set, Add or Update commands, as this is the only input field that expects a JSON input type. This can be toggled by navigating to the settings tab on datasource.

Added MultipartFormDataType variants for REST Datasource

We’ve added ​​MultipartFormDataType variants; with this, users can add an array as a value for multipart requests on REST APIs. It will handle every item in the list as a part of the multipart request. Here’s what an example body will look like:


--IpJ9ACFxVbhXlAQuIFccoJ0K0ttFS5PVyfX3
Content-Disposition: form-data; name="myArray"
Content-Type: text/plain;charset=UTF-8
Content-Length: 3

One
--IpJ9ACFxVbhXlAQuIFccoJ0K0ttFS5PVyfX3
Content-Disposition: form-data; name="myArray"
Content-Type: application/json
Content-Length: 1

2
--IpJ9ACFxVbhXlAQuIFccoJ0K0ttFS5PVyfX3
Content-Disposition: form-data; name="myArray"
Content-Type: application/json
Content-Length: 4


Enhanced Text Widget

When the truncating text option is enabled in a text widget, the scroll switch is no longer valid; as we truncate to fix this issue, we made the following changes.

We’ve added an overflow property option with the following configurations:

  • Scroll Contents - Enables scroll in the text widget
  • Truncate Text - Enables truncation in the text widget
  • No Overflow - No scroll or truncation
CleanShot 2022-05-02 at 16.39.07@2x.png
Added Support to MinIO in S3

You can now connect MinIO instances on Appsmith using the S3 plugin. This can be done when:

  • over the unsecured connection (HTTP)
  • when secured via a CA-signed certificate (HTTPS)

You can find this connecting in a dropdown from the S3 datasource.

Select Widget is now 50x Faster on Large Datasets

We’ve been listening to a few issues with the select widgets when large datasets are used. Previously, in the case of these large data sets, the Select widget took up to 5 seconds to render. The initial render is down to ~100ms once the options are available from the API call.

Our engineers tested this thoroughly by loading 1000 options from a mock API on a newly upgraded test widget :)

New Collaborations, Technical Content, Videos & Tutorials

Last month was crazy for us; we’ve published a blog post on how you can connect Stipe on Appsmith to manage your transactions; not just this, we’ve successfully hosted four live sessions.

  • We’ve created a small guide on how you can use JSON forms on Appsmith. This widget lets you build any kind of form with JSON dynamically. Learn how to use it here.
  • Stephen Tillman, one of our active community members, joined us to show how Appsmith helped him to build a great dashboard that lets him manage credit cards on Brex using BrexAPIs. Watch the recording here.
  • Pranav and I hosted the third How Do I Do X session, where we picked questions from the community to integrate with ZohoCRM and Stripe APIs. We’ve also talked about how you can use resetWidget and clearWidget functions on Appsmith to handle form actions. Watch the recording here.

If you're curious about what we were up to, look no further and follow this link.

If you’re interested in using a database not listed on our website as an integration, please let us know about it by raising a PR on Github, and we will do our best to include it at the earliest.

Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

What’s a Rich Text element?

asdsadasdsa

asdsadasdsa

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.

sfdfsdfds

dsfdsfdsf

adfkaldf

The rich text element allows you to create and format

sadadasdasdas dsada sadas asd ad

Static and dynamic content editing

  1. vdfgdgd
  2. gjgjg

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.

swzdswxzdsw