【Form Designer】Creating a simple web based solution with minimal effort

# Introduction #
I’m using Sharperlight Form Designer to design the fields of a table for capturing data and generating reports both on the web and in Excel.

Currently, I'm focusing on capturing data in Excel with Sharperlight Form Designer. While Excel is a great tool for data capture, it’s not easily shareable within the organization. Therefore, I plan to convert this into a form design. Once that’s complete, I’ll be able to enter data via the web and generate reports in both Excel and on the web.

::: note
**Creating table and its fields in a database, capturing data and transferring data to the table, preparing the entry form on the web, they are all done by Sharperlight Form Deisgner.**  
:::
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f055bfaa-f85e-dd01-12ce-770e3931fc71.png)

# Sample Data #
I use a simple Sales Order data here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/088b9c82-ac01-eba9-2346-f4fcc3654c2e.png)

# Sharperlight Form Designer #
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f635fdae-0f1d-3b4c-4c90-065640a02cff.png)

## New Form ##
Let's start creating a new form.
I can start creating a new form with the popup menu,
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/419f30d7-e22d-825b-b473-ff16c9c45e58.png)
 or with `New` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b19612f7-ee52-3669-80d7-63f1760cafab.png)
The template list is displayed, and choose `New - Blank`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ffbb0347-1f6e-d66f-5a82-b243b097a64f.png)
Once the new form dialog is opened, input **Code**, **Group** and **Title** at least. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7c3e5041-ae0c-43a7-0559-37e49f183d55.png)

## Designing Fields ##
I move to `Fields` tab and I can design fields one by one here.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a6bdee1a-7502-1116-f12f-c4a448e77ade.png)
Let’s start by creating fields using the Excel sheet as the base.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/19ad22ce-f78b-f10c-a4cf-7a265ce5f080.png)
To create a field, begin by clicking the New icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6f21a2c9-b0cc-fe97-f25b-6def9259b40d.png)

|**SOCode** |  |
|---|---|
|Code |*SOCode*  |
|Caption |*SO Code*  |
|Data Type |*String* |
|Mandatory |*true* |
|Is Unique |*true* |
|Default Value |*_Expression( "SO"+Format( "yyMMddHHmmss",Now()))* \
<br>

|**Order Date** |  |
|---|---|
|Code |*OrderDate*  |
|Caption |*Order Date*  |
|Data Type |*Date* |
|Mandatory |*true* |
<br>

|**Region** |  |
|---|---|
|Code |*Region* |
|Caption |*Region* |
|Data Type |*Code* |
|Mandatory |*true* |
|Advanced - Value List|E=East<br>C=Central<br>W=West|
<br>

|**Rep** |  |
|---|---|
|Code |*Rep* |
|Caption |*Rep* |
|Data Type |*String* |
|Mandatory |*true* |
|Input Placeholder|*Enter your name*|
<br>

|**Item** |  |
|---|---|
|Code |*Item* |
|Caption |*Item* |
|Data Type |*String* |
|Mandatory |*true* |
<br>

|**Units** |  |
|---|---|
|Code |*Units* |
|Caption |*Units* |
|Data Type |*Integer* |
|Mandatory |*true* |
|Default Value |*1* |
<br>

|**Unit Cost** |  |
|---|---|
|Code |*UnitCost* |
|Caption |*Unit Cost* |
|Data Type |*Decimal* |
|Mandatory |*true* |
|Display Format |*$#,##0.00* |
<br>

|**Total** |  |
|---|---|
|Code |*Total* |
|Caption |*Total* |
|Data Type |*Decimal* |
|Mandatory |*true* |
|Display Format |*$#,##0.00* |
|Advanced - Is Calculated|*([Units] * [UnitCost])* |
<br>

I also add two more fields to the form.
Add the following fields.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c14cde6d-47d4-5a42-1130-9c4a921d457c.png)

|**Attention** |  |
|---|---|
|Code |*Attention* |
|Caption |*Attention* |
|Data Type |*Color* |
<br>

|**Attachment** |  |
|---|---|
|Code |*Attachment* |
|Caption |*Attachment* |
|Data Type |*File* |

Click `Apply` to save the definition, and click `View` to show the form generated on the browser.
Please make sure the Sharperlight service is running.

An web input form is created at the same time as I design the fields that will hold the data.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4e707b99-060c-3bed-da24-cf7ad3101373.png)

# Transferring data from Excel #
Let's insert some data to the fields. I can input data one by one with the web form but I do bulk insert here.

Open the Excel sheet containing the Sample Sales Order data.
Login to Sharperlight.
Go for `Writeback` option.
Choose **System** product where the form is saved as a table.
Select the form I just designed **Simple Sales Orders**. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/30c499ac-248e-86a1-ff9a-89c520e6df26.png)

Map the fields, set **SO Code** field as the `driver`. (Black Star Icon)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a4e8bbfa-6e45-f2a6-d678-8dc2a91889f6.png)
Click `Preview` to check it.
Give a name for the writeback definition. I put **SO** this time.
Click `Execute` to push data to the table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e301bccc-f746-aaab-7696-621609b528fd.png)

# Viewing data in the form #
Now I check the data transferred with Query Builder.
Create a new sheet.
Select Cell B5, click Table formula icon to open query builder.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6622e9b4-dbb1-af34-16e2-126715af124a.png)
Choose **System** product and the **Form** designed, and select all fields to output.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/69a34161-e8a2-c7b2-10a2-2907601786ff.png)
Click `Preview` button to see the query result.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e6e70cbb-e5ec-3a25-9700-7da929bacad2.png)
Go back to Query definition, and click `OK` to populate a table onto the sheet.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/85da8622-e2f9-b211-69f2-b07f751bc2c6.png)

# Managing data with an integrated web report #
Open Sharperlight **Publisher** to create an integrated web report.
From the right-click menu, choose **Other -> Create Table Reports**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ee4e95e3-ff9a-3716-5d9d-74c5cb7b6f5e.png)
Select **System** as Product.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a342301a-98fc-3fc1-96cb-a962a583853e.png)
Select **Sample Sales Orders** form.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/077b23fe-50bb-ab77-5d01-a30419874dd2.png)
A report is created like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ffa34d4e-3432-e71c-a8c7-f7dbd1b6775c.png)
Open the report with the web browser by choosing **View Report** from the right-click menu. 
I see the default pormpt first.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/13338f5f-00f2-41b6-08a1-fc4bee50fb88.png)
Click `Submit` button to show the table.
This is a simple web report.
However, we can edit the data with a form, or in a grid view.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/cb40ea4e-3858-5ef2-41c5-c902bca5d4c5.png)
Click the icon shown in the first column of a row.
The form is displayed like this, and I can update or delete the record.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f0f88cbb-8b2e-e212-4f37-da4c1c204eb4.png)

Next, click the grid icon at the top-right corner of the report.
A Built-in grid editor is displayed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/934fdf44-40b8-6a1b-6a99-17f1acb1a004.png)

Let's set a colour for the **Attention** field. I can choose a colour with the color picker. 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e4a1c307-4b30-2713-e636-0ee263daaecc.png)
I also upload a file with the `Upload` button for the attachment.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/de20373a-ba83-ec08-f5e1-f3deb5657158.png)
Then save the changes.

Re-submit the main report, and I can now see the change I made.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f74d70b0-7372-1af9-37b3-a57a3d519b84.png)


# Conclusion #
This is just a part of the functionalities of Form Designer. We can still create simple web-based solutions with minimal effort and deploy them to the intranet or internet via the Sharperlight service.