3

Background: My business performs remote training appointments with our customers, and they can be scheduled in any increment of 15 minutes. We track/deduct minutes from a lump-sum field on the Account object called "Used Training Minutes" by scheduling Events in 15 minute increments and updating the Used Minutes field when those events are Completed.

Recently our business need has changed. We can no longer use the lump-sum to just track the total number of training minutes that a customer has available to them, because now our education team wants those minutes to expire X days after purchase. So, we needed to track all purchased training separately, and I've created an object to store that.

In the new design, an event should be marked as Completed at which point we would get a list of all un-expired Training_History__c (Custom Object) records that are tied to that account, get the OLDEST one (First in), and deduct from those minutes until there are none left on that record (First out). If by chance the training was 60 minutes but the oldest Training_History__c record only had 30 minutes remaining on it, we still want to deduct those 30 (First out), and then move on to the SECOND oldest (which is now the 'first in with remaining minutes > 0') record and deduct the remaining 30 from that record, so on and so forth until we've deducted all of the minutes from the oldest unexpired records that still had minutes available.

Now, this question isn't about that logic - I think I have some ideas on how to make that work (but I have not started it yet), before I can even begin to work out that logic, I need to first tackle the problem of how to identify the "first in", specifically in a situation where multiple records were inserted simultaneously via Apex.

Problem: The customer might have purchased 2 or more different training products from us at the same time, which get synced into Salesforce from our billing system at the same time as well. Each training product should equal one Training_History__c record. Apex kicks off on the newly synced records which loops through them and builds a list of new Training_History__c records to insert for those trainings, and then inserts them all.

Of course, since these are all being inserted as a single list rather than hundreds of individual inserts, they all have the same exact CreatedDate time stamp, which (unless I'm mistaken) makes it impossible for me to get the "oldest" or "newest" one using a SOQL query with ORDER BY Field DESC/ASC


So I guess, ultimately, my question is : How does Salesforce handle ORDER BY when dealing with DateTime fields where the DateTime is identical between multiple records? Will they always return out of SOQL in the same order, or is the order subject to random changes since they all have the "same" CreatedDate DateTime stamp?

  • IF they always get ordered in the same order in the list, then how is Salesforce determining that order of Ascension/Decending considering they all have the same TimeStamp? Would it use a combination of Time Stamp and Row Number and/or Record ID?
  • IF the order is subject to change since they all have the same time stamp due to being inserted simultaneously, then how would I best identify the "oldest" record for use in my logic?
2

According to the Force.com SOQL and SOSL Reference on the ORDER BY clause:

You can use ORDER BY in a SELECT statement to control the order of the query results. There is no guarantee of the order of results unless you use an ORDER BY clause in a query.

So if you have multiple results where you do specify a field to ORDER BY, but they all have the same value, the same behavior applies: there is no guarantee of the order of results. You might find the following question relevant: SOQL result ordering in the absence of an Order By clause

If you want to get the most recently inserted records first, you can include Id as a fallback sort order for records where CreatedDate is the same.

ORDER BY CreateddDate DESC, Id DESC
3
  • You can order by up to 32 fields, one of which may very well be the Id field, which would force a specific order among all values that tied.
    – sfdcfox
    Nov 16 '16 at 22:52
  • Ah, didn't really interpret that part of the question correctly.
    – Adrian Larson
    Nov 16 '16 at 22:54
  • I think this is the best route to go in, as I'd still be getting the OLDEST records (CreatedDate), and they should always be in the same ID order every time I get them - right? (Since they were inserted on X day and obviously ID can't change so multiple items sorted by CreatedDate,Id should return in the same order today, tomorrow, and next week). Considering the multiple records w/ same CreatedDate would all expire on the same day anyway, I don't need to get the -oldest-, just need to make sure I'm only deducting from one record until the available minutes on that record hit 0, then moving on Nov 17 '16 at 15:39
3

The best way to order by insertion order and also avoid records that have the same ordering value (as can happen using CreatedDate) is to add an auto-number field and ORDER BY that (with or without DESC as appropriate).

4
  • 1
    You can order by Id, which avoids the need for an auto-number field.
    – sfdcfox
    Nov 16 '16 at 22:51
  • 1
    @sfdcfox This documentation Testing Best Practices "Record IDs are not created in ascending order unless you insert multiple records with the same request. For example, if you create an account A, and receive the ID 001D000000IEEmT, then create account B, the ID of account B may or may not be sequentially higher." and my personal experience (of tests failing due to unexpected Id ordering) contradict that. I'm not 100% sure of Id ordering so auto-number seems the safer approach.
    – Keith C
    Nov 16 '16 at 23:50
  • That's interesting. Unfortunately, auto-number may have the same problem once we get to the sub-second processing time. They're guaranteed to be unique, but I don't think they're guaranteed to be in order.
    – sfdcfox
    Nov 17 '16 at 0:09
  • 1
    @sfdcfox I see what you mean: the same mechanism may be used for the allocation of auto-number values as is used for Id values (e.g. a block of number is pre-issued from the database to an app server). Looks like order by CreatedDate, Id is the best that can be done.
    – Keith C
    Nov 17 '16 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.