If you are comparing in SQL, you can use either:

<>

or

!=

both mean 'not equal to'.

It is detailed here:

www.w3schools.com/.../sql_where.asp

and here:

blog.sqlauthority.com/.../

It is worth noting, when comparing dates, to note the time stamp, you may want to convert the date into a format such as mm/dd/yyyy, so the exact time does not have to match. If you are writing one date to another, this will not be an issue as they will exactly match.

What I mean by the time stamp is this...

If you have two dates:

1. 3/21/2014 14:36:55

2. 3/21/2014 10:12:33

And you ask the question 'Does date 1 = Date 2', the answer is no, because the time stamps do not match. On the flip side, when you ask is does date 1 <> date 2, the answer would be it does not equal, because the time stamps are different, which may not be what you are looking for. If you are comparing two independent date time fields, what are the chances the date and time will exactly match?

I am saying, convert it to be:

1. 3/21/2014

2. 3/21/2014

Then when you ask the question of whether they equal each other, the answer is yes.

Obviously, this is dependent on your requirement, I just wanted to make you aware, it may be irrelevant in your scenario, so feel free to ignore me.

2 things:

In the set column value, I have

oppo_biddatecompare = oppo_biddate;

in the Create script box. I'm thinking I need to execute SQL Statement and make it read (I'm hoping that this will execute on the current opportunity only.)

UPDATE Opportunity

SET oppo_biddatecompare=oppo_biddate

Second thing:

I thought the time stamp was server specific. They're the same field type, so the only thing it'll do is compare them?

For that field time is also important, so even if it's a minute change (although unlikely) they're going to want it.

The reason that they'll be exactly the same is because (if I can figure it out) the only way one of the fields gets data is when it's told to pull the date and time from the other field. (Only one field is user-entered, the other is to hold the old unpitted time date and then compare it to the user defined one.) Once I get some actually running scenarios, I'll be testing for what you said, so we'll see if I'll need to set up some DECLARE statements.

As for setting oppo_biddatecompare = oppo_biddate, I think my code in the createscript of the change column value might be off. What do you think?

I think I may have confused things here..

>>I'll be testing for what you said, so we'll see if I'll need to set up some DECLARE statements.

You wouldn't need to declare anything, just convert the date format so it ignores the time. I was just setting out an example in my last post.

>>As for setting oppo_biddatecompare = oppo_biddate, I think my code in the createscript of the change column value might be off. What do you think?

Yeah.

If it is part of the escalation rule, why not use an execute SQL rule and use the #oppo_opportuntyId# in the clause:

UPDATE Opportunity

SET oppo_biddatecompare = oppo_biddate

WHERE oppo_opportunityId = #oppo_opportunityId#

Run this on your test, and backup first!

That seemed to work. Now I've got to think about modifying an appointment that is created when the date time is entered the first time. Here's the working code that I'm using. Which way do you think would be easier, to try and modify the existing appointment, or find and delete it and then just recreate a new one?

DECLARE @Comm_CommId INT
DECLARE @Comm_OppoId INT
DECLARE @Comm_DateTime DATETIME
DECLARE @Comm_ToDateTime DATETIME
DECLARE @Comm_Note NVARCHAR(50)
DECLARE @Comm_CreatedBy INT
DECLARE @Comm_CreatedDate DATETIME
DECLARE @Comm_TimeStamp DATETIME
DECLARE @Comm_SecTerr INT

DECLARE @CMLI_CommLinkId INT
DECLARE @CMLI_UserId INT
DECLARE @CMLI_CommId INT
DECLARE @CMLI_CreatedBy INT
DECLARE @CMLI_CreatedDate DATETIME
DECLARE @CMLI_TimeStamp DATETIME
DECLARE @CMLI_CommPersonId INT
DECLARE @CMLI_CommCompanyId INT
DECLARE @CMLI_SalesPerson INT

SET @Comm_OppoId = #Oppo_OpportunityId#
SET @Comm_DateTime = (SELECT Oppo_biddate FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)
SET @Comm_ToDateTime = (SELECT Oppo_biddate FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)
SET @Comm_Note = '#Oppo_Description#'
SET @Comm_CreatedBy = '57'
SET @Comm_CreatedDate = GETDATE()
SET @Comm_TimeStamp = GETDATE()
SET @Comm_SecTerr = #Oppo_SecTerr#
SET @CMLI_CreatedBy = '57'
SET @CMLI_CreatedDate = GETDATE()
SET @CMLI_TimeStamp = GETDATE()
SET @CMLI_CommPersonId = (SELECT Oppo_PrimaryPersonId FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)
SET @CMLI_CommCompanyId = (SELECT Oppo_PrimaryCompanyId FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)
SET @CMLI_SalesPerson = #oppo_salesperson#

EXEC @Comm_CommId = eware_get_identity_id Communication

SET @CMLI_CommId = @Comm_CommId

INSERT INTO Communication (Comm_CommunicationId, Comm_OpportunityId, Comm_ChannelId, Comm_Type, Comm_Action, Comm_Status, Comm_Priority, Comm_DateTime, Comm_ToDateTime, Comm_Note, Comm_CreatedBy, Comm_CreatedDate, Comm_TimeStamp, Comm_SecTerr) VALUES (@Comm_CommId, @Comm_OppoId, '7', 'Appointment', 'biddate', 'Pending', 'Normal', @Comm_DateTime, @Comm_ToDateTime, @Comm_Note, @Comm_CreatedBy, @Comm_CreatedDate, @Comm_TimeStamp, @Comm_SecTerr)

SET @CMLI_UserId = '44' EXEC @CMLI_CommLinkId = eware_get_identity_id Comm_Link INSERT INTO Comm_Link (CmLi_CommLinkId, CmLi_Comm_UserId, CmLi_Comm_CommunicationId, CmLi_CreatedBy, CmLi_CreatedDate, CmLi_TimeStamp, CmLi_Comm_PersonId, CmLi_Comm_CompanyId) VALUES (@CMLI_CommLinkId, @CMLI_UserId, @CMLI_CommId, @CMLI_CreatedBy, @CMLI_CreatedDate, @CMLI_TimeStamp, @CMLI_CommPersonId, @CMLI_CommCompanyId)

SET @CMLI_UserId = @CMLI_SalesPerson EXEC @CMLI_CommLinkId = eware_get_identity_id Comm_Link INSERT INTO Comm_Link (CmLi_CommLinkId, CmLi_Comm_UserId, CmLi_Comm_CommunicationId, CmLi_CreatedBy, CmLi_CreatedDate, CmLi_TimeStamp, CmLi_Comm_PersonId, CmLi_Comm_CompanyId) VALUES (@CMLI_CommLinkId, @CMLI_UserId, @CMLI_CommId, @CMLI_CreatedBy, @CMLI_CreatedDate, @CMLI_TimeStamp, @CMLI_CommPersonId, @CMLI_CommCompanyId)

They would be both the same. You'd have to find it to delete it, you'd have to find it to update it. I'm not sure why you would want to delete it and then recreate it, that would create a lot of rows in the comm/comm_link table which I'd want to avoid.

So I've just decided to find and update it since I'll have to find it anyway and it does make sense that deleting and creating a new one is a waste of space. Here's the code that I think will work.

DECLARE @Comm_OppoId INT

DECLARE @Comm_DateTime DATETIME

DECLARE @Comm_ToDateTime DATETIME

SET @Comm_OppoId = #Oppo_OpportunityId#

UPDATE Communication

SET @Comm_DateTime = (SELECT Oppo_biddate FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#), @Comm_ToDateTime = (SELECT Oppo_biddate FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)

WHERE Comm_OppoId = #Oppo_OpportunityId# AND #Comm_Action# = 'biddate'

I only need a few declares; the opportunity ID in the communication entity, and the dates that are to be changed.

Then it sets the opportunity ID to the current opportunity.

Then it sets variables (the DateTime and the toDatetime) to the new biddate that's been inputted only for the communication that has the same opportunity id AND has a "biddate" action. (There's only one)

Am I missing anything?

I think you have your code a little confused:

DECLARE @Comm_OppoId INT

DECLARE @Comm_DateTime DATETIME

DECLARE @Comm_ToDateTime DATETIME

SET @Comm_OppoId = #Oppo_OpportunityId#

UPDATE Communication

SET @Comm_DateTime = (SELECT Oppo_biddate FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#), @Comm_ToDateTime = (SELECT Oppo_biddate FROM Opportunity WHERE Oppo_OpportunityId = #Oppo_OpportunityId#)

WHERE Comm_OppoId = #Oppo_OpportunityId# AND #Comm_Action# = 'biddate'

You declare a number of variables, but only set one (but then you don't go on to use it). So, when the code executes, it isn't actually going to update the communication, as you are actually just assigning a variable to the table, and then setting its value, but aren't doing anything with it there after, e.g.

All that code does is set a variable (@variable) to have a value of '999' in the context of that query, it doesn't actually update a column (if there was one) named 'variable'.

You do not need to declare any variables for what you are wanting to do:

UPDATE Communication
SET Comm_DateTime = (SELECT Oppo_timestamp FROM Opportunity WHERE Oppo_OpportunityId = #oppo_opportunityId# ), Comm_ToDateTime = (SELECT Oppo_timestamp FROM Opportunity WHERE Oppo_OpportunityId = #oppo_opportunityId#)
WHERE Comm_OpportunityId = #oppo_opportunityId# AND Comm_Action = 'biddate'

The above code would update the comm records you are looking for. You may want to look at rewriting the query to use joins to update the comm fields to the oppo fields, rather than using the nested selects, as the nested selects make the query more expensive.

I know you say there will only be one, but, it may be worth putting in a catch, in case there is more than one, as the update will blanket update where the comm_action and the comm_opportunityId match in the WHERE clause which may cause issues down the line. For that, you would use variables, declare them, and set them, e.g.

Where you decide what actions are taken when the criteria is met for the variable.

I have gotten it to work in CRM, and I also added comm_subject so the required field would also be filled.

One last issue that I can't figure is that after I've created the comm appointment and the commlink information it all comes up fine on CRM. When I sync Outlook with it, the new appointment won't show up. However, if I change anything in the appointment (like add some extra notes or change the time of the appointment) then it will sync and show up on Outlook.

Any ideas why it doesn't sync with Outlook?

Of the top of my head I am afraid I don't know.

Take a look at what changed when you run the update through the UI, make sure you are not missing any fields when you create it.

Ok so I made an appointment and then made another one the usual way. I compared the two in an SQL statement and found that there's 3 fields that differ:

comm_Updatedby

comm_UpdatedDate

comm_OutlookID

So I created a new variable and tried to put it in my SQL:

DECLARE @Comm_OutlookID

EXEC @Comm_CommId = eware_get_identity_id Communication

INSERT INTO (yaddayadda, Comm_OutlookID)

VALUES (yaddayadda, @Comm_OutlookID)

And it didn't work. It grabbed a number way off from what my other appointment was (usual comm_outlookID is like 3292438414 and with that EXEC statement it grabbed like 28269; I knew it was a longshot). So instead I went the other route and DECLARE, SET, and INSERT the UpdatedBy and UpdatedDate. It worked! Apparently the comm_OutlookID isn't updated until there's a sync done from Outlook, and then one is assigned.

Huzzah!