0

So the what I'm trying to achieve: At the moment a contact which is assigned to an order creates or change his address, a task will go out to owner of that order.

The problem is that i refer to the Usr.Id = Placement.OwnerId. This is the order owner. But i want to check if the address.CreatedbyId or address.LastModifiedById is actually the contact itself and NOT someone else (one of our staff members, which includes the order owner as well). CreatedbyId as well as the LastModifiedById refer to the Usr, which now is related to the Order Owner and NOT to the contact.

So how could i check if the Usr is my contact?

address.CreatedbyId = ??? address.LastModifiedById = ???

select distinct
contact.Id as Id
, contact.FirstName as FirstName
, contact.LastName as LastName
, contact.email as email
, contact.status__c as AtlasRelation
, usr.email as OwnerEmail
, usr.name as OwnerName
, usr.Id as OwnerId
, address.createddate as AddressCreateDate
, address.LastModifiedDate as AddressModifiedDate
, contact.Id as CandidateId
, contact.Last_Project__c as LastOrder

from Contact_Salesforce contact

left join Address__c_Salesforce address
on contact.Residence_Address__c = address.Id

left join Placement__c_Salesforce placement
on contact.Last_Project__c = placement.Id

left join User_Salesforce usr
on usr.Id = placement.OwnerId

where 
contact.status__c = 'Assigned'
and DATEPART("year", address.CreatedDate) = DATEPART("year", GetDate())
and DATEPART("month", address.CreatedDate) = DATEPART("month", GetDate())
and DATEPART("day", address.CreatedDate) = DATEPART("day", GetDate())
and address.CreatedbyId = ???
or
contact.status__c = 'Assigned'
and DATEPART("year", address.LastModifiedDate) = DATEPART("year", GetDate())
and DATEPART("month", address.LastModifiedDate) = DATEPART("month", GetDate())
and DATEPART("day", address.LastModifiedDate) = DATEPART("day", GetDate())
and address.LastModifiedById = ???

3 Answers 3

1

There's many ways this could be done / written. Without having your data schema to test on, I have put together this - but you will need to test and likely adjust to your need :

(EDITED : Updated with working code)

select distinct
contact.Id as Id
, contact.FirstName as FirstName
, contact.LastName as LastName
, contact.email as email
, contact.status__c as AtlasRelation
, usr.email as OwnerEmail
, usr.name as OwnerName
, usr.Id as OwnerId
, address.createddate as AddressCreateDate
, address.LastModifiedDate as AddressModifiedDate
, address.Id as AddressId
, contact.Last_Project__c as LastOrder


from Contact_Salesforce contact

left join Address__c_Salesforce address
on contact.Residence_Address__c = address.Id

left join Placement__c_Salesforce placement
on contact.Last_Project__c = placement.Id

left join User_Salesforce usr
on usr.Id = placement.OwnerId

where 
contact.status__c = 'Assigned'
and datediff(d,address.LastModifiedDate,GetUTCDate()) between 0 and 1
and ((SELECT COUNT(a.CreatedbyId) FROM Address__c_Salesforce a 
                    Inner Join  User_Salesforce CbS on a.CreatedbyId = CbS.CreatedbyId) = 0 or 
    (SELECT COUNT(b.LastModifiedById) FROM Address__c_Salesforce b 
                    Inner Join  User_Salesforce Cb on b.LastModifiedById = Cb.LastModifiedById) = 0)

I'm simply performing a count on CreatedByStaff and ModifiedByStaff - which if they return >=1 then you know its been modified by staff.

I've also simplified your date selection to datediff(d,address.LastModifiedDate,GetUTCDate()) between 0 and 1 and this will get you any records which were modified between 0 and 1 days ago. You can play with the number or change to hours by modifying : datediff(h,address.LastModifiedDate,GetUTCDate()).

Also, this will get you results where the Staff member edited the address - but if you want records where it wasn't the staff member, then use :

and (CreatedByStaff = 0 AND ModifiedByStaff = 0)

3
  • Hi iManage, this is very helpful thank you! The error i get is "Errors: Invalid column name 'CreadedByStaff'." This Column is not in one of my Synchronised DE's.
    – eazy4me
    Commented Oct 16, 2018 at 6:49
  • I noted a spelling mistake in CreadedByStaff rather than CreatedByStaff field. If it still fails to work, then we will need to move the inner select statements to the Where clause
    – Gravitai
    Commented Oct 16, 2018 at 7:37
  • yeah i noticed the spelling mistake, i changed the code but still returns: Errors: Invalid column name 'CreatedByStaff'. I really appreciate your help!
    – eazy4me
    Commented Oct 16, 2018 at 7:42
1

I think it might help to take a step back to look at your desired result, and not jump into SQL right away.

From what I understand, the main goal is to create a task in Salesforce “Core” (I’m assuming force.com / Sales Cloud / Service Cloud, i.e. NOT SFMC). So:

  1. Use Process Builder in SF Core. Process builder is a drag-and-drop tools to build the kind of automation you’re talking about. I’m guessing that currently you’re using Journey Builder (in SFMC) to create the tasks based on the data your query returns. That’s a nice workaround, but assuming that all your data is coming from SF Core anyway, why not just build the automation there? Also, I don’t know how often you run your query at the moment via an automation, but if the data changes between the time a contact changed his/her address and the query actually runs, then it’s possible no task is created...
  2. Have you considered using a third-party application to avoid having to write SQL in the first place? We saved time in the past using DESelect to just drag-and-drop and preview our queries. That way you don't need to learn SQL... :)
  3. Perhaps I’m missing something and you absolutely want to build that query for this specific use case. Now the other answer(s) provided here offer some ways to filter for edits based on LastModifiedTime. However, you are describing that contacts can update their own address data. Is it possible you’re using Salesforce Communities? In that case, you could check for the Community Member ID on the contact, and compare this to the LastModifiedBy field on Address. This should give you more accurate results than based on timing (e.g. what if someone else edited in the meantime?).

Let me know if this helps!

0

I did manage it to get it working by using most of iManage his code. Thanks for that!

select distinct
contact.Id as Id
, contact.FirstName as FirstName
, contact.LastName as LastName
, contact.email as email
, contact.status__c as AtlasRelation
, usr.email as OwnerEmail
, usr.name as OwnerName
, usr.Id as OwnerId
, address.createddate as AddressCreateDate
, address.LastModifiedDate as AddressModifiedDate
, address.Id as AddressId
, contact.Last_Project__c as LastOrder


from Contact_Salesforce contact

left join Address__c_Salesforce address
on contact.Residence_Address__c = address.Id

left join Placement__c_Salesforce placement
on contact.Last_Project__c = placement.Id

left join User_Salesforce usr
on usr.Id = placement.OwnerId

where 
contact.status__c = 'Assigned'
and datediff(d,address.LastModifiedDate,GetUTCDate()) between 0 and 1
and ((SELECT COUNT(a.CreatedbyId) FROM Address__c_Salesforce a 
                    Inner Join  User_Salesforce CbS on a.CreatedbyId = CbS.CreatedbyId) = 0 or 
    (SELECT COUNT(b.LastModifiedById) FROM Address__c_Salesforce b 
                    Inner Join  User_Salesforce Cb on b.LastModifiedById = Cb.LastModifiedById) = 0)
2
  • Cheers for the mention - apologies for not getting back sooner with updated code, but you seem to have figured it out as to what I was saying. To help others in future and avoid confusion, I will update my answer/code with yours
    – Gravitai
    Commented Oct 16, 2018 at 16:45
  • No problem iManage. You really helped me out and got me on the right path. Thank you again for your help!
    – eazy4me
    Commented Oct 18, 2018 at 7:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .