Worksheet (spec)

From RPM Wiki

This feature is still in development.
Table of contents
1 Summary
2 Worksheets

2.1 Layout and contents
2.2 Templates and forms
2.3 Security
2.4 Name
2.5 Form fields
2.6 Header and footer
2.7 Tables
2.8 Column types

3 UI

3.1 Pages
3.2 JavaScript
3.3 Download
3.4 Views
3.5 Import

4 API
5 More

5.1 Form history
5.2 Trash
5.3 Code style

6 Future
7 History
[edit]

Summary

The feature gives forms worksheets for storing tabular data that doesn't fit well into fields and notes. It also includes functionality to improve the presentation of this data. The theory is that these worksheets are like attached Excel files, but with more structure and the ability to work with them through the UI and API.

The goal of this feature is to allow process management in RPM to be used to store and present telecom price quotes. However, I think the flexible nature of this functionality will also have applications for other table-based data including inventory management. In a way, it gives forms spreadsheet capability.

[edit]

Worksheets

The main piece of this feature is the worksheet. In a lot of ways these are similar to Excel worksheets, but instead of being one large spreadsheet, they contain one or more smaller spreadsheets in the form of tables. In this way they're more like sheets in Apple's spreadsheet application Numbers (http://www.apple.com/iwork/numbers/).

[edit]

Layout and contents

Worksheets have a set overall layout. Within this layout, fields can be arranged amongst themselves and tables arranges amongst themselves. Each element of the structure is optional. The overall layout can not be changed (can't mix fields & tables and the header is always first, the footer always last).

Layout

  • Header
  • Form fields
  • Tables
  • Footer
[edit]

Templates and forms

A worksheet belongs to a single process template or a single form.

  • A form or template can have more than one worksheet.
  • The intent is that worksheets are designed in the template and then used in the forms.
  • This distinction is only a matter of the way we present the interface as a worksheet in a template is technically the same as a worksheet on a form.
  • There is one minor difference, when a worksheet is added to a form we make the user select a worksheet from the form's template. The form worksheet then stores a reference to the worksheet it was based on. This link is only used for reporting and if the template worksheet is deleted the form worksheet continues on with no worksheet reference.
  • The correct terminology is "template worksheet" and "form worksheet"
  • When a form worksheet is created it inherits everything from its template worksheet. This is a one-time copy and subsequent changes to the template worksheet will not affect form worksheets started from it.
  • The template worksheet has options that affect how it can be used in forms. These options exist within form worksheets as well, but no longer have meaning.
    • Enabled Yes or No. Can form worksheets based on this one be added to forms in this process
[edit]

Security

  • Template worksheet options per user type: "Hidden", "Read", "Edit", "Add". Security settings are only provided in template worksheets so they can be inherited by form worksheets and determine who can add worksheets to forms. Viewing and editing a template worksheet requires solely the privilege to design processes.
  • Form worksheet options per user type: Hidden, Read, Edit ("Add" from the template worksheet becomes "Edit"). However, viewing a form worksheet also requires access to view the form it belongs to and editing a worksheet also requires edit access to that form.
  • Currently we are only allowing a setting of "Add" for staff users for template worksheets ("Edit" for form worksheets).
  • When an agent user is modifying the design of a worksheet, they can not change the security
[edit]

Name

Each worksheet has a name. It must be unique among worksheets within the template or form.

[edit]

Form fields

Worksheets can show fields from their template/form.

  • These are selected in worksheet design.
  • The value of these fields can only be edited from the form, not in the worksheet
[edit]

Header and footer

Worksheets have an optional header and optional footer. These are text blocks that allow some HTML.

  • The header and footer values are edited in worksheet design
[edit]

Tables

Worksheets have tables. These are like little spreadsheets, but with pre-defined header row, optional spanning header rows, set column types, and some other options.

Terminology

  • Table design means modifying the columns, super headers, and options.
  • Table edit means modifying the data within a table including adding and editing rows.

We enforce different rules depending if the worksheet belongs to a template or a form.

  • Every table has a name. In a template worksheet the name must be unique among tables in the worksheet. In a form worksheet tables can have the same name.
  • In template worksheet users can add then design a blank table.
  • In a form worksheet users can only add tables by copying a table from the template worksheet the worksheet came from. (this may change by launch to allow new tables in a form worksheet)
  • A user who can edit a form worksheet can design and edit data in tables in that worksheet
  • A user who can design template worksheets can design tables in that worksheet. We do not show or provide an edit for data in template worksheet tables.
  • Template worksheet tables have an option to be enabled or no which determines if they can be added in form worksheets started from this template worksheet.
  • Template worksheet tables have an option to include them by default in form worksheets started from this template worksheet.

Options

  • Optional supplier reference. It can be fixed (set in design) or a single select set during edit.
  • Show comment field. An optional text field shown under the table.
  • Label comment field. A label for that field.
  • Show footer row. The footer row is auto generated with a sum cell for each money, number, and percent column (non-fixed only). Other columns just have a blank cell.

Super headers

  • Tables can have one or more super header rows. These are special header rows where each cell can span multiple columns. Example: "1 year term" that spans the 5 columns below it followed by "2 year term" that spans the next 5 columns.
[edit]

Column types

These are like the field types used in forms

  • Supplier - A supplier reference
  • Text - Free-form text
  • Text (fixed) - Defined in the template and auto-populated in every cell
  • Money
  • Money (fixed) - Defined in the template and auto-populated in every cell
  • Number
  • Number (fixed)
  • Percent
  • Percent (fixed)
  • Formula - Displays the money result from an operation on two other columns
  • Date
[edit]

UI

[edit]

Pages

Form

  • The form will list worksheets as tabs. If

Worksheet add FormWorksheetAdd.aspx

  • Wizard launched from form to add a worksheet by picking a template worksheet

Worksheet

  • FormWorksheet.aspx
  • Worksheets shown 1 per page
  • Also used for template worksheet "Preview" (basically just display a template worksheet as if it was a form worksheet)

Worksheet import

  • FormWorksheetImport.aspx
  • Launched from worksheet, user must pick a table from the worksheet in a drop down. List by table name in same order as on the worksheet.

Template

  • The process template will have a new box that lists existing worksheets and has a toolbar link to add a new one

Worksheet Design

  • TemplateWorksheet.aspx
  • Worksheet field & table setup by JS

Worksheet Edit

  • TemplateWorksheetEdit.aspx
  • Used for add, copy, edit templates worksheets
  • Same page used to edit form worksheets with some template-only options hidden

Worksheet Table Edit

  • TemplateWorksheetTable.aspx
  • Add/edit table options. Actual table data is edited from FormWorksheet.aspx using JS
[edit]

JavaScript

  • Code is in \Style\Worksheets.js
  • Design
    • All fields JSON array in pre tag with class WSFieldsAll
    • Selected fields JSON array in pre tag with class WSFieldsSelected
    • Worksheet JSON object with tables in pre tag with class WSTablesDesign
    • \ClientRequest\Worksheets\Fields
    • \ClientRequest\Worksheets\Table
    • \ClientRequest\Worksheets\TableOrder
    • \ClientRequest\Worksheets\TableTrash
  • Use
    • Worksheet JSON object with tables in pre tag with class WSTables
    • \ClientRequest\Worksheets\Table
  • Where needed \ClientRequest\Suppliers will be used to get a list of suppliers
[edit]

Download

Excel download

  • Table - Every form worksheet table has a download. The result is a simple Excel file.
    • Filename "{form number} {worksheet name} {table name}.xls"
    • Example: "Q00123 Regular quote Data.xls"
  • Worksheet - Downloading a form worksheet is a bit more complicated as it contains every table and field in the form worksheet.
    • Filename "{form number} {worksheet name}.xls"
    • Example: "Q00123 Regular quote.xls"

Download notes

  • Cells in formula columns become Excel formulas
  • Cells in the footer row in money & number columns become Excel sums
[edit]

Views

Process view filters

  • "Worksheet" - The user can pick a value from the worksheet templates. Applicable forms will have one or more worksheets from that worksheet template.

Process view columns:

  • "Worksheets" - A list of worksheet names the form has separated by comma. All worksheets are listed even when there is more than one from a single template, even if there are worksheets who's template is gone.
[edit]

Import

  • Excel import for a table in a worksheet.
  • The first row of the Excel file is assumed to be a header row. The importer does its best to fit the data into the editable columns of the table.
  • There is no error page since the resulting table is better than an error report
  • The values are always added as new rows under any existing data
[edit]

API

Worksheets & tables have IDs so they can be referenced by the API. All the new calls return the same thing, a worksheet object that has all the table objects of that worksheet. It does not have the fields, header, or footer.

  • Api/ProcForm
    • This existing call will be updated to include a list of worksheets
  • Api/ProcFormWorksheet
    • Gets a worksheet and all its field values and tables
    • Requires: a form worksheet ID
    • Returns: a worksheet object including table objects
  • Api/ProcFormWorksheetAdd
    • Adds a worksheet to a form based on a worksheet template or an existing worksheet (copy)
    • Requires: a form ID & a template worksheet ID
    • Returns: a worksheet object including table objects
  • Api/ProcFormWorksheetTableAdd
    • Adds a table to a form based on an existing table
    • Requires: a form worksheet ID & a table ID
    • Returns: a worksheet object including table objects
  • Api/ProcFormWorksheetTableUpdate
    • Modifies an existing table by sending an entire table object
    • Requires: a table object (includes table ID)
    • Returns: a worksheet object including table objects
[edit]

More

[edit]

Form history

All worksheet edits are saved in the form history

  • Worksheet Added
  • Worksheet Deleted
  • Worksheet Restored
  • Worksheet Renamed
  • Worksheet Edited
  • Worksheet Table add, delete, restore
  • Worksheet Values (table edit)
[edit]

Trash

Trashed:

  • Worksheets
  • Tables
  • Worksheet templates
  • Table templates

Removed (not trashed):

  • Table data, columns, and super headers
  • Form fields selected in worksheets
[edit]

Code style

Worksheet and WS are appropriate names, never WorkSheet

[edit]

Future

Important

  • Supplier logos
  • Subscriber logo
  • Agency logos
  • Download as PDF

Ideas

  • Design/edit worksheet by API
  • Shell-type functionality to do design & formatting by Excel
  • Worksheets in the form download
  • Role-based security for staff users
  • Allow agent users to set customer user security
[edit]

History

Retrieved from "http://www.rpmsoftware.com/wiki/index.php/Worksheet_%28spec%29"
  • This page was last modified 20:59, 6 Jan 2010.
  • This page has been accessed 5156 times.