Clay Icon

Setting a Conditional Formula for Checking Salesforce Lookup Results

·
·

How do I set a conditional formula to say "Only check this box IF Salesforce lookup has run successfully AND no results were found" Right now, I don't see a way to know if column ran successfully. Use case: Check if record exists in Salesforce. If not, create a record.

  • Avatar of Clay T.
    Clay T.
    APP
    ·
    ·

    Hey Jacob! I think Upsert Record/Object SFDC integration should solve this for you.

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    what's the difference between upsert and update?

  • Avatar of Clay T.
    Clay T.
    APP
    ·
    ·

    Upsert is Salesforce's version of Create or Update Record, similar to the option that Hubspot offers.

  • Avatar of Clay T.
    Clay T.
    APP
    ·
    ·

    I haven't used it before because we don't come across many of those scenarios, but according to Google, that's what this endpoint should do.

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·
  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    having an error because I'm not adding a record key

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    but I don't see an option to include a key here

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    also, I'm not sure why a record id is necessary here, because the point of this is to lookup or create if nothing is exists. So seemingly I wouldn't need a record id

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    lastly, the tutorial linked here seems to show off update record, not upsert https://docs.clay.com/enrichments-and-providers/enrichments/all-providers/salesforce/upsert-object

  • Avatar of Clay T.
    Clay T.
    APP
    ·
    ·

    Ah, we'll fix that link, but it should be this one here https://docs.clay.com/enrichments-and-providers/exporting-data/crms/salesforce

  • Avatar of Clay T.
    Clay T.
    APP
    ·
    ·

    This is the doc about that external Id required for it to run, by salesforce though: https://salesforcethinkers.home.blog/2020/06/04/external-id-in-salesforce/

  • Avatar of Clay T.
    Clay T.
    APP
    ·
    ·

    If it's an auth issue though, I'll loop in Colin P. to help us check if there may be some issues and why it returns that error, which is odd.

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    Arturo O. I'm not sure why but I just get errors when I run a search here

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    I can't actually pass in an external id

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    it just asks me to select an object and then it errors

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    I think it's a bug

  • Avatar of Kareem Amin
    Kareem Amin
    ·
    ·

    Jacob T. if a column has run successfully it would have data in it. Roger T. potentially we should expose the metadata of failed and run successfully to formulas so people can write these conditions more easily

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    Kareem Amin yes your solution would be great

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    No data is exposed when a column has a failed run, so I can't setup a conditional logic here

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    This would be a great solve

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    Roger T. any chance this is being prioritized somewhat soon?

  • Avatar of Roger T.
    Roger T.
    ·
    ·

    Thanks for the feedback, that's an interesting use case. I'll relay it to our team.

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    Roger T. I'm pretty surprised nobody else has asked about it. It makes the "update or create contact if none exist" flow impossible. This is a problem for customers like Sendoso. Not sure if it's possible to accelerate or not / how difficult it is?

  • Avatar of Roger T.
    Roger T.
    ·
    ·

    That makes sense, let me circle back shortly

  • Avatar of Roger T.
    Roger T.
    ·
    ·

    My colleagues are looking into a workaround for this (cc Eric E. / Yash T.)

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    Thank you keep me posted Roger T.

  • Avatar of Roger T.
    Roger T.
    ·
    ·

    np

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    Jacob T. there is a workaround for this - I just added it to your table - see the screenshot. Kareem Amin Roger T. this isn’t super intuitive for users because you have to map the column out from a successfully run look up in order to get the blank data. also Jacob T. upsert should solve this problem in general, not sure what error is you’re getting, will update here when I know more

  • Avatar of Eric E.
    Eric E.
    ·
    ·

    Hey Jacob T. — The issue here is that Salesforce Upserts, in general, require a field in Salesforce to be marked as an external ID. According to the key you’re using in Clay, no fields in your Salesforce organization are marked as an externalID, so you can’t actually run upserts because no fields load for the upsert key. “ExternalID” is a salesforce setting that says “this field can be used to dedupe salesforce records, but it’s values come from another source”

  • Avatar of Eric E.
    Eric E.
    ·
    ·

    We can definitely make changes to enable this in our regular lookups, as mentioned in this thread, but the quickest win here would be to mark a field in salesforce as an external ID and then try again

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    thank you folks!

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    Yash T. did you add the "found at least one" column? If so I don't think this solves the problem, as the value is "false" even if the lookup has not yet run. I need to know if the cell has actually run or not

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    Jacob T. I did - and what’s the edge case for knowing whether or not the cell has run before it’s run at least once?

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    Yash T. I want to lookup a record in salesforce, and if it exists, update the record. If not, create a record. I can't update/create until I run the lookup to see if it exists. Right now, even before the lookup runs, the "found at least one" is false. I would use "if lookup ran AND found at least one is true, THEN update record" or create record if FALSE. But since the "ran at least once" column is blank by default, my logic would automatically create a new record before the lookup had a chance to run

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    I see why you want this Jacob T., but also, if you make the update record column conditional on the “found at least one” result column being true from lookup contact, that column won’t run until the lookup has first run, which effectively solves your issue

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    What about create contact? What is that conditional on?

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    you would also make it conditional on the found at least one value in the lookup record, the value would just be opposite, but it would still wait for that integration to run

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    this is how most SFDC customers do this right now - but still agree with you that it could be way easier

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    Yash T. but found at least one is false even before the integration ran

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    that's the main problem I'm highlighting

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    Jacob T. that doesn’t matter, if you link the later column to the result of found at least one, it will wait for it to run

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    it’s not about it actually being false

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    it’s about queing it

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    I’ll make an example and show you here haha

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    I hear what you're saying

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    I can prob test without you spending more time here

  • Avatar of Jacob T.
    Jacob T.
    ·
    ·

    ty!

  • Avatar of Yash T.
    Yash T.
    ·
    ·

    yeah give it a test and lmk if you have any issues, if you do, then there’s a deeper problem