The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
External acquisition/creation of next AccountID/ContactID
Posted: 30 Jan 09 7:12 AM
|
I am trying to insert records directly into the Account/Contact/Salesorder tables via stored procedure from another system. This needs to be done completely outside of the application. We are using version 5.2.4...yes, dark ages...Is there a way to acquire, create the unique IDs for those tables external to the application? I know they last value is stored in the sitekeys table and very interesting the stepping method, but can this be called externally?
Thanks in advance for any help...
ChrisD |
|
|
| |
| |
|
Re: External acquisition/creation of next AccountID/ContactID
Posted: 02 Feb 09 6:51 AM
|
Ok, I would love to take a look at that...
Again, we are on version 5.2.4....where would I go to find that "SDK". I have been through all of the documentation on the cd's, not much there other than implementation...
Thanks for your help.
ChrisD |
|
|
| |
|
Re: External acquisition/creation of next AccountID/ContactID
Posted: 02 Feb 09 7:48 AM
|
You are on the right path but you need some (include) files in order to do the actual dev. the SDK has samples as well.
Log into the SalesLogix support site and go to teh 5.2 section. There's SDK there (it's labeled 5.0.. don't worry, it's the right one).
|
|
|
| |
|
Re: External acquisition/creation of next AccountID/ContactID
Posted: 02 Feb 09 9:41 AM
|
A few words about stored procedures and SalesLogix - not a good mix. If you ever want remotes to work than stored procs are completely out of the question. Also, using a stored proc to do this is not a good idea. You are better off using SalesLogix provided "calls" since they properly interface with key gen, etc.
Oh yes.. the stored proc you ref is almost 6 years old and does not take into account that key gen is basically done using a "spin-lock" procedure to guarantee that the key is absolutely distinct. |
|
|
|
Re: External acquisition/creation of next AccountID/ContactID
Posted: 04 Feb 09 9:20 AM
|
Originally posted by Bob (RJ)Ledger
A few words about stored procedures and SalesLogix - not a good mix. If you ever want remotes to work than stored procs are completely out of the question. Also, using a stored proc to do this is not a good idea. You are better off using SalesLogix provided "calls" since they properly interface with key gen, etc.
Oh yes.. the stored proc you ref is almost 6 years old and does not take into account that key gen is basically done using a "spin-lock" procedure to guarantee that the key is absolutely distinct. |
|
Sage Advice, as always.
Here's a modern way to get new DBIDS in TSQL
1. Create a Linked SQL Server to the SLX OLE DB Provider box.
Example: SLXProv1
2. Reference that linked server in this TSQL call:
-- get slx ID from provider
SET @myNextAccountID = (SELECT * FROM OPENQUERY(SLXProv1, 'slx_dbids('"ACCOUNT",1)'))
3. Voila, SLX ID spin locked and spun dry.
Still doesn't sync however.
|
|
|
|
Re: External acquisition/creation of next AccountID/ContactID
Posted: 04 Feb 09 9:27 AM
|
Originally posted by Bob (RJ)Ledger
The real question here is why SP's?
There is an "SDK" that you can use w/VB6 that will do everything you need to do. |
|
Unfortunately, some developer's are more fluent in TSQL, SQL, et al.
Any Oracle DBA worth his salt won't let you write to his Oracle DBA without using HIS SP's.
And most of us (me included) know that writing in TSQL on a native SQL Connection is 2-10 times FASTER than using the SLX OLE DB Provider....for fetches, inserts, and updates.
SLX tends to fire off too many fetches for the most mundane of tasks....whether it's retrieving GroupS when you are in Detail View, to firing off all of the Tabs when you go to a MainView detail, to firing off Data Grids 2-4 times each time you do an AXFormChange (including returning from a grid popped Data Form).....
Just going through the SLX provider is painfully slow for a 14,000 row "Get a list of Schools in CA" fetch....when the data is SECCODEID = EVERYONE |
|
|
|
Re: External acquisition/creation of next AccountID/ContactID
Posted: 05 Feb 09 1:56 PM
|
RJS, I think he's still on 5.2.x, so there wouldn't be an OLEDB provider involved...?
| does it matter what the accountid is, as long as it has the correct number of characters? We are not running multiple locations, or using remote users... |
|
Actually, if you have no remote users or offices whatsoever, and don't intend to get any, then you could get away with generating the ID's yourself if you don't want to deal with the (now "legacy") API DLL. You should probably use all 12 characters, though, in case any of your ID's are run through a Trim() function somewhere. What I used to do in these situations was to put at least one underscore character in positions 2-5 of the ID, since SLX never uses underscores in site codes (in fact, it never uses any non-alphanumeric characters in ID's, though it might someday I suppose).
It would also be a good idea to at least start the ACCOUNTID's with the letter "A," ADDRESSID's with "a", and so on - it helps to keep things straight later on, in case someone else has to diagnose a problem and you're not around.
This isn't really "supported" (and certainly isn't recommended) by the SLX folks, but like I say, if you don't have any remotes you can get away with it. In fact, if you use ADO methods to read and write to the database, it'll be easier to adapt your app to the OLEDB provider later on, if you ever you upgrade to SLX 6.x/7.x. |
|
|
|
Re: External acquisition/creation of next AccountID/ContactID
Posted: 05 Feb 09 3:31 PM
|
After the initial responses, we started looking closer at the API. The issue we have is that we are using an ASP.Net web application to do an import of sales data from a remote web store. Currently this data is printed from web store and manually input into SLX. We have written the interface that does the appropriate lookups into SLX and finds matches 75% of the time. The other 25% are either human error or 10% new customers (per day). The web app actually gathers all of the necessary data and then triggers a SP to import the data into staging tables in SLX to be queued into the primary tables to limit locks (again legacy headaches). Thus the creation of the necessary IDs fits into the SP very easily. We really don't care where we get those IDs, as long as it is quick and works. The stored procedure referenced earlier actually does pull the appropriate IDs based on the target table (including the correct prefix letter). We did multiple tests against test data and it worked nicely. I thought that as a way of avoiding issues with the live site code, perhaps we could create a different site code just for the sake of segregating the imported data from the client input data. If you can use underscores, that should work as well, correct? Also, we are using ADO....
Thanks again for all of the comments...It has definitely been a help on this project...
CD |
|
|
|
Re: External acquisition/creation of next AccountID/ContactID
Posted: 11 Feb 09 9:34 AM
|
No need to worry about SiteCodes if you creat your own unique Char 12 string. Some CODE out there works on AccountID beginning with an A, ContactID with a C, Opportunity ID with an O, etc.
But we've had NO TROUBLES with developing our own ID's for a custom table.....
They simply MUST be unique, Char(12). "A00000000001" works just fine as an ACCOUNTID. |
|
|
|