Excel Calculate activity

An activity that lets you write (temporarily or permanently) and read from a Microsoft Excel file in the same step.


Excel Calculate activity

Configure the Excel Calculate activity

To configure the Excel Calculate activity, do the procedure in this topic.

Examples

Good to Know

How to Start

  1. Open Process Builder.

    For information about how to open this screen, refer to Process Builder.


    Open Process Builder
  2. In Process Builder, in the Activity Library, and open the SharePoint tab.

    Open SharePoint tab
  3. On the SharePoint tab, drag the Excel Calculate activity onto your process.

    Drag Excel Calculate activity

Procedure

  1. Complete the fields on the General Configuration screen.
  2. Click Design Time Repository Configuration Design Time Repository Configuration icon.
  3. Complete the fields on the Design Time Template Configuration screen.
  4. Click Run Time Repository Run Time Repository icon.
  5. On the Runtime Repository screen, select the SharePoint as target location for your file..
  6. Click Run Time Repository Configuration Run Time Repository Configuration icon.
  7. Complete the fields on the Runtime Template Configuration screen.
  8. Click Save To Repository Save To Repository icon.
  9. On the Save To Repository screen, select the repository to save your file.
  10. Click Save To Repository Configuration Save To Repository Configuration icon.
  11. On the Save To Repository Configuration screen, configure the repository to save your file.
  12. Click Excel Calculate Configuration Excel Calculate Configuration icon.
  13. Configure Excel Calculate Configuration screen.
  14. (Optional) Click Advanced Advanced icon > Email Notifications E-mail Notifications icon.

    For more information, refer to Email Notifications screen (Process Activities).

General Configuration

Specifies the basic settings for the Excel Calculate activity.

Figure: General Configuration screen

General Configuration screen

Good to Know

  • Documentation for this screen is provided for completeness. The fields are mostly self-explanatory.

Fields

Field NameDefinition

Display Name
Description:
Specifies the activity name that shows in your process.
Allowed Values:
One line of text (a string).

Accepted:

  • Letters
  • Numbers
  • Spaces
Default Value:
None
Accepts Variables:
No
Example:
This is a common configuration field that is used in many examples. Refer to:
  • Examples - Step-by-step use case examples, information about what types of examples are provided in the AgilePoint NX Product Documentation, and other resources where you can find more examples.

Description
Description:
Specifies an optional text description for your activity.
Allowed Values:
More than one line of text.
Default Value:
None
Accepts Variables:
No

Design Time Template Configuration

Specifies the data source for your design time template in SharePoint.

Figure: Design Time Template Configuration screen

Design Time Template Configuration screen

Fields

Field NameDefinition

SharePoint
Description:
Specifies the access token that connects to your SharePoint site.
Allowed Values:
A list of access tokens configured for your environment.
Default Value:
None
Example:
Refer to:

Create Add Token icon
Opens this Screen:
Access Token for SharePoint
Function of this Screen:
Configure an access token to connect to SharePoint.
Example:
Refer to:

Site
Description:
Specifies the URL for your SharePoint site.
Allowed Values:
A valid SharePoint site URL.
Default Value:
None
Accepts Variables:
Yes
Example:

Upload Files To
Description:
Specifies whether to upload files to a SharePoint list or library.
Allowed Values:
  • List Item - Lets you upload files in a SharePoint list.
  • Document Library - Lets you upload files in a SharePoint library.
Default Value:
Document Library
Limitations:

This field is available in these releases:

List/Library
Description:
Specifies the name of the SharePoint library or list.
Allowed Values:
A valid SharePoint library name or list name.
Default Value:
None
Accepts Variables:
Yes

List Item ID
Description:
Specifies the ID of a SharePoint list item.
To Open this Field:
  1. On the Design Time Template Configuration screen, in the Upload Files To field, select List Item.
Allowed Values:
The ID of your SharePoint list item.
Default Value:
None
Accepts Variables:
Yes
Limitations:

This field is available in these releases:

File Path
Description:
Specifies the SharePoint file. The activity uses the location of the specified file when the process runs.
To Open this Field:
On the Design Time Template Configuration screen, in the Upload Files To field, select Document Library.
Allowed Values:
A valid absolute path for the file.
Default Value:
None
Accepts Variables:
No
Limitations:

Some information about third-party integrations is outside the scope of the AgilePoint NX Product Documentation. It is the responsibility of the vendors who create and maintain these technologies to provide this information. This includes specific business use cases and examples; explanations for third-party concepts; details about the data models and input and output data formats for third-party technologies; and various types of IDs, URL patterns, connection string formats, or other technical information that is specific to the third-party technologies. For more information, refer to Where Can I Find Information and Examples for Third-Party Integrations?

Runtime Template Configuration

Specifies the data source for your runtime template in SharePoint.

Figure: Runtime Template Configuration screen

Runtime Template Configuration screen

Fields

Field NameDefinition

SharePoint
Description:
Specifies the access token that connects to your SharePoint site.
Allowed Values:
A list of access tokens configured for your environment.
Default Value:
None
Example:
Refer to:

Create Add Token icon
Opens this Screen:
Access Token for SharePoint
Function of this Screen:
Configure an access token to connect to SharePoint.
Example:
Refer to:

Site
Description:
Specifies the URL for your SharePoint site.
Allowed Values:
A valid SharePoint site URL.
Default Value:
None
Accepts Variables:
Yes
Example:

Document Library
Description:
Specifies the name of the SharePoint document library.
Allowed Values:
A valid SharePoint document library name.
Default Value:
None
Accepts Variables:
Yes

File Path
Description:
Specifies the SharePoint file. The activity uses the location of the specified file when the process runs.
Allowed Values:
  • Choose File Path - Specifies an absolute path for the file.
  • Dynamic File Path - Specifies a process data variable that stores the relative path of the file.
Default Value:
Dynamic File ID
Accepts Variables:
Yes
Limitations:

Some information about third-party integrations is outside the scope of the AgilePoint NX Product Documentation. It is the responsibility of the vendors who create and maintain these technologies to provide this information. This includes specific business use cases and examples; explanations for third-party concepts; details about the data models and input and output data formats for third-party technologies; and various types of IDs, URL patterns, connection string formats, or other technical information that is specific to the third-party technologies. For more information, refer to Where Can I Find Information and Examples for Third-Party Integrations?

Save To Repository

Selects the target storage repository as SharePoint for your files.

Figure: Save To Repository screen

Save To Repository screen

Fields

Field NameDefinition

SharePoint SharePoint icon
Description:
Specifies that SharePoint is the target location for your files.

To select the location of the Excel document, complete the fields on Save To Repository Configuration.

Save To Repository Configuration

Specifies the target location for your Microsoft Excel document on SharePoint.

Figure: Save To Repository Configuration screen

Save To Repository Configuration screen

Fields

Field NameDefinition

SharePoint
Description:
Specifies the access token that connects to your SharePoint site.
Allowed Values:
A list of access tokens configured for your environment.
Default Value:
None
Example:
Refer to:

Create Add Token icon
Opens this Screen:
Access Token for SharePoint
Function of this Screen:
Configure an access token to connect to SharePoint.
Example:
Refer to:

Site
Description:
Specifies the URL for your SharePoint site.
Allowed Values:
A valid SharePoint site URL.
Default Value:
None
Accepts Variables:
Yes
Example:

Document Library
Description:
Specifies the name of the SharePoint document library.
Allowed Values:
A valid SharePoint document library name.
Default Value:
None
Accepts Variables:
Yes

Folder Path
Description:
Specifies the path of the folder.
Allowed Values:
  • Choose Folder Path - Specifies an absolute path for the folder.
  • Dynamic Folder Path - Specifies a variable that stores the relative path of the folder.
Default Value:
Dynamic Folder Path
Example:
  • ${HomeDirectory}\sample2.doc
  • MyFolder1/Sample3.doc

Also refer to:

Overwrite If File Exists
Description:
Specifies if the activity replaces the file if it already exist with the same name at the specified target location.
Allowed Values:
  • Selected - The activity replaces the file if it exists with the same name at the target location.
  • Deselected - The activity saves the file with a different name.
Default Value:
Selected

Excel Calculate Configuration

Specifies an operation to create, read, and store data from a Microsoft Excel file.

Figure: Excel Calculate Configuration screen

Excel Calculate Configuration screen

Fields

Field NameDefinition

SharePoint
Description:
Specifies the access token that connects to your SharePoint site.
Allowed Values:
A list of access tokens configured for your environment.
Default Value:
None
Example:
Refer to:

Excel Services URL
Description:
Specifies the URL of SharePoint Excel Services.
Allowed Values:
Default Value:
None
Accepts Variables:
Yes
Example:
  • http://ind-01:8888/_vti_bin/ExcelService.asmx
  • $\{SharePointURL\}$\{ExcelService\}

Runtime Template
Description:
Specifies the name of Microsoft Word or Microsoft Excel template file the activity uses to create Microsoft Word or Excel file when the process runs.
Allowed Values:
  • A valid template file name.
  • A process data variable.

    You can use App Data screen to specify a process data variable.

Default Value:
None
Accepts Variables:
Yes
Limitations:

Some information about third-party integrations is outside the scope of the AgilePoint NX Product Documentation. It is the responsibility of the vendors who create and maintain these technologies to provide this information. This includes specific business use cases and examples; explanations for third-party concepts; details about the data models and input and output data formats for third-party technologies; and various types of IDs, URL patterns, connection string formats, or other technical information that is specific to the third-party technologies. For more information, refer to Where Can I Find Information and Examples for Third-Party Integrations?

Design Time Template
Description:
Specifies the name of Microsoft Excel template file that is used when you are creating or changing a process.

After you enter the Microsoft Excel template file name in the Design time Template box, the named fields box is populated with all the named fields from Microsoft Excel sheet.

The design time template document is used only to access the named fields of Microsoft Excel sheet. This document is not used at runtime.

Allowed Values:

A valid Microsoft Excel template file name with the full path.

Note the following:

  • The runtime template file and the design time template file must have the same named fields. This indicates that both the files must have same content.

  • Also, while designing in Process Builder, the file must be accessible to the app designer from their computer using the Browse button.
Default Value:
None
Accepts Variables:
No
Example:
C:\Excel\ExcelSheet\ExcelServices.xlsx

Save To
Description:
Specifies the location and file name to save the resulting Microsoft Excel document.
Allowed Values:
Default Value:
None
Accepts Variables:
Yes
Example:
http://ind-01:8888/ExcelServicesDemo/${/my:myFields/my:SaveWrite}.xlsx

Click to Load Excel Fields
Description:
Specifies the named fields populated from Microsoft Excel sheet once you specify the design time Template file.

This field is used for the mapping purpose. You must map Microsoft Excel named fields to the process data variable to:

  • Write to an Excel document.
  • Read from an Excel document.

A named field in Microsoft Excel is the name of a cell or range that is used instead of the cell's reference name. For example, empName, a named field, is easier to use instead of its cell's reference name, C21.

Allowed Values:
A named field selected from the list.
Default Value:
None
Accepts Variables:
No

Move All
Function:
Moves all the fields in the Excel Field box at one time.

Excel Field
Description:
Specifies Microsoft Excel field to map to the process value. The mapping is performed to change the fields in Microsoft Excel document, supplied as output.
Allowed Values:
Read only - A Microsoft Excel field.

Use the Move All button to move the fields from Click to Load Excel Fields box to the Excel Field column.

You can also drag the Microsoft Excel fields from the Click to Load Excel Fields box to the Excel Field column.

Default Value:
None

Process Value
Description:
Specifies the process data variable to map to the Excel Field to change each field in Microsoft Excel document.
Allowed Values:
  • A number or a text string that can contain spaces.

    This must be in accordance with the data type of Microsoft Excel field.

  • A process data variable

Note:

  • Once Microsoft Excel field is in the Excel Field column, to map a Excel Field to a process value, select the row, and use Process Data screen.
Default Value:
None
Accepts Variables:
Yes

Formatted
Function:
Specifies if the formatted value of Microsoft Excel cell is read instead of the actual value.

The formatted values are useful to denote numbers or dates such as in e-mails.

To Open this Field:
Click the Read tab.
Allowed Values:
  • Deselected - The numerical value of the cell is read. For example, 1023
  • Selected - The formatted value of the cell is read. For example, €1,023.34
Default Value:
Deselected.

However, when you add a row, by default, the Formatted check box is selected.

Example:
When the Formatted check box is selected:
  • A Percentage type cell returns 15%, instead of 15, where 15 is the numerical value and % is the formatting code.
  • A Currency type cell returns €1,023.34 instead of 1023.34, where 1023.34 is the numerical value and € is the formatting code.

This indicates that the formatting code of Microsoft Excel cell is also read along with the cell's numerical value.

Delete Delete icon

Function:
Deletes the row.