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!
|
|
History Tables
Posted: 26 Jul 07 8:10 AM
|
| Does anyone have experience adding information to the integration saleslogix history tables? We're using an in house web application that adds and changes information stored in the sales logix tables and are wanting to add data auditing type functionality to it. Does anyone have any best practices, or words of caution/wisdom for us? Ideally we would be able to display a change log for an entire field based upon this history table. Do you think that this would be possible, or would we better off making and maintaing our own history table from the web application? |
|
|
|
Re: History Tables
Posted: 27 Jul 07 10:43 AM
|
Devin,
Just to clarify: "able to display a change log for an entire field based upon this history table"
You want to log every time someone changes a particular field IN the History table? Or you want to log a field change (not from History table) TO the History table?
What you propose (either option) is possible; but there is a business question here. Why are you changing history? If you are making changes to data is history the correct place to do this? Perhaps a Note or ToDo. Or is it is a solid process - i.e.: Call center - have you explored processes? That way you capture all interaction with your customer.
Carla |
|
|
|
Re: History Tables
Posted: 27 Jul 07 10:55 AM
|
| We want to log a field change to the history table. |
|
|
|
Re: History Tables
Posted: 27 Jul 07 12:04 PM
|
OK - that one is easy!
If internal to SLX - Select the field you want to track. Look at the Properties for this field. Select "RecordChanges" chkbox. SLX will automatically track field this for you.
If external - there are many ways to skin that cat. Here are a couple. If these don't do it for you I am sure everyone here in the forum has ways of handling this.
Use a combination of OnChange and Validation processes.
IF you do NOT have any remotes or Remote Offices - you could use a DB trigger, but that will be a performance impact. History tables are big and bulky
Hope this helps!
c 
|
|
|
|
Re: History Tables
Posted: 07 Aug 07 1:19 AM
|
Hi Carla,
I saw your post mentioning about creating DB trigger in SalesLogix. I use the native SalesLogix (7.1) SQL Server. MSDE to be precise. I am using QueryExpress to connect to DB. There is no provision in SLX to create trigger. could you please tell me how to create trigger in it?
I need to do backend trigger in literature request form to get all past due requests and re-assign it to a different person. This has to happen only in backend. Your help in this regard is appreciated.
Thanks,
Rekha |
|
|
|
Re: History Tables
Posted: 07 Aug 07 2:36 PM
|
Hi Rekha,
Adding a trigger is done at the database level via SQL code. Triggers are snippets of code that fire off when an Insert, Update or Delete command against a specific table is encountered. These fire everytime the table is hit with one of those commands. You can not add these via SLX because the are DB actions not application actions.
If you are looking to reassign the LitRequest fullfilment user you would need a single SQL command to be run only one time. I will try and take a look at the LitRequest section later today and give you an example statement if this is what you are really looking for. If you are intent on a trigger, you will need to discuss it with you DBA &/or refer to SQL help to get started.
c
Community: Does anyone already have a pre-prepped SQL command for this area? (read: up to my eyeballs in projects right now)
|
|
|
|
Re: History Tables
Posted: 08 Aug 07 3:53 AM
|
Hi Carla,
Thanks for your reply. what i really need is a DTS job that would find out literature requests that are past due date and re-assign it to someone else. I was hoping to achieve it using SLX but i think i have to do it through DTS job...
Thanks,
Rekha |
|
|
|
Re: History Tables
Posted: 08 Aug 07 11:24 AM
|
Rekha,
DTS moves data. Here is an example statement to find your information. I do not have any data to test against so - 'No Guarentees'. I strongly recommend you talk to your DBA to help you find these records and reassign fullfillment to a new user.
SELECT LITREQID, FILLUSER, SENDVIA, REQDATE
FROM LITREQUEST
WHERE REQDATE < '2007-08-07'
AND SENDDATE IS NULL |
|
|
|
Re: History Tables
Posted: 09 Aug 07 6:56 PM
|
DTS jobs do not necessarily have to move data. It is a convenient way of storing and scheduling almost any process that has anything to do with SQL Server - including moving data.
So a scheduled DTS job might be what is required. |
|
|
| |
|