Best practices for Excel Functions integrations
For a smooth Excel Functions integration setup, we recommend adhering to the following best practices.
Combining integrations where possible
If you are running multiple formulas/integrations for the same project or set of items within a project, don’t create a separate standalone integration for each formula. Rather, create a single integration with multiple field mapping groups.
If multiple standalone integrations are updating the same set of items, syncs can get caught in a continuous loop of updates, slowing down the system and creating new versions of items unnecessarily. Combining formulas to the same integration prevents this, because each integration has internal filtering capabilities to prevent extraneous updates.
Using the correct field type for your formula
When mapping Jama Connect fields in an Excel Functions integration, be sure to use the field type that best fits your formula.
Mathematical Functions — Use Jama Connect Integer or Float fields for mathematical functions such as SUM or MAX. Text fields are not supported. For these functions, the input cells in your template must also include non-numeric placeholder values such as “n.” Make sure the Excel formula also includes an argument to omit the placeholder value from the calculation. For an example, see the Max Severity Excel template available on the Community.
Non-Numeric Functions — Use Jama Connect Text or HTML fields. Placeholder values can be included in the Excel template but are not required.
Picklists — Make sure that the value being written to the picklist from Excel exactly matches the picklist display value. See Processing numeric values using picklists for additional guidelines.
For non-numeric functions involving picklists, placeholder values can be included in the Excel template but are not required.
For numeric functions involving picklists, the input cells in your template must also include non-zero placeholder values. Make sure the Excel formula also includes an argument to omit the placeholder value from the calculation.
Multi-select Picklists — Using Excel Functions, you can write to and from multi-select picklists. The following field type combinations are supported:
Multi-select to multi-select
Multi-select to text
Text to multi-select
When writing from a text field or from the Excel template to the multi-select field, you can use commas to separate values. Jama Connect Interchange accepts both with leading spaces (as in Low,Medium,High) and without leading spaces (as in Low, Medium, High).
You can also combine the values from two separate multi-select fields into a third. Duplicates are removed automatically. For example:
Input fields | Output field |
|---|---|
Field 1: Low, Medium Field 2: Medium, High | Field 3: Low, Medium, High |
Here is a simple example of a field mapping configuration to accomplish the above scenario. In the Excel template, use the following formula in cell C1: =TEXTJOIN(",",TRUE,(A2:B2))
Using functions supported by Apache POI
The Excel Functions module uses an Excel SDK (Apache POI 5.2.3) to run functions in memory. The SDK supports an extensive list of functions, but some are still not supported.
Make sure your Excel template includes only functions supported by the Apache POI SDK. See a full list of supported functions in Supported Excel Functions.
Here is a list of commonly requested functions that are NOT supported, and their recommended substitutes.
Unsupported function | Support function |
|---|---|
CONCAT | CONCATENATE |
XLOOKUP | LOOKUP, VLOOKUP |
IFNA | ISNA |
IFS | IF (nest multiple IF statements for the same effect) |
Also, the SDK doesn’t currently support R1C1 format, or functions that reference Excel macros or Excel tables. For details, see the Apache POI website.
Avoiding use of Excel tables
The Excel SDK doesn't support references to Excel tables or to individual cells inside those tables. If you try to reference an Excel table in your formula, you receive an error message on the Logs page similar to the following: “The column doesn’t exist in table [Table Name]”. To address this, convert the table to a range, and update your formula to reference that range of cells rather than the table name.
For example, change this formula:
From this... | To this... |
|---|---|
=VLOOKUP(A1,Table1,2,FALSE) | =VLOOKUP(A1,A2:E10,2,FALSE) |
Using IFERROR to prevent processing errors
Use the IFERROR function whenever possible to catch errors in your formulas. Jama Connect Interchange generally doesn’t process templates containing errors.
Ensuring mapped cells don’t include sample data
Only include sample data in your Excel template if you want that data incorporated in calculations and written back to Jama Connect. In most cases, cells are blank except for the formulas applied to them.
In certain scenarios, you might want to include static data in the Excel template to intentionally incorporate into formulas. For example, the Excel template can include a separate sheet with a static library of data that is referenced by the mapped formulas.
Processing numeric values for picklists
When processing picklist values, Interchange reads the Display Value and not the Numeric Value. If you want to run formulas based on the numeric value of a picklist selection, be sure your template includes a way to first map the Display Value to the Numeric Value.
For example, if you are calculating a risk score based on the selection in the following FMEA Severity field, make sure your template includes a lookup table to map the Display Value (Low, Medium, High, Critical) to the Numeric Value (1, 2, 3, 4).
This mapping can be accomplished using a VLOOKUP formula or nested IF statements.
Here is a simple example:
Marking output fields as “Read Only” and “Allow API Overwrite”
Identify any fields in Jama Connect that are being written to from Excel (←), then mark those fields as read-only in Jama Connect. This prevents users from updating these fields manually.
If you mark a field as read-only in Jama Connect, you must also select Allow API Overwrite in the field settings to allow the integration service to write the updated Excel value to the item.
How fields are processed
All mapped fields are processed at the same time, and not in sequence.
The order in which fields or field mapping groups are listed on the Field Mapping page doesn’t impact how calculations are run. If fields must be processed in a certain order, incorporate that order into the formula itself.
How error links are processed
If something is preventing an item in Jama Connect from syncing correctly with Excel Functions, that item is marked with a red “Error” status on the Links page in Interchange. The Message column provides a little more information about the error so that you can address it. Common reasons for errors include:
The item in Jama Connect is in Locked status.
The Excel template includes an unsupported function, or the function is in an error state.
The mapped field in Jama Connect can't support the value that Interchange is attempting to write to it, for example, an invalid picklist value,\ or an invalid data type.
As long as the integration is running (toggled ON), Interchange constantly retries any error links at the defined integration frequency (5 seconds, 15 seconds, etc). As soon as the cause of the error is resolved (the item is unlocked, the Excel template error is corrected, etc) and Interchange can successfully sync the item, the Error status is removed and the item is changed to a green Success status.