Thanks for that!!! I had few questions around the formatter, the ids come and are joined like ‘a,b,c,d’ but salesforce query would need WHERE ID IN (‘a’,’b’,’c’,’d’)
Also SOQL doesn't support DISTINCT keyword as in SQL in Salesforce, do we have direct ways in Zap to find unique record by name, or we need to use loop?
Best answer by SamB
Sorry to hear that didn’t work as hoped, @mjanwani. I’ve been doing some testing on my end and I don’t think it’s going to be possible to use SOSL in the API Request to do what you need.
As you can’t filter the name in the query perhaps you could get the name included in the output of the query. Then use an additional action to filter out the package IDs with names that aren’t a match for those that you were looking to select, then check the expiry dates.
For example this query:
SELECT Id, SubscriberPackageId, SubscriberPackage.Name FROM InstalledSubscriberPackage
Would output line items with both the ID and the package name:
From there, you could use a Code by Zapier action to check the names output the corresponding ID for those that do match. Then you could make a query to check the expiration date of the packages with those IDs supplied by the Code step.
If that doesn’t work and you’re looking to carry out certain actions for when packages expire, or are due to expire, I wonder if a different approach might be needed. Perhaps a workaround would be to keep a record of all the expiry dates for each package in a Google Calendar. For example, you could have one Zap add a new “expiry” event when a new subscriber package is added. Then have another Zap trigger when the event starts (or X amount of time before it starts) then carry out the desired action(s).
Could that be a viable option here?
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.
Happy to help, @mjanwani. As the previous topic already had an answer marked I’ve moved your follow-up questions over to this new topic so we can answer them separately, hope you don’t mind! 🙂
I had few questions around the formatter, the ids come and are joined like ‘a,b,c,d’ but salesforce query would need WHERE ID IN (‘a’,’b’,’c’,’d’)
I’d suggest using a Formatter (Text > Replace) action to replace , with ','
That should give you an output of a','b','c','d which you can select inside the query to make sure the ids are included as WHERE ID IN ('a','b','c','d') and not WHERE ID IN ('a,b,c,d').
Also SOQL doesn't support DISTINCT keyword as in SQL in Salesforce, do we have direct ways in Zap to find unique record by name, or we need to use loop?
To search for a single record by it’s name you could use a Find Record Salesforce action. It would let you pick up to 2 fields and values to search for:
Keep me posted on how it goes, want to make sure you’re all set!
Hi Thanks SamB, for helping me here. 1. I did created inserting ‘,’ in place of , The documentation says that the list should be ‘a,b,c,d’ but now when I see it in the query it comes as a’,’b’,’c’,’d I don’t want to have one more node for just having ‘ at the start and end.
I am a beginner, are such problems solved this way step by step only? or there is better way?
Also As we are using API Request we can’t use Find Record to remove duplicates.
Anytime, @mjanwani—always happy to lend a hand! 🤗
To get the quote marks to appear before and after the reformatted list of IDs you can just type in the missing quote marks directly before and after the Output Text field that has been selected:
That said, judging by your second screenshot it looks like the quote marks in between the ids are missing:
So it’s likely that the IDs you’re passing to the Formatter step are actually line items and not a comma separated list of text strings. So I would suggest adding another Formatter (Utilities > Line-item to Text) action (ahead of the Replace one) to first convert the line items into a comma separated list.
For example:
Also As we are using API Request we can’t use Find Record to remove duplicates.
The Find Record action has the ability to find or create a new record, it would not be able to remove a duplicate record. To remove a duplicate record you could use a Delete Record Salesforce action, and pass it the ID of the duplicate record that needs to be removed.
Hope that helps. Look forward to hearing how it goes! 🙂
May I kindly ask if you could confirm whether my requirement is achievable?
In Query 1, I retrieve the expiration date and namespace from Salesforce.
In Query 2 (via the Tooling API - API request beta), I want to compare the combination of expiration date and namespace from Query 1, and then retrieve the corresponding record ID and name. Thanks for your time!! I appreciate it,!
The above you can skip to answer, but in situations like below where we can’t use SOQL what do you prefer?
Failed to create a http request in Salesforce (2.20.4)
installedsubscriberpackage where SubscriberPackage.Name IN ('CloudingoAgent' ^ ERROR at Row:1:Column:75 field 'Name' can not be filtered in a query call
In such cases salesforce says using SOSL instead of SOQL but how will Zapier handle it?
Lovely to hear from you, @mjanwani. Hope you’ve had a good start to the week! 😁
In Query 1, I retrieve the expiration date and namespace from Salesforce.
In Query 2 (via the Tooling API - API request beta), I want to compare the combination of expiration date and namespace from Query 1, and then retrieve the corresponding record ID and name. Thanks for your time!! I appreciate it,!
We don’t have any internal documentation to confirm this for certain, but I’d have thought it should be possible to query the records in Salesforce as you described—to get the expiration date and namespace for the package, and then retrieve the corresponding record ID and name if they aren’t included in the results of the first query.
Failed to create a http request in Salesforce (2.20.4)
installedsubscriberpackage where SubscriberPackage.Name IN ('CloudingoAgent' ^ ERROR at Row:1:Column:75 field 'Name' can not be filtered in a query call
In such cases salesforce says using SOSL instead of SOQL but how will Zapier handle it?
I’ve not come across that specific error before, but I found this thread on the Salesforce Community where someone encountered the same error on the Contacts object. In that case, the issue was caused by the Name field being encrypted: https://trailhead.salesforce.com/trailblazer-community/feed/0D54S00000A8k5RSAR. So I wonder if something similar could be causing the error you’re seeing here. 🤔
Could you check in your Salesforce account to see if the Name field for the Subscriber Package is encrypted as well?
Sorry to hear that didn’t work as hoped, @mjanwani. I’ve been doing some testing on my end and I don’t think it’s going to be possible to use SOSL in the API Request to do what you need.
As you can’t filter the name in the query perhaps you could get the name included in the output of the query. Then use an additional action to filter out the package IDs with names that aren’t a match for those that you were looking to select, then check the expiry dates.
For example this query:
SELECT Id, SubscriberPackageId, SubscriberPackage.Name FROM InstalledSubscriberPackage
Would output line items with both the ID and the package name:
From there, you could use a Code by Zapier action to check the names output the corresponding ID for those that do match. Then you could make a query to check the expiration date of the packages with those IDs supplied by the Code step.
If that doesn’t work and you’re looking to carry out certain actions for when packages expire, or are due to expire, I wonder if a different approach might be needed. Perhaps a workaround would be to keep a record of all the expiry dates for each package in a Google Calendar. For example, you could have one Zap add a new “expiry” event when a new subscriber package is added. Then have another Zap trigger when the event starts (or X amount of time before it starts) then carry out the desired action(s).
Thanks for confirming we can’t use sosl. I will have to retrieve all records and then filter - though it is little costly, I can try that. THANKS, I will get back.
+11
