11/22/2024 9:55:45 AM
|
| |
| 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.
|
|
|
|
SQL in script problem - Part 2
Posted: 22 Sep 09 3:22 AM
|
Hi All, i am trying to execute the following piece of SQL in the script of the Activity Details View plugin:
Dim strSQL
strSQL = "Update ACTIVITY Set NOTES = '" & memComments.Text & "'" & _
", LONGNOTES = '" & memComments.Text & "'" & _
", STARTDATE = '" & dteStartTime.DateTime & "'" & _
", DESCRIPTION = '" & pklRegarding.Text & "'" & _
", PRIORITY = '" & pklPriority.Text & "'" & _
", CATEGORY = '" & pklCategory.Text & "'" & _
", ACCOUNTNAME = '" & account & "'" & _
", CONTACTNAME = '" & contact & "'" & _
", OPPORTUNITYNAME = '" & opportunity & "' " & _
"Where ACTIVITYID = '" & Activity.Key & "' "
objSLXDB.ExecuteSQL(strSQL)
When i tried to execute this i got a type mismatch error, which i realised was due to me trying to convert the dteStartTime into text by enclosing it in ' ' as i have done with all the other variables.
Removing the ' and ' means the line now reads as: ", STARTDATE = '" & dteStartTime.DateTime & "'" & _
This successfully stopped the error from displaying but it does not insert the value of dteStartTime.DateTime into the database, but instead inserts 1900-01-01 00:00:00.000.
Can anyone suggest a solution to this?
Many Thanks.
|
|
|
|
Re: SQL in script problem
Posted: 22 Sep 09 10:45 AM
|
Well, DateTimes' need a ' ' around them in a SQL statement, just like a string. without getting into ISO dates, which you should always use in raw SQL....
you could try
DIM sDATE
sDATE = dteStartTime.TEXT
and then
", STARTDATE = '" & sDATE & "'" & _
always good to:
MSGBOX strSQL (or application.debug.writeline strSQL)
objSLXDB.ExecuteSQL(strSQL)
|
|
|
|
Re: SQL in script problem
Posted: 22 Sep 09 2:45 PM
|
I have seen this happen with the the Date is actually Empty or equal to ""
Then you need to set it to null
|
|
|
|
Re: SQL in script problem
Posted: 23 Sep 09 12:34 AM
|
One other suggestion - it appears you may start having problems anytime someone uses an apostrophe in the notes or other string-type fields. Try doing something like this:
strSQL = "Update ACTIVITY Set NOTES = '" & Replace(memComments.Text, "'", "''") & "'" & _
", LONGNOTES = '" & Replace(memComments.Text, "'", "''") & "'" & _
", STARTDATE = '" & Application.BasicFunctions.TzLocalToGMT(dteStartTime.DateTime, 0) & "'" & _
", DESCRIPTION = '" & Replace(pklRegarding.Text, "'", "''") & "'" & _
", PRIORITY = '" & Replace(pklPriority.Text, "'", "''") & "'" & _
", CATEGORY = '" & Replace(pklCategory.Text, "'", "''") & "'" & _
", ACCOUNTNAME = '" & Replace(account, "'", "''") & "'" & _
", CONTACTNAME = '" & Replace(contact, "'", "''") & "'" & _
", OPPORTUNITYNAME = '" & Replace(opportunity, "'", "''") & "' " & _
"Where ACTIVITYID = '" & Activity.Key & "' "
That should escape the apostrophes so you don't have "Failed to Parse SQL" errors.
Also, I put in something that should help with the date. Can't remember if that will help the format, but the date needs to go in as GMT - I think. There are a few fields out there that aren't in GMT, but it's rare. I don't have an SLX database to look at right now, but chances are, if the type is DateTime, it should be saved in GMT. |
|
|
|
Re: SQL in script problem
Posted: 23 Sep 09 3:39 AM
|
Thank you all for your replies. I managed to solve my problem with the following:
---------------------------------------------------------------------------------
Dim objRS
Dim account
Dim contact
Dim leader
Dim opportunity
account = getfield("ACCOUNT","sysdba.ACCOUNT","ACCOUNTID = '" & lveAccount.LookupID & "'")
contact = getfield("LASTNAME + ', ' + FIRSTNAME","sysdba.CONTACT","CONTACTID = '" & lveContact.LookupID & "'")
leader = getfield("LASTNAME + ', ' + FIRSTNAME","sysdba.USERINFO","USERID = '" & lveLeader.LookupID & "'")
opportunity = getfield("DESCRIPTION","sysdba.OPPORTUNITY","OPPORTUNITYID = '" & lveOpportunity.LookupID & "'")
Set objRS = objSLXDB.GetNewRecordSet
'Creates the structure of the recordSet.
strSQL = "Select * from Activity where ACTIVITYID = '" & Activity.Key & "'" 'DNL
objRS.Open strSQL, objSLXDB.Connection
'Adding the record To the database.
objRS.Fields("NOTES").Value = memComments.Text 'DNL
objRS.Fields("LONGNOTES").Value = memComments.Text 'DNL
objRS.Fields("STARTDATE").Value = dteStartTime.DateTime 'DNL
objRS.Fields("DESCRIPTION").Value = pklRegarding.Text 'DNL
objRS.Fields("PRIORITY").Value = pklPriority.Text 'DNL
objRS.Fields("CATEGORY").Value = pklCategory.Text 'DNL
objRS.Fields("ACCOUNTNAME").Value = account 'DNL
objRS.Fields("CONTACTNAME").Value = contact 'DNL
objRS.Fields("OPPORTUNITYNAME").Value = opportunity 'DNL
if chkTimeless.Checked = True then
objRS.Fields("TIMELESS").Value = "T" 'DNL
else
objRS.Fields("TIMELESS").Value = "F" 'DNL
end if
if chkAlarm.Checked = True then
objRS.Fields("ALARM").Value = "T" 'DNL
else
objRS.Fields("ALARM").Value = "F" 'DNL
end if
If cboDurationUnits.Text = "days" then
objRS.Fields("DURATION").Value = (CInt(cboDuration.Text) * 1440) 'DNL
else if cboDurationUnits.Text = "hours" then
objRS.Fields("DURATION").Value = (CInt(cboDuration.Text) * 60)
else if cboDurationUnits.Text = "minutes" then
objRS.Fields("DURATION").Value = cboDuration.Text
end if
end if
end if
objRS.Update
objRS.Close
Set objRS = Nothing
Dim strActID
strActID = Application.BasicFunctions.CompleteActivity(Activity.Key)
-----------------------------------------------------------------------------------
Unfortunately a new problem has now presented itself. As i am saving any changes to the activity before the Complete Form is called then if a user chooses to Cancel the Complete their changes are still saved, which is what was wanted.
However, we have now found the following problem scenario:
The user changes the Start Time to a later date.
They then click my button which saves the changes and takes them to the Complete screen.
They then choose to Cancel the complete process which closes down the forms but still saves their date change.
When they go back into the same activity the Alarm time (whether the alarm is checked or unchecked) has been re-calculated with the time lapse (whether hours or days) between the orignal date and the one they have now chnaged it to.
For example, as a test, i changed the date of one such activity forward 1 day. I followed the steps above and when i went back in the alarm time was changed to 1.0104 days.
Does anyone know why this happens?
Thanks for your time (especially due to how long this post is!!!!!) |
|
|
| |
|
Re: SQL in script problem
Posted: 23 Sep 09 9:36 AM
|
And I was about to respond to the thread.
I guess you figured out that the ALARMTIME is a separate field (Separate table as well, since the one that is now being used is on USER_ACTIVITY), and that you may want to recalculate it (depending on your requirements).
|
|
|
|
Re: SQL in script problem
Posted: 29 Feb 16 3:34 PM
|
Hello
We are using client server version of Saleslogix. Current production version is 7.5.
We are in the process of modifying the Priority Field in the form screen "Phone Call", "Meeting", "To Do", "Personal Activity" from Schedule menu. Instead of Priority, Introduce the new field called "Activity" and associate a new picklist. I was able to do these changes.
I have tried to create few records using Phone Call, Meeting, To Do and Personal Activity and I was able to create the entry in the Saleslogix DB. However, when I try to view through Activities, the new field doesn't show up
Please advise
Thanks
Gopinath Srinivasan |
|
|
|
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!
|
|
|
|
|
|
|
|