【Datamodel】Try using Sharperlight XERO Datamodel

# Introduction #
I try to use Sharperlight XERO datamodel to sync data from [XERO Accounting Software](https://www.xero.com/).
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f794f913-3ad0-e410-796d-3fa91e1e3bfc.png)
What I need are;
+ [XERO Developer Account](https://www.xero.com/au/signup/developers/)
+ [XERO App](https://developer.xero.com/app/manage/) (Client ID, Client Secret and Redirect URI)
+ [Sharperlight](https://www.sharperlight.com/builds-released/)
+ [Sharperlight XERO Datamodel](https://www.sharperlight.com/datamodels/)
+ Micorosoft SQL Database (SQL Express is also ok)
+ [Setup Guide](https://www.sharperlight.com/datamodels/)

I think that's all so let's start setting up one by one.

# Setup #
### XERO Developer Account ###
Start from [here](https://developer.xero.com/)
I sign up as I don’t have a developer account. If you have the one, please just login with your account.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/785a105e-3633-6383-342c-2090f5afdf35.png)

### XERO App ###
When I login to my account, I am taken to this page. I click `New app` button to start creating my own XERO application.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c469dccb-c315-926d-a5c1-3a71d01ee6da.png)
The page shown below is displayed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/979645a9-6da4-b8b8-73c1-fdb8e50f3e4e.png)
I set **App name**, **Company or application URL** and **Redirect URI**.
Redirect URI I entered is used when accessing to XERO API later so take a memo of it.
Click `Create app` button to create it.

I am taken to this page.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/085e7cd4-5550-3cb3-8732-ac72f6b98c2c.png)
I can see what I entered when I created this App with **App details** from the menu on the left.  
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d07b00a9-c5d8-371e-4bce-cb522a313ae0.png)

I move to **Configuration** I can get all information for accessing to the API from here.
They are **Redirect URI**, **Client ID** and **Client Secret** you can generate.
We need to keep those 3 items in a safe place for further use. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/afe89ff3-25b5-3c78-64bf-322c632df26b.png)

### Sharperlight ###
I have Sharperlight installed on my PC so skip it.
FYI: [Installation – Quick Guide](https://www.sharperlight.com/installation/2020/05/20/quick-installation-guide/)

### Sharperlight XERO Datamodel ###
This is the core object required I need to pull data from XERO API with Sharperlight.
I can download **SLXERO.meta** from [here](https://www.sharperlight.com/datamodels/)

#### Installation of the datamodel ####
Go for Sharperlight Application Menu, and select `Datamodel Installer`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2e60bfa8-cc0a-b47d-bde0-8461d61ef5d6.png)
Once it is started, use `Add` button and browse the downloaded datamodel meta file to import it.
Click `Finish` button to close. It kicks in for compiling the datamodel.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3884a02a-681e-58d0-3513-28e1b059f1e2.png)

### Microsoft SQL Database ###
Sharperlight System database has to be in Microsoft SQL Server so it should be already there as I have Sharperlight installed already.
Let's open SQL Management Studio and go for the database server.
Create an empty database for XERO like this. The database name is whatever you want. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/38d66da4-ad50-0121-893b-21abcf58fb82.png)

#### Making the connection between Sharperlight and the local XERO database ####
Sharperlight will communicate with the local XERO database via the XERO datamodel.

Open Sharperlight **Client Setup**.
Configure the database connection details for XERO datamodel.
Make sure the details are right with `Test` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e64e7e26-2718-6178-c5ac-635ee28e631c.png)

#### Making the connection between Sharperlight and XERO API ####
Sharperlight will also communicate with the XERO API via the XERO datamodel.

::: note info
When you have e.g. 5 organisations, you have to repeat the following steps in this section 5 times to register all 5 organisations to your XERO App.
:::
Open Sharperlight **Client Setup**.
Choose the database connection for XERO Accounting Software.
Click `Setup` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6afb76b6-5dbc-8e27-3e54-b7f4d2dfaf2c.png)

The Setup application UI is opened like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3f1a02e9-9e47-7b74-4c7f-1f8361927590.png)

Set **Client ID**, **Client Secret** and **Redirect URI** to their textboxes. When those three textboxes are filled in, `Get User Authorized` button gets enabled.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fb7b1d5b-a10d-2122-0451-18af7a636239.png)

Click `Get User Authorized` button and I will be taken to the XERO login page.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/595ac181-645f-d6aa-b0bf-d55213f04352.png)
Once I am authorized to login, I am taken to this page where I can choose which organisation I can add to my app.
I have only one (Demp Organisation) at this time, so it is already selected so click `Allow access` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/456dc51f-a6be-1874-c6e4-28c798cf7381.png)
I am taken to this page next. Copy the whole URL address to the clipboard.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/33b9c7e7-21b9-5290-c80d-71c4a150f397.png)
Go back to XERO Setup UI and paste the URL address to the box at Step 2.
Then click `Get Access Token / Organisations`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f61472ab-a2da-0a77-4b73-da9fc22d5140.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f8b11ec1-197a-c788-ab16-673bab492c11.png)

Click `Close` to finish, and also click `OK` to close **Client Setup**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c892946d-2736-0349-2533-22350fb91a20.png)

I think the setup has all done here.

# Synchronisation #
Let's try to sync data with XERO API.
### Sharperlight Service ###
Open Windows Service Manager.
Choose Sharperlight Service, and click `Restart` or `Start`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/284ef2da-25b6-0551-cba2-07a39d70c06b.png)

### Sharperlight XERO Management Dashpage ###
Open Sharperlight **Solutions** from the Sharperlight Application Menu.
Right-click on `Homepage` under `XERO Account Software`, and click `Execute`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3a6d0a3e-1e3d-0777-4667-05b94d9ef384.png)
Then I am taken to the homepage.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9c79c1f4-37d9-ac01-08c8-0dbbec4d67d7.png)

:::note warn
There are some restrictions to execute some menu items on this page without Sharperlight license.
:::

#### Syncing Data ####
Click `Synchronisation on Demand` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/07691c97-9953-c06a-861c-1e07d6395705.png)
I am taken to this page.
Click `Sync` button of **Sync All** though there is only one organisation.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/932bddd0-57ab-2b6a-7a0f-27cc39e4f3a5.png)
Click `OK` on the confirmation dialog.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/957dfdd7-6c6a-3d57-0786-8fbdc9297511.png)
Then the Sync process is kicked in.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ccc9b8a5-e09f-9e71-a29b-8a8d6d0d68e9.png)
When the process is finished, I can see the its finish time and duration.
Click the left-arrow icon to go back to the home page.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/41a1d207-c39a-dadc-a4ee-256c599cdc1d.png)
I can see the total record number for each table on the home page.
I am also noticed that there is no error as Error count shows 0.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/573b5f49-d536-aa52-d742-9def90c2976d.png)

I can now make a query to see the data.
Open **Query Bulder** from the Sharperlight Application Menu.
Create the following query.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f5b3cb08-6e13-8bcf-d17f-bde8824feae2.png)
Preview it, I can see data which is sysnced with XERO API.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0073e185-7726-8dbe-245e-83f9d32de855.png)

# Conclusion #
It is interesting solution with the cloud accounting software. We can make a local copy of data set and can create any types of reports over it.
It supports **Delta Updates** so once all data synced initially, it syncs just new or updated data only from then.
By using Sharperlight Scheduler, we can sync data every day at the time we decide.