sql 2005 and feature sysaid updates.

 
Author
Message
Elite SysAider
234
 
Hello illient,

I have added some custom fields to a couple of the sysaid tables and 2 little extra tables as well. I've made a nice Access database and I needed a couple of custom fields.
will they stay there when I update SysAid to a newer version??

Kind regards,
René
Take a look at my 3D creations and here is a little animation I made
SysAid VP Customer Success
601
 
René,

Basically, the changes you mentioned that you make on the SysAid's database should not affect the system. Version updates usually make changes to the database, adding new tables and adding new columns to existing tables. The only problem that could occur would be if a new version will try to create a table with the same name that you chose (or add a column with the same name).

Couple of advices:
1. Try not to change existing tables. While I cannot think of any obvious problem that will occur by doing that, it has a higher chance of causing issues. You can create a table and cross-reference data with existing SysAid tables (if it relates to asset then you can connect it by MACHINE_ID, if it relates to service reqeust then you cna connect it by ID, etc).
2. When you do these changes, make sure to choose table/column names that are unique to prevent that problem from occurring (maybe add your company name as a part of the name - E.G. Acme-Table1, Acme-Table2, etc).

In case of a problem that may occur (that I cannot predict now), we can always reconstruct the table to fix it.

Thanks,
Joseph.
Elite SysAider
234
 
Dear Joseph,

Thank you very much for the reply.
I know it is best to make a new table for the "extra" fields I need to add.
The problem is, it has to be a 1 on 1 relation. This means that we have to empty all the records make the new tables and relate them 1 on 1 with the "master" table.
Now when we fill in the new records it works.

I can't add a new Table and relate it 1 on 1 with the existing and already filled with records "master" table. (dbo_company)
Or do I miss something here?


We use the dbo_comapny table. In this table I've added extra fields we need in Access. Like 3 E-mail fields A webpage field etc.
I'm not a Access Guru so perhaps It can/must be done different.
If you have any tips....

Kind regards,
René

Take a look at my 3D creations and here is a little animation I made
SysAid VP Customer Success
601
 
René,

If you are referring to the company table, you could do the following:

1. Create a new table named AcmeCompanyAdditions (replace Acme with your company name). Create a column for company_id (make it a primary key) and then all other columns that you need.

2. Run the following query:
insert into AcmeCompanyAdditions (company_id,) select company_id from company;

This will make a new line on the new record for every company that you have within the new AcmeCompanyAdditions table. You can add your information into the other columns in that table.
You can also setup Access to cross link those tables (meaning, you can take some information from company and some information from AcmeCompanyAdditions. The company_id data should relate the two tables). I'm not sure how to do this, but I think that you can find help at https://www.sqlservercentral.com/articles/SQL+Server+2000+Upgrading/accesstosqlserverlinkingtables/1722/

3. The query on step 2 is a one time update. You should run this query periodically to update new companies from SysAid into the new table.
Try to find a way to schedule a script that would do that.


Elite SysAider
234
 
Thanks again for the help.

I'll try to do as you told.
In Access I know how to link the 2 tables. The problem I have/had is the missing index keys of the new table.
The new table does not have unique keys yet. So if I link the new table to the company table I'll get an error of course.
The company table have lets say, 10 records with primary keys 1 to 10.
My new table does not have the same 1 to 10 primary keys yet because the table is still empty.
But as you told, I must run a update query to fill the new table with corresponding ID's of the company table.

Haha, I need a SQL course I guess. I'm learning every day.

Kind reards,
René
Take a look at my 3D creations and here is a little animation I made
Elite SysAider
234
 
Dear Joseph,

I did as you told and it works. Great....
Now I need to add fields to my new table and delete the fields I added in the original company table.

Kind regards,
René
Take a look at my 3D creations and here is a little animation I made
SysAid Wiz
915
 
Joseph Zargari wrote:
1. Try not to change existing tables. While I cannot think of any obvious problem that will occur by doing that, it has a higher chance of causing issues. You can create a table and cross-reference data with existing SysAid tables (if it relates to asset then you can connect it by MACHINE_ID, if it relates to service reqeust then you cna connect it by ID, etc).

I can give you one...
I deleted several tabs to streamlined the service info once and the view go bezerk. Took support months to figure it out.
Problem came from a field in one of those tabs. Apparenty somebody in sysaid somehow hardcoded that field to sysaid's "kernel" so sysaid kept looking for that field.
I stopped customizing since then.


This message was edited 2 times. Last update was at Nov. 07, 2008 08:00 PM

Elite SysAider
234
 
You mean a tab on the webpage in SySAid?

I don't delete any fields in the database tables. I only added some extra fields.
I made a new table with the missing fields and linked that one to a table in the database. The extra added fields in the original table I will remove.

Kind regards,
René
Take a look at my 3D creations and here is a little animation I made
SysAid VP Customer Success
601
 
René Rijk wrote:Now I need to add fields to my new table and delete the fields I added in the original company table.


René,

Adding and removing columns to/from the tables is done with a basic ALTER TABLE command. You get learn it at https://www.techonthenet.com/sql/tables/alter_table.php

Let me know if you need some more assistance,
Joseph
Elite SysAider
234
 
Thank you Joseph.

I'm still busy with the extra table and linking the Company table and my extra table together in Access.
The insert to code works ok.
But there is a drawback.

I have made 1 Form in access. On this form there are fields from the Company table and my Xtra tabel.
Now when I add a new record it goes like this,

I fill in the company name
I fill in the rest of the fields from the company table.
Now I come to the fields of my Xtra table...
Here it goes wrong.
Because the new company record is not saved yet so there is no new Primary ID created.
This will cause problems when I fill in fields from my Xtra table.
At the moment I start typing in a Xtra table field, Acces tells me that it can't be done because the ID does not exists.

Your tip for the Insert into query works but only if I first add a new record (new company) save the record run the insert into query and after that I can continue with filling in the rest of the fields...

Not a great solution...

I know you don't work with Access so I'll try to find out how to make this work myself...
Untill then I will keep it as it is now... Xtra fields in the company table.
I love databases..

Kind regards,
René

PS:
If you have any tips on how to create a link to a file field in SQL, I would love to hear it..
Although this is a bit beyond SySAid support of course...
Take a look at my 3D creations and here is a little animation I made
SysAid VP Customer Success
601
 
René,

You can make that form add the company ID to both tables. That should solve your problem.
However, before getting this form in to regular use, make sure to thoroughly test it (I'm not sure how SysAid would react if you insert new records into its tables). Try to create a company with that form, and then check if you can access it in SysAid. Also, check if you can successfully create another company in SysAid's interface.