Today, the need for automation is increasing. The foundation of automation is machine-to-machine communication. Today this is achievable through API integrations (today it is the de facto industry standard). Transferring data from one system to another works through the ETL process (Extract – Transform – Load). It is a complex process and requires experienced software developers’ engagement. It often happened that the cost of integration was much higher than the cost of manual data entry (it was especially non-profitable for SMBs). But now in ERPAG, there is a simple and effective DIY solution (Do it yourself).

In this blog, we will explain step-by-step how to create the automatic process of receiving bills and records of charging the electric vehicle of the Tesla brand.


The first step – Document header setup

They say – even the greatest journey begins with the first step. First, we need to create a Custom Document where we will receive data from the API. You can integrate, of course, directly (Tesla Invoice > Bill), but one of the goals of ERPAG is to be the concentrator of all changes. This way, we have additional data that we can analyze efficiently.

erpag custom documents

In the Automatization – Customization > Database menu, you have the “Custom documents” option. Here we will create a new document, with a description and we will include one more table, in addition to the document header. For example, a Sales Order has a document header (document number, customer information, etc) and a table with details (items, quantity, price, etc).

erpag create custom document

We will also include the possibility of document attachments (pdf files), and create descriptions and types of document header fields.

The field setup is identical to our custom field setup. For more information regarding custom fields, you can check this blog:

Every vehicle has a VIN (Vehicle Identification Number) and that information is important for us to know for which vehicle was the transaction for. For human-readable data, we will enter the “Plate number” and “Fixed asset” fields. We use the Fixed asset field to link the transaction with the fixed assets codebook (we assume that the vehicle is our property).

custom document fields

We will define the “Paid” field, which will be a “list” type.

custom document

And we will include three values “Paid”, “Unpaid” i “Free”.

custom document options

Custom documents have the possibility of conditional formatting.

conditional formatting in custom tables
conditional formatting

You can find more information regarding conditional formatting on this blog:


Defining Table 1. in the Custom Document

As we mentioned, a custom document can contain one or more tables in addition to the header. We will define the Caption (name) of Table 1 and table fields.

define table in custom document

There is a special type of “formula” field. By clicking on the info icon, we get a pop-up to enter the formula.

add formula in custom table

In our example, the formula will be the product of quantity and price, rounded to two decimal places. And we will add the summarized value in the table.

You can find more information about defining formulas on this blog:


The main menu and screen design

After saving the custom document setup, the custom document will appear in the main menu under Automatization / Customization.

custom document

Note: Each user can separately add any option from the menu as favorites.

When setting up a custom document, by selecting users in the “User scope” and “Read only” options, we determine which users have access, that is, which users will have read-only privileges.

user scope and privileges in custom document

Note: Custom document will not appear on the menu for users which are not selected in “user scope”

Initially, there are no entered documents. After you click on the create button, a new blank document will be created.

add new custom document

The first eye-catcher is that the fields are a little bit scattered. Of course, each user can set the position and size of the fields independently. But for more detailed settings, you can use the “Size” and “Group” columns in the custom document setup.

custom document setup

You can use ERPAG on different devices with different resolutions. So the fields can hold different values! The operator who is setting up the custom document probably assumes what the field size should be. For example, if the “plate number” field consists of 7 or 8 digits, that field should occupy less space on the screen than the name of the fixed asset itself.

The “Group” field is for separating data logically in a specific panel or grouping them in a table.

Now the input form looks much more neater and more organized.

custom table

Quick add in Custom Document

When you need to add a new document or a new row in the custom document table, you will find the Quick add option very useful.

quick add in custom document

This option enables data entry directly in the pop-up window. With this option, we indicate where we can activate the option. By selecting ‘menu’ we enable the document creation from the main menu or from the inventory menu on the mobile app.

quick add custom document

Entry from the Quick Add pop-up option.

quick add pop up

If there is a need not to display some fields, or they should be read-only, we can specify “User scope” and “Read-only” for each field or column in the custom document.

scopes in custom document

Now quick add (and manual document entry) will be adjusted to the new parameters.

new quick add option

Adding logic in the Custom Document

For testing purposes, we will enter a couple of items in the “Fees” table. The first thing that we notice is that the value of the document (the amount field in the document header) is not filled with the sum of the Amount column in the “Fees” table.

value of custom document

Different types of documents have different logic. The solution to that problem is Blockly script support in ERPAG.

Note: Blockly script is a Google VPL (visual programming language). You can find more examples and instructions on our YouTube channel: https://www.youtube.com/watch?v=sfp56eN_BSo&list=PL1i-kfzO49WlFqMtyzpxymIpeAe7rkeo_

blockly script

Just connect two blocks and set the status to “read-only” for that field.

amount read only

And every time you save the document, you will execute the blockly script and populate the amount automatically.

amount in custom document

Print / PDF export Custom Document

By default, you can print the custom document or export it to PDF as a screen (WYSIWYG browser print). But also, you can customize every custom document for printing through the Report Designer options.

custom document report designer

By clicking on the Add new option, the report designer will activate the wizard and format the printout of all fields/columns.

wizard in report designer

If you activate wizard manually, you can select fields/columns that you want to generate with a wizard.

generate fields and columns

After you create it in the Report Designer, the report will be added as a sub-menu for printing.

printing menu

You can find more information on Report Designer use on the following link:


Connecting Tesla fleet API

Previously, we defined the custom document for manual entry/input (human to machine). Now, we will cover creating an API integration (machine to machine). To access an API service, you have to go through authorization process (to know who accesses which resources).

To make this process easier in ERPAG there is a special option – “API Authorizations”.

api authorizations

There are several standards for API authorization. The following are implemented in this option:

  • OAuth 2.0
  • Basic Authorization
  • Bearer Token
  • API Key

The Tesla Fleet API uses the OAuth 2.0 type so we will select that one.

select api authorization

When you open the form, due to the complexity of the process, you probably get the real “what now ?!” effect. Fortunately, we at ERPAG have created a wizard template (for the most frequently used API OAuth 2.0 authorizations) in which you only need to enter a few data.

wizard template api authorization

For Tesla, we only have to enter “Client ID” and “Client Secret”.

client id and client secret

On the popup entry, we have an option to click on the link with detailed instructions on how to get to the necessary parameters. This is the link:

Note: Note that various services often change the way authorization is performed. It may happen that the step-by-step instructions written now are not up-to-date in some future period.

And our form will be populated with all required parameters.

filled in api authorization form

You have to pay attention to each service’s “Scope”, ie. what data we require access to. For some specific data, the portal of the API service provider requires special verification.

After entering the parameter, it is necessary to request “a new Access Token”.

get new access token

Tesla login form, after authorization

In the browser, you will get to the Tesla login form where you have to enter your credentials (it may require two-factor authorization).

login to tesla

Note: If the login form does not appear, but an error with the text “We don’t recognize this redirect URI/URL. Please use the redirect URI/URL found in your app details.” it means that something is not configured properly in the Tesla Fleet API portal. You might have skipped a step in the step-by-step instructions.

This operation is done only initially. If there are major changes in the API or the user’s Tesla account itself (e.g. country change), it will be necessary to re-authorize.

If you successfully authorize, the status will be “Authorized”.

authorized

The “Test” option can be useful, where we can directly test a request to the API service.

For our test, we will enter the URL with the charging history:

https://fleet-api.prd.na.vn.cloud.tesla.com/api/1/dx/charging/history

Note: The URL for the Tesla fleet API is divided by region. So if the link above doesn’t work, you have to select the appropriate link for your region. You can find more information on the Tesla developer site: https://developer.tesla.com/docs/fleet-api?shell#setup

test url

If we have a response in JSON text format, it means that we have successfully authorized it.

json text format

The first API call

API integrations seem quite complicated, but ERPAG Smart API drastically simplifies the complicated integration process. It’s quite simple to do with specially designed blocks in Blockly Scripts. In our example, we will activate the ADD new BOT script in the custom document setup.

ADD NEW BOT SCRIPT

Go to BOT script, and create a SETUP in the “Parameters” section:

setup in blockly

We will define “Return value” to be Text type. We have to do this, in order to get a response on our screen.

parameters blockly

Then just drag and drop “Return Value” block.

blockly script

And connect that block with “HTTP – Get” block from the API menu.

blockly script

We enter the URL (same as on the test option) and select that the block uses the authorization of “Tesla” name (previously defined in API Authorization).

tesla authorization

And that’s it! Everything is ready for the first API call. The BOT script for custom document is activated from the custom document list.

the first api call

After activating, the pop-up in the browser will display the response from the API service. In our example, the response is in raw JSON text.

json text response

Extracting data

Most API call responses are in XML or JSON text format. Today, the JSON format prevails (https://en.wikipedia.org/wiki/JSON).

Note: In order to successfully extract data, you need to know the JSON structure well.

The next thing we’ll explain is the interaction through JSON data via Blockly script. First, we’ll create an “api_response” variable and connect it (add a value) to the API call (Http – Get block).

blockly script

This way, we avoid multiple identical calls. We simply make one call and load the result into a variable from which we will extract the data.

In the “Json” menu, there are several types of blocks with which we can extract data via the path name.

blockly script bot script

In our example, we use the “List from JSON variable” path “data” block, which we iterate through the “for each item” block.

blockly script

We add the extracted data from the block “Text from JSON variable”, path “siteLocationName” to the variable list “site_locations”.

blockly script

Return value is now a text, separated by comma, which represents the list of locations.

location list

Transform data

We need to transform the extracted data, ie. in our case, to input them in the created custom document. We also do this via the Blockly script, by using blocks in the Database menu.

blockly script

In “for each item” block, we will add blocks “Create/New” and “Save” for our custom document.

bockly script

For the corresponding field, we will set up an adequate block where we extract data from the JSON variable (ie Tesla fleet API response).

Note: When importing data through the API service, we need to build in logic to prevent re-importing the same data. i.e. if you repeatedly activate the import (BOT script execute) from the above image, each time new documents would be written in the database.

There are two methods to prevent duplicate import. One is to request only new documents through the API calls (status or time range from the last import date). Or, to have information indicating that the document has already been imported. We suggest the second solution because the Tesla API itself has a unique id called “sessionId”.

Custom document settings

In custom document settings, we will open a new field with “Hidden” status.

custom document

Through the BOT script, we will enter the unique data from the JSON response into that field. Before the data entry, we will check whether a document with such an ID exists in our database.

blockly script

We achieve this by inserting the list of all “sessionId” documents from our database in one variable (indicated by the red rectangle in the image above). And, by iterating through the JSON variable, we will check if the ID is in that list. This is indicated with a red arrow in the picture above. If it is not found, only then we will enter the information.

Note: This example is not really a good programming practice because we are loading all data. According to good practice data transmission should be limited with, for example, a date range. But due to the simplicity of the example, we made it the simplest.

Activating the BOT script

After activating the BOT script from the document list, the list will be populated.

document list

By opening the document, we will see the entered data. The “Fees” data remains empty, as we did not do it in our BOT script.

custom document

That is the second iteration level. So it is necessary to create an additional loop (for each).

blockly script

We can delete previously transferred documents via bulk action and reactivate the BOT script.

Now we have received the “Fees” data and document value.

fees in custom document

If you remember, in the custom document setup, we enabled attachments. Tesla Fleet API can download invoices in pdf format. For that we need another loop (for each) and another API call.

The call is via this URL, except that the {id} is the “contentId” data from the JSON response.

https://fleet-api.prd.na.vn.cloud.tesla.com/api/1/dx/charging/invoice/{id}

Note: URL for Tesla fleet API is split by region. So if the above link doesn’t work, you have to select the appropriate one for your region. You can find more information on the Tesla developer website: https://developer.tesla.com/docs/fleet-api?shell#setup

bot script

With a new loop, we go through the list of invoices, and again call the API server where we built the URL with the parameter “contentID” in the URL address. We assigned the response from the call to the variable “api_invoice”.

Using the “Add attachment” block, we inserted an attachment into the document, i.e. file name and data that we downloaded from the mentioned variable. The attachment data must be in encode in base64 format.

Note: Every response that is a file type (eg .pdf), “http get” block will return encoded in base64 format.

Now the attachment will have a bill in PDF format.

pdf attachment

Connecting with ERPAG entities

In the custom document setup, we added fields “plate number” and “fixed asset”

custom document fields

These data should be filled in/connected with fixed assets item type. Go to custom field setup, Item type, we will create two new fields “VIN” and “Plate number.

custom fields

In item, we will add appropriate information.

item custom fields

In the BOT script, in the menu, we will allow access to Database blocks, for item manipulation.

bot script

For search, we will use block “find first”. The result will be added to variables, and use them in SET document header block.

bot script

Now, the BOT script will fill in the missing data.

bot script filling in data

Schedule execution

ERPAG has great automation capabilities. To avoid manually activating the BOT script every time to download new data via the API service, each BOT script has the option of setting “Schedule execution”.

schedule execution in bot script

In our example, we will set the BOT script to be executed daily at 07:00 am. Before the working day starts, the data will already be imported to ERPAG.

Note: In order to use the “Schedule execution” option, you have to specify a user (user type is API). The script will be activated on behalf of this user!

Start NOW

Read More

Related Posts

Conditional formatting in ERPAG

Conditional formatting in ERPAG

What is conditional formatting? Conditional formatting is a feature commonly found in spreadsheet software, such as Microsoft Excel, Google Sheets, and similar applications. It allows users to automatically apply formatting—such as font color, background color, font...

read more
JSON / XML Designer

JSON / XML Designer

JSON/XML Designer in ERPAG is a visual tool that will allow users to transform data from the ERPAG database into JSON or XML format. This can be further used for future integration with API services. This tool provides a user-friendly interface where users can define...

read more
Change log 05/29/2023

Change log 05/29/2023

The biggest change we developed is Automatization and Customization. This change is a huge milestone for us, and we will publish individual instructions for using the new features in the upcoming period. This module is divided into individual wholes: JSON Designer -...

read more