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!
|
|
Creating SalesLogix Tables/Tablefields via VBScript-Source SLX 6.1 SP3
Posted: 25 Jul 06 7:32 AM
|
fiogf49gjkf0d Hi,
i have written a small set of functions that create a custom table within SalesLogix. I have done this watching the profiler of MS-SQL-Server. There exist functions for adding and removing fields to my custom tables as well.
I have written these functions, because need a lot of custom tables that change very dynamically. I could not use a simple 1:n relation table, because it cannot be used in SalesLogixs Group Manager.
Why I tell you this is because it seems that the OLE DB provider is not very satisfied with what I do. From time to time it simply cannot access any table any more when I used one of my functions.
If I restart the provider everything just works fine. The error message I get is like "... error with sql syntax: FROM not known" or anything like it. It happens at any possible situation: selecting a contact, searching for an account ... just anything.
Does anyone of you ever have done a similar function? Anyone having a small tip for me?
Thanks,
Andreas |
|
|
|
Re: Creating SalesLogix Tables/Tablefields via VBScript-Source SLX 6.1 SP3
Posted: 26 Jul 06 9:46 AM
|
fiogf49gjkf0d Andreas,
SLX is very picky about how new tables and fields are added. If these were not added through the DBManager located in both the Architect and Administrator, the chances are it will never function correctly. 
Please consult the SECTableDefs table for a better understanding of how SLX handles it's own little world.
SELECT * from SECTableDefs
c |
|
|
|
Re: Creating SalesLogix Tables/Tablefields via VBScript-Source SLX 6.1 SP3
Posted: 26 Jul 06 10:00 AM
|
fiogf49gjkf0d C,
thanks for your reply.
I think I dont have problems understanding SLXs litte world  I for sure checked SecTableDEFS and ResyncTableDefs.
The problem is, that everything seems to be all right. You can see each of my created tables, you can see each of my created fields.
You can work with them within SLX as you like and as you know doing with Standard-Tables.
(All my Tables are 1:1 related Tables to ACCOUNT or CONTACT)
You can use them in the group manager as well.
Everything is fine as long as you don't use my functions too much.
For example if you use them to create just one or a few custom table with its fields - no error occurs! You can work with SLX for days without any error.
Then you create a new field or a new table with exactly the same source that has worked dozens of times before.... A simple SELECT * FROM ACCOUNT does not work anymore.
Andreas
|
|
|
|
Re: Creating SalesLogix Tables/Tablefields via VBScript-Source SLX 6.1 SP3
Posted: 26 Jul 06 10:06 AM
|
fiogf49gjkf0d I am not sure the provider is going to like you dynamically adding/deleting tables/fields. This may confuse the provider in situations where it is caching some information and the underlying table/field structure changes.
Is there anyway you can do this with static tables? |
|
|
|
Re: Creating SalesLogix Tables/Tablefields via VBScript-Source SLX 6.1 SP3
Posted: 26 Jul 06 10:21 AM
|
fiogf49gjkf0d One method I've used for this is to create a table with a bunch of predefined fields (say, DUMMYFIELD01 - DUMMYFIELD20) using the SLX DB Administrator. Once you do this you can programmatically change the fieldnames in sectabledefs as much as you want. This works well (as long as you don't need to change the data types of each column.)
|
|
|
| |
|
Re: Creating SalesLogix Tables/Tablefields via VBScript-Source SLX 6.1 SP3
Posted: 26 Jul 06 1:44 PM
|
fiogf49gjkf0d There's always going to be a perf hit if you're dynamically creating and updating tables. One method I can think of is to use BLOBs to store the data, perhaps in XML format so you can use the MSXML COM object to manipulate it. This way your XML can change without having to trick the provider, jump through hoops, sacrifice chickens, or whatever else you can think of to try to please the SLX gods. Of course that throws groups out the window but there's more than one way to achieve similar results.
If you have remote users, I'd hate to see what their TEFs look like. You're possibly synchronizing table structure changes PLUS entire rebuilds of the data. Not a problem for tables that barely change but you stack enough of these up and your dialup users will start to hate you quickly. You can work with the knowledge by morphing data instead of deleting and reinserting but it's definately an uphill battle.
I consider SLX a ship with the table structure being the hull. I can puncture and weld the hull while the ship is sailing but it's either going to sink (almost typed sync, really bad pun) or at the very least tread water. Other CRM packages may be better equipped to handle work on the outside while the ship is sailing but due to synchronization (the fact that SalesLogix is the leader from what I hear) you're kind of forced to operate within a box, if you'd like that box to sync to your remotes that is. |
|
|
|
Re: Creating SalesLogix Tables/Tablefields via VBScript-Source SLX 6.1 SP3
Posted: 26 Jul 06 3:51 PM
|
fiogf49gjkf0d It sounds like the tables and data are ephemeral. If they are not needed on the remotes (ie: it is only needed by Network/Web Clients) the tables could be created in another database that is accessed directly through a SQL Connect. This would go completely around the Provider. |
|
|
|
Re: Creating SalesLogix Tables/Tablefields via VBScript-Source SLX 6.1 SP3
Posted: 26 Jul 06 3:53 PM
|
fiogf49gjkf0d It sounds like the tables and data are ephemeral. If they are not needed on the remotes (ie: it is only needed by Network/Web Clients) the tables could be created in another database that is accessed directly through a SQL Connect. This would go completely around the Provider. |
|
|
|