Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..
327 views

Adding new fields to a table for an array

I am trying to add a new array field to a table, but the expected results are matching the actual.

I want to be able to add a new field to a table, that when I search at the database level, I see two rows of data with the same unique identifier.  Let me explain in an example that already works in our environment.

I had modified the model table some time ago.  Added a new field as an Array to capture approvers that need to approver certain line items.  When I look at this table in the database, if there are multiple lines of approvers, I see multiple rows of data.  But when I just tried to duplicate this to another table, the row only appears once and my new field contains all the data in one column.

How can I create an array field that will do what I am looking for.  It has been awhile since I did the previous and I don't remember any additional things I needed to do besides create an array field, with one character field, and point it to a1 SQL Table in the dbdict which it created fine.

This is for Service Manager 9.30

0 Likes
12 Replies
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Adding new fields to a table for an array

From the sounds of it, the system created your array field as a Blob within the main table instead of create a separate array table? Is that right?  so, for example, you see a Blob instead of a probsummarya5.....right?

Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: Adding new fields to a table for an array

I guess that is what I am seeing?  What is also weird is that when I did this previously with the model table, it also built another field into the table called record number.  That didn't happen here.  It is still in a new table, this is for Configuration Items so it built the table as device2a1.

 

 

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Adding new fields to a table for an array

what is the name of the field you added?  Can you show us a screenshot of the dbdict record that has the new field?

Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: Adding new fields to a table for an array

I added two fields, upstream and downstream.  I know this isn't the best way to accomplish this but this is how the requirements came.  Attached is a screen shot of the Device DBDICT.

I guess the better question would be, How do I create a new array field on a table so that they create seperate entries at the SQL Level.

 

 

0 Likes
Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: Adding new fields to a table for an array

And yes, I know that I should have two seperate a tables per field but I was just testing something and it still didn't help.

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Adding new fields to a table for an array

well, your dbdict looks right except they both say a1. 

Just for testing/learning purposes, can you create a dummy field and make it a character array?  Nothing fancy, just a simple array field to see how SM handles it.

Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: Adding new fields to a table for an array

Would you mind detailing the steps you want me to take?

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Adding new fields to a table for an array

1.  pull up the device dbdict
2.  click New Field/Key
3.  In Name, type:  dummy.array
4.  In Type, select:  Array
5.  Click Add.
6.  In type, select: character
7.  click Add.
8.  Click OK.
9.  Click SM Alters

Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: Adding new fields to a table for an array

So following your steps, here are the results.

It created a new field in the M2 table of the device DB.

 

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Adding new fields to a table for an array

I'm sure this is what happened with your other field.  You'll need to convert it.

Try this:

1. Look through your dbdict and see what the last "a" table was, for example a10.

2.  Double-click on that first line of the dummy array to open an edit window.

3.  In SQL table, change m2 to the whatever the next a table should be, such as a11.

4.  Click OK.

5.  Double-Click on the 2nd line of the dummay array.

6.  Chage SQL Type to VARCHAR2(60) (this is the default for me in Oracle, do whatever your default text field is)

7.  Change SQL Table to same a table, such as a11.

8.  Click OK.

9.  Go to SQL Tables tab.

10. Create a new line for the new a table, such as:

Alias: a11

Name: DEVICE2A11

Type: oracle10 (or whatever it is for you)

11.  Now click OK and save the dbdict.  This should convert the TEXT field to an array table.  If you then go look in the database, you see an array table.

0 Likes
Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: Adding new fields to a table for an array

Still the same results.  I have opened a case with HP on this.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.