11/23/2024 8:51:57 PM
slxdeveloper.com
Now Live!
|
|
| |
| Creating SalesLogix Table ID Values - The Definitive Guide |
|
| Description: |
Knowing how to create valid SalesLogix table ID values is a crucial part of working with SalesLogix. The subject of creating SalesLogix table ID values is common among those new to SalesLogix. Let's face it. There is some knowledge that is required when working with SalesLogix, and creating table IDs is no exception. This article will provide all details needed to start creating ID values to get you inserting new data in SalesLogix in no time.
|
| Category: |
SalesLogix OLE DB Provider
|
| Author: |
Ryan Farley
|
| Submitted: |
8/10/2006
|
| |
|
| Stats: |
Article has been read 83601 times
|
Rating:
  - 4.7 out of 5 by 10 users |
|
|
|
fiogf49gjkf0d
Knowing how to create valid SalesLogix table ID values is a crucial part of working with SalesLogix. The subject of creating SalesLogix table ID values is common among those new to SalesLogix. Let's face it. There is some knowledge that is required when working with SalesLogix, and creating table IDs is no exception. This article will provide all details needed to start creating ID values to get you inserting new data in SalesLogix in no time.
SalesLogix table ID values are a beast of their own. The table you are inserting to determines what the table ID will look like. The ID is comprised of a single char, which represents the type of most tables. Custom tables will all start with a 'Q' and most stock tables will have their own table prefix char. The second set of 4 digits represents the site code for the local database. The sitecode is a code to uniquely identify all databases in a SalesLogix implementation) for the local database. The last 7 digits are a incrementing base 36 number.
[SINGLE CHAR][SITE CODE][INCREMENTING BASE-36 NUMBER] -> Sample: AQF8AA0006EH
You can see the list of tables and the last base-36 number used for the table (for the given site code) in the SITEKEYS table in your SalesLogix database. Note, your custom tables will all fall under the 'Other' category.
I've covered the topic of creating table ID values for SalesLogix before. So why the new article? Well, there are a few other things not covered by the original article. Firstly, there is another route not mentioned in the original article when creating ID values from within SalesLogix scripts. Also, the code samples are in VBScript (which, frankly, sucks). Also, there are some gotchas to be aware of. This article will attempt to cover all of those areas. Of course, as soon as I decided to call this "the definitive guide" I was destined to forget something, but I'll give it my best shot ;-)
Creating new table ID values from inside of SalesLogix VBScript plugins is a simple task. If all you need is a single ID value, then there is a built in function to do that. The GetIDFor function can be used to return a single ID value for a specified table.
accid = Application.BasicFunctions.GetIDFor("account")
That is sure easy enough. If you need to create multiple ID values at once (e.g.: to pre-create the ID values for multiple rows you are about to insert), you can also use the SalesLogix procedure SLX_DBIDS from VBScript plugins as well. We'll cover that next (this can also be seen in the original article as well).
The SLX_DBIDS procedure is a procedure you can call to create SalesLogix table ID values. It is not a database level procedure. You won't find it in the database. It exists in the SalesLogix provider only and therefore can only be used via a connection that is using the SalesLogix provider (to be clear, this means that the connection must use a SalesLogix connection string). You can call it to create a single ID or multiple ones at once.
So, why might you want to create multiple ID values at once? If you are looking for speed and enhanced performance, you can pre-create the ID values for the rows you are about to insert. Basically, when you create a new ID value, a trip is made to the SITEKEYS table, where it grabs the last incremented value for the table and local sitecode. It increments it by one, writes it back to the SITEKEYS table and then gives you your ID value. When you create multiple ID values at once, it goes to the SITEKEYS table, grabs the last incremented value, increments it by the number of ID values you are creating and writes it back. Then it continues to give you the list of created ID values. Far more efficient and could drastically reduce the trips to the SITEKEYS table, especially if you're working with a very large number of rows.
To use the SLX_DBIDS procedure, you establish a connection via the SalesLogix provider and then call it, passing the table to create the ID for and the number of ID values to create. (BTW, in the code samples, we'll use the using statement, because it rocks, but of course, you could do it with a try/finally block as in the VB.NET samples). These code samples will return a single ID value.
Sample
SLX_DBIDS([TABLENAME], [NUMBER TO CREATE])
C#
public string NewID(string table)
{
using (OleDbConnection conn = new OleDbConnection(SlxConnectionString))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(string.Format("slx_dbids('{0}', 1)", table), conn))
{
return cmd.ExecuteScalar().ToString();
}
}
}
VB.NET
Public Function NewID(ByVal table) As String
Dim conn As New OleDbConnection(SlxConnectionString)
Try
conn.Open()
Dim cmd As New OleDbCommand(String.Format("slx_dbids('{0}', 1)", table), conn)
Return cmd.ExecuteScalar().ToString()
cmd.Dispose()
Finally
conn.Dispose()
End Try
End Function
When the data is returned, it is returned with a single column named 'ID'. If multiple rows are returned, then you'll get back multiple rows in the ID column. Let's just look at a quick sample that returns multiple rows, just so we're all clear.
C#
public List<string> NewID(string table, int count)
{
List<string> list = new List<string>();
using (OleDbConnection conn = new OleDbConnection(SlxConnectionString))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(string.Format("slx_dbids('{0}', {1})", table, count), conn))
{
OleDbDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (r.Read())
{
list.Add(r.GetString(0));
}
r.Close();
}
}
return list;
}
VB.NET
Private Function NewID(ByVal table As String, ByVal count As Integer) As List(Of String)
Dim list As List(Of String) = New List(Of String)
Dim conn As New OleDbConnection(SlxConnectionString)
Try
conn.Open()
Dim cmd As New OleDbCommand(String.Format("slx_dbids('{0}', {1})", table, count), conn)
Dim reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
list.Add(r.GetString(0))
End While
reader.Close()
cmd.Dispose()
Finally
conn.Dispose()
End Try
Return list
End Function
In the samples above, I am returning a generic list of the ID string values instead of a DataTable to keep the data work separate from where ever these methods are being called from.
The first parameter passed to SLX_DBIDS is the name of the table you want to create new ID values for. However, there are some exceptions to this. Internally, each table has a key name. This is what is used for creating table ID values. The problem is that some of the table names do not match up with their key name. For example, if you are in the SalesLogix client and attach a file to a contact record, then examine the row in the ATTACHMENT table, you'll be able to match that ID value up with the KEYTYPE=25 from the SITEKEYS table, that is the 'Other' key type, not the actual key type for the attachment table (which is KEYTYPE=37) as listed in the SITEKEYS table. This is because when the client requests a new ID value for an attached file, it uses it's key name, which is FILEATTACH, instead of it's table name, which is ATTACHMENT. The problem that this can cause is this; if the client uses KEYTYPE=25 for creating ID values for the ATTACHMENT table (By creating an ID for key name 'FILEATTACH', instead of 'ATTACHMENT'), and your own code uses KEYTYPE=37 for creating ID values for the ATTACHMENT table (by creating an ID for table name 'ATTACHMENT', instead of 'FILEATTACH') then at some point the ID values will conflict. That is, you'll end up attempting to duplicate the ID values of the table as they will not be unique (and you'll get errors). There are 8 tables that fall into this category where the key name does not match the table name.
| Table Name | Key Name |
| ATTACHMENT | FILEATTACH |
| USERNOTIFICATION | USERNOTIFY |
| AGENTS | HOSTTASK |
| RESOURCELIST | RESOURCE |
| USEROPTION | USERVIEW |
| JOINDATA | JOIN |
| PROCEDURES | PROCEDURE |
| SEC_FUNCTIONOWNER | FUNCTIONHANDLER |
To solve this problem, we'll just need to also use the key name for these tables instead of the table name. That way, we'll match how the SalesLogix client creates ID values for these tables. Let's modify the code we used above to do this:
C#
public List<string> NewID(string table, int count)
{
List<string> list = new List<string>();
using (OleDbConnection conn = new OleDbConnection(SlxConnectionString))
{
string keyname = table;
switch (table.ToUpper())
{
case "ATTACHMENT": keyname = "FILEATTACH"; break;
case "USERNOTIFICATION": keyname = "USERNOTIFY"; break;
case "AGENTS": keyname = "HOSTTASK"; break;
case "RESOURCELIST": keyname = "RESOURCE"; break;
case "USEROPTION": keyname = "USERVIEW"; break;
case "JOINDATA": keyname = "JOIN"; break;
case "PROCEDURES": keyname = "PROCEDURE"; break;
case "SEC_FUNCTIONOWNER": keyname = "FUNCTIONHANDLER"; break;
}
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(string.Format("slx_dbids('{0}', {1})", keyname, count), conn))
{
OleDbDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (r.Read())
{
list.Add(r.GetString(0));
}
r.Close();
}
}
return list;
}
VB.NET
Private Function NewID(ByVal table As String, ByVal count As Integer) As List(Of String)
Dim list As List(Of String) = New List(Of String)
Dim conn As New OleDbConnection(SlxConnectionString)
Try
Dim keyname As String = table
Select Case table.ToUpper()
Case "ATTACHMENT" : keyname = "FILEATTACH"
Case "USERNOTIFICATION" : keyname = "USERNOTIFY"
Case "AGENTS" : keyname = "HOSTTASK"
Case "RESOURCELIST" : keyname = "RESOURCE"
Case "USEROPTION" : keyname = "USERVIEW"
Case "JOINDATA" : keyname = "JOIN"
Case "PROCEDURES" : keyname = "PROCEDURE"
Case "SEC_FUNCTIONOWNER" : keyname = "FUNCTIONHANDLER"
End Select
conn.Open()
Dim cmd As New OleDbCommand(String.Format("slx_dbids('{0}', {1})", keyname, count), conn)
Dim reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
list.Add(r.GetString(0))
End While
reader.Close()
cmd.Dispose()
Finally
conn.Dispose()
End Try
Return list
End Function
Now we have our method complete. Not only will it create valid SalesLogix table ID values, but also ensure that the proper key name is passed for the problem tables and guarantee that we won't have ID conflicts on those tables.
If you are on version 6.2.1 or higher, then you can make use of an awesome new feature. Auto-incrementing ID values. If you've worked with identity columns in SQL or Access then you'll love this feature. This will allow you to simply omit the ID value from your insert and it will be created for you automagically by the SalesLogix provider. This feature is not on by default. You have to turn it on for the tables you want it to be active on (which I don't like. I think it should just be on for all tables by default).
Beware! Although this feature did get introduced with SP1 for SalesLogix 6.2, I would only recommend using it on version 6.2.3 or higher. 6.2.1 and 6.2.2 had a nasty bug related to this feature that caused problems when the data is synched out the remotes. This bug was fixed in SP3.
Here's how you turn this feature on. A new column called AUTOINCREMENT now exists on the SECTABLEDEFS table. You can set this to "T" to turn on this feature for the field you want to enable it for, or an "F" (or NULL) to turn it off. The feature is only supported for the primary key for the table, and it is ignored for any other columns. This is also now seen as a new checkbox for the key column within the DB Manager of the Architect or Administrator tools.
Another cool item added with the auto-incrementing ID values was the @@IDENTITY variable. This variable works the same way it does in SQL Server with identity columns. After a new ID value is automatically generated via the AUTOINCREMENT feature, you can use this variable to get the ID value that was created. You can use this:
select @@identity
To get the last auto-incremented ID value. You can even use it like this:
select * from account where accountid = @@identity
to select the account record of the last inserted account. That's just cool.
While creating a valid table ID for SalesLogix is only the first step in writing code to insert data, it is an important one. Now you know all you'll ever need to get this done. Don't you feel good now?
-Ryan
|
|
|
|
|
|
Rate This Article
|
you must log-in to rate articles. [login here]
|
|
|
|
Please log in to rate article. |
|
|
|
Comments & Discussion
|
you must log-in to add comments. [login here]
|
|
|
- subject is missing.
- comment text is missing.
|
|
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 8/14/2006 3:31:04 PM | fiogf49gjkf0d Hi Mike,
thanks for this great article! You might remember - I was the guy who needs some help with the slx_dbids() in the last days ;-)
Especially the section "Exceptions to be aware of" was very helpful for me, because I had some trouble with generating IDs for the attachment table!
What would we do without slxdeveloper.com??????
Thanks a lot!
Cheers,
Thomas | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 8/14/2006 3:32:12 PM | fiogf49gjkf0d Sorry, I meant "Hi Ryan" !
Thomas | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 8/14/2006 3:32:28 PM | fiogf49gjkf0d Sorry, I meant "Hi Ryan" !
Thomas | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 8/14/2006 3:40:36 PM | fiogf49gjkf0d Hehe. Thanks Thomas!
-Ryan | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 9/28/2006 5:16:02 PM | fiogf49gjkf0d Ryan,
I'm a bit confused by the 'Exceptions to be aware of' section in your article re: slx_dbids.
I tried a test to create ids for all of the tablenames and keynames in your list, and I got mixed results (against Slgx 6.2.0.1474). These are the results of getting 100 ids for each item in the list:
"ATTACHMENT" QQF8AA0000DW
"FILEATTACH" QQF8AA0007SY
"USERNOTIFICATION" QQF8AA0007VQ
"USERNOTIFY" QQF8AA0007YI
"AGENTS" QQF8AA00081A
"HOSTTASK" QQF8AA000842
"RESOURCELIST" RQF8AA000000
"RESOURCE" QQF8AA00086U
"USEROPTION" vQF8AA00006J
"USERVIEW" QQF8AA0008CE
"JOINDATA" jQF8AA00009M
"JOIN" >>> error parsing SQL <<<
"PROCEDURES" XQF8AA00002X
"PROCEDURE" QQF8AA0008F6
"SEC_FUNCTIONOWNER" WQF8AA00003W
"FUNCTIONHANDLER" QQF8AA0008HY
It looks like some of the pairs return a different entity and site prefix (RESOURCELIST/RESOURCE), while other pairs both return Custom entity keys (ATTACHMENT & FILEATTACHMENT), and one fails entirely (JOIN).
Any ideas on this? And is this still an issue with 7.0 to your knowledge?
Thanks,
Joe
| |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 10/11/2006 11:35:09 AM | fiogf49gjkf0d Hi Joe,
As far as the expections go, there are differences in using the table name vs the name that SLX uses for these tables internally. Creating an ID for "ATTACHMENT" will produce a difference ID value than creating an ID value for "FILEATTACH" and that's where the problem is. Internally, when you add an attachment to a record, when SLX adds the record to the ATTACHMENT table it uses the alias "FILEATTACH" to create the table ID value. So, if you then programatically add a record to the ATTACHMENT table and you use "ATTACHMENT" to create the ID value then you're getting the ID value from a different "pool" and you have the possibility of having a conflict with identical ID values.
For example, if you repeatedly create table ID values for both ATTACHMENT and FILEATTACH you might end up with the following:
<table border=0><tr><td>ATTACHMENT</td><td>FILEATTACH</td></tr>
<tr><td>0001</td><td>0005</td></tr>
<tr><td>0002</td><td>0006</td></tr>
<tr><td>0003</td><td>0007</td></tr>
<tr><td>0004</td><td>0008</td></tr>
<tr><td>0005 Conflict!</td><td>0009</td></tr>
</table>
You can see that eventually we started to attempt to create conflicting ID values and our users will be getting errors and no one will be able to add attachments because it keeps failing due to the conflicting ID values.
For whatever reason, SLX uses these aliases instead of the table names internally when creating ID values, so if we don't use the same we are likely to end up with these conflicts eventually.
Does that make sense?
-Ryan | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 10/11/2006 11:36:23 AM | fiogf49gjkf0d Oops! Forgot I wasn't allowing table tags in the comments :-p (you can still see what I mean, although it looks a mess) | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/30/2007 10:13:03 PM | fiogf49gjkf0d Thanks ,Ryan.But I was unsuccessful.
*-------------------------------------------------
string con ="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sysdba;password=masterkey;Initial Catalog=CSI_backup;Data Source=TRACY;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=TRACY;Use Encryption for Data=False;Tag with column collation when possible=False" ;
using (OleDbConnection conn = new OleDbConnection(con));
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(string.Format("slx_dbids('{0}', {1})", keyname, 1), conn))
{
this.TextBox1.Text = cmd.ExecuteScalar().ToString();
}
*---------------------------------------------------------------------------------
ERROR:Incorrect syntax near 'ACCOUNT'.
Please help me,thanks | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/30/2007 10:16:31 PM | fiogf49gjkf0d Thanks ,Ryan.But I was unsuccessful.
*-------------------------------------------------
string con ="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sysdba;password=masterkey;Initial Catalog=CSI_backup;Data Source=TRACY;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=TRACY;Use Encryption for Data=False;Tag with column collation when possible=False" ;
string keyname = "ACCOUNT";
using (OleDbConnection conn = new OleDbConnection(con));
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(string.Format("slx_dbids('{0}', 1)", keyname), conn))
{
this.TextBox1.Text = cmd.ExecuteScalar().ToString();
}
*---------------------------------------------------------------------------------
ERROR:Incorrect syntax near 'ACCOUNT'.
System.Data.OleDb.OleDbException: Line 1: Incorrect syntax near 'ACCOUNT'
Please help me,thanks | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/31/2007 8:54:44 AM | fiogf49gjkf0d Tracy,
The problem is your connection string. You're using a native SQL connection string (and therefore the SQL provider), not the SLX provider. You need to change your connection string to a valid one using the SLX provider (e.g.: using a SLX connection string) - although I think I saw in the forums that you might have gotten that figured out??
-Ryan | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 6/25/2007 5:13:24 AM | fiogf49gjkf0d Cheers Ryan,
I would like your opinion on custom tables (Mainviews). If you create a new mainview, should you alter the SITEKEYS table to generate a specific tableIDs for each row added to new mainview, or should you just accept the default of the tableids starting Q? (I have been thinking for a while that it would be nice to change the tableids to something specific rather than 'Q....' but I am a bit nervous about what might happen!)
Kind Regards
Nick
| |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 6/28/2007 10:40:59 PM | fiogf49gjkf0d Auto-Incrementing ID fields in 7.0.1. I created a custom table and set the primary key field to auto increment. I am trying to do a insert from the Administrator but I am getting this error.
[6/28/2007 11:31:30 PM] The statement failed and returned the following error: "Warning: Null value is eliminated by an aggregate or other SET operation. : The statement has been terminated. : Cannot insert the value NULL into column 'C_OPPORTUNITY_HISTORYID', table 'SalesLogix.sysdba.C_OPPORTUNITY_HISTORY'; column does not allow nulls. INSERT fails"
I checked and double checked and auto-increment is checked for this field. Here is the insert statement I am using:
INSERT c_Opportunity_History
(estimatedclose, description, accountname, status, closeprobability, totalrevenue,
servicerevenue, totalgp, servicegp, invoicedate, invoicerevenue, invoiceproductrevenue,
invoiceservicerevenue, invoicegp, invoiceproductgp, invoiceservicegp, closeyear,
closequarter, closemonth, accountid, opportunityid, accountmanagerid, accountmanagername,
createdate, createuser, createname, modifydate, modifyuser, modifyname, asofdate)
SELECT estimatedclose, description, accountname, status, closeprobability, totalrevenue,
servicerevenue, totalgp, servicegp, invoicedate, invoicerevenue, invoiceproductrevenue,
invoiceservicerevenue, invoicegp, invoiceproductgp, invoiceservicegp, closeyear,
closequarter, closemonth, accountid, opportunityid, accountmanagerid, accountmanagername,
createdate, createuser, createname, modifydate, modifyuser, modifyname, asofdate FROM v_Opportunity_History
I am not specifying the primary field in the INSERT statement. Based on what I'm reading about the auto-increment ID checkbox the provider should automatically generate the ID for me. What am I missing?
Thanks,
John G. | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 6/29/2007 7:50:59 AM | fiogf49gjkf0d Just an update... I figured out the issue. Its two-fold actually. The first part of the problem is originating in the view. I found an article on SQL Server Central that describes the
Warning: Null value is eliminated by an aggregate or other SET operation.
message perfectly. It turns out my view had SUM aggregates where I didn't put a ISNULL() around the field to convert any NULL values to a 0. The warning is just what it says, it left that record out of the aggregate. After surrounding the offending fields with ISNULL() the SQL worked without a hitch.
I am still having an issue with the Provider not automatically generating the ID for the primary key field. Is there something else I need to do?
Thanks,
John G. | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 9/26/2007 8:15:36 PM | fiogf49gjkf0d Great articlebut I have a related question:
What if I have turned on the autoincrement feature on a custom table but now I want to use integer values for the primary key instead of the standard SLXID (Char, 12)...
1) How do I set make the provider use integer values with the auto-increment?
2) What might i be concerned about? (E.g., mainviews built on that table?)
Hope someone can help.
I just personally joined SLXDeveloper so if there's a better way (place on this site or elsewhere) I should be asking such questions please let me know | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 9/26/2007 8:42:09 PM | fiogf49gjkf0d Ezi,
You said:
How do I make the provider use integer values with the auto-increment
You can't. SalesLogix table I'd values are what they are. You just need to get used to working with them.
-Ryan | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 9/26/2007 9:14:06 PM | fiogf49gjkf0d Ryan, Thanks. | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 1/24/2008 8:27:09 AM | fiogf49gjkf0d Hi Ryan,
Nice article - very useful. Maybe you can help with some additional info that I'm looking for. Since SalesLogix is getting the nextId for custom tables from where the KeyDesc = "Other" (keyvalue 25), how can I then know which tablea are actually custom tables? We are currently having an issue where the SLX_DBID is giving a value that already exisxt for a custom table. Since Sales Logix tech support doesn't seems to be able/villing to give me a script that can correct this, I'm writing it myself. For this purpose I'm looking for a way to find out which tables to go thorugh in order to find the highest Id used. I'd axpect this to be in a table somewhere, but still haven't been able to find it.
Regards
Steen | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 2/13/2009 11:43:41 PM | fiogf49gjkf0d Hi everyone,
I'm on v7.0.1 and I'm trying to insert records to the ContactExtension table and getting a Primary key violation. Has anyone seen this? I've checked the DB and there isn't a ContactExtension record for these particular contact IDs but for some reason I keep getting rejected. ContactExtension is a one to one table off of Contact and the contactid is what is giving me the error.
Any ideas?
Thanks! | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 2/13/2009 11:52:42 PM | fiogf49gjkf0d Hi Ian, it sounds very much like you may have tried to insert a NULL to the extension table's ContactID PK field - is this possible? Have you tried running SLX Profiler to check the SQL that is causing the error?
Cheers
Phil | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 2/16/2009 9:19:29 PM | fiogf49gjkf0d Phil,
Thank you for the response, I had a repeated line in my function that was processing my SQL statements so I was running my inserts twice and that is why I was getting the error.
Hope you had a great President's Day.
--
Ian | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 2/16/2009 10:08:54 PM | fiogf49gjkf0d No problem Ian - problems which are that simple to solve are the ones I like best!
Phil | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/27/2009 9:45:53 AM | fiogf49gjkf0d Great article -- very helpful.
However, I am not having any success trying to use the autoincrement feature. I have tried to turn on autoincrement for opportunity as described by Ryan, but after setting AUTOINCREMENT to 'T' for OPPORTUNITYID, I am still getting a "Cannot insert the value NULL into column 'OPPORTUNITYID'" error from this insert statement.
INSERT INTO sysdba.OPPORTUNITY
(ACCOUNTID, SECCODEID, DESCRIPTION,
CLOSEPROBABILITY, STATUS, SALESPOTENTIAL, AccountManagerID,
LeadSourceID, CreateDate)
VALUES
(@ACCOUNTID ,'SYST00000001', @DESCRIPTION,
'10', 'Open', '0', @ACCTMGRID,
@LEADSRCID, getDate());
Ryan mentions that autoincrement only works for key columns, but I took that to mean conceptual keys, since - in my SQL2005 database at least - there are no PKs on any of the tables. I also do not see the 'Key' checkbox in DB Manager for any table. I did check and OPPORTUNITYID does not have KEYFLAG set, but neither does ACCOUNTID (from Ryan's example), or any other field in the entire SECTABLEDEFS table.
Is KEYFLAG the issue, and if so can I change the value myself? Would setting the flag direcltly in SECTABLEDEFS be sufficient, or would I need somehow effect the change through DB Manager to make it take effect.
We are running 7.22 - recently upgraded from 6.01 by our integrator. I am working in Architect as admin.
| |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/27/2009 10:08:39 PM | fiogf49gjkf0d Matt
Where are you running that statement from? You should be logged in as ADMIN using an SLX connection, not a SQL connection. Also, you should not use the sysdba prefix - that is already your default schema when you are logged in as ADMIN.
You should also use GetUTCDate() rather than GetDate(), because all SLX dates are stored as GMT. Actually, if you have remotes, you should avoid this completely and INSERT an explicit date, otherwise when the INSERT syncs out to your remotes and executes, the create date value will be different for all of them (depending on when they sync'd).
After setting the Auto-increment flag, you may need to restart the SLX services on your server.
All of the SLX tables should have a physical PK on their first field, the 'ID' field. If that is not there, you should be asking your integrator why not.
Phil
| |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/28/2009 9:04:24 AM | fiogf49gjkf0d Phil - thanks for your reply.
You have, of course, hit upon my big mistake. This insert is from a legacy external app that I have inherited. Since our upgrade from 6.01, I have been unable to get the Provider code to work on our production application server (I get a 'Unable to initialize data dictionary.' error). This insert into OPPORTUNITY was the only thing that the application needed the provider for, and after reading about the auto-increment option here, I mistakenly believed it would allow me to forego the Provider connection and perform the insert directly through SQL. After searching around for more information on the auto-increment capability, I eventually realized that it doesn't allow you to bypass the Provider, so it looks like I am back to having to figure out what the Provider is choking on in our server environment.
Good to know about the physical PKs. I will ask about that. Notably, our integrator inherited this installation from a previous vendor, too, so the upgrade has - for better or worse - been a bit of a learning process for all of us. | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/29/2009 6:49:30 AM | fiogf49gjkf0d I have been unable to get the Provider code to work on our production application server (I get a 'Unable to initialize data dictionary.' error).
Matt,
Seeing the 'unable to initialize data dictionary' error I am wondering if the SQL Server native client is installed on the production server. I know from past experience that error would appear after installing the 7.x.x client without installing the SQL Server native client.
John G. | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/29/2009 9:58:37 AM | fiogf49gjkf0d Hi John - thanks for the response.
Sorry to be such a newb, but which server are you referring to, the application server for our external application, or the SalesLogix server where our database and SLX services reside? I checked in SQL Configuration Manager on the SalesLogix server, and there is a Native Client configuration item there, which appears to me to indicate that it is installed.
There is no Native Client service showing up as a service on either machine, but I don't know that there would be. I used the test.udl to test creating a Native Client connection from the application server, and that connection succeeded - but when I use the connection string from the udl file in the application, it still gives me the 'unable to initiate the data dictionary' error.
Two other notes, for what they are worth. First, the new connection string works fine for me in the development instance of the application on my XP machine. It only has problems on the Windows Server 2003 production server. Second, the pre-upgrade application's connection string pointed to an instance of the Provider running as a service on the application server itself, but when I changed the connection string to the new format for 7.2.2, I pointed the connection directly to the database server - which as I already mentioned - works fine with test.udl, just not in the application.
Anyhow, I know this is pretty far off topic for this article, but at this point any advice anyone can provide that will let me get this application back up and running would be greatly appreciated.
Thanks,
Matt
| |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/29/2009 10:10:37 AM | fiogf49gjkf0d Looking back over that last post, I think I got myself a little tangled up, and had better clarify. Using test.udl, I was successfully able to connect to the database from our app server using both the Native Client, and the SLX Ole DB Provider, but it was the Provider connection string that I have tried to use in my application. I'm taking it that the Native Client connection string, which uses the sysdba credentials, would not be appropriate for use in the application. | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/29/2009 10:15:38 AM | fiogf49gjkf0d Matt,
I was referring to the "production application server" that you referenced. Basically, any machine that will run an application that uses the SLX OLE-DB provider must have the SQL Native Client installed. Your last response stated "It only has problems on the Windows Server 2003 production server" so I would check to make sure that server has the SQL Native Client installed.
John G. | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/29/2009 3:37:48 PM | fiogf49gjkf0d Hi John - thanks for your help.
As it turns out, the server does already have the SQL Native Client installed, so it looks like I'll need to keep digging.
It does look to me like my problem is related to this existing forum thread, http://www.slxdeveloper.com/forum.aspx?forumid=3000&postid=11604 so I will need to work through the solutions offered there and see if one of those helps.
Thanks,
Matt | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 8/13/2009 11:21:46 AM | fiogf49gjkf0d This is a great article, but I am struggling with the same issue as John Gundram was . . . The auto increment box is checked, but it's not auto incrementing. I'm trying to insert into a custom table. Is this why?
I'm very new at this, so I'm sure it's something I'm doing, but any help here would be greatly appreciated!
Here's my script:
Dim objDB
Dim strSQL
Dim objRS
Dim AcctID
Set objDB = New SLX_DB
strSQL = "Insert Into PRODUCTION "&_
"(ACCOUNTID, CREATEDATE, CREATEUSER, MODIFYDATE, MODIFYUSER, PROJECT) " &_
"Select Accountid, " &_
"'" & NOW & "', " &_
"'" & Application.BasicFunctions.CurrentUserID & "', " &_
"'" & NOW & "', " &_
"'" & Application.BasicFunctions.CurrentUserID & "', " &_
"'" & pklProdSeason.Text & "' FROM ACCOUNT WHERE USERFIELD9 = 'NEWLINES'"
Set objRS = objDB.GetNewRecordset
objRS.Open strSQL, objDB.Connection
If objRS.State = adStateOpen Then
objRS.Close
End If
Set objRS = nothing
Set objDB = nothing | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 8/13/2009 12:04:32 PM | fiogf49gjkf0d Hi Samantha,
If you want the key to be auto-created, you need to omit it from the statement completely. What you are doing is copying the accountid value in the select statement on ACCOUNT into the insert statement into PRODUCTION. Does that make sense? Take the accountid column out of your insert statement (and also the select statement). Then it *should* auto-create the keys.
-Ryan | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 8/13/2009 2:17:15 PM | fiogf49gjkf0d I'm trying to create a new row in the production table (one to many table off of the account table) for every account that has a specific value in userfield9 of the account table. I thought I'd need to use that select statement to get the accountid for each of the 950 accounts that I'd like to create the production table record for. My production table has ProductionID (PK), Accountid, etc. Is there a better way to do this? | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 8/14/2009 4:44:33 AM | fiogf49gjkf0d Hi Samantha
You are correct about AccountID. A couple of things I would do:
1) Remove CREATEUSER, CREATEDATE, MODIFYUSER and MODIFYDATE from your INSERT - these are (or should be) populated automatically by the SLX OLEDB Provider.
2) If you have not rebooted your SLX server since setting the ID field to Autoincrement, try a reboot.
Note that you may find it faster to test out simple insert statements in the Administrator client (Tools / Execute SQL) - until you get this bit working. | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 9/15/2009 3:10:32 AM | fiogf49gjkf0d Hi Ryan,
nice article. Thanks a lot.
Additionaly it would be interesting if there's the possibility to add an own "first letter". For instance I wanna integrate an DB based logging where the ID's starting with "Z". Is it possible to add this number group to SLX?
Joern | |
|
|
Posted: 9/15/2009 4:40:47 AM | fiogf49gjkf0d [QUOTE]Hi Ryan,
nice article. Thanks a lot.
Additionaly it would be interesting if there's the possibility to add an own "first letter". For instance I wanna integrate an DB based logging where the ID's starting with "Z". Is it possible to add this number group to SLX?
Joern[/QUOTE]
Unfortunately not, this is all "black box" stuff. And would IDs such as ZZ123456789A really be compatible with your other system anyway? | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 9/15/2009 5:33:22 AM | fiogf49gjkf0d Thats the question, only because the fact that this is "black box stuff" dosen't means there's no way to do it. :o) | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 3/31/2010 7:27:26 AM | fiogf49gjkf0d Where I will get SLX OLEDB Provider? Can we get SalesLogixX data using this provider?
We have a requirement where we have to capture data from SalesLogix also we have to insert some data. Please send me some needful information on it | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 3/31/2010 11:41:55 AM | fiogf49gjkf0d Ravi,
The SLX OLEDB Provider is installed with SalesLogix. You can install it separately from the SLX installation CD.
-Ryan | |
|
| Re: Creating SalesLogix Table ID Values - The Definitive Guide
Posted: 5/4/2010 5:18:07 PM | fiogf49gjkf0d Ryan i'm getting duplicated TargetResponseId with a particular Oracle installation. The AddEditTargetResponse is using the regular _entity.save() method.
Is there a way to modify the _entity.Id of the current Response (or any other smartpart) so when this error is detected i can hadle it making it transparent for users? | |
|
|
|
|
|
Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
|
|
|
|
|
|