Most organizations start out with 1GB of storage for Salesforce CRM. This can be a challenge especially for nonprofits who would like to collect a lot of data needed to report to their funders, collect program data or consolidate multiple aspects of their business data into Salesforce. Over time the data fills up the allotted storage and puts organizations into a bind to either purge data or purchase more storage.
Over the years we have explored different solutions that can be used as preventive measures and limit the storage used. The traditional approach is to either export and archive or aggregate and purge older records. The drawback with these solutions is that you can no longer see the details of the historical data in Salesforce.
Below we describe a different preventive approach inspired from document-oriented databases that allows organizations to keep their data and not run the risk of running out of storage.
Background
Salesforce CRM is a relational database, and as such you must define a schema (tables and relationships) before adding records. Each record you add will take up storage, 2K for most types of objects, regardless of the number of fields or the size of those fields. Therefore, a structure such as the one below where we model 3 pay periods each with up to 15 time entries can add up to 96K.
In contrast, a document-oriented database contains “documents”, which are records that describe the data in the document, as well as the actual data. Documents can be as complex as you choose; and you can use nested data to provide additional sub-categories of information about your object. We can leverage this concept to store self-contained data (like an invoice or a timesheet). Using the same example as above, through this method we essentially reduce the storage usage from 96K to 6K since each pay period contains all its time entries. That translates to a 93% savings in storage usage.
Use Case – Timesheet
One typical use case where we can apply this solution is a timesheet. Let’s assume you are asking staff to submit timesheets by entering their daily time entries twice monthly. Each month will have two pay periods each with at most 15 days.
You care to keep a record of their time entries, but there is really no value in storing each time entry separately as a Salesforce record. The value to your organization is mainly the aggregate information (how many hours staff worked per pay period) rather than their daily entries. Of course, as a manager you would still want them to enter their individual time entries in a table format and you want to be able to see the details of each time entry should the need arise.
The approach we propose suggests that instead of creating a Salesforce record for each time entry per each pay period (which comes out to 15 records/pay period) you store the 15 times entries into one Salesforce Long Text field in JSON format in a “document” style directly on the pay period record.
With this design you have basically reduced storage from multiple records (15 per pay period) down to one field that can be stored on the pay period record. JSON will allow you to store very rich information about each time entry, provided the string produced is of a reasonable length and doesn’t exceed the Salesforce Long Text field size of 131,072 characters.
The next step is of course to make this more human readable. The time entries string can be displayed in a Visualforce page or Lightning component in a table format by parsing out the details from JSON, and making the data viewable and even editable with some custom code.
To illustrate our example, we used a free Lightning component from the Salesforce AppExchange called Lightning DataTable Dev . We customized this Lightning component to display the time entries for the pay period in a user friendly, table format. We then added the Lightning component to the record detail page of the Pay Period.
Conclusion
Using a concept inspired by document-oriented databases allows us to consolidate multiple time entries into a single field on a record instead of having these distributed across multiple objects. This solution greatly reduces the storage usage over time. There are a few things to consider about the data when deciding whether to use this approach:
- The data does not need to be readily available for reports through the out of the box Salesforce reporting engine.
- The data you’re considering is self-contained.
- The value of storing the data decreases over time.(The value of knowing how many hours someone worked on a particular day goes down over time. You will only care how many total hours someone worked during a certain pay period.)
- The data to be stored as the JSON string does not grow over time to exceed the length of the string field. (For our example there can be at most 15 time entries for each pay period.)
Implementing this approach does requires Apex and Lightning development skills. Contact DaizyLogik to learn more about how we can support you with specialized consulting and custom development and help you get the most out of Salesforce.
DaizyLogik 