How to Use Edit with Excel

PointForce iTopia enables you to export the results of a search operation into a Microsoft Excel spreadsheet, and then modify the records obtained by the original query or add new ones altogether.

While viewing the results in Excel format, you can perform a number of actions, such as:

• Sort or filter columns;
• Perform mass updates;
• Perform mass data loads; and,
• Save the results directly in the database, and then email or print them in Excel format. Please refer to the Excel product's online help system for more details.

It is also possible to create records in Excel without having to export data. Usage rules are the same as described in this topic. Using the Create Records with Excel action on the search criteria/results page of a view, you can create records using Microsoft Excel without having to export an existing record.

The following list identifies the parameters in which Edit with Excel works in PointForce iTopia:

• Edit with Excel does not support subtotals—they cannot be exported to an Excel spreadsheet, and therefore, are not available for update. It is assumed that Excel templates will be used to generate totals, if desired.

• Dates and numbers in Microsoft Excel are displayed and entered using the running PC's configured regional settings. Edit with Excel supports this functionality.

• There is a limit to the number of cells that can be safely selected for editing with Excel and/or saved to the database at any one time. (The number of cells represents the number of rows multiplied by the number of columns.) The value of the general.results.max_num_cells property, configured within the Tecsys.properties file, determines the limit, which is set to 50000 by default. This limit identifies the maximum number of cells that can be safely processed without huge performance degradation or reaching system limits at any level: application server, operating system, network and client device (PC). Only a user with sufficient permission may modify this value. Changing the default might require more memory to be allocated for the affected environment.

• For initial data loading purposes, you can use the Load Data feature which is available via the Views resource.

• Although PointForce iTopia supports a total numerical precision of 17 digits, Excel has a limitation of 15 digits. Any attempt to import or export numbers which are greater than 15 digits will result in rounding.

• View names containing non-alphanumeric characters may cause this feature to fail unexpectedly, as they may be incompatible with worksheet naming conventions in Microsoft Excel.

Prerequisites

To use the Edit with Excel feature, the following technical requirements must be met:

• Microsoft Excel (32- or 64-bit) version 2010 and above must be installed on your system.
• A Web services license must be assigned to the user by a system administrator.
• The TECSYS Excel Add-In must be installed.
• The Is Create Allowed flag associated with the view's primary table must be set to Yes.
• The Maintainability Scope flag for all applicable columns associated with the view's primary table must be set to 1 - Maintainable.
• Only views set up via the Role Permissions resource to allow the Maintain List operation, as well as either the Create or Update operation, can support the Edit with Excel feature.

Configuring Interactivity with Microsoft Excel

To configure MS Excel to work with the Edit with Excel feature in PointForce iTopia, see the Configuring Interactivity with MS Excel topic.

Using the Edit with Excel Feature

In this example, let's assume that you have launched the Customers resource to execute a search that will list all the currently saved customers:

At this point, you have the option to take either one record, a handful of selected records, or all available records and edit those records in Microsoft Excel. To do so, simply select the check boxes belonging to each record you wish to edit, click the More Actions action on the Grid Toolbar, and choose either Edit with Excel or Edit All Records with Excel. Alternatively, select a single record by clicking the Actions action appearing on the individual row.

Note: Once you have selected the records to process, if the number of selected rows multiplied by the number of columns is found to exceed the total number of cells allowed to be edited, you are informed that at most total cells divided by number of columns rows have been copied to the Excel worksheet for editing purposes.

Once the action is selected, a separate window opens and transfers the records to Microsoft Excel. You receive control when the transfer is complete. If the system is unable to transfer the records to Microsoft Excel, it will display an error message.

To modify a record, simply change the value of any modifiable column.

To create a new record, add a new row with a unique business key and complete the required columns.

When all the data has been modified or created, click the Save action from the TECSYS iTopia tab in the Excel ribbon to write the data to the database. It may be important to note that this does not save the spreadsheet itself—only the data it contains. Should the number of rows in the Excel worksheet multiplied by the number of columns exceed the total number of cells allowed to be edited, you are informed that at most total cells divided by number of columns worksheet rows have been updated or created. The system processes these rows and ignores the rest. In this case, delete those rows that have a status of Updated or Created and resave the remaining worksheet rows.

The system will send every record until if finds an empty row in the worksheet. You can use this to your advantage to separate information that you want to write to the database from other miscellaneous information.

Once the Save action is performed, the status of every record will be displayed in the Status column. Any record that has been modified successfully will have a status of Updated. Every record that has been created will have a status of Created. Any record with an Updated or Created status has been written to the database. Every record that was unsuccessfully created or updated will have a status of Invalid. This record will also have cell notes in each column where an error occurred. Simply correct the errors and try saving again.

Up to 10 extra columns can be added to the spreadsheet, between two data columns, to make temporary calculations using the data from a view. The data in these new columns will not be written to the database.

If needed, customize the search results view to include all columns you wish to export to Microsoft Excel.

• A warning message is displayed during view personalization when the number of selected columns multiplied by the maximum number of rows to retrieve exceeds the maximum number of cells configured.

• It is possible to delete columns from the Microsoft Excel worksheet; however, deleting business key columns or any required columns may result in an error.

About the Excel Worksheet

The Excel worksheet containing the search results you wish to edit will have the following characteristics:

• Color-Coded Cells. This feature color-codes cells in Microsoft Excel to identify columns having a special significance, and as such, may not be altered.
  • Primary and business key columns are colored dark blue, and cannot be modified.
  • Columns pertaining to the relationships between tables are colored aqua, and cannot be modified, as referential integrity must be maintained.
  • Columns which are colored black may be maintained, given that the correct permissions have been granted by the system administrator.

• Comments. Required columns or columns with domain values will contain notes in the column headings. These notes contain information relating to the kind of data allowed within that column, such as accepted values with their descriptions, or the maximum number of characters allowed.

• The spreadsheet data is configured for automatic filtering.

• The Save action is only available through the Microsoft Excel ribbon's TECSYS iTopia tab provided by the TECSYS Excel Add-In when it is installed. (For details on installing the TECSYS Excel Add-In, refer to the Configuring Interactivity with MS Excel topic.)

  

Note: Edit with Excel may also be set to use the data refresh interval option available in the TECSYS iTopia tab; however, if you are editing data when the refresh operation begins, this will clear your data out. For this reason, this approach is not recommended.