As he listened to parts of my story, he was surprised by a couple of points. First, he was surprised that I wasn't able to use Data Loader to download the deleted records on Friday, while they were still in the table. When I explained that they were gone now, he was surprised but noted that the sweeper utility may have just come through our org. According to him, I wouldn't have been able to simply restore the records by toggling the IsDeleted flag (but being able to download them while they were still in the table would have been useful - where were you on Friday?).
When I mentioned the "invalid cross reference id" error, he noted that the issue there was trying to re-import the data with an ID - that the system had to assign an ID. I told him that this would make it impossible to re-link the "Invoice Line" child records, but he said that it could be done with a bit of work in Excel. He then asked me to send those sandbox files that I needed to re-upload and offered to solve the import issue for me.
In all, I'm feeling a bit better about all this than I was yesterday at 6:30, but we'll see how it all unfolds. I'll keep you updated.
----- Origitnal Post ------
If you've been watching my Twitter feed, you've already seen parts of this story, but you may find more coherence (and a few more details) in this version.
A couple of years ago, following a mention at Dreamforce, I purchased a suite of software extensions for Salesforce from Passage Technology. As a Salesforce Admin, what drew me in was a product that they offered, Rollup Helper, that made it possible to build data roll-ups of a variety of data even across custom objects. To translate this into simpler language, a roll up field enables you to do something like total the value of all opportunities on an account. Salesforce has always had roll-up fields but the functionality was limited to records that had parent-child relationships (with the roll-up on the parent). Rollup Helper enables you to create roll-ups on records, even if there isn't a parent child relationship.
The pricing for Rollup Helper was about $2K for a one-year license, but they also offered two other software utilities, Lookup Helper and Storage Helper. If you bought them as a bundle, the other two software tools (each about $800), were included at a discount. Storage Helper seemed like it held some promise. Essentially, what it enables you to do is set up a filter condition and automate the deletion of records. Since the base data storage allocation for Salesforce.com is 1GB, depending upon your record usage, this could represent a savings for you versus paying for added storage.
For the most part, our data all fit easily within Salesforce data storage limits, with the exception of Orders and Invoices that were brought in through our Oracle back-end system integration. While the header record volume wasn't large, each of those records also included multiple lines and, as this data accumulated over the years, we hit our data limits. Initially, I manually deleted old records, but eventually wound up buying more storage space. In this environment, a software utility that we could configure to erase records that were over five years old seemed like a handy utility.
Some "In Hindsight" Data Points I Should Have Weighed More Heavily
I had mixed results with Passage Technology software, and I probably should have given more consideration to that when I ran Storage Helper on Friday. I actually created custom fields and set up Storage Helper back in October of 2016, but with our added storage, I didn't really need to run it, so the software sat, unused in my org for a couple of years. Meanwhile, I made a couple of separate attempts to use Lookup Helper, none of which were successful despite connecting with Passage Technology customer support both times.
I should have known...
In many ways, Storage Helper should be pretty simple software. The premise - set up a set of conditions to filter a search, then delete the results of the search. The thing that should make it cool is that you can set it to run on an automated script, so it will just work for you. Initially, I'd built a filter for both Orders and Invoices that looked at the year in the date record, then deleted things greater than 6 years old. Part of the reason why I'd waited to run the script was, after talking to some of my colleagues, I'd thought about possibly trying to calculate the age down to the month.
Anyway, background aside, I was getting ready to do an evaluation of Salesforce.com's Wave Analytics module. As part of the set-up for that, Salesforce also provided a trial license to a full Sandbox. But since we were going to evaluate the analytics, it seemed like it might be a good idea to remove some of the old order and invoice data, both to streamline the data migration and to simplify charting functionality. And so I ran Storage Helper on Invoices.
This was my first mistake.
Before I ran the software for the first time, I did previewed the data that would be deleted, something that's included in the software and recommended by the wizard. A quick sample of the data preview showed records from 2010 - so that looked good. It also triggered an error alert because the data set that it was getting to run on was greater that 50,000 records. I okayed that - figuring that it would require multiple runs of the software to get the job done.
After running the software, the interface offers a little dashboard that says, "Storage Helper saved you this much money and freed up this much data storage space". I was curious if it was deleting the child "Invoice Line" records and how much space that accounted for, so I went over to the Salesforce System Monitor and looked at the storage usage. Unfortunately, when stuff is in your recycle bin, it's still included in your storage count on System Monitor. I'd previewed the data (if you've ever looked at data in your recycle bin, there isn't a lot of helpful record detail info there - practically speaking, the only way to really know the details of a record is to undelete it), so I decided to go ahead and delete it. Now the system monitor showed a change and, for a moment, I was happy.
Back to Storage Helper, and I previewed the data again. Again, records from 2011, so I ran the script again. I should say that the script seemed to lag while it ran, but that's not unusual with some of these Salesforce processes. Because of the lag (thinking back, I think it lagged the first time it ran as well), I wasn't really sure whether it was working or not. Again, I deleted the records in the recycle bin. With the lag, I might even have pushed the "Run" button multiple times. Somewhere in there though, it finished it's second or third run, and I was curious how much data was left in these old records.
So I ran preview again. This time, I noticed an Invoice Owner who joined the company more recently. I was surprised -- how could this guy have owned a record from >6 years ago. I clicked through the preview to the record and, sure enough, the record was from last year. Last year? Then I looked at the Storage Helper script and the filter that I'd built -- it was gone. There was no filter.
I quickly went over to the recycle bin and "undeleted" all of the records in there. I also emailed the Storage Helper product manager from Passage Tech. This was before lunch. He emailed me about 10 minutes later to say that he was no longer the product manager for that product -- and forwarded my email to the current product manager and their customer support team. Meanwhile, I started to assess the damage.
A little after noon, I sent an follow up email to Passage Tech with these screenshots, the first being a Pivot Table report of Invoices from Live, the second being an extraction from my Sandbox that was refreshed last September:
That's an 84,948 record difference between live and seven month old data.
It goes without saying that, after that first contact with Passage Technology, I didn't hear back from them until Monday morning when I got an email from their customer support.
Salesforce Steps Up To Take Things From Bad to Worse
After not hearing anything back from Passage Technology (guessing that they were on East Coast Time and had probably gone home), I reached out to my Salesforce AE. Was there any way that Salesforce could help?
She suggested creating a case. Once there was a case, she said, they could escalate the case. And so I wrestled with the stupid subject tree to find a case and submit it. By 4:00pm, I finally had a case number. I forwarded the case number to her and she escalated it. Salesforce customer support reacted quickly, but the guy who called me talked with me as we both went to the recycle bin and looked to see if the records were there (they weren't - I'd already 'undeleted' what records were there when I realized what Storage Helper had done). Since there were no records there, he told me that Salesforce did offer a paid data recovery option, but I'd need to speak with my AE for pricing on that option. He also suggested that I consider the Idea Exchange (seriously).
So I emailed my AE again. She let me know that the data recovery thing started at $10K. Other than that, she reached out to some other Salesforce colleagues for suggestions. Queue the crickets.
Seeing that this wasn't going well, I attempted to restore some of the missing data by "Upserting" my old data from Sandbox. If you aren't familiar with the term, upserting essentially updates a record if it finds one, otherwise it inserts it. Unfortunately, this didn't work because all of the deleted records were still in the database table, they just had a field called "IsDeleted" flagged with true. Realizing this, I tried to fix it by updating all of the "IsDeleted" to false. Unfortunately, that didn't work because Salesforce won't let you modify the "IsDeleted" field.
So I searched for ways to update the IsDeleted field. I found a couple of suggestions. One suggestion was the Developer Workbench, an online tool that provides you access to a bunch of the underlying aspects of Salesforce. Using this tool, I was able to verify that the records were there. I could even look at the records individually. Thinking that this would be helpful for knowing exactly which records had been deleted, I tried to Bulk Export a .csv file of the deleted records. Unfortunately, bulk exports of this type of data weren't supported. I tried to work with specific records to change the IsDeleted flag, but that wouldn't work either. I even tried to generate a list of the deleted records so that I could copy and paste them into a spreadsheet, but that wouldn't work.
From an older post that I found online, I saw where somebody had written that some third-party data loader tools might enable you to modify IsDeleted. I tried rerunning an older version of LexiLoader that I had. It said that I needed an older version of Java to run it - so I installed that. Even then, while LexiLoader would run, it couldn't log in. By 6:30 on Friday, I'd exhausted all of the options that I could find. In another exchange with my AE, she said she was out of ideas and that we'd have to wait for technical support - on Monday.
At that point, I posted this on Twitter with what I expected to happen:
There are a couple of things worth noting at this point:So here's what I expect to happen. Despite me trying to work with them on the issue immediately, @salesforce will do nothing and, by Monday my data will only be accessible through tape back-ups and the $10K recovery. #FAIL #sucks— yaMarketingGoomer (@MarketingToMe) April 7, 2018
- What I expected to happen is that at some point that night, Salesforce's system would process (and erase) the data that was flagged with IsDeleted. This would mean that data that was easily accessible and a situation that was potentially correctable with the right tools and/or permissions would soon be out of reach.
- Once this data was deleted, depending upon how the Salesforce system works, it's possible that they might recycle the ID numbers of the records that were deleted. I don't know about this aspect of how Salesforce works and, if they do, how quickly those ID values are recycled. However, if an ID value was recycled, that would essentially mean that you couldn't just "re-upload" the data. And worse, any data that depended on being linked to that ID (like my Invoice Line child records) couldn't be re-uploaded.
- Once this data was wiped from the data table, it would be significantly more difficult to determine which records had been erased and which ones had not, making a recovery and reset even more difficult.
As you can guess, I didn't hear anything back from Salesforce or Passage Technology until Monday morning. After I granted Passage Tech customer support login access, they came back with a grand toreador...
Unfortunately, we have no history to determine as to whether the filter existed, or was setup correctly to begin with since the job has not been successfully run in the past.See, it's not their fault.
Meanwhile, I hadn't heard back from my Salesforce AE Monday morning, so I emailed her. I emailed her a second time after I used the Developer's Workbench tool and did indeed verify that all of the deleted records were now gone. My AE responded after 11:00am, noting that she'd been in a customer meeting. By lunchtime, she emailed me again with more scoping questions. I didn't hear back again until later in the day when I emailed a second follow up. What did seem clear from her afternoon email was that, for any data restoration being done, it would probably be on us to do it.
So yesterday evening I made a second attempt to upsert the older header records from my Sandbox instance using Data Loader. Errors again, only this time a different error message, "invalid cross reference id". It goes without saying that, it's not just a handful of records either.
At this point, it seems like the whole thing is just F@#ked!
Because of the inter-relationship of records, I don't think there's a tool (short of our integration orchestration), that can upload the data and the related. While we have the source data - we didn't loose that, it looks like there is no easy way to reload it back into Salesforce. And, essentially, all of the data is worthless because you can't easily tell what data is missing.
It goes without saying that I'm so pissed off at this point that it's even difficult for me to go back through and write about it. I'm trying really hard to keep to keep my vocabulary restrained and professional. What more can I say about Passage Technology and their software -- I think that they can expect that I won't be renewing our license for their software, but I don't expect much from them since we're really talking about, at most, a $3K per year customer.
But Salesforce? What can I say. We often talk about Salesforce.com being an unsupported platform -- when the rubber met the road, their customer support FAILED. They followed up the case on Friday night with ANOTHER email about the Idea Exchange. And, at 6:30pm on Friday when, if they had a resource that could extract or modify the IsDeleted field on two tables in our org, we could have recovered from this entire mess. Instead, they were done for the week. TGIF. Pick up the pieces next week.
At this point, I don't know how the rest of this will work out, but I do know one thing -- I don't expect to have good words about Salesforce.com going forward.