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!
|
|
Can a custom table be created with VB Script 
Posted: 01 Sep 08 3:50 PM
|
| I have a large account extended table that needs to be split up. It is almost at the max table width of ~8K characters. I have remodeled this into the smaller tables. Can use a VB Script to create and populate these tables? |
|
|
| |
| |
|
Re: Can a custom table be created with VB Script
Posted: 02 Sep 08 7:38 AM
|
There IS one way that is "almost script": A bundle.....
NOTE: Run the following in a test environment and be sure it's working for you. If you run this against a production db and it trashes it.. well... c'est le vie!
1 - Create the new tables using SalesLogix DB manager in a test db
2 - Bundle the Create Tables using the SalesLogix bundlin process.. do NOT include any data
3 - Drop the tables from the test db (using SalesLogix db manager)
4 - Run in the bundle
5 - Verify the table(s)/field(s) were created correctly.
The bundle should work fine on your production db.
WARNING: DO NO apply the bundle to a SalesLogxi db while:
sync is running
SQL backup is happening
ANY DB maint is going on
ANY users are connected to teh db. |
|
|
|
Re: Can a custom table be created with VB Script
Posted: 02 Sep 08 8:10 AM
|
There are literally hundreds of fields that are mostly unrelated in one giant extended table. Many fields should be in 1 to many tables. I plan to keep the field names and data types. I was able to export the table structure from to DB Manager and paste it in Excel where I added a column for each new table. Then I put a "1" for each field under its new table. This helped me verify and explain how every field will be mapped. Now that each new table's fields and data types are flagged I was hoping to append this to SQL scripts that would create each new table instead of manually creating the tables and pasting each field to ensure the names matched.
Now for the real fun! I need to identify every form, group, report, and script that uses each field. Scripts are easy searches. I have a script that uses Application.BasicFunctions.GetGroupSQL to identify the groups. I do not know of a similar function to test the fields bound on forms or reports. Do you have any suggestions? |
|
|
|
Re: Can a custom table be created with VB Script
Posted: 02 Sep 08 8:26 AM
|
Lawrence,
A down and dirty way of quickly 'copying' a table in SQL:
Select * Into sysdba.myNewTable From sysdba.myOldTable
This will create the table, copy all column information and data into the myNewTable in the database you ran it from.
If you know exactly which columns you want (review constraints and Identifiers to verify the columns exists in your call out.):
Select Column1, Column3, Column5, Column7 Into sysdba.myNewTable From sysdba.myOldTable
Carla
|
|
|
|
Re: Can a custom table be created with VB Script
Posted: 02 Sep 08 8:51 AM
|
Thanks. This is what I tried first. The DB Manager recognizes the tables exist and allows me to enable it, but I get a warning message The key field has not been idendified for this table. Child tables cannot be created." It does not allow me to associate to the account either.
I also created a script to ensure the SLX data provider was used, but this also gives the same warning message, and I can't associate it with the account table.
Dim objSLXDB
Set objSLXDB = New SLX_DB
Dim objRS, strSQL
strsql = "CREATE TABLE ATEST2 (ACCOUNTID CHAR(12) NOT NULL,CREATEUSER CHAR(12) NULL,CREATEDATE DATETIME NULL, " & _
"MODIFYUSER CHAR(12) NULL,MODIFYDATE DATETIME NULL,SECCODEID CHAR(12) NULL,TEST1 VARCHAR(32) NULL)"
objSLXDB.ExecuteSQL strSQL
strsql = "CREATE UNIQUE INDEX ATEST2_PRIMARY ON ATEST2 (ACCOUNTID)"
objSLXDB.ExecuteSQL strSQL
Msgbox "Done!"
objRS.Close
Set objRS = Nothing
|
|
|
|
Re: Can a custom table be created with VB Script
Posted: 02 Sep 08 7:03 PM
|
Are you running this DDL through the SLX OLEDB Provider? Back in V6 this wasn't possible, but maybe it is now.
As well as creating the table like this, you will need associated entries in the following tables:
RESYNCTABLEDEFS
SECTABLEDEFS
JOINDATA
and you will have to update SITEKEYS and the XMLSCHEMA entry in the PLUGIN table. Hopefully the provider takes care of all that for you, if you are using it. If not, you're in for an unpleasant time, I think.
Phil |
|
|
|
Re: Can a custom table be created with VB Script
Posted: 03 Sep 08 7:27 AM
|
Originally posted by Phil Parkin
Are you running this DDL through the SLX OLEDB Provider? Back in V6 this wasn't possible, but maybe it is now.
As well as creating the table like this, you will need associated entries in the following tables:
RESYNCTABLEDEFS
SECTABLEDEFS
JOINDATA
and you will have to update SITEKEYS and the XMLSCHEMA entry in the PLUGIN table. Hopefully the provider takes care of all that for you, if you are using it. If not, you're in for an unpleasant time, I think.
Phil |
|
... and you have to update the contents of every SecProfile Record data Blob.. where the field info is "encoded"...
|
|
|
|
Re: Can a custom table be created with VB Script
Posted: 04 Sep 08 3:17 PM
|
AND you'll have to have the correct FieldIndex and FieldOffset in SecTableDefs.....
AND.....
This is asking for BIG trouble.....
OK to create SQL tables for eventual Insert/Update into a SLX Table that itself was created in the DB Manager .....otherwise.....
all bets are off
and Your Mileage Will Not Vary..... |
|
|
|
Re: Can a custom table be created with VB Script
Posted: 05 Sep 08 10:17 AM
|
Using the enable function like that does not do all of the set up you need. It does populate the sectable and resynctabledefs table entries but the error message you are getting is because the resynctabledefs table must have the keyfield attribute set. You can use a tsql script like this:
update resynctabledefs set keyfield = 'CustID' where tablename = 'CustFinancials'
As others have suggested, I would not recommend you try automating the table creation script the way you have. I have noticed that some other things like the UTC date flag in the sectabledefs are not honored for fields in tables enabled in the fashion you have.
I would suspect the issue of not being able to tie to the Account table is due to missing information in the JOINDATA table. |
|
|
|
Re: Can a custom table be created with VB Script
Posted: 05 Sep 08 10:25 AM
|
| Thanks for the update. Even though it took hours to recreate the tables, it took less time than I spen trying to automate it. I am just always trying to find a faster and easier way to do everything. It would have been nice if it worked. Then the reuse factor would have been worth the effort. Win some, lose some. |
|
|
|