EASA Help Pages

User Tools

Site Tools

You are here: EASA 6.1 » Author Guide » Author a simple Excel Client/EDC EASAP

Sidebar

About EASA | Contact

Contents

Table of Contents

Author a simple Excel Client/EDC EASAP

Guide Section: Excel Desktop Client
Overview | Get started with EDC | Simple EDC Example | EDC template | EDC reference

An Excel Client interface is launched from within an EDC EASAP.

CASE VIEWER is a GUI object on the Tree with visual and functional similarities to a DATA TABLE but specialized for the search, retrieval and selection of a database 'case' record, and subsequent launch of an Excel Client.

  • CASE VIEWER may have a child FILTER to narrow a search
  • A temporary Excel Client instance is opened with data from the cases table
  • Cells may be modified and new values propagated
  • After an 'EASA Ribbon > Save' all the named ranges are written back to the cases table

This schematic details the Excel Desktop Client architecture,

This example provides a concrete introduction to an Excel Desktop Client EASAP.

For an experienced Author we provide streamlined steps, see Build an EDC EASAP from a template

Complete parts A. - I. to build the Mortgage Calculator EDC EASAP - 60min total:

A. Create an EDC EASAP and its database schema - 5min
B. Search and select with CASE VIEWER - 5min
C. Create and modify a case with DATABASE TO EXCEL DESKTOP - 10min
D. Run the example - 5min
E. Complete the lifecycle of a case - 10min
F. Activate a spreadsheet's locked-mode for Excel Client sessions - 5min
G. FILTER results based on case status and term length - 10min
H. Display the number of cases which meet FILTER criteria - 5min
I. Only enable 'modify' and 'delete' buttons when a case is selected - 5min

Mortgage Calculator EDC EASAP

In this example we use the Mortgage Calculator spreadsheet to calculate a loan repayment schedule.

We use the terms 'case' and 'case record' to signify the particular values of all of the Excel named ranges at the beginning or end of some analysis using an Excel spreadsheet.

We do not lose any generality by calling this data a 'case' or a 'case record'.

In this example a case represents the values of the named ranges (inputs and outputs) for a particular mortgage as defined within a mortgage calculator spreadsheet.

The existing spreadsheet contains the default case.

We will:

  1. Launch the spreadsheet as an Excel Client via an EDC EASAP's
    • CASE VIEWER
    • DATABASE TO EXCEL DESKTOP ACTION
  2. Modify the loan amount and repayment period on the spreadsheet
  3. Save the new spreadsheet data as new case in a database
    (Select 'EASA Ribbon > Save' from within the Excel Client)

The User may create, view, modify and save a case almost entirely within a familiar Excel spreadsheet interface.

The EASA Ribbon will be installed automatically the first time an Excel Client instance is launched.

The completed Mortgage Calculator (Excel Desktop Client) EASAP may be downloaded and imported
https://help.myeasa.com/resource/easap/6_1/MortgageCalculatorEDC.easap

The steps below generate an EDC EASAP that will enable a User to generate database-persistent mortgage loan records from within an Excel Client session.

A. Create an EDC EASAP and its database schema

1. With a browser download the Mortgage Calculator spreadsheet and an image file:

2. Create a new EASAP.

  • Navigate to the EASA Server
  • Login as an Author
  • Under: EASA > Applications > New
  • Click: Create
  • Click: EASAP Builder link
  • Download then open: atos.easa
    …The New Application Wizard will launch
  • Optionally complete the fields
    …We have named ours Mortgage Calculator (EDC)
  • For Image: click the folder icon and browse to: mort_calc_edc.jpg
  • Set Category: Training
  • Click: Next
  • Select: Use EASAP Builder I want to create my EASAP manually.
  • Select: Finish
    …The EASAP Builder will open

We recommend resizing this browser window to occupy half of the screen and resizing the EASAP Builder window to occupy the remaining space to lower the chance of typo or a missed step.

The Excel Client does not support the binary *.xlsb Excel format.

To translate to *.xlsm open an *.xlsb file in Excel and save it as *.xlsm

3. Upload a spreadsheet file to the EASAP via the Builder.

  • In the EASAP Builder click the 'Files' tab
  • Click: Browse
  • Navigate to: MortgageCalculatorEDC.xlsm that we downloaded above
  • Select: Upload
  • Confirm: MortgageCalculatorEDC.xlsm spreadsheet file appears under the 'Files' tab

4. Set and activate the spreadsheet to use, then 'Save' the EASAP to generate the database schema.

  • Click the Tree tab
  • Right-click EASAP > SPREADSHEET LIST
  • Select: Child > Add SPREADSHEET
  • Set
    File: MortgageCalculatorEDC.xlsm
    Desktop: TRUE
    …This step activates the Excel Client interface for the EASAP
  • Save the EASAP

The database will create records for the spreadsheet itself and the cases table to store the values of each named range therein.

Due to some unfortunate internal behavior of Excel, any named range with a prefix ' prop_ ' or any combination of this sequence of capital or lowercase letters will cause the complete loss of data integrity within that range. For an Excel Client spreadsheet rename any named range with the prefix ' prop_ ' to ' my_prop_ ' or ' property_ ' …etc.

At first 'Save' the spreadsheet's named ranges alongside the metadata fields below, populate a dropdown list for CASE VIEWER > Columns:

  • create - datetime
  • created_by - varchar(255)
  • last_modified - datetime
  • last_modified_by - varchar(255)
  • status - varchar(255)

5. Use a CASE VIEWER to verify the schema has been created.

  • Right-click USER INTERFACE > TABBED PANE LIST > tabbed_pane1
  • Select: Child > Add CASE VIEWER
    …The default name case_viewer1 is fine
  • Click the Columns: field
    …The Columns: list includes all the named ranges from the spreadsheet
  • Click: Cancel
    as we will configure case_viewer1 in the next part

We have successfully configured an EASAP to utilize the database-backed Excel Client interface.

B. Search and select with CASE VIEWER

In this EASAP we give the User the ability to:

  • View existing mortgage loan records inside an EDC EASAP with a CASE VIEWER
  • Create a new mortgage loan record in an Excel Client instance
  • Modify an existing mortgage loan in an Excel Client

1. Configure a CASE VIEWER.

  • Select tabbed_pane1
  • Set Tab: Mortgage Calculator
  • Select case_viewer1 from above
  • Set
    Columns: loan_amount, term, starting_monthly_payment, status, last modified by, last modified
    Column Titles: Loan Amount, Term (Years), Starting Monthly Payment, Status, Last Modified By, Last Modified
    Formats: $%,.0f/%.0f/$%,.2f/%s/%s/%s
    Separator for Formats: / (' / ' is the separator between the format specifiers in Formats:)
    Header Height: 40
    Pattern: dd-MMM-yyyy
    Refresh on Save: TRUE (this will report an 'EASA Ribbon > Save' back to the EASA Server)

Below are column formatting details for our choices for Formats:

  • $%,.0f — dollar-sign plus a zero decimal place, floating point number with a regional thousands separator
  • $%,.2f — dollar-sign plus a two decimal place, floating point number with a regional thousands separator
  • %s — format a string of text

2. Save and then test, the following CASE VIEWER will appear in the browser:

C. Create and modify a case with DATABASE TO EXCEL DESKTOP

The DATABASE TO EXCEL DESKTOP ACTION follows from an EASAP-originating EVENT, we use a BUTTON.

DATABASE TO EXCEL DESKTOP launches a local Excel Client on a User's desktop via an Action:

  • Action: New - to open an Excel Client with default fields from the original spreadsheet
  • Action: Open - to open an Excel Client with a CASE VIEWER-selected case record from the database

1.Add a LAYOUT PANEL and two BUTTONs.

  • Right-click case_viewer1
  • Select: Sibling > Add LAYOUT PANEL
  • Rename it layout_panel_buttons
  • Set Layout: HORIZONTAL
  • Right-click layout_panel_buttons
  • Select: Child > Add BUTTON (twice)
  • Rename the top one button_new
  • Set Label: Create New Loan
  • Rename the lower one button_open
  • Set Label: Modify Selected Loan

Each button will be linked to an ACTION in order to open an Excel Client with:

  1. Default values from the spreadsheet
  2. Cell values from an existing database case

2. Add a DATABASE TO EXCEL DESKTOP ACTION to open a new case.

  • Right-click button_new
  • Select: Child > Add DATABASE TO EXCEL DESKTOP
    …The default name database_to_excel_desktop1 is fine
  • Set Action: New
  • Right-click database_to_excel_desktop1
  • Select: Sibling > Add REFRESH OBJECTS
  • Rename it refresh_case_viewer
  • Set Objects: case_viewer1

The REFRESH OBJECTS will refresh our CASE VIEWER immediately after an Excel Client has launched, we will see that a new record has been INSERTed into the database with default cell values prior to any interaction from within the Excel Client.

3. Add a DATABASE TO EXCEL DESKTOP to open an Excel Client with an existing case.

  • Right-click button_open
  • Select: Child > Add DATABASE TO EXCEL DESKTOP
  • Set
    Action: Open
    Case Id: %case_viewer1%
    (case_viewer1 takes value of a User selected case ID)

4. We want our CASE VIEWER in the EASAP GUI to update once the User clicks: EASA Ribbon > Save

  • Set a parameter so the EASAP checks the EASA Server every second for updated data
  • Set USER INTERFACE > Results Time: 1

D. Run the example

1. Save the EASAP.

2. Click Test, the EASAP below should appear in a browser:

3. Click 'Create New Loan'

A new case record with default data is created in the cases database table.

The EASAP will prompt the User to save an automatically-generated temporary spreadsheet containing default case data (you may need to disable a popup blocker).

4. Download and launch the temporary spreadsheet:

  • Save the spreadsheet file locally
    …Do not attempt to open the file in the browser before it has been saved, the browser may not launch Excel correctly
  • Open the Excel spreadsheet after the file has been saved
    …The first time an EDC EASAP is run, the User will be prompted to install the EASA Ribbon
  • Select 'Yes' in the popup window
  • Click: Open Spreadsheet
    …To later remove the EASA Ribbon from Excel click: EASA > Options > Remove

After the EASA Ribbon is installed and a temporary spreadsheet is opened the interface below will appear.

We call this instance of a database-linked Excel spreadsheet an Excel Client.

5. Interact with the spreadsheet as a User might.

  • Change some of the default fields, our example uses
    loan_amount - 150000
    term - 30

6. Click 'EASA > Save' to write the modified data in Excel named ranges back to a case record in the database.

A message window announces, 'Your case has been saved successfully'

Click OK

7. Close the Excel window; if prompted by Excel, choose 'Don't Save'.

Now the Excel Client file, edc.xlsm, is no longer a valid Excel Client nor is it a valid Excel spreadsheet.

The life cycle for each Excel Client session is below:

  • Initiated from the CASE VIEWER
  • A new temporary spreadsheet is generated, download, opened
  • Case data may (or may not be saved)…
  • Close the temporary spreadsheet and the file will be 'cleaned' of its formulas, macros and data (though the 'empty file' will remain)

Any subsequent attempt to open the empty Excel file simply returns an Excel error; 'Error during download'

8. Return to the browser, our new record should be visible:

9. Explore the rest of the functionality:

  • Create a few more cases in this way
  • Highlight one of them and click 'Modify Selected Loan' button
  • Change one or several values within an Excel Client
  • Click: EASA > Save

E. Complete the lifecycle of a case

A case in the cases table is stored with a 'status' field:

  • NEW
  • IN_PROGRESS
  • FINISHED
  • DELETED
  • Creating a new case, stores a default case record and metadata with status, NEW
  • Upon 'EASA > Save' within an Excel Client the case data is recorded with status, IN_PROGRESS
  • status, FINISHED, may (optionally) be used to show some later validation step from any database client
  • status, DELETED, may be used to remove a case from active use in a non-destructive way

1. Add the ability to delete a case by setting status to DELETED using SET CASE STATUS

  • Right-click button_open
  • Select: Sibling > Add BUTTON
  • Rename it button_delete
  • Set Label: Delete Selected Loan
  • Right-click button_delete
  • Select: Child > Add SET CASE STATUS
    • Set
      Status: Deleted
      (this is actually mapped to the string DELETED)
      Case Id: %case_viewer1%

2. Refresh the CASE VIEWER after a Delete.

  • Right-click tabbed_pane1 > layout_panel_buttons > button_new > refresh_case_viewer
  • Select: Copy
  • Right-click button_delete > set_case_status1
  • Select: Paste (Sibling)
    …This will update case_viewer1 in the EASAP GUI just as with button_new

The Builder supports keyboard shortcuts for selected objects or for highlighted text:

  • CTRL-x to Cut - object or text selected
  • CTRL-c to Copy - object or text selected
  • CTRL-v to Paste (Child) or Paste text - depends on whether an object is selected or text is highlighted
  • CTRL-b to Paste (Sibling) - has no meaning if text is selected

Copy-paste an object on the Tree to make a copy with a new unique name:

  • Select an object on the Tree then type: CTRL-c
  • Then do either of:
    Select a new parent then type: CTRL-v
    Select a new sibling then type: CTRL-b
  • If 'Paste (Child or Sibling)' is not valid an error message is displayed

Cut-paste an object on the Tree, preserving its name:

  • Select an object on the Tree then type: CTRL-x
  • Then do either of:
    Select a new parent then type: CTRL-v
    Select a new sibling then type: CTRL-b
  • If 'Paste (Child or Sibling)' is not valid an error message is displayed

Text cut-copy-paste in the parameter table:

CTRL-x (cut), CTRL-C (copy) and CTRL-V (paste) text between different object's parameter fields.

3. Add a FILTER to hide from display any record with status of DELETED in case_viewer1

  • Right-click case_viewer1
  • Select: Child > Add FILTER
  • Rename it filter_not_deleted
  • Set
    Value: DELETED
    Static Operator: NOT_EQUAL
    Case Data to Compare: status

4. Add an exit button so a User may confidently close the EASAP.

  • Right-click button_delete
  • Select: Sibling > Add BUTTON
  • Rename it button_exit
  • Set Label: Exit
  • Right-click button_exit
  • Select: Child > Add MENU ACTION
  • Rename it menu_action_exit
  • Set Action:Exit
    (equivalent to a User clicking the grey X in upper right corner of an EASAP)

5. We want to make some cosmetic enhancements for a better User experience.

  • Let's make our buttons larger by
    Raising the font size
    Adding a margin around the text
  • Additionally let's add more space or padding between the buttons
  • Right-click USER INTERFACE
  • Select: Child > Add USER INTERFACE STYLE LIST
  • Right-click USER INTERFACE STYLE LIST
  • Select: Child > Add USER INTERFACE STYLES
  • Right-click USER INTERFACE STYLES
  • Select: Child > Add USER INTERFACE STYLE
  • Rename it button_style1
  • Right-click button_style1
  • Select: Child > Add TEXT STYLE
  • Set
    Font Size:12
    Weight: bold
  • Right-click TEXT STYLE
  • Select: Sibling > Add MARGIN
  • Set
    Top: 5px
    Right: 5px
    Bottom: 5px
    Left: 5px
  • Right-click MARGIN
  • Select: Sibling > Add PADDING
  • Set
    Right: 5px
    Left: 5px
  • Set Style: button_style1 for each of the following
    button_new
    button_open
    button_delete
    button_exit

6. Save and test.

  • Select one of the mortgage loans
  • Click: Delete Selected Loan
  • One second later the case record should disappear from the CASE VIEWER
  • Actual destruction of a case record is not supported because of the possibility of a later audit

We now have a complete Excel Desktop Client example.

We have created an EASAP, below, that uses a temporary Excel spreadsheet on the User's desktop as its user interface and stores a modified case record back to a database.

F. Activate a spreadsheet's locked-mode for Excel Client sessions

If MortgageCalculatorEDC.xlsm were to contain proprietary data in its formulas or VBA we would want to insure the spreadsheet is locked by adding and activating two VBA macros, detailed here: Implement locked-mode for an Excel Client spreadsheet

1. For illustration purposes these macros have already been added to MortgageCalculatorEDC.xlsm and we may examine them.

  • Start Excel
  • Select the 'Developer' tab
  • Click: Visual Basic
  • Expand: VBAProject (MortgageCalculatorEDC.xlsm)
  • Enter the workbook password: easa123
  • Double-click 'ThisWorkbook' to see the two macros, you may need to select: View > Code (or <F7>)

Each of these macros is 'active' when the (locked) 'EASA' named range on the (password protected) 'EASA' sheet is non-empty.

2. Our EASAP needs to set this cell to insure that an Excel Client user will not be able to save the formulas or macros in the spreadsheet.

We do this by initializing this named range for each DATABASE TO EXCEL DESKTOP to 'locked' (or any non-empty string)

  • Select layout_panel_buttons > button_new > database_to_excel_desktop1
  • Set
    Initialize Named Ranges: EASA (MortgageCalculatorEDC.xlsm)
    Initialization Data: locked
  • Select layout_panel_buttons > button_new > database_to_excel_desktop2
  • Set
    Initialize Named Ranges: EASA (MortgageCalculatorEDC.xlsm)
    Initialization Data: locked
  • Save and test
  • Click: Create New Loan
  • Open the temporary spreadsheet.
  • Select 'File > Save' or click the diskette icon ( )
    A message explains that 'save is disabled because this spreadsheet has been locked'

Now for MortgageCalculatorEDC.xlsm, the only way to extract data is via 'EASA > Save' during an Excel Client session.

Conversely, a password-authorized Author may open and modify MortgageCalculatorEDC.xlsm in Excel without any restrictions.

3. Protect the spreadsheet intellectual property.

In general a customer may wish to protect the spreadsheet content by either:

  • Locking every cell to start with
  • Changing (only) the input cells to 'hidden' to hide formulas but still allow user input
    …We have taken these steps with MortgageCalculatorEDC.xlsm

G. FILTER results based on case status and term length

As the cases table grows, we will need more ways to refine and limit the results set shown in the CASE VIEWER.

We will add two new FILTERs which will take User input from two INPUTBOXs above the CASE VIEWER.

1. First define CSS-style attributes for Label: text on the INPUTBOXs with a new USER INTERFACE STYLE.

  • Right-click USER INTERFACE > USER INTERFACE STYLE LIST > USER INTERFACE STYLES > button_style1
  • Select: Sibling > Add USER INTERFACE STYLE
  • Rename it label_style1
  • Right-click label_style1
  • Select: Child > Add TEXT STYLE
  • Set
    Font Size:10
    Weight: bold
    Alignment: right
  • Right-click TEXT STYLE
  • Select: Sibling > Add PADDING
  • Set
    Right: 5px
    Left: 5px

2. Create a SUB PANE and add two INPUTBOXs.

  • Right-click tabbed_pane1
  • Select: Child > Add SUB PANE
  • Rename it sub_pane_filters
  • Set Layout: HORIZONTAL
  • Right-click sub_pane_filters
  • Select: Child > Add INPUTBOX
  • Rename it inputbox_status
  • Set
    Label: Status:
    Label Style: label_style1
  • Right-click inputbox_status
  • Select: Child > Add DATA VALIDATION
  • Set
    Allow:List
    Ignore Blank:FALSE
    Source: ,NEW, IN_PROGRESS, FINISHED
    (note the initial 'comma', we allow a blank option which accepts all status possibilities)
  • Right-click inputbox_status
  • Select: Copy
  • Right-click inputbox_status
  • Select: Paste (Sibling)
  • Rename it inputbox_term
  • Set Label: Term:
  • Select inputbox_term > DATA VALIDATION
  • Set Source: ,15,20,25,30
  • (note the initial 'comma', we allow a blank option which accepts all term possibilities)

3. Add a FILTER for each INPUTBOX to narrow the results set displayed in our CASE VIEWER.

  • Right-click case_viewer1 > filter_not_deleted
  • Select: Sibling > Add FILTER
  • Rename it filter_status
  • Set
    Value:%inputbox_status%
    Static Operator: EQUAL
    Case Data to Compare: status
  • Right-click filter_status
  • Select 'Copy' then 'Paste (Sibling)
  • Rename it filter_term
  • Set
    Value: %inputbox_term%
    Static Operator: EQUAL
    Excel Value To Compare: term
    Case Data to Compare: [empty] (ie. set this parameter to the blank choice)

We need our FILTERs to run, and, our CASE VIEWER GUI to update when a User makes a change to either INPUTBOX.

4. Add an EVENT PROCESSING to trigger a REFRESH OBJECTS ACTION for case_viewer1 to follow a VALUE CHANGED EVENT from either inputbox_status or inputbox_term.

  • Right-click sub_pane_filters
  • Select: Sibling > Add EVENT PROCESSING
  • Rename it event_processing_filters
  • Expand it with the plus box
  • Right-click event_processing_filters > event1
  • Select: Child > Add VALUE CHANGED
  • Rename it value_changed_filters
  • Set Objects: inputbox_status, inputbox_term
  • Right-click tabbed_pane1 > layout_panel_buttons > database_to_excel_desktop1 > refresh_case_viewer
  • Select: Copy
  • Right-click event_processing_filters > action_group1
  • Select: Paste (Child)

5. Save and test the EASAP.

We now have two functional filters which respond to User selected values, 'Status' options are shown below.

H. Display the number of cases which meet FILTER criteria

When a User changes one of the INPUTBOXs it would be useful to know how many cases meet the modified criterion.

1. Make our CASE VIEWER a larger height to show more cases.

  • Set case_viewer1 > Height: 200
    …In a real application this number should be slightly less than the vertical display height in pixels

2. CASE VIEWER gives us the size of the results set, if we declare a new SCALAR in Count:

  • Select case_viewer1
  • Set Count: total_cases

3. Display the size of the current results set in a LABEL.

  • Right-click event_processing_filters
  • Select: Sibling > Add LABEL
  • Set
    Label: Number of Loans: %total_cases%
    Style: label_style1

4. Confirm the EASAP appears as below:

I. Only enable 'modify' and 'delete' buttons when a case is selected

Two of our buttons don't have any significance until a case is selected; we need to disable them until they are valid actions.

The SCALAR case_viewer1 has no value until a User selects a case and then case_viewer1 becomes equal to the case ID of the selected case.

Below we use an EXPAND to create a SCALAR which will have the value 0 until a User selects a case when it is set to the case ID.

Then we use Enable If: clauses on each button to activate/deactivate the buttons.

1. Create an EXPAND which is '0' until it until it contains a case ID.

  • Right-click USER INTERFACE > tabbed_pane1
  • Select: Child > Add DATA PROCESSING
  • Expand it
  • Right-click SCALAR
  • Select: Child > Add EXPAND
  • Rename it Selected_loan
  • Set
    Value: %case_viewer1%
    Value On Error: 0

2. Enable 'Open Selected Loan' and 'Delete Selected Loan' once a case is selected.

  • Select USER INTERFACE > tabbed_pane1 > layout_panel_buttons > button_open
  • Set Enable If: Selected_loan>0
  • Select USER INTERFACE > tabbed_pane1 > layout_panel_buttons > button_delete
  • Set Enable If: Selected_loan>0

3. Confirm the 'Modify…' and 'Delete…' buttons are greyed out (below) until a case is selected.

Congratulations, our EDC EASAP/Excel Client tutorial is complete.

An Author may now publish the EASAP to make it available to a User.

Finally, there are a few caveats to using an Excel spreadsheet as an temporary interface, see Excel Desktop Client Reference

Overview | Get started with EDC | Simple EDC Example | EDC template | EDC reference

Page Tools