6/19/2025 4:29:52 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
How to track changes to a database field (RecordChanges)
Posted: 06 Aug 07 7:09 AM
|
Good day,
My client wants tracking on a number of their database fields, which can normally be achieved quite easily using the RecordChanges property of the field in Architect. However, I have an interesting twist....
I have to put QA data on opportunity product lines. The way I did that was to add another right-click option to the Opportunity Product table, and to launch a form from there that contains the QA data. Now I need to track any changes made to these fields. Normally I can just select the "RecordChanges" property, and all would work wonderfully. However, in this case no history is recorded for these fields.
I thought the reason for my troubles may be because the data is in a custom table. The base table for the form is OPPORTUNITY_PRODUCT, and the database table in which the data is that I need to track is a one-to-one link with OPPORTUNITY_PRODUCT called C_OPPORTUNITY_PRODUCT_EXT. However, I was able to successfully track changes to data in a table called C_ACCOUNT_EXT which has a one-to-one link with the ACCOUNT table.
It does look like my form is a data form (however, I do not know how to check if it is a data form or manage form after the fact - I created it quite some time ago.)
Could it be that there is once again something sinister that we need to know about opportunity product, or am I missing something else?
Regards
Ina
|
|
|
|
Re: How to track changes to a database field (RecordChanges)
Posted: 06 Aug 07 8:55 AM
|
Ina, I can help you with the Data form vs Manage form: use the bound property of a control to look at the name of the bound table. If you click the button next to the Text property of a control and the table name is not blank, the form is either an Account, Contact, Opportunity, or Data form. Data vs Account, etc not as easy to tell, but this is a very quick test for manage forms.
Michael |
|
|
|
Re: How to track changes to a database field (RecordChanges)
Posted: 06 Aug 07 9:16 AM
|
On many forms, and many fields, clicking True to RecordChanges on an SLX control won't generate a History record. And for userID fields, it's formatted incorrectly (Display's the ID, not the User Name, so it's not readable by humans.....
We provide a shadow control that stores the initial value of the data field when the form is 'OnChange'......then compare the value in the field to the shadow value at Validate time and write our own history record, TYPE = 262156, for many of these controls.....especially on custom tables and one:many:many tables that SLX doesn't always record changes on.
We also write to a custom Sarbanes-Oxley kind of table that includes the Reason for the change, etc. (use the same table for Delete Requests)......
RJ Samp |
|
|
| |
| |
|
Re: How to track changes to a database field (RecordChanges)
Posted: 07 Aug 07 9:53 AM
|
Hello, Ina:
Another option you can explore is to use Sage KnowledgeSync to track changes to the field's value. With KnowledgeSync, you can track changes to any field in the SLX database, trigger alerts that contain both the old and new field values, and trigger the creation of history records that track these changes. This does not require any programming. Let me know if you want any more details.
Don Farber |
|
|
|
Re: How to track changes to a database field (RecordChanges)
Posted: 07 Aug 07 10:12 AM
|
Hi Don,
Thanks a lot. I have worked wiht KnowledgeSync before and I must admit it is quite nice, but the client is on a budget and we did not sell them KnowledgeSync.
Looks like I will just have to spend a bit of time coding this. If I do, I will post the code for future reference.
Thanks
Ina |
|
|
|
Re: How to track changes to a database field (RecordChanges)
Posted: 08 Aug 07 8:43 AM
|
Don't have it handy.....here's a simple store of a history record:
FUNCTION Insert_HISTORY_Rec()
DIM whateveryourconnectionis
whateveryourconnectionis = application.GetNewConnection
IF anhType = "" THEN anhTYPE = "262148" ''''NOTE
IF anhCategory = "" THEN anhCategory = "Note"
IF anhPriority = "" THEN anhPriority = "Medium"
IF anhDescription ="" THEN anhDescription = "Note History"
IF anhUSERID = "" THEN anhUSERID = application.basicfunctions.CurrentUserID
IF anhResult = "" THEN anhResult = "Completed"
IF anhResultCode = "" THEN anhResultCode = "C"
IF anhDateTime = "" THEN anhDateTime = NOW()
anhSQL = "SELECT * FROM HISTORY WHERE 2 = 1 "
rsANH.Open anhSQL, whateveryourconnectionis
WITH rsANH
IF .EOF THEN
.ADDNEW
G_intUpdateFields = G_intUpdateFields + 1
anhHISTORYID = application.basicfunctions.GetIDFor("HISTORY")
.Fields("HISTORYID").VALUE = anhHISTORYID
.Fields("ACTIVITYID").VALUE = anhHISTORYID
.Fields("TYPE").VALUE = anhTYPE
.Fields("CATEGORY").VALUE = anhCategory
.Fields("DESCRIPTION").VALUE = anhDescription
.Fields("RESULT").VALUE = anhResult
.Fields("RESULTCODE").VALUE = anhResultCode
.Fields("DURATION").VALUE = anhDuration
.Fields("ACCOUNTID").VALUE = g_strACCOUNTID
.Fields("ACCOUNTNAME").VALUE = strACCOUNTNAME
.Fields("CONTACTID").VALUE = g_strCONTACTID
.Fields("CONTACTNAME").VALUE = strCONTACTNAME
.Fields("OPPORTUNITYID").VALUE = g_strOPPORTUNITYID
.Fields("OPPORTUNITYNAME").VALUE = strOPPORTUNITYNAME
.Fields("USERID").VALUE = anhUSERID
.Fields("USERNAME").VALUE = anhUSERNAME
.Fields("ATTACHMENT").VALUE = "F"
.Fields("PRIORITY").VALUE = "Medium"
.Fields("RECURRING").VALUE = "F"
.Fields("TIMELESS").VALUE = "F"
.Fields("PROCESSID").VALUE = anhProcessID
.Fields("PROCESSNODE").VALUE = anhProcessNode
.Fields("COMPLETEDUSER").VALUE = anhUSERID
.Fields("COMPLETEDDATE").VALUE = NOW()
.Fields("ORIGINALDATE").VALUE = NOW()
.Fields("STARTDATE").VALUE = NOW()
.Fields("NOTES").VALUE = TRIM(LEFT(anhNOTES,255))
.Fields("LONGNOTES").VALUE = anhNOTES
END IF
END WITH
'Store ModifyUSer, CreateUser, ModifyDate, CreateDate HERE!!!
rsANH.UPDATE
rsANH.CLOSE
cnANH.close
set cnANH = nothing
END FUNCTION |
|
|
|
Re: How to track changes to a database field (RecordChanges)
Posted: 09 Aug 07 6:33 AM
|
Originally posted by Ina Nortje
....
Looks like I will just have to spend a bit of time coding this. If I do, I will post the code for future reference.
Thanks
Ina |
|
It sometimes is difficult as to which direction to go sometimes:
A- Build it all yourself
B - Purchase a "framework" and add your value.
I tend to favor the "B" approach because:
1 - It saves a lot of time
2 - provides more value to the client (which generates more business!)
I'd use TaskCentre and plugin the code RJSamp posted. If you take a hard look at it you will find it saves the client money.
--
rjl |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|