function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Marlimar MSMarlimar MS 

Need help pulling results in 1 query.

Hi. 

So here is our business need. We need to use the SOQL REST api to pull all "appointments" from the "appointments" object that are exactly 10 days old that are in "Demo, not sold" result. We want to send these people a message, but ONLY if they don't have a subsequent appointment setup after. So if 10 days ago the result was "Demo, not sold" and they have no appointments setup after that, they qualify. 

I don't want to do this in 2 queries. I can go into details why if needs be, but for the sake of this post, I need to accomplish this in 1 single query. I need to get all appointments that resulted in "Demo, not sold" from the appointment table exactly 10 days ago BUT have not had any other kind of appointment setup within the last 9 days. 

More details. I need the SOQL query to return exactly the result set that I need. I don't want to have to do logic or a 2nd query. We are doing it with 2 queries right now, but with a new business need I need to get my expected results in 1 query with no further need to process logic after the API call. I just need to be able to loop over the exact result set and know that I can call each of these people and not have anyone that had another type of appointment setup in the days since the "Demo, not sold" result.

Can someone help? I don't need an exact query, just how I can do it. 
Andrew GAndrew G
Would it be simpler to update the "appointment" status from "Demo, not sold" to "Demo, Appt booked" if the followup appointment is created?  this assumes that there is some function that creates the followup appointment, perhaps as an event in a Calendar, and that same appointment creation can update the status field.

That would save changing the existing API request and ensure you are only hitting those appointments that are 10 days old and have no followup appointment created.

regards
Andrew
want fallwant fall
Apple ID is your entry ticket to just about everything iPhone. You need it to sign in and make purchases at the iTunes Store, the App Store, the iBook Store, and the online Apple Store, as well as to sign in and set up iCloud, Apple’s remote syncing and storage service. Get How to Create an Apple ID here. https://kinetichifi.com/how-to-create-an-apple-id/
Marlimar MSMarlimar MS
So this is where the "additional info" comes in. We are a 3rd party vendor. Several of our clients use i360 and we interface with their i360 and send messages to customers when certain criteria is met. "We" don't do anything in i360, our clients do, so we can't control how they do/don't use it. We just need to make sure before we send the 10 day message from a "Demo, not sold" status, that a new appointment for this customer hasn't been setup. 

What I am trying to accomplish this in 1 query so we can fit this into our wrapper. We can manually interface with each client for this, but to generalie it it has to be done in 1 query. So you are right objectivly. Just change the status of the existing appointment, but, I can't control that all our clients will do it that way. I know some of our clients create new appointments in that situation. 

So it still stands that I need to accomplish this in 1 query. I am unsure on the boundaries of SOQL. Can we use nested SELECT statements? Can we use "count" functions like SQL? Any ideas?