Importing from Excel with a given ID column

Carl Fischer

I'd like to import data from an Excel spreadsheet that includes a key column. This is data from the risk management framework (NIST SP 800-53), so the keys are fairly well standardized to the point where people will often just refer to an item by its key (e.g. AC-6).

Does Jama have a way to do this and use the keys provided instead of generating its own sequentially numbered list? If not, is there a way to change the interface so the imported key column is displayed in lists?

------------------------------
Carl Fischer
Ball Aerospace
------------------------------

[Deleted User]

Jama manages the actual keys (Jama requires the key to be unique, and strings imported from an external source cannot be guaranteed to be unique).   But you can create a custom field (e.g, "NIST-key"), and populate it with those NIST key values.   To display that field in lists, you would just use the standard way you cause a field to be displayed in list (i.e., click on the Gear icon, and add that field to the list of fields being displayed).   You can also then sort and filter on that field.

------------------------------
Geoffrey Clemm
Jama Software
------------------------------
-------------------------------------------
Original Message:
Sent: 06-02-2023 07:59
From: Carl Fischer
Subject: Importing from Excel with a given ID column

I'd like to import data from an Excel spreadsheet that includes a key column. This is data from the risk management framework (NIST SP 800-53), so the keys are fairly well standardized to the point where people will often just refer to an item by its key (e.g. AC-6).

Does Jama have a way to do this and use the keys provided instead of generating its own sequentially numbered list? If not, is there a way to change the interface so the imported key column is displayed in lists?

------------------------------
Carl Fischer
Ball Aerospace
------------------------------

Carl Fischer

Thanks.  Two follow-ups:

1 is regarding the lists of items in the left-hand navigation bar. The Control ID would be really helpful if it were visible here: 

1. Is there any way to get the field to show up in requirement lists?
2. All the requirement associations are linked to the Control ID keys in the spreadsheet. Is there a way to make references in Jama based on another unique key? 

I've attached the actual spreadsheet for reference.  It's a published NIST standard. The Control Identifier column is the one I want to use, and it's refernces are comma-separated in the "Related Controls" section.

Thanks,

  Carl

------------------------------
Carl Fischer
Chief Technologist
Ball Aerospace
------------------------------
-------------------------------------------
Original Message:
Sent: 06-03-2023 07:53
From: Geoffrey Clemm
Subject: Importing from Excel with a given ID column

Jama manages the actual keys (Jama requires the key to be unique, and strings imported from an external source cannot be guaranteed to be unique).   But you can create a custom field (e.g, "NIST-key"), and populate it with those NIST key values.   To display that field in lists, you would just use the standard way you cause a field to be displayed in list (i.e., click on the Gear icon, and add that field to the list of fields being displayed).   You can also then sort and filter on that field.

------------------------------
Geoffrey Clemm
Jama Software
------------------------------

Original Message:
Sent: 06-02-2023 07:59
From: Carl Fischer
Subject: Importing from Excel with a given ID column

I'd like to import data from an Excel spreadsheet that includes a key column. This is data from the risk management framework (NIST SP 800-53), so the keys are fairly well standardized to the point where people will often just refer to an item by its key (e.g. AC-6).

Does Jama have a way to do this and use the keys provided instead of generating its own sequentially numbered list? If not, is there a way to change the interface so the imported key column is displayed in lists?

------------------------------
Carl Fischer
Ball Aerospace
------------------------------

[Deleted User]

1. In the attached screen shot, I believe you are showing the Explorer view (left side of screen).   If so, no, you cannot annotate that view with additional fields.   To see custom fields of items, you would use the List View, which is what shows up on the right side of your screen when you select any container (component, set, folder).
2. Jama relationships are always based on Jama ID's, not some other field (which Jama cannot guarantee is unique).   What you can do is to use the "import relationships" functionality to create Jama relationships based upon those IDs.   For information about importing relationships, see https://community.jamasoftware.com/blogs/kristina-king/2017/04/06/how-to-set-up-and-use-the-relationship-import-plugin and https://help.jamasoftware.com/ah/en/administration/organization-administrator/manage-content/plugins/configure-import-relationships-plugin.html

------------------------------
Geoffrey Clemm
Jama Software
------------------------------
-------------------------------------------
Original Message:
Sent: 06-05-2023 10:45
From: Carl Fischer
Subject: Importing from Excel with a given ID column

Thanks.  Two follow-ups:

1 is regarding the lists of items in the left-hand navigation bar. The Control ID would be really helpful if it were visible here: 

1. Is there any way to get the field to show up in requirement lists?
2. All the requirement associations are linked to the Control ID keys in the spreadsheet. Is there a way to make references in Jama based on another unique key? 

I've attached the actual spreadsheet for reference.  It's a published NIST standard. The Control Identifier column is the one I want to use, and it's refernces are comma-separated in the "Related Controls" section.

Thanks,

  Carl

------------------------------
Carl Fischer
Chief Technologist
Ball Aerospace
------------------------------

Original Message:
Sent: 06-03-2023 07:53
From: Geoffrey Clemm
Subject: Importing from Excel with a given ID column

Jama manages the actual keys (Jama requires the key to be unique, and strings imported from an external source cannot be guaranteed to be unique).   But you can create a custom field (e.g, "NIST-key"), and populate it with those NIST key values.   To display that field in lists, you would just use the standard way you cause a field to be displayed in list (i.e., click on the Gear icon, and add that field to the list of fields being displayed).   You can also then sort and filter on that field.

------------------------------
Geoffrey Clemm
Jama Software

Original Message:
Sent: 06-02-2023 07:59
From: Carl Fischer
Subject: Importing from Excel with a given ID column

I'd like to import data from an Excel spreadsheet that includes a key column. This is data from the risk management framework (NIST SP 800-53), so the keys are fairly well standardized to the point where people will often just refer to an item by its key (e.g. AC-6).

Does Jama have a way to do this and use the keys provided instead of generating its own sequentially numbered list? If not, is there a way to change the interface so the imported key column is displayed in lists?

------------------------------
Carl Fischer
Ball Aerospace
------------------------------