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.

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.

Sample Data

I use a simple Sales Order data here.

Sharperlight Form Designer

New Form

Let's start creating a new form.
I can start creating a new form with the popup menu,

or with New button.

The template list is displayed, and choose New - Blank.

Once the new form dialog is opened, input Code, Group and Title at least.

Designing Fields

I move to Fields tab and I can design fields one by one here.

Let’s start by creating fields using the Excel sheet as the base.

To create a field, begin by clicking the New icon.

SOCode
Code	SOCode
Caption	SO Code
Data Type	String
Mandatory	true
Is Unique	true
Default Value	_Expression( "SO"+Format( "yyMMddHHmmss",Now())) \

Order Date
Code	OrderDate
Caption	Order Date
Data Type	Date
Mandatory	true

Region
Code	Region
Caption	Region
Data Type	Code
Mandatory	true
Advanced - Value List	E=East C=Central W=West

Rep
Code	Rep
Caption	Rep
Data Type	String
Mandatory	true
Input Placeholder	Enter your name

Item
Code	Item
Caption	Item
Data Type	String
Mandatory	true

Units
Code	Units
Caption	Units
Data Type	Integer
Mandatory	true
Default Value	1

Unit Cost
Code	UnitCost
Caption	Unit Cost
Data Type	Decimal
Mandatory	true
Display Format	$#,##0.00

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

I also add two more fields to the form.
Add the following fields.

Attention
Code	Attention
Caption	Attention
Data Type	Color

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.

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.

Map the fields, set SO Code field as the driver. (Black Star Icon)

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.

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.

Choose System product and the Form designed, and select all fields to output.

Click Preview button to see the query result.

Go back to Query definition, and click OK to populate a table onto the sheet.

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.

Select System as Product.

Select Sample Sales Orders form.

A report is created like this.

Open the report with the web browser by choosing View Report from the right-click menu.
I see the default pormpt first.

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.

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.

Next, click the grid icon at the top-right corner of the report.
A Built-in grid editor is displayed.

Let's set a colour for the Attention field. I can choose a colour with the color picker.

I also upload a file with the Upload button for the attachment.

Then save the changes.

Re-submit the main report, and I can now see the change I made.

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.