Building an Employee Survey Dashboard with Supabase and N8N
3
December
2021
Tutorial

Building an Employee Survey Dashboard with Supabase and N8N

Building an Employee Survey Dashboard with Supabase and N8N
Confidence Okoghenun
0
 minutes ↗
#
analytics
Tutorial

Many companies use questionnaires to keep track of their employee’s health and general wellbeing. Because of the time and complexity involved in building one, many choose to go ahead with off-the-shelf solutions that may be quick to set up but have poor analytics tools. For example, the organization might be interested in knowing specifics about the use of the survey/questionnaires. For example (include one or two examples here) But there’s something better than off-the-shelf, and it can be built using Appsmith.

In this article, I will teach you how to build a custom employee survey dashboard to fit your specific requirements. We’ll achieve this by tying together the best open-source tools in the business to build powerful workflows.

We’re going to cover a lot, but specifically, you will learn how to:

  • Use Supabase as a data source
  • Use filters in DB queries
  • Display data in charts
  • Perform email automation with N8N

You can peek ahead to see the app we’ll be building here. Let’s get started!

Project set up

As mentioned earlier, we’ll be using Supabase as our storage DB. Signup for Supabase and create three tables; an employee, a question, and a responses table. You can seed the database with this dummy data

You should also go-ahead to set up N8N. You can signup for the cloud version or self-host it. Create a new workflow, and we’ll come back to it in a bit.

Lastly, signup for Appsmith and create a new app. Go to Datasources > Create New > PostgreSQL and fill the form to connect to your Supabase instance.

Awesome! With this, we have completed all the setup. Let’s begin writing queries.

How to use filters in DB queries

We need to write a query to get questions from the DB. Let’s call this query get_questions, and it should look like this:

SELECT * FROM public."questions";


With the returned data, we can build a question filter. Drag in a select widget and configure its Options property shown below.

The name of the widget has been updated to q_filter
{{
[{label: 'all', value: '%'}]
.concat(get_questions.data.map(q =>({label: q.question, value: q.id})))
}}


question filter

Similarly, go ahead to build a team filter. Name the select widget t_filter, and its Options property should be:

[
    {
    "label": "all",
    "value": "%"
  },
    {
    "label": "hr",
    "value": "hr"
  },
  {
    "label": "marketing",
    "value": "marketing"
  },
  {
    "label": "engineering",
    "value": "engineering"
  },
    {
    "label": "logistics",
    "value": "logistics"
  }
]


Using the filters we just built, we can write filtered queries i.e., to get employees based on the selected team and get responses based on the chosen question and team.

Create a new query for employees and update its name to get_employees. The query should look like this:

SELECT id AS employee, name, email, team FROM public."employees"
WHERE team LIKE "{{t_filter.selectedOptionValue}}";

Similarly, the get_responses query for responses should look like this:

SELECT r."completedAt", r.employee, r.response, r.comment, e.age, e.sex, e.team
FROM responses r
INNER JOIN employees e ON r.employee=e.id
WHERE
    r.question LIKE "{{q_filter.selectedOptionValue || '%'}}"
    AND e.team LIKE "{{t_filter.selectedOptionValue}}";


Using a table widget, you can display the employee completion status like so:

employee completion

And, here’s the snipped used for the Table Data property:

{{
    get_employees.data.map
      (e => _.omit({
        ...e,
        ...get_responses.data.find(r => r.employee === e.employee)

      }, ['sex', 'age', 'response', 'employee', 'comment'])
      )
}}


How to display data in charts

In the last section, we wrote a query to get responses for the selected question and team. Now, we’re going to focus on displaying the data in charts.

From the widgets tab, drag a chart widget into the canvas. We want this chart to display the aggregate number for responses for a particular rating level, i.e. 26 employees rated 5. So for the Series Data property on the chart widget, use the snippet below:

[
  {
    "x": "5",
    "y": {{get_responses.data.filter(r => r.response === 5).length}}
  },
  {
    "x": "4",
    "y": {{get_responses.data.filter(r => r.response === 4).length}}
  },
  {
    "x": "3",
    "y": {{get_responses.data.filter(r => r.response === 3).length}}
  },
  {
    "x": "2",
    "y": {{get_responses.data.filter(r => r.response === 2).length}}
  },
  {
    "x": "1",
    "y": {{get_responses.data.filter(r => r.response === 1).length}}
  }
]


And your chart should look like this:

responses chart

We can also display responses by gender. For this, we’ll need a sex filter. You know the drill, so go and make one. Here’s mine (named f_sex):

sex filter

And now, we can display the data using a chart widget set to a Pie Chart Chart Type. Here’s the snippet:


chart for responses by gender

For some extra credit, use the principles above to build a filtered chart to display responses by age group. Here’s mine:

chart by age group

How to perform email automation with N8N

In this section, we’re going to look at how to send out emails from the dashboard using N8N.

For employees who haven’t completed a survey questionnaire, we need a way to resend it to them via email. Let’s build an N8N workflow for this.

The workflow would consist of two nodes, a webhook that accepts post requests and the second being a Gmail node. Here’s a snapshot of the workflow:

n8n workflow

The Gmail node should be configured to send an email by setting the Resource to Message and the Operation to Send. The subject should contain a link to the questionnaire form, and the recipient email should be an expression that is evaluated the payload of the webhook as shown below:

email field

Going back to the dashboard, we can create a new API to trigger this workflow. Go to Datasources > Create New > Create New API and enter the URL of the N8N webhook. Give the request a name i.e. send_email, set the request type to POST, and the body would be the email of the employee selected from the table:

send email API

Now, going back to the table widget, we can create a custom column of Column Type Button. Configure this column to run the send_email API when it is clicked. Thus, clicking on the button for a particular employee would resend the questionnaire to that employee via email.

Wrap up

you did  it

Awesome! We’ve been able to build the employee survey dashboard. You can take a look at the app here https://app.appsmith.com/applications/61281a99695af65b46d75843/pages/61281a99695af65b46d75845

You can expand on this and add more features to the app. The floor is all yours! We’d love to see what you add next. By the way, give us a star any time you visit github.

How To Build A Catalog Manager for a Shopify Store
3
December
2021
Tutorial

How To Build A Catalog Manager for a Shopify Store

How To Build A Catalog Manager for a Shopify Store
Confidence Okoghenun
0
 minutes ↗
#
Shopify
#
admin
Tutorial

In this article, we will be building a Shopify Dashboard that fits into your custom workflow. But before that, let’s set some context. Lots of store owners choose Shopify as their go-to online store. It’s a mature product with excellent features like the store admin panel and full API access.

That said, it’s not without its flaws.

Integrating with no-native Shopify services can be a real pain, and it’s almost impossible to build custom workflows with the Shopify admin panel. We’ve also published another post on how to make a Shopify admin panel.

In this article, I’ll be showing you how to overcome all of these hurdles so you can build a dashboard app for your Shopify store that fits into your workflow. We’re going to do this by covering the following concepts:

  • How to integrate with Shopify APIs on Appsmith
  • How to set a custom theme for your Shopify dashboard
  • How to use dynamic parameters in API calls
  • How to use the Google Sheets integration

Feel free to peek into the app we’ll be building.

Sounds good? Let’s begin!

How to integrate with Shopify APIs on Appsmith

Lots of services provide secure access to resources via APIs. This makes it possible to design complex workflow and unlock new capabilities. Shopify is not exempt from this rule as we’ll be extensively making use of their APIs.

To get started, enable Private apps in your Shopify dashboard. Create a new app and configure the permission level. You’ll be presented with the following details to interact with the API securely: Your store endpoint i.e https://your_store_name.myshopify.com/admin/api/2021-10/ A username And also a password i.e shppa_cc*******

With these details, we can begin building the dashboard. So signup for Appsmith if you’re yet to do so, and create a new app.

In the new app, click on Datasource > Create New > Authenticated API. Be sure to set Authentication Type to Basic and then fill the form. Here’s a quick snapshot of mine:

creating a Shopify datasource

Using the Shopify data source, let’s create a new API to get all products from the store. Call this get_products and it should be a request to the products.json route.

writing a query to get all products

Now we have data coming into the app. Let’s go build some styled UI components to display this.

How to set a custom theme for your Shopify dashboard

There are many widgets to choose from on Appsmith; the best part is that you can customize and style the look and feel of many. Let me show you how to do this.

From the widgets tab, drag a container widget into the canvas. In the opened configuration menu (called property pane), give it a Border Radius of 5 and set some Box Shadow. You should have a cool looking card container:

styling the container widget

Now you can bring in a list widget into the container and bind the data from the get_products query like this:

binding data to the list widget

And similarly, you can display the product image and name by accessing the data from {{currentItem}}. Here’s an example:

displaying list item data

Cool! That’s how easy it is to style components by using the Styles section in the property pane.

How to use dynamic parameters in API calls

So far, our app can read data and display it in the UI. Now let’s add a flow that allows us to write data. To do this, let’s build a product update feature.

Using the concepts illustrated in the previous sections, build an update form UI that looks like this:

update from UI

And should be configured such that all input widgets are named and have default values based on the product selected from the product_list widget i.e the tile input widget is named up_title and has a Default Text of {{product_list.selectedItem.title}}:

customizing the title input

Cool. Do the same for all the other input widgets in the form.

Now, using the Shopify data source configured earlier, create a new update query that takes input from the form widgets as parameters. Here’s an example:

{
    "product": {
        "title": {{up_title.text}},
        "body_html": {{up_body_html.text}},
        "product_type": {{up_product_type.selectedOptionValue}},
        "status": {{up_status.selectedOptionValue}}
    }
}


creating an update query

You’ll also notice that the ID of the product to be updated is populated from the product selected on the product_list widget and used in the endpoint URL.

Awesome! To finish this up, go back to the form submit button and configure it to run the update query. We should also add a get_products query so that all data in the app is refreshed i.e:

{{update_product.run(() => get_products.run())}}


linking the update query to the submit button

And that’s how easy it is to pass dynamic parameters to API calls!

Lastly, let’s talk about how to integrate with Google Sheets.

How to integrate with Google Sheets

This section will be quite advanced, so stick with me! We will build a bulk import functionality from Google sheet, a feature not natively supported by the Shopify API.

To get started, create a new page and under Datasource > Create New, click on Google Sheets. Authorize the datasource and create a new API that we’ll call get_products_from_sheets.

You can use this sample sheet as a reference for the Spreadsheet URL config. Here’s a sample configuration:

get products from sheets config

Now, you can build UI widgets to display the data returned as we did for the product list. In this sample, I used a table widget:

displaying sheet data

When the Sync button is clicked, we loop through the items and create each product by making a couple of API calls. Also, notice here that we have parameters passed to the API calls as the third arguments:

{{
    (() => {
      get_products_from_sheets.data.forEach((i) => {
        new_product.run(
          (res, i) => {
            new_image.run(
              () => {},
              () => {},
              { ...i, id: res.product.id }
            );

            new_price.run(
              () => {},
              () => {},
              { ...i, id: res.product.variants[0].id }
            );

            new_track_inventory.run(
              (res, i) =>
                new_inventory.run(
                  (res, i) => showAlert('Added: '+i.Title, 'success'),
                  () => {},
                  i
                ),
              () => {},
              { ...i, id: res.product.variants[0].inventory_item_id }
            );

          },
          () => {},
          i
        );
      });
    })();
}}


And here’s a snapshot of each of the queries call:

new product
new image
new price
new track inventory
new inventory

You can learn more about these resources/endpoints in the official Shopify docs.

What’s next?

Congratulations, you did it!

So far, we’ve implemented the core functionalities such as viewing, updating, and bulk import of products. But you can expand on this and add additional services i.e., payment with Stripe.

The floor is all yours! We’d love to see what you add next. By the way, give us a star any time you visit github.

Appsmith Basics: How to Query Databases and Bind Data to Widgets
3
December
2021
Tutorial

Appsmith Basics: How to Query Databases and Bind Data to Widgets

Appsmith Basics: How to Query Databases and Bind Data to Widgets
Confidence Okoghenun
0
 minutes ↗
#
admin
#
beginners
Tutorial

Every application involves some level of CRUD operations, be it reading records from a database, creating or updating records, or deleting records. All of which is put together by well-designed application UI and logic.

In this article, I will show you how to build a task manager in Appsmith using Firestore as the database. By doing this, we will cover and solidify core concepts like connecting to databases, writing queries, infusing data to widgets, and writing custom functionality using JavaScript.

In this article, you will learn how to do the following:

  1. Connecting to and querying Firestore
  2. Binding data to widgets on Appsmith
  3. Deploying the application you built

Let’s get started with the first.

1. Connecting to and querying Firestore

Before we go in-depth into using Firestore, you need to create an app. Signup for Appsmith cloud and click on the New button with orange. Now, we can connect the created app to the database.

Click on Datasources > Create New and then Firestore. Fill in the credentials to connect to your firebase instance. To make sure all details are correct, click on the Test button, after which you can save the data source.

To get your Service Account Credentials, go to your project settings in Firebase and generate a new Nodejs private key that will be copy-pasted into your Appsmith app.

Here’s a snapshot of my setup:

my Firestore configuration

Using the Firestore data source, we need to query for data. Let’s write a query to get tasks from the database. To display some data, I have a simple collection called asana that has some seeded data. A query to get tasks would look like this:

query to get tasks from database
The response data in the snapshot above shows the shape of each entry in the database. This will be useful in seeding your database.

Now that we have linked to Firestore and have some data coming in, let’s display that data in the app using widgets.

2. Binding data to widgets

To display the data coming in, we’ll need a List widget. Switch to the widget tab in the sidebar and drag a List widget into the canvas. (The widgets tab is below the app name)

widgets tab

On the List widget, a simple binding can be written like this:

binding data into the list widget

Similarly, we can display the data for each item in the List widget by bringing in Text widgets and writing their corresponding bindings.

displaying data in the list

That’s all you need to know about binding data to widgets; write {{}}, and you’re good to go.

Before we move on to deploying the app, let’s spend a few minutes building a form that will allow us to update/delete tasks.

To do this, drag in a Form widget into the canvas, and use a couple of text widgets to display the information about the task selected from the List widget, such as clicking on a new item on the list will automatically update the form. Your binding should look like this:

binding to display selected item from list

You should also do the same for the Datepicker, Input, and Select widgets required to build the form. For example, you should configure the selected widget this way:

![select widget configuration] (res.cloudinary.com/confidenceappsmith/image..)

You should also rename your widget to something more descriptive as I did in the Select widget shown above

Now, using the Firestore datasource configured earlier, you can write an update query like this.

query to update a task

And then, configure the submit button on the form to call the update_task query when clicked.

running update query on submit button click

Wasn’t that was quite easy? Using the same principles, you can write a query to delete the task.

3. Deploying the application

This is by far the easiest step. To deploy an app, click on the Deploy button to the top right corner of the screen.

You may also invite others to use/develop the app or even make it publicly available. You can use the share button just beside the Deploy button. This brings up a modal:

share button modal

Wrap up

you made it

You’ve made it! You’ve learned the core concepts of building apps with Appsmith, and I’m sure you’re ready to put this knowledge to use.

I’m going to leave a link to the app here with more features added to make it production ready. Click on this text to see the app.

Awesome! Go into the world and build! (Also consider giving us a star on GitHub)

If you have specific questions for me, just send me an email and I’ll be happy to help!

Building an Admin Panel with Appsmith
29
April
2021
Tutorial

Building an Admin Panel with Appsmith

Building an Admin Panel with Appsmith
Confidence Okoghenun
0
 minutes ↗
#
admin
#
dashboard
#
developer-tools
#
tools
Tutorial


Admin panels are an important part of every organization. They are necessary to serve customers better and run business processes smoothly. They help reduce management and people overhead significantly. But on the other hand, they take a lot of time to build and are troublesome and expensive to maintain. As a result, it is not the choice of project for most developers.

Thankfully, there are a lot of options available to developers today to build complex admin panels and dashboards fairly easily while making maintenance easier. An example of such a framework that we have explored, React-admin. We’ve written an article on how to use it. It’s better than building an admin panel from scratch, but it is still cumbersome to move from a simple admin panel to an admin panel that behaves like an internal app.

And that’s one of the reasons why we built Appsmith. Building Admin panels and internal apps should be easy and, more importantly, fun. Appsmith offers a drag and drop interface for creating UI elements and provides the option to write code wherever you may need to. You can build dashboards a hundred times faster on Appsmith, and that’s not an exaggeration.

For this article, I’d like you to imagine it's 2005. Netflix doesn’t stream video yet. You run a local DVD store, and you need software to manage your rentals. You’re the only developer in the organization. How do you go about building one that is fast, scalable and easily maintainable? In this article, I’m going to show you how to build an admin panel using Appsmith to manage DVD rentals for your store.

The app would have two pages. The first page would have a table listing all registered members of the store. The second page will have a table that holds all rental records. From here, new rental entries can be created, and existing rentals can be updated i.e from borrowed to returned. We would also be able to click on a customer from the first page and then taken them to the rentals page to see his rental history.

Before we begin, I’d like to give you a quick overview of the dataset we’ll be making use of. We’ll be using a modified version of the Sakila dataset. The database of choice is MongoDB on MongoDB Atlas and here’s the schema for each collection

// Customer collection
{
  "store_id": String,
  "first_name": String,
  "last_name": String,
  "email": String,
  "address_id": String,
  "activebool": Boolean,
  "create_date": Date,
  "last_update": Date,
  "active": Number
}
// Film collection
{
  "title": String,
  "description": String,
  "release_year": Number,
  "language_id": String,
  "rental_duration": Number,
  "rental_rate": Number,
  "length": Number,
  "replacement_cost": Number,
  "rating": String,
  "last_update": Date,
  "special_features": String,
  "fulltext": String
}
// Rental collection
{
  "status": String,
  "rental_date": Date,
  "film_title": String,
  "customer_email": String,
  "return_date": Date,
  "staff_id": String,
  "last_update": Date
}

That’s the overview of everything. Now let us get started!

Oh, snap! I almost forgot. Here are some of the core Appsmith concepts we’re going to cover:

  • How to connect Appsmith directly to your database (eliminates the need for an API server)
  • How to create data-driven widgets
  • How to perform CURD operations
  • How to securely share your application (no auth required)
  • How to deploy your application

We can get started now!

Connecting to your data source

It’s quite important that our application is linked to a persisted data source because admin panels are almost always data-driven applications. This data can come from an API, a database, or a Google sheets table as we have the freedom to use any of these on Appsmith. We’ll be with the database route.

Create an Appsmith account if you do not have one yet and let’s begin to smith our application 😁

As highlighted earlier, our application would be based on a MongoDB database. Don’t despair if you would like to follow along and do not have a ready-to-go database. Here’s a database connection you can make use of. Mind you, it’s read-only (don’t forget to say thank you)

Username: article_reader

Password: 0HkfXpxVpvvSvUOx

Connection url: cloud.r4vra.mongodb.net

Database name: movie_store

Alright, we’ve removed all hurdles from the track, now let’s start racing!

Go ahead to create a new application from your Appsmith dashboard and give it a fancy name. Next, let’s connect to the mongo database. Click on the DB Queries button on the left panel on the page and select MongoDB from the list of supported databases. Now go in and supply the database credentials to the from, also give this data source a descriptive name i.e. movie_store or movie_store_db. I think I’ll go with movie_store. And lastly, hit the big green save button.

1-connecting to mongodb.png

Awesome! We’ve connected the application to the database. We can now do some querying and build data-driven widgets. Let’s move on to the next section.

Creating data-driven widgets

We now have a database connected to the application. The next thing we need to do is to create a widget that can be used to create, update and display data from the database. To achieve this, we need a way to fetch data from the database. This can be done on Appsmith by writing database queries.

Rolle up your sleeves because we’re going to be writing our first DB query! Click on the DB Queries section again, and you’ll see a button to create a new query on the MongoDB movie_store data source. Give the query a name i.e. get_customers, and select a read template from the list because we’re going to be reading documents from the Customer collection.

The get_customer query should find all customers in the Customer collection, sort them by email and return a fixed limit of customers. Your query should look like this.

// Customer collection
{
  "find": "Customer",
  "sort": {
    "email": 1
  },
  "limit": 10
}

Configure your query and hit the run button, and you’ll get a list of 10 customers. Sweet!

It would be much readable to display this data in a table. So let’s do exactly that. Click on the Add widget button just below the query editor, and you’ll get a new table widget on the canvas that has been automatically linked to the get_customers query. An alternative way would be to click on the Widgets section and drag a table widget to the canvas. Then, data from the query can be bound to the widget via the Table Data property on the widget’s configuration menu using moustache syntax.

// Customer collection
{{get_customers.data}}
It’s important you give the table a descriptive name. Consider changing the name from Table1 to customers_table
2-writing binding for customer table.png

Go ahead to customize the table by re-ordering the columns and hiding columns you don’t like. We’ll also need to paginate the table, so on the table’s config menu, turn on Server Side Pagination and configure the onPageChange action (in the Actions subsection of the menu) to execute a DB query and then select the get_customers query.

We’ll need to update the query and add a skip property that calculates a skip based on the current table page number to simulate pagination. The moustache syntax would be very handy in this case to perform quick evaluations, so let’s make use of it. Your updated get_customer query should look like mine.

{
  "find": "Customer",
  "sort": {
    "email": 1
  },
  "skip": {{(customers_table.pageNo - 1) * 10}},
  "limit": 10
}

As you may have noticed, we are accessing the table’s page number from within the {{}} through the object bearing the widget’s name (customers_table in this case). You can learn more about writing Javascript in Appsmith here.

Click on the page number on the table widget now fetches new records. We’ve been able to query the database, bind data to a widget and perform customization on the widget. Good job!

Now, we’re going to create a new page to manage rentals. Click on the plus button on the Pages section of the left-hand panel and create a new page. Call this the Rentals page and update the name of the home page to Customers. On this new page, you’re going to be building a table to display rentals like we just did for the Customers page. You’ll need a query (should be named get_rentals) and a table widget (should be named rentals_table) for this. Your query to get rentals should look like this.

{
  "find": "Rental",
  "sort": {
    "_id": 1
  },
  "skip": {{(rentals_table.pageNo - 1)*10}},
  "limit": 10
}

Where rentals_table is the name of your table widget on the Rentals page.

Good luck!

Creating new rentals

After completing the table on the Rentals page, the next feature we need to build in is the ability to create new rental records.

Here’s the game plan: We’re going to build a form that takes as input the status, rental data, film title, customer email, and return date. This form would be housed within a modal widget that would be triggered by clicking on a ‘Create new rental’ button. Now, you have a good overview, let’s get started!

On the Rentals page, head to the widget’s section and drag in a modal widget. In the modal’s config menu, update the name to create_rental_modal and set the Modal type to Form Modal. Now you see we have more space to work with.

With create_rental_modal open, drag in three dropdown widgets and two date picker widgets. The name of the first dropdown widget should be updated to customer_dropdown. Following this convention, go ahead and rename the other two dropdowns to film_dropdown and status_dropdown. For the two date pickers, their names should similarly be updated to rental_datepicker and return_datepicker. You should drag in a few text widgets to label these inputs.

4-create rental modal.png

We need a button on the UI to trigger the opening up of the modal. Let’s drag a button from the widgets section to the canvas. Give this button a new label i.e Create New Rental, and set the onClick action to Open Modal, then select create_rental_modal. Clicking on the button now opens up the modal.

Nice! We’ve completed the UI for the crate form. Now let’s wire it up with some queries.

The first query we’ll be writing for the create form would get all registered customers from the DB. We would use the returned data to build the options for the customer_dropdown. This query would be similar to the get_customers query from the Customers page.

Go ahead and create a new query named all_customers. It should be configured as shown below

{
  "find": "Customer",
  "sort": {
    "email": 1
  }
}

Now, we can use the data returned from this query to build the options of the customer_dropdown. Open up the create_new_rental modal and head to the config menu of the customer_dropdown. Now replace the content of the Options section with the snippet shown below.

{{all_customers.data.map(c => ({label: c.email, value: c.email}))}}

This returns an array of objects whose label and value are the email addresses of the customers from the all_customers query. You can play around with the dropdown and see that the options generated from the query. Sweet 🤩

Now you’re going to do the same thing for the film_dropdown. Create an all_films query and use the title field of each document to build the options for the dropdown. Your query should look like this.

{
  "find": "Film",
  "sort": {
    "title": 1
  }
}

Cool! For the status_dropdown, we can simply hard code the Options with this simple JSON array.

[
  { "label": "borrowed", "value": "borrowed" },
  { "label": "delayed", "value": "delayed" },
  { "label": "lost", "value": "lost" },
  { "label": "returned", "value": "returned" }
]
Don’t forget to set a default option for each dropdown widget The rental_datepicker and return_datepicker widgets do not require further configuration, and they are good to go. The last thing we need to do is to write a query that would create a new document on the ‘Rental’ collection using the data from the input widgets. Alright, go ahead and create a new query called create_rental and configure it this way.
{
  "insert": "Rental",
  "documents": [
    {
      "staff_id": "{{appsmith.user.email}}",
      "status": "{{status_dropdown.selectedOptionValue}}",
      "customer_email": "{{ customer_dropdown.selectedOptionValue }}",
      "film_title": "{{ film_dropdown.selectedOptionValue }}",
      "rental_date": "{{ rental_datepicker.selectedDate }}",
      "return_date": "{{ return_datepicker.selectedDate }}",
      "last_update": "{{moment().format()}}"
    }
  ]
}

Now we can go back to configure the big green Confirm button on the modal to run this query when it is clicked. We also want to close the modal and refresh the rental table when the button is clicked. Because what we want to do is a complex chain of actions, we can write some JavaScript to achieve this. Click on the JS button just beside the onClick button of the Confirm button and supply the bellow code snippet.

{
{create_rental.run(
  ()=> {
      closeModal("create_rental_modal");
    get_rentals.run();
  }
)}
}

Great Job! Go ahead, give it a spin and create a few new rentals.

Updating a rental

The flow for updating a rental is quite similar to that of creating a rental. We’ll need a modal that would be opened by clicking on a button. The modal would only contain form elements for updating the status and return date of the rental.

The only difference here is that we want the rental in question to be the one selected from the table. To do this, we need to head back to the config menu for the rentals_table and create a new column by clicking on the Add a new column button. Click on the gear icon of the newly created column and set the column name to Update. Also, set the Column type to button and set its Lebel property to Update. For its onClick action, it should be configured to open up the update_rental_modal which you’re going to build on your own.

And that all you need. Oh, you’ll also need to write a update_rental query too. It should look somewhat like this.

{
  "update": "Rental",
  "updates": [
    {
      "q": {
        "_id": "{{rentals_table.selectedRow._id}}"
      },
      "u": { 
        "$set": {
      "status": "{{ update_status_dropdown.selectedOptionValue }}", 
          "return_date": "{{update_return_date_datepicker.selectedDate}}",
          "last_update": "{{moment().format()}}"
     } 
      }
    }
  ]
}

Now you have everything you need to know. Go ahead and build the update flow.

Putting it all together

You’ve done a good job making it this far. I’m so proud of you that I’m going to leave the gif below. 🤗

We’ve been able to accomplish most of what we set out to do. We have a table on the Customers page to view all customers. On the Rentals page, we can view all rentals, create a new rental or update existing rentals. It was no small feat we accomplished.

I’ll like us to add one more feature. Let’s make it such that clicking on a customer on the customers_table takes us to the Rentals page, and then we can view all rentals made only by that customer.

We’ll need column buttons for each row like we did for the update flow. In this case, the onClick action of the custom column buttons would perform a navigation to the Rentals page and pass the selected customers’ email from the table as query params. Use the below snippet in the Query Params configuration for the navigation.

{{{customer_email: customers_table.selectedRow.email}}}

And lastly, we need to update the get_rentals query in the Rentals page to filter by customer_email coming in as a query param. We also add a fallback just in case no email is passed.

{
  "find": "Rental",
  "sort": {
    "customer_email": 1
  },
    "filter": {
    "customer_email": {
      "$regex": "{{appsmith.URL.queryParams.customer_email || ""}}"
      }
    },
 "skip": {{(rentals_table.pageNo - 1) * 10}},
  "limit": 10
}

And that’s it! You’ve completed the app. 🥳️🎉🎉

Sharing and deploying the app

It’s quite easy to hand off your application to the team that would make use of it i.e customer support. You don’t have to worry about an authentication/authorization flow; Appsmith does all the work for you.

There’s a SHARE button on the top right side of the page. You can easily use it to invite users to the application and set roles i.e app viewer or developer. No hassle is required!

Clicking on the ‘DEPLOY’ button to the right of the share button will publish all changes made in the editor to the hosted version of the application. Isn’t that cool?

Wrapping up

We set out to build an admin panel, and we’ve seen how we can do it very easily using Appsmith. Going this route will drastically reduce the development time and resources put into maintaining the app.

If you found this tutorial helpful, please consider giving us a star on Github.

Cover Photo Credits: Photo by Serpstat from Pexels

Building an Admin Dashboard with React-admin
29
April
2021
Resources

Building an Admin Dashboard with React-admin

Building an Admin Dashboard with React-admin
Confidence Okoghenun
0
 minutes ↗
#
admin
#
dashboard
#
frontend-frameworks
Resources

React admin has been one of the holy grail frontend frameworks for building responsive admin panels. It offers a lot of really cool features such as data validation, optimistic rendering, accessibility, and action undo. React-admin is also plug-and-play as it supports standard REST APIs and a handful of GraphQL dialects. Being a Reactjs framework, it also gives you access to thousands of plugins and libraries available in Javascript and the React ecosystem.

In this article, I would like to show you how to build an admin panel using React-admin.

We’re going to be building a dashboard to manage DVD movie rentals for a local rental store. The first page would have a table listing all registered members of the store. The second page will have a table that holds all rental records. From here, new rental entries can be created and existing rentals can be updated i.e from borrowed to returned. We would also be able to click on a customer from the first page and then be taken to the rentals page to see his rental history.

Here’s a gif and a link to the completed application

You can view the demo app here

Dashboard link: as-react-admin.netlify.app

username: cokoghenun@appsmith.com

password: 123456

Through building this dashboard, we’re going to cover core React-admin concepts such as

  • Resources
  • List view
  • Edit/Create view
  • Reference inputs and
  • Authentication

Since React-admin requires an API server we would need to build one on top of the database. Speaking of the database, we’ll be making use of MongoDB and the demo dataset is a modified version of the Sakila dataset.

To save time and get to the fun part of building the dashboard with React-admin, we’ll be making use of Loopback to generate a Nodejs API over the database. If you are not familiar with Loopback, it is a highly extensible Node.js and TypeScript framework for building APIs and microservices.

You can skip this if you already have an API to use

We’re almost set. But before we begin, I’d like to give you a mini-map of the entire article. The first part of this article will focus on generating an API server over the database on MongoDB using Loopback. The second part of this article would cover how to use React-admin to build a dashboard from the API generated in the first section.

Alright, everything looks good. Let’s get started!

Generating an API server

There are many ways to build an API server. You can roll up your sleeves and build one yourself(this takes a lot of time) or you can choose to go with a framework. Loopback is the fastest framework I found to build Nodejs APIs over a database. It supports a host of databases ranging from in-memory to document to relational databases.

The API that would be generated using Loopback will have three resources, the first being the customer resource that represents customers who come to rent DVDs from the store. We also have the film resource, representing DVDs that are in stock. Lastly, we have the rentals resource, which records each rental.

Here’s the schema for each resource

// Customer resource{
  "store_id": String,
  "first_name": String,
  "last_name": String,
  "email": String,
  "address_id": String,
  "activebool": Boolean,
  "create_date": Date,
  "last_update": Date,
  "active": Number
}


// Film resource
{
  "title": String,
  "description": String,
  "release_year": Number,
  "language_id": String,
  "rental_duration": Number,
  "rental_rate": Number,
  "length": Number,
  "replacement_cost": Number,
  "rating": String,
  "last_update": Date,
  "special_features": String,
  "fulltext": String
}

// Rental resource
{
  "status": String,
  "rental_date": Date,
  "film_title": String,
  "customer_email": String,
  "return_date": Date,
  "staff_id": String,
  "last_update": Date
}

Okay! Now let’s get started by install Loopback CLI with npm

npm i -g @loopback/cli

We can easily scaffold the Nodejs server using the Loopback CLI. It configures a Typescript compiler and installs all required dependencies. Let’s run the CLI and answer a few prompts to generate a new app

lb4 app

You should have your app configured as shown below

Hit enter and give the CLI some time to set up the app.

Creating a model

Now that the loopback app has been scaffolded, cd (change directory) into the app folder, and let’s start by creating a model for each resource. A model communicates the shape of each document for a particular resource, much like the schema shown earlier.

Let’s create a model for the customer resource using the Loopback CLI

lb4 model

As we did when generating the app, answer the CLI prompts. Yours should look like this

3-configuring the customer model.png

Great Job! Now, go ahead and do the same for the film and rental resources. Don’t forget that to create a new model, you’ll need to run the lb4 model command.

Connecting to the database

Next, we’ll need to link the Loopback app to the Mongo database. Loopback provides two entities to help us accomplish this, and they are the datasource and repository mechanisms.

A datasource represents a database connection that would be used to store and retrieve documents from the database i.e MongoDB or PostgreSQL. On the other hand, a repository links a resource on the Loopback app to a particular table or collection in the database. For example, the customer resource is linked to the Customer collection in the database using a repository.

Now, let’s add a datasource to the app, and link it to our MongoDB database. We can easily do this using the CLI command below

lb4 datasource

As usual, go ahead and answer the CLI prompts, supplying the database credentials to the CLI

4-configuring the mongo datasource.png

Awesome! Now we can add a repository for each resource.

Run the command below and let’s set up a repository for the customer resource. Notice that we have to link the created resource to the target resource, and in this case, it is the customer resource

lb4 repository

Cool! Go ahead and do the same for the film and rental repositories. I’m confident you can finish up on your own 😜

Adding CRUD functionality

Great Job! That was a lot we just covered. Right now, we have models for each resource, a datasource, and repositories linking each model to its respective collection in the database.

The last piece of the puzzle is to add CRUD functionality for each resource.

We can do this by creating controllers. Controllers do the grunt work of creating, reading, updating, and deleting documents for each resource.

As you may have already guessed, we can create a controller using the controller command. Now, let’s create a REST controller for the customer resource. Notice we’ll need to use the model and repository created earlier for the customer resource.

lb4 controller
Note that the Id is a string and is not required when creating a new instance
6-creating a controller.png

As usual, go ahead and do the same for the film and rental resources.

Awesome! We now have a full-blown REST API that was generated in a few minutes. Open up the project folder in your favorite code editor and you’ll see all the code(and folders) generated by Loopback.

I recommend you change the default port in the index.ts file to something else i.e 4000 because Create React App (used by React-admin) runs by default on port 3000

You can start the server using the start script

npm start

You can find a playground and the auto-generated API documentation for your server by visiting the server address on your browser i.e http://localhost:4000/

7-api explorer.png

Alright! Now we have a REST API server with CRUD functionality, we can move on with creating the admin dashboard for using React-admin.

Enter React-admin

We’ve finally gotten to the fun part, yay!

As a quick recap, we have a Loopback API generated in the last section that serves the customer, film, and rental resource with the following endpoints and data schema

// /customers endpoint
{
  "store_id": String,
  "first_name": String,
  "last_name": String,
  "email": String,
  "address_id": String,
  "activebool": Boolean,
  "create_date": Date,
  "last_update": Date,
  "active": Number
}


// /films endpoint
{
  "title": String,
  "description": String,
  "release_year": Number,
  "language_id": String,
  "rental_duration": Number,
  "rental_rate": Number,
  "length": Number,
  "replacement_cost": Number,
  "rating": String,
  "last_update": Date,
  "special_features": String,
  "fulltext": String
}

// /rentals endpoint
{
  "status": String,
  "rental_date": Date,
  "film_title": String,
  "customer_email": String,
  "return_date": Date,
  "staff_id": String,
  "last_update": Date
}

So here’s the game plan. We’re going to use this API to build a dashboard to manage DVD movie rentals. The first page would be a table showing all customers. Then we can click on a customer and view all his rentals on a new page. We can update the return date and status of each rental i.e from borrowed to returned. Lastly, we can view all rentals on the rentals page and create a new entry or edit an existing one.

Phew! Now we can finally begin with React-admin 😅

React-admin is a powerful front-end framework for building admin panels and dashboards. It is highly customizable and has a host of other great features. Since it is based on Reactjs, it can be used with thousands of other Reactjs and Javascript libraries.

React admin requires a base Reactjs project. We are going to be going with Create-React-App (CRA) in this article. So let’s set up the project with CRA

npx create-react-app rental-admin-panel

Give the CLI some time to install all dependencies and finish setting up the project. Then, cd into the project directory and go-ahead to install React-admin and the Loopback dataprovider.

npm install react-admin react-admin-lb4

A dataProvider is the mechanism with which React-admin communicates with a REST/GraphQL API. The Loopback provider for React-admin enables it to understand and use Loopback APIs i.e how to paginate or filter requests. If you aren’t using a Loopback generated API, you should look into using one of these dataProviders for React-admin.

Open up the project in your favourite code editor and replace everything in the App.js file with the below starter code

//src/App.js

import React from 'react';
import lb4Provider from 'react-admin-lb4';
import { Admin, Resource } from 'react-admin';

function App() {
  return (
    // ------------- Replace the below endpoint with your API endpoint -------------
    
      
    
  );
}

export default App;

So far so good. But we have some new concepts to clear up. In the starter code above, we supply a dataProvider to React-admin which enables it to query the API. The next thing we did up there is to register a resource from the API that we would like to use in React-admin. This is done simply by supplying the endpoint as a name prop to the <Resource> component.

You don’t need to add the forward-slash “/” to the resource name

Going by this rule, we must register it as a resource whenever we need to query a new API endpoint. In this way, React-admin becomes aware of it. Moving on...

Creating the Customers' table

The easiest way to view all customers’ info is to have a paginated table displaying all customers’ info. React-admin makes it easy to do this by providing us with a <List> component.

The <List> component generates a paginated table that lists out all documents in a particular resource. We can choose which fields we want to show up on the table by wrapping them in the appropriate <Field> component i.e a date property on a document would be wrapped in a <DateField> component.

The data property on the document is linked to the <Field> component using the source prop. This prop must contain the exact property name. And the field name showing up on the table can be customized using the label prop.

We can also create a filter for the table using the <Filter> component and specify an action to be triggered whenever an item is clicked on the table using the rowClick props on the <Datagrid> component. You can learn more about filtering here and row actions here

Alright! So we want a customer table to show all the customers. We also want this table to be filterable by customer email. Lastly, we want to be able to click on a customer and see all his rentals (we haven’t created the rentals page yet, but we will shortly).

Let’s see all of this in action. Go ahead to create a customer list component with the following content

//src/components/CustomerList.js

import React from 'react';
import { List, Filter, Datagrid, TextField, SearchInput, } from 'react-admin';

// ------------- filter component which filters by customer email -------------
const CustomerFilter = (props) => (
  
    
  
);

const CustomerList = (props) => (
  } title='List of Customers'>
// ------------- rowclick action that redirects to the rentals of the selected customer using the customer id -------------
     {
        return `/rentals?filter=%7B%22customer_email%22%3A%22${record.email}%22%7D&order=ASC&page=1&perPage=10&sort=film_title`;
      }}
    >
      
      
      
      
      
    
  
);

export default CustomerList;

Next, we need to link the <CustomerList> component with the customer resource component.

//src/App.js

// ------------- import CustomerList -------------
import CustomerList from './components/CustomerList'; 

//…

// ------------- use CustomerList as the list view on the customer resource -------------

Save your code and let’s head over to the browser. You can see we have a nice paginated, and filterable customer table that has been automatically generated and is rendering customer information from the API. Cool right? 😎

8-customer table ui.png

Not so fast! Go ahead and create a similar list table for the rental resource. You can name this component RentalList. If you are curious or get stock, feel free to fall back on the code here.

Creating and Editing a Rental

We have two more views to create and they are the edit and create view for the rental resource. They are quite similar to each other and are both similar to the list view but with a few differences.

The edit view would be used to edit an item clicked on the rental table.

To wire up this behaviour ensure that you have rowClick='edit' on the <Datagrid> component in <RentalList>

An edit view uses a <SimpleForm> component, which in reality is a simple form with nested <Input> components. Like with the <Field> components, each <Input> component used is based on the data type of the property to be edited i.e a <TextInput> component is used on a text property. Inputs also require the source props and optional label props as we’ve already seen with the <Field> component.

Bringing it all together, the edit view for the rental resource would look like this:

Notice that some inputs have been disabled using the disabled props
// src/components/RentalEdit.sj
import React from 'react';
import {
  Edit,
  SimpleForm,
  TextInput,
  DateTimeInput,
  SelectInput,
} from 'react-admin';

const RentalEdit = (props) => (
  
    
      
      
      
      

      
      
    
  
);

export default RentalEdit;

Don’t forget to import and use the edit view in the rental resource component in your App.js file.


//src/App.js

// ------------- import RentalEdit' -------------
import RentalEdit from './components/RentalEdit'; 

//…

// ------------- use RentalEdit as the edit view on the rental resource -------------
 

Save your files and let’s head to the browser. Click on an order to see the magic!

Okay, so we’ve completed the edit view. Now moving on to make the create view.

The create view is quite similar to the edit view. It’s so similar that I’m just going to paste the code right here and you wouldn’t be able to tell the difference. Just kidding 😜. Anyway, here’s the code for the create view

// src/components/RentalCreate.js
import React, { useState, useEffect } from 'react';
import {
  Create,
  SimpleForm,
  DateTimeInput,
  SelectInput,
  useNotify,
  useRefresh,
  useRedirect,
  useQuery,
  TextInput,
} from 'react-admin';

const RentalCreate = (props) => {
  const notify = useNotify();
  const refresh = useRefresh();
  const redirect = useRedirect();

  const onSuccess = ({ data }) => {
    notify(`New Rental created `);
    redirect(`/rentals?filter=%7B"id"%3A"${data.id}"%7D`);
    refresh();
  };

  const [customers, setCustomers] = useState([]);
  const { data: customer } = useQuery({
    type: 'getList',
    resource: 'customers',
    payload: {
      pagination: { page: 1, perPage: 600 },
      sort: { field: 'email', order: 'ASC' },
      filter: {},
    },
  });

  const [films, setFilms] = useState([]);
  const { data: film } = useQuery({
    type: 'getList',
    resource: 'films',
    payload: {
      pagination: { page: 1, perPage: 1000 },
      sort: { field: 'title', order: 'ASC' },
      filter: {},
    },
  });


  useEffect(() => {
    if (film) setFilms(film.map((d) => ({ id: d.title, name: d.title })));
    if (customer)
      setCustomers(customer.map((d) => ({ id: d.email, name: d.email })));
  }, [film, customer]);

  return (
    
      
        
        
        
        

        

        
      
    
  );
};

export default RentalCreate;

The only difference here is that we have two select inputs that display a list of all customers and films by manually querying those resources.

Instead of writing custom logic to query the customer and film resources, we could have easily use the built-in <ReferenceInput> component. But currently, there's no way to set the selected value from the <SelectInput> component to something other than the document id. In the create form, we require the email field from the customer resource and the title field from the film resource. That is why we are manually querying, else the <ReferenceInput> component would have been awesome.

Do not forget to import and use the create view we just made. Also, register the film resource in App.js

//src/App.js

// ------------- import RentalCreate -------------
import RentalCreate from './components/RentalCreate';


//…

// ------------- use RentalCreate as the create view on the rental resource -------------
 
// ------------- register the film resource -------------
 
If a resource is registered and no list view is passed to it, React-admin hides it from the navbar. But the resource is still useful for querying as we did for the film select input in the <RentalCreate> component.

This is the moment you’ve been waiting for! Save your files and head over to the browser. You’ll notice that we now have a create button on the rentals table, and clicking on a rental takes you to edit that rental. Sweet!

We’ve finally completed the dashboard! 🥳 🎉 🎊

We have a complete admin panel to manage rentals. We can see a list of customers, select a customer and view all his orders and lastly, we can create new rental entries or edit existing ones. Awesome!

For some extra credit, let's add some authentication.

Extra credit: Authentication

You must add some authentication to your apps, else anyone would be able to use it, even malicious individuals! Thankfully, adding authentication to our API and admin dashboard is not too difficult.

The first part of this section will focus on adding authentication to the Loopback API. You can skip this if you’ve been following along with your API. Next, we’ll look at implementing auth on the React-admin panel.

Securing the API

Loopback has various authentication strategies that we can implore to secure the API. We are going to be going with the JWT authentication strategy, mostly because it’s super easy to set up and is fully supported by React-admin.

Enough talk, let's get started by installing the JWT auth extension library and Validatorjs on the Loopback API server.

npm i --save @loopback/authentication @loopback/authentication-jwt @types/validator

Next, bind the authentication components to the application class in src/application.ts

// src/appliation.ts

// ----------- Add imports -------------
import {AuthenticationComponent} from '@loopback/authentication';
import {
  JWTAuthenticationComponent,
  SECURITY_SCHEME_SPEC,
  UserServiceBindings,
} from '@loopback/authentication-jwt';
import {MongoDataSource} from './datasources';
// ------------------------------------

export class TodoListApplication extends BootMixin(
  ServiceMixin(RepositoryMixin(RestApplication)),
) {
  constructor(options: ApplicationConfig = {}) {

    //…

    // ------ Add snippet at the bottom ---------

    // Mount authentication system
    this.component(AuthenticationComponent);
    // Mount jwt component
    this.component(JWTAuthenticationComponent);
    // Bind datasource
    this.dataSource(MongoDataSource, UserServiceBindings.DATASOURCE_NAME);
    // ------------- End of snippet -------------
  }
}

Great job! We now have a foundation for auth.

Authentication usually works by validating the credentials of the user attempting to sign in and allowing him to go through if valid credentials are supplied. Thus, we’ll then need to create a user resource to represent a user. For our purposes, a user only has an id and an email field.

Alright, let’s create the user model using the Loopback CLI. Answer the CLI prompts as usual

lb4 model

11-user model.png

We’ll also need to create a controller for the user resource that handles all authentication logic. You can use the CLI to generate an empty controller.

Note that this controller would need to be an empty controller and not a REST controller
lb4 controller
12-user controller.png

The generated empty controller file can be found in src/controllers/user.controller.ts. Copy the contents of the file linked here into your controller file. It contains all the authentication logic. You can find the file here

Visit the link above and copy its contents into the user.controller.ts file

Finally, we can secure the customer resource by adding the authentication strategy we just implemented to its controller. Here’s how to do it:

// src/controllers/order.controller.ts

// ---------- Add imports -------------
import {authenticate} from '@loopback/authentication';

// ------------------ Add auth decorator -----------
@authenticate('jwt') // <---- Apply the @authenticate decorator at the class level
export class CustomerController {
  //...
}

Do the same for the film and rental resources by adding the authentication strategy to their respective controller files.

And that’s it! Visiting the API playground on the browser http://localhost:4000/explorer/ you’ll notice we have a nice green Authorize button at the top of the page. We also now have signup and login routes to create user accounts and log in.

You’ll need to use this playground/explorer to create a new user

Now, let’s use this authentication on the React-admin dashboard.

Adding authentication to React-admin

Implementing authentication on the React-admin dashboard is fairly straightforward. We need an authProvider which is an object that contains methods for the authentication logic, and also a httpClient that adds the authorization header to every request made by the dashboard.

Create an Auth.js file in src/Auth.js that contains the authProvider method, and the httpClient function. Here’s what the content of the file should be

// src/Auth.js

export const httpClient = () => {
  const { token } = JSON.parse(localStorage.getItem('auth')) || {};
  return { Authorization: `Bearer ${token}` };
};

export const authProvider = {
  // authentication
  login: ({ username, password }) => {
    const request = new Request(
      process.env.REACT_APP_API_URL + '/users/login',
      {
        method: 'POST',
        body: JSON.stringify({ email: username, password }),
        headers: new Headers({ 'Content-Type': 'application/json' }),
      }
    );
    return fetch(request)
      .then((response) => {
        if (response.status < 200 || response.status >= 300) {
          throw new Error(response.statusText);
        }
        return response.json();
      })
      .then((auth) => {
        localStorage.setItem(
          'auth',
          JSON.stringify({ ...auth, fullName: username })
        );
      })
      .catch(() => {
        throw new Error('Network error');
      });
  },
  checkError: (error) => {
    const status = error.status;
    if (status === 401 || status === 403) {
      localStorage.removeItem('auth');
      return Promise.reject();
    }
    // other error code (404, 500, etc): no need to log out
    return Promise.resolve();
  },
  checkAuth: () =>
    localStorage.getItem('auth')
      ? Promise.resolve()
      : Promise.reject({ message: 'login required' }),
  logout: () => {
    localStorage.removeItem('auth');
    return Promise.resolve();
  },
  getIdentity: () => {
    try {
      const { id, fullName, avatar } = JSON.parse(localStorage.getItem('auth'));
      return Promise.resolve({ id, fullName, avatar });
    } catch (error) {
      return Promise.reject(error);
    }
  },
  getPermissions: (params) => Promise.resolve(),
};

Alright! Now let’s make use of the authProvider and httpClient in our app. Import authProvider and httpClient from ‘Auth.jsintoApp.jsand passhttpClientas a second parameter tolb4Provider. Then add an authProvider prop to theAdmincomponent and pass inauthProvider` as its value.

Simple and easy!

// ----------- Import Auth -------------
import { authProvider, httpClient } from './Auth';

//…

// ------------ Use httpClient and authProvider in the Admin component ---------
 //...

Save the files and head back to the browser and you’ll be greeted with a login screen. Fill in the email and password of your registered user and you’ll be taken to the customers’ table like before.

And that’s it! We now have a super-secured app! 💪

Deploy 🚀

We now have a fully functional admin dashboard with authentication. Lastly, I’ll like to walk you through deployment to your favourite cloud provider.

Since the API generated using Loopback is a standard Nodejs server, you can deploy your app to any Nodejs hosting provider i.e Heroku or Glitch. But note that you will need to move all packages under devDependencies to the dependencies section in your package.json file.

And for the React-admin dashboard, you can deploy it on any static hosting service i.e Netlify or Vercel. Don’t forget to replace the lb4Provider URL with that of your hosted backend.

Building a Discount Management Dashboard With Postgres
12
May
2021
Tutorial

Building a Discount Management Dashboard With Postgres

Building a Discount Management Dashboard With Postgres
Confidence Okoghenun
0
 minutes ↗
#
postgresql
#
javascript
#
dashboard
#
open-source
Tutorial


Businesses love loyalty programs. Loyalty programs are a proven and effective way to keep customers coming back to your business. The most simple loyalty program is offering discounts to your top quartile customers, so they keep coming back and get the best prices.

But if you're a small yet digitally savvy business, how do you set up and manage such a program to drive customer loyalty?

In this article, we will be building an admin dashboard to manage giving out discounts to loyal customers. Discounts will be assigned to customers based on their purchase history and will be available for use on their next purchase.

Completed Dashboard GIF

In this dashboard, we'll have a list of customers from where we can choose a customer we want to give a discount. On selecting a customer from the list, we can see all orders that have been made by that customer. We would also have a form that would allow us to give a percentage discount to the customer based on the total amount spent in the store by the user and set an expiration date for the discount.

You can check out the completed dashboard here 👈.

By the end of this article, you’d have learned how to build a Postgres admin panel using Appsmith. We’re going to cover core concepts such as

  • Connecting to the Postgres DB
  • Writing queries to perform CRUD operations on the database
  • Binding query data to widgets
  • Writing Javascript in Appsmith
  • Sharing your application and managing permissions

Now you’ve got a good overview of what we’re going to be building, so let’s get started!

Creating the Application

As I mentioned earlier, we’re going to be building the discount management dashboard using Appsmith. Appsmith is an open-source platform that lets you create powerful apps/dashboards/tools quickly using a simple drag and drop interface.

Head over to appsmith.com and sign in. We’re going to be creating a new application, so click on the create new button. Please do well to give this app a creative name. The best I could come up with is Discount Dashboard.

Connecting to Postgres

The bread and butter of Appsmith is that we can connect to various sources of data by using APIs or talking directly to the database. We’re going to be connecting the application directly to our Postgres database, eliminating the need for a middle-man API.

To connect to the database, click on the + icon to the right of DB Queries then + New Datasource and select PostgreSQL. On the next page give the datasource a name and fill in the connection details.

screenshot showing the database connection form
A little side note here: This application was built using the mock Postgres DB connected to every application by default. Please feel free to use this if you do not have a database of your own

Querying the Database

Since the application has been connected to the database, we can write queries to fetch data. The first query we’ll be writing would be to fetch users from the database.

Click on the + New Query button close to the datasource card to create a new query. Go ahead and give this query a name, let’s call it get_customers and it should be a select query:

SELECT * FROM public."users";

Clicking on the Run button will fetch us a table containing all customers we have on our database. Now we build a list from which we can choose a customer using the data returned from this query.

Binding data to widgets

We need to create a list of customers. Each item on the list will display relevant information about the customer such as the customers’ name, email, and picture. We’ll be using the brand new List widget to make this happen.

Head over to the widgets section, click on the + icon, and drag a list widget into the canvas. In the property pane of the list widget, rename the widget from List1 to customers_list. In the items section, delete the dummy data that was prefilled and bind in data from the get_customers query by referencing it using mustache template syntax shown below:

{{get_customers.data}}

Now we’ve connected the data from the customers’ query into the List widget. All we need to do is to use that data. To do so, we can reference the currentItem object within other widgets in the list item and use its data to build up the list item. Here’s a gif illustrating how to do it.

gif showing how to bind data to widgets within the list item

Awesome! We now have a list showing all customers.

Triggering Actions

Continuing with the requirement for the application, we’ll want to see a list of orders for the customer clicked on from the list. To do this we’ll need to run an action whenever an item is clicked on the list.

Under the actions section of the List widgets’ property pane, let’s go and add an action for the onListItemClick event. From the dropdown list, select Execute a DB Query and click on + Create Query.

The new query we’ll be creating will be fetching all orders made by the customer. So call this query get_orders and set the query to:

SELECT * FROM public."orders" WHERE "userId" = {{customers_list.selectedItem.id}};

Or we can write a slightly more complicated query that gets the product name for each order from the products table using the orderProductMap table:

SELECT o.*, p.* FROM public."orders" o
    left join "orderProductMap" op on o.id = op."orderId"
    left join products p on op."productId" = p.id
WHERE o."userId" = {{customers_list.selectedItem.id}};

Clicking on the run button will get the orders for the customer selected from the List widget. Now going back to the onListItemClick event, when the get_orders query is successful, we want to execute another database query to get discounts that have been given to the customer.

Go ahead and set up the query to get the customers’ discount. Your query should look like this:

SELECT * FROM public."discounts" WHERE "userId" = {{customers_list.selectedItem.id}};
image showing the actions section of the list widget property pane

Visualizing Data

At this point, we have a list of all customers, and clicking on a customer gets all orders made by that customer as well as the customers’ discount. It will be nice if we can neatly display the orders made by the customer.

We can neatly display the orders using a Table widget. Like we did with the List widget, go on and drag a Table widget into the canvas. You’ll also need to bind the data from the get_orders query into it and give it a nice name i.e orders_table. Your binding should look like this:

{{get_orders.data}}

Also, you can hide some columns from the Table widget so that we only have the relevant ones showing.

image showing data binding and column configuration for the Table widget

We also need to display the discount amount and expiration time from the get_discount query. A couple of Text widgets will be sufficient for this. I’ll leave you to figure it out but here’s my solution:

image showing discount amount and expiration display

Writing JavaScript in Appsmith

Finishing up with the dashboards’ requirement, we’ll need a form that will allow us to give a percentage discount to a customer based on the total amount they’ve spent on the store. We will also be able to set an expiration date for this discount.

To build this form, we’ll need a few new widgets. We will be making use of a Dropdown, Input, and Datepicker widget. We’ll also need a Button widget to submit the form and a Text widget to label the Datepicker widget.

I have mine arranged as shown below.

image showing discount form configuration

Alright, let’s move on to configuring the form.

To configure the Dropdown widget, give it a new i.e discount_dd and supply the JSON array shown below as its Options:

[
  {
    "label": "Clear Discount",
    "value": "0"
  },
  {
    "label": "10% Discount",
    "value": "10"
  },
  {
    "label": "20% Discount",
    "value": "20"
  },
  {
    "label": "30% Discount",
    "value": "30"
  },
  {
    "label": "40% Discount",
    "value": "40"
  },
  {
    "label": "50% Discount",
    "value": "50"
  }
]

The Input widget is where the magic happens. In this widget, we’ll need to sum all the amounts from the get_orders query and apply a discount based on the percentage selected in the Dropdown widget.

To do this we need to write multiline Js and this can be done with an IIFY(Immediately Invoked Function Expression). Set the Default Text of the input widget to:

{
{
  function(){
    const totalAmount = get_orders.data.reduce((acc, order) => (acc + order.orderAmount),0);
    return totalAmount * discount_dd.selectedOptionValue/100;
  }();
}
}

Thus, the value of this widget will be the calculated discount. It’s a good idea to make the Input widget disabled to prevent manually updating its value. Give this widget a name i.e calc_discount. Also, the Datepicker widget should be named expire_dp.

All we have to do now is to write a query that will save the discount when the Button widget is clicked on. To do this create a new query called save_discount having the below body:

INSERT INTO public."discounts" ("userId", "amount", "expiresAt")
VALUES ({{customers_list.selectedItem.id}}, {{calc_discount.text}}, '{{expire_dp.selectedDate}}')
ON CONFLICT ("userId") 
DO 
  UPDATE SET "amount" = {{calc_discount.text}}, "expiresAt" = '{{expire_dp.selectedDate}}'

The above query will insert or update the discount of the selected customer.

Lastly, we’ll need to run this query when the Button widget is clicked on. We’ll also need to re-fetch the get_discount query and show a message on the UI to let the admin know that the discount has been successfully applied.

To do this enable JavaScript on the onClick action of the Button widget by clicking on the JS button close to it. Then set its content to the code below:

{
{
  save_discount.run(() => {
    get_discount.run();
    showAlert('Discount Applied!','success');
  })
}
}

That’s it! We have a fully working discount management dashboard!

Sharing the application

Sharing the dashboard we just built is an easy thing to do on Appsmith. You can easily hand off the application to the management team or invite developers to work with you by using the share feature.

Click on the SHARE button at the top right of the page and you’ll be presented with a dialog to invite users to the app and set their roles or permissions.

And lastly, you can publish all changes we made to the application by clicking on the DEPLOY button. Sweet!

Wrapping off

That’s a wrap! If you found this article helpful, please do leave a Like ❤️. Feel free to share any concerns or questions you may have in the comments section. Also, we’d love you to check out our Github page here https://github.com/appsmithorg/appsmith.

Credits: Photo by Karolina Grabowska from Pexels

Building CRUD Apps with Firebase
27
May
2021
Tutorial

Building CRUD Apps with Firebase

Building CRUD Apps with Firebase
Confidence Okoghenun
0
 minutes ↗
#
apps
#
crud
#
firebase
Tutorial


Since its release back in 2012, Firebase has received lots of love from the developer community because it aims to remove the burdens many engineers have to deal with during development, including database, storage, authentication, hosting, and more.

One of its core products, the Firestore database, has been a game-changer in the database world because it allows highly flexible schemaless database access. But sometimes, it can be a bit challenging to work with, especially for new developers trying to build CRUD apps with Firestore.

Firestore and, by extension, Firestore is a great product, but it's only one side of the coin. However, to build production-grade applications, an interface is required for users to interact with and services provided by Firebase. Appsmith is the open-source UI framework that fills this gap if you're making something for your colleagues. Appsmith saves you the time of building UI and routers, focusing on the logic and the architecture of your app.

In this article, I will show you how to build CRUD applications using Firestore with Appsmith. We're going to cover the core concepts of Firestore, such as connecting to the database, reading data, and writing to the database by building a Todo app.

Here's what the finished application will look like:

gif show completed todo app

Awesome! Let’s start hacking away.

Connecting to Firestore

Firstly, we need to create an app on Appsmith. Go ahead and sign in to your Appsmith account and click on the + create new app button. You should then be taken to a new app where we can begin building our Todo app. You might also want to rename the app from Untitled Application to something like Todo app.

Now that we have a new app setup, we can go on to connect to Firestore. Click on the + button to the right of the DB Queries section, and then the new data source button. We’ll be connecting to Firestore, so select Firestore from the list of supported databases.

On the opened form, fill in your Firebase connection credentials. Note that your Project Id can be gotten from your project settings in the Firebase console. Also your Database URL is <your_project_id>.firebaseio.com.

image showing Firestore connection form

Lastly, you’ll need your Service Account Credentials. Go to the Service accounts tab in your project settings on Firebase. Click on the Generate new private key button. It’s going to download a JSON file, so copy its content and paste it in the Service Account Credentials input.

Save your connection and we’re good to go!

Reading data from Firestore

Now we have a connection to the database and we can begin to make use of it. Assume that we have a bunch of todos from the database with the following structure:

{
  "due": Date,
  "task": String,
  "created": Date
}

We can easily fetch todos from the database by writing a query. Click on the New Query button on the Firestore card, and let us write a query to get todos.

Give this query a nice name i.e _gettodos. Its Method will be set to Get Documents in Collection. For the collection path, fill in your collection path. And we can go ahead to set the Order By to ["-created"], doing this will sort the documents by their created date. Here’s my query:

image showing query to get todos

Click on the Run button and you’ll get an array of todos from your Firestore database.

Now we can go back to the canvas and display this data using a List widget. Head to the widgets section and drag in a List widget into the canvas. On its configuration menu (called Property pane), replace the content of the Items field with the bellow binding that pulls in data from the get_todos query into the widget:

{
{get_todos.data}
}
gif showing how to bind data to the list widget

You wouldn’t notice any change but rest assured that we’re now feeding data from the query into the widget. Now we can go on to display the todos on the List widget.

You can delete the Image widget in the list as we do not have images in our todos

Drag in a few Text widgets to display the todos text and due dates. For each of the respective Text widgets, use the following bindings in their Text property to display data: For the task field

{
{currentItem.task}
}

For the due date field. We’re using the built in moment library to format the due date

{
{moment(currentItem.due).fromNow()}
}
gif showing how to bind data to task and due date text wiegets

Great work! The app is coming to life!

Creating new Todos

Wouldn’t it be cool if we could create new todos? Yeah, let’s do that.

We’ll need a form for this. So, drag in a Modal widget into the canvas. You can close up the modal because will need a button on the UI to open it up. To do this, drag a Button widget into the canvas. On its property pane, set the onClick Action to showModal and the Modal Name to the modal we just created, that is Modal1.

Now clicking on the button should automatically open the modal. Sweet!

On the modal, we’ll need a few new widgets to capture the todo information. So go ahead and drag in an Input widget to collect the task info, and a Datepicker widget to collect the due date info. A screenshot of mine is shown below:

image show my form modal configuration
It is also important to rename the widgets so that we can easily access them later on. Here’s a gif showing how you can do that:
gif show how to update widget names

Awesome! Now we can go ahead to write the query to create a todo.

Like we did previously, go ahead and create a new query using the Firestore datasource we earlier configured. You can call your query _createtodo and set’s its Document Path to:

todo_app/{{Math.random().toString(36).substring(7)}}

What we have above generates a random string ID for the document. Then set its Body to grab data from the Input widgets we configured on the modal:

{
    "task": "{{task_input.text}}",
    "created": "{{moment().format()}}",
    "due": "{{due_datepicker.selectedDate}}"
}

Awesome! Now we can go back to write an onClick Action for the confirm button on the Modal widget. Enable the Javascript mode by click on the small JS button close to it and set it’s content to:

{
{create_todo.run(() => {
    get_todos.run();
    closeModal('Modal1');
})}
}
image showing the confirm button configuration

The script above will run the _createtodo query, after which it will update the list by running the _gettodos query and the close form modal.

Go ahead and give it a try. Fill in a task and set a due date, click on the button to see the magic happen!

Deleting a Todo

The last feature we’ll like to add to our app is the ability to delete a to-do that has been completed. We’ll need a few new tricks to do this.

Let’s go-ahead to write a delete query. Call this _deletetodo. Set this query’s Method to Delete Document then it’s “Document Path* to the following binding which will read the todo_path from the local store API on appsmith:

{{appsmith.store.todo_path}}

Now, let’s go and drag a Button widget into the list. For the button’s onClick Action, we need to store the current todos path in the local store and then run the delete_todo query. We’ll also need to update the list by running the get_todos query. Here’s what you need:

{{
    (() => {
      storeValue('todo_path', currentItem._ref.path);
      delete_todo.run(() => get_todos.run());
    })();
}}
image showing delete todo button configuration

Paste that in and give it a test run. Cool right?

Conclusion

We’ve been able to create a full blow CRUD todo Firebase app using Appsmith. Here a gif of the app in action:

gif showing completed app in use

Awesome! Now you can click on the DEPLOY button to deploy it and share the app with friends (be sure to update sharing permissions by clicking on the SHARE button).

If you found this helpful, show some love and leave a star on our GitHub repo https://github.com/appsmithorg/appsmith.

Building an Admin Panel with Django Admin in 2021
15
June
2021
Tutorial

Building an Admin Panel with Django Admin in 2021

Building an Admin Panel with Django Admin in 2021
Confidence Okoghenun
0
 minutes ↗
#
django
#
python3
#
python
#
admin
Tutorial


Admin panels are at the heart of every business operation, be it sales, analytics, user management, and so much more. That said, it isn't easy to find the right tools to help you build robust and scalable systems without spending too much time.

Django admin has been the go-to solution for many developers over the years. It provides a fast, easy-to-use, and highly maintainable framework for building full-stack applications and admin dashboards. These reasons make it a great choice to build admin panels.

To show you how to build admin panels with Django, we will build a dashboard for moderators to manage business reviews. All of this will be built on Postgres and the Public Yelp dataset.

Here’s a sneak peek of what we’ll be building:

Awesome right? Let’s get started!

Setting up a project

We start by setting up a project. A project is a collection of settings for an instance of Django, which includes a database and app configuration. A project may contain multiple apps, but we are only going to need just one.

To set up a project for our Yelp review dashboard, use your terminal and cd into a directory where you’d like to store the project and run the below command:

django-admin startproject yelp

When that’s done, cd into the yelp directory that was created and start up the server by running the command below:

python manage.py runserver
The server automatically watches the project for changes, so you don’t need to restart it manually

Creating an app

In Django, you can have multiple apps inside a project. This means that apps are modular and can be used in multiple projects.

Go ahead and create a reviews app that will hold the code for the dashboard by running the below command:

python manage.py startapp reviews

You’ll notice that a reviews directory has been created with a couple of files. This is where all the code will go.

Registering the app and database

Next, we’ll need to let Django know about the reviews app we just created. We do this by registering the app.

To register the app, open yelp/settings.py file and add reviews.apps.ReviewsConfig to the INSTALLED_APPS list. After adding this, INSTALLED_APPS should look like this:

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'reviews.apps.ReviewsConfig',
]

When that’s done, move on to configure your database connection in the DATABASES dictionary:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'your_database',
        'USER': 'your_database_user',
        'PASSWORD': 'your_database_password',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

Save the changes and run the below command so that Django can set up tables in the database for the app in the INSTALLED_APPS list:

python manage.py migrate

The last thing that needs to be done here is to modify the path of the admin dashboard in the yelp/urls.py file so that it is mounted on the root path instead of /admin. Update the urlpatterns list to:

python manage.py migrate

Creating models

With everything set up, we can continue with creating modes that describe the database layout. Models also show relationships between tables and can be used to specify validation and the widgets that Django will create for the dashboard UI.

We are going to need three models: one for businesses, users, and reviews. To create these, head to reviews/models.py and paste the below code:

from django.db import models
# Model for Businesses
class Business(models.Model):
    id = models.CharField(primary_key=True, max_length=100)
    name = models.CharField(max_length=100)
    address = models.CharField(max_length=100, null=True)
    city = models.CharField(max_length=100)
    state = models.CharField(max_length=100)
    stars = models.FloatField()
    categories = models.TextField(null=True)

    def __str__(self):
        return self.name

# Model for Users
class User(models.Model):
    id = models.CharField(primary_key=True, max_length=100)
    name = models.CharField(max_length=100)
    yelping_since = models.DateTimeField()

    def __str__(self):
        return self.name

# Options of the select widget on the UI
status_choice = (('approved', 'APPROVED'), ('pending',
                 'PENDING'), ('rejected', 'REJECTED'))

# Model for Reviews
class Review(models.Model):
    id = models.CharField(primary_key=True, max_length=100)
    status = models.CharField(
        max_length=10, choices=status_choice, default='pending')
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    business = models.ForeignKey(Business, on_delete=models.CASCADE)
    stars = models.IntegerField()
    text = models.TextField()
    date = models.DateTimeField()

    def __str__(self):
        return self.user.name

You’ll notice that each class represents a model of a table in the database. Each model has variables that represent the fields. The fields are annotated with their respective field types and validation/relationship where necessary.

Now let’s create migrations for these models. Run the below command to do this:

python manage.py makemigrations reviews

Then apply the migrations

python manage.py migrate

Taking a look at the database, you’ll notice that tables for reviews_business, reviews_user, and reviews_review have been created. At this point, you can import the yelp dataset.

Download the public Yelp dataset and import it into the reviews_business, reviews_user, and reviews_review tables respectively.

Taking the app for a spin

Are we ready to take the app for a spin? Not so fast. You’ll need to create an admin user to be able to view the site.

To create an admin user, run the following command and answer the prompt:

python manage.py createsuperuser

Once that’s done, start the server(if you previously closed it) and visit http://127.0.0.1:8000/:

python manage.py runserver
image showing admin dashboard

After logging in, you’ll notice that none of the models we created is showing up. Let’s fix this in the next section.

Displaying the reviews app

To make the models from the reviews app visible in the admin panel, you’ll need to register them in the reviews/admin.py file.

This is quite easy to do. Add the below code to your reviews/admin.py file:

from django.contrib import admin

from .models import Business, Review

admin.site.register(Review)
admin.site.register(Business)

Save the file and head back to the browser. Upon reloading, you should now see Business under the Review app.

image showing dashboard with review app

Easy as pie, right?

Play around with the dashboard to see the power of Django. But you’ll notice that the way data is displayed a little off, and we can’t see reviews for each business. Django gives us the room to improve user experience, so let’s do that.

Customizing the dashboard

We can make the reviews dashboard much better by adding some options to customize it.

Head to the reviews/admin.py and replace its content with the below code:

from django.contrib import admin
from .models import Business, Review


# A class that displays Reviews in a table form
class ReviewInline(admin.TabularInline):
    model = Review

    # Don't add any extra blank form for new Reviews
    extra = 0


# A class that lets us customize the Business list
class BusinessAdmin(admin.ModelAdmin):
    # Embed reviews in related businesses
    inlines = [ReviewInline]

    # Show the below properties in the Business list
    list_display = ('name', 'city', 'stars', 'categories')

    # Add filters for state and stars
    list_filter = ['stars', 'state']

    # Make the Business list searchable by name
    search_fields = ['name']

    # We don't want ids showing up
    exclude = ('id',)


admin.site.register(Business, BusinessAdmin)

Save the file and head to your browser. You’ll immediately notice that you have a much better dashboard experience.

image showing improved dashboard

We can further make the dashboard cleaner by removing the annoying AUTHENTICATION AND AUTHORIZATION app that shows up by default.

To do this, add these lines to your reviews/admin.py file:

# Add this to the top of your file
from django.contrib.auth.models import Group, User

# …previous code goes here

# Add this to the bottom of your file
admin.site.unregister(User)
admin.site.unregister(Group)

Save the file and reload your browser. Now we have a super clean functional Django reviews dashboard.

image of landing page with no auth

Conclusion

Now that the application is fully built, you can play around with it. This dashboard makes it easy for moderators to search for a business and approve reviews made by users.

Here’s everything in action:

And that’s a wrap!

If you found this article helpful, please leave a like and check out our awesome product for building internal tools appsmith.com