Form Controls Linked to Spreadsheet Cells

OpenOffice.org 1.1 / SO 7

Document - ID	Specification Owner	Last Change	Status
	Daniel Rentz	2004-11-01	Final
Conforms to	
Applies to	spreadsheet
Task ID(s)	19067, 19368, 18995, 18996, 18999, 19366, 19367, 19371, 18994, 18997, 19365
Category	Feature

Abstract

To increase compatibility to other spreadsheet applications, Calc needs the possibility to connect sheet cells with form controls. This connection synchronizes the contents of the cell and the form control in both directions.

i-Team Members

	Name	E-mail Address
Specification Owner	Daniel Rentz (dr)	dr@openoffice.org
User Experience	Frank Loehmann (fl)	fl@openoffice.org
Development	Frank Schönheit (fs) Niklas Nebel (nn) Daniel Rentz (dr)	fs@openoffice.org nn@openoffice.org dr@openoffice.org
Quality Assurance	Oliver Crämer (oc)	oc@openoffice.org
Documentation	Frank Peters (fpe)	fpe@openoffice.org

Approved for Implementation

	Approved by	Date
User Experience	Frank Loehmann	2003-09-26
Development	Frank Schönheit	2003-09-25
Quality Assurance		
Documentation		
String Review	Elizabeth Matthis	2003-10-15

Document Change History

Rev. Level	Change	Initials	Date
1.0	Initial revision	dr	2003-09-23
1.1	Review, property browser additions	fs, dr	2003-09-25
1.2	Review by Frank Loehmann	fl	2003-09-26
1.3	UI String review	em	2003-10-15
1.4	added/changed: allow string exchange for option buttons and check boxes (#116712#) Clearing the "List entries" when clearing the "Source range of the list"	fs	2004-05-13
1.5	changed: care for the new "Reference value (off)" in check boxes and radio buttons	fs	2004-08-24
1.5.1	Clarified the binding of radio buttons when exchanging string values	fs	2004-11-01

Contents

Glossary	1

1	Motivation	1

2	Goals	1

3	Requirements and Dependencies	1

3.1	Requirements	1

3.2	Technical Dependencies	1

4	User Scenarios	1

5	Competitive Analyses	1

6	Detailed Specification	1

6.1	Check Box	2

6.2	Option Button (Radio Button)	2

6.3	Edit Field	3

6.4	Numeric and Formatted Field	3

6.5	List Box	4

6.6	Combo Boxes	4

6.7	Control Property Browser	4

6.8	Error Conditions	5

7	Future Tasks	5

8	Legal Issues	5

9	Notes	6

10	References	6

Glossary

Term	Description
Linked cell	A spreadsheet cell connected to a form control.
List source range	A spreadsheet cell range connected to a list box/combo box, used as source for the entry list
Control Property Browser	A dialog for manipulating all properties of a form control.

Motivation

Goals

Requirements and Dependencies

Requirements

This feature will allow users to easily adopt their skills on working with other spreadsheet applications to migrate to Calc.

Technical Dependencies

None

User Scenarios

An imported Microsoft Excel document contains form controls that are connected to cells. These cells reflect the contents of the form controls. After exporting this document back to Microsoft Excel format, these connections are lost, and the cell does not show the control's contents anymore.

Competitive Analyses

See chapter 4, “User Scenarios“.

Detailed Specification

A number of form controls are enhanced (please see below for a detailed list) with the capability to store the address of a cell from the containing spreadsheet. This cell address will occur in the form control's properties dialog. The connection synchronizes the control's contents and the cell contents in both directions. That means the control is updated whenever the cell is changed and vice versa. A cell that is connected to a form control will be called “linked cell” in the following.

List boxes and combo boxes additionally will be associated with the address of a cell range. This cell range is used to fill the string list of the list/combo box. A spreadsheet cell range that is connected to a list/combo box will be called “list source range” in the following. The source range may consist of several columns, but only the contents of the leftmost column are used to fill the control.

The following chapters describe the behavior of all form controls that will support linked cells. The left column of each table (“Action”) shows a specific user action, and the right column (“Reaction”) describes the reaction performed automatically by the spreadsheet and/or the form control.

Check Box

Check Boxes can exchange either boolean or string values with the linked cell. They do the former if and only if the "Reference value (on)" property is not an empty string.

Exchanging boolean values

Action	Reaction
Check the check box	TRUE is entered into the linked cell
Uncheck the check box	FALSE is entered into the linked cell
Set check box to third “don't-care” state	#NV is entered into the linked cell
Enter a number in the linked cell, or a formula that returns a number	Check box is checked, if value is not zero or FALSE, else unchecked
Clear the linked cell, or enter text, or a formula that returns text or an error	Check box is set to third “don't-care” state, if available, else unchecked

Exchanging string values

Check Boxes in OpenOffice.org feature a so-called reference value. With OpenOffice.org 2.0, they even feature two such properties: "Reference value (on)" and "Reference value (off)". Those string properties can be freely chosen by the user.

So, since OpenOffice.org 2.0, check boxes also allow exchanging those reference values: As soon as such a value is set (i.e. is a non-empty string) for a particular check box, the following holds:

Action	Reaction
Check the check box	The "Reference value (on)" is entered (as string) into the linked cell.
Uncheck the check box	The "Reference value (off)" is entered (as string) into the linked cell.
Set check box to third “don't-care” state	#NV is entered into the linked cell
Enter any content into the linked cell	If the content evaluates to a string which equals the "Reference value (on)", the check box is checked. If the string equals the "Reference value (off)", the box is unchecked. Else, the box is put into indetermined state. If the box does not allow the indetermined state (due to its Tristate property being 'No'), then it is unchecked.

Option Button (Radio Button)

Option buttons can exchange either boolean or string values with the linked cell. They do the former if and only if the "Reference value (on)" property is not an empty string.

Exchanging boolean values

Action	Reaction
Select the option button	TRUE is entered into the linked cell
Option button is deselected by selecting another option button	FALSE is entered into the linked cell
Enter a number in the linked cell, or a formula that returns a number	Option button is selected, if value is not zero or FALSE, else deselected
Clear the linked cell, or enter text, or a formula that returns text or an error	Option button is deselected

Because an option button may be grouped with other option buttons, an action may cause the change of other cells. For instance, while selecting an option button its linked cell is set to TRUE. At the same time the linked cell of the old selected option button is set to FALSE, and this option button is deselected.

Exchanging string values

Option buttons in OpenOffice.org feature a so-called reference value, called “Reference value (on)” in the property browser (Note that in OpenOffice.org 1.x, it was named “Reference value”) only). Since OpenOffice.org 2.0, option buttons also allow exchanging this reference values: As soon as such a value is set (i.e. is a non-empty string) for a particular option button, the following holds:

Action	Reaction
Select the option button	The "Reference value (on)" is entered (as string) into the linked cell
Option button is deselected by selecting another option button	An empty string is written into the linked cell. Note that usually, the user will not see this: A radio button is unchecked when another button in the same radio group is checked. Usually, this other radio button will be bound to the same cell, so it will also propagate its reference value to this cell.
Enter any content into the linked cell	If the content evaluates to a string which equals the "Reference value (on)", the radio button is checked, else unchecked.

Edit Field

Action	Reaction
Enter text into the edit field	Text is copied into the linked cell
Clear the edit field	Linked cell is cleared
Enter text or a number into the linked cell	Text/number is copied into the edit field
Enter a formula into the linked cell	Formula result is copied into the edit field
Clear the linked cell	Edit field is cleared

The linked cell always shows the original text, even if in the edit field a password character is set. Text formatting like bold, italic, color or underlining of the cell will not be displayed in the control.

Numeric and Formatted Field

Action	Reaction
Enter a number into the field	Number is copied into the linked cell
Clear the field	Value 0 is set into the linked cell
Enter a number or a formula returning a number into the linked cell	Number is copied into the field
Enter text or a formula returning text or an error into the linked cell, or clear the linked cell	Value 0 is set into the field

List Box

List boxes will support two different linking modes:

Linked contents: Synchronize the text contents of the selected list box entry with the cell's text.

Linked selection position: The position of the list box (single) selection is synchronized with the cell's numerical value.

Action	Reaction
Select a single list item	Contents are linked: Text of the item is copied into the linked cell Selection is linked: One-based position of the selected list entry is copied into the linked cell (for instance: Third entry is selected – the value 3 is set into the linked cell)
Select several list items	#NV is entered into the linked cell
Deselect all list items	Contents are linked: Linked cell is cleared Selection is linked: Value 0 (zero) is entered into the linked cell
Enter text or a number into the linked cell (contents are linked)	Contents are linked: Try to find and select an equal list item Selection is linked: The list item at the specified position (one-based) is selected. If not found, all entries are deselected.
Enter a formula into the linked cell	Try to find and select a list item according to formula result and link mode
Clear the linked cell	Deselect all list items
Change the contents of the list source range	List box items are updated according to the change. Selection is preserved. That may cause an update of the linked cell.

Combo Boxes

Action	Reaction
Enter text into the edit field, or select an entry from the drop-down list	Text is copied into the linked cell
Clear the edit field	Linked cell is cleared
Enter text or a number into the linked cell	Text/number is copied into the edit field
Enter a formula into the linked cell	Formula result is copied into the edit field
Clear the linked cell	Edit field is cleared
Change the contents of the list source range	Drop-down list items are updated according to the change. Edit field and therefore the linked cell are not changed.

Control Property Browser

The property browser for controls will get new fields for binding controls to spreadsheet cells, located on the „Data“ tab page, and above all previously existing fields. Figure 1 shows all new fields for a list box. Other control types will have less fields, depending on the concrete linkage type they support.

When a valid cell address is entered for a control, specifying the cell to link this control to, then all properties related to binding this control to a database column will be disabled in the property browser. In particular, these are "Data Field", "Type of Content", "Content", "Analyze SQL command", "Sort", "Empty string is NULL", and "Filter proposal". This is because a valid cell linking of a control overrules any potential database column binding.

Similar, if a list or combo box is properly linked to a list source range, then all other fields in the property browser, which are normally used to specify the list source of the control, are disabled. In particular, these are "List entries", "Bound field", "Type of list contents", and "List content".

If the “Source range of the list” is emptied, then the “List Entries” are also immediately reset. This is to not confuse the user with the list still displaying the cell content.

Figure 1: Control property browser String List – Control Property Browser, tab page “Data”

Item	English	German	Comments
Fixed text	Linked cell	Verknüpfte Zelle	
Fixed text	Contents of the linked cell	Inhalt der verknüpften Zelle	
List box entry	Selected entry	Ausgewählter Eintrag	
List box entry	Position of the selected entry	Position des ausgewählten Eintrags	
Fixed text	Source range of the list	Quellbereich der Liste	

Tab Order

In the “Data” tab page of the control's property browser: All enabled fields from top to bottom.

Key Board Shortcuts

None.

Error Conditions

Invalid cell addresses or cell ranges (i.e. exceeding the sheet limits) are deleted from the control property browser without an error message.

Future Tasks

It should be possible to link form controls not only to concrete addresses or address ranges, but also to named ranges defined in a document (this may occur in MS Excel documents). The form controls would then only know the name of the range to exchange data with, and when the range is moved, this is automatically propagated to the control.

When list boxes will support multi-column data, the different columns of a list source range will be used to fill them.

Legal Issues

None.

Notes

None.

References

None.

Form Controls Linked to Spreadsheet Cells

Page 6

http://specs.openoffice.org/calc/compatibility/linkedcontrols.sxw

Daniel Rentz

2003-09-23T12:34:21

Frank Schönheit