0

I am working on an App and am coming up with the object model. I am using custom Log__c records, and, of course, I want to limit having excessive records if possible.

I want these logs to contain text field(s) with lists of sObject record Ids that are filterable via SOQL. So, long text field is out; I must put these Ids in fields within 255 char text fields.

In one possible model, I have very few Log__c records, but 556 text fields, to accommodate up to 9996 sObject records processed by a DML per transaction. I get to this number with the following:

  • 13 characters per sObject (18 digit Id, without the first 5 characters) Note that in this case, all sObjects are the same type, and that sObject type can be stored in a different field, so the first 3 characters, the object prefix, can be dropped. And the 2 characters for pod identification can also be dropped I believe. But, because SOQL text filters are not case sensitive, the last 3 digits of the 18 digit id must be present.
  • 1 character as a delimiter
  • 255 / (13 + 1) = 18.214 or 18 sObject Ids per 255 char text field
  • 9996 / 18 = 555.333 or 556 text fields needed to store up to 9996 sObject record Ids
  • The SOQL to search through these Log__c records will look like this:
  • String recordId = String.valueOf(mySobject.get('Id')).right(13);
  • SELECT Id FROM Log__c WHERE Sobject_Record_Ids_1__c LIKE :recordId OR Sobject_Record_Ids_2__c LIKE :recordId OR Sobject_Record_Ids_3__c LIKE :recordId ... OR Sobject_Record_Ids_556__c LIKE :recordId
  • This is a big SOQL query but it should be well below the 100k characters per SOQL query limit.

That's a lot of fields! There are other potential models where I have more Log__c records being created, and fewer fields to store all the sObject Ids.

My question is three parts:

  1. Do you see any flaws in my logic?

  2. What is the absolute limit for the number of fields an ISV partner can place on a custom object? This needs to apply to all SF Orgs (Personal, Contract Manager, Group, ... through Enterprise)

  • From what I see here, it seems like that limit is 900 fields: https://help.salesforce.com/s/articleView?id=sf.custom_field_allocations.htm&type=5
  1. What are some practical concerns we might have when having such a large number of fields? Ex: Apex limits like heap size, SOQL timeouts, general slowness etc.

Thank you!

6
  • Since you must develop the package, the maximum number of custom fields you can have in the package is lower; the number you can have on a scratch org. Make that a Partner Developer edition and the limit is 800, I believe (though I have not confirmed this).
    – Phil W
    Commented Dec 29, 2023 at 19:02
  • While the approach you suggest is probably doable, and is space efficient in the database, it is less than ideal to store IDs in text fields. You need to have a very good reason for it.
    – Phil W
    Commented Dec 29, 2023 at 19:08
  • 1
    How are you expecting to use this information? That is, in what circumstances or for what purpose do you expect to query logs by sObject Id? Are there potentially ways you could separate logging from that use case to open up new implementation possibilities?
    – David Reed
    Commented Dec 29, 2023 at 19:39
  • One reason for this is to have an LWC on whatever sObject record page showing which logs this record was a part of. Another reason is having the ability to reverse the transactions. And, I'd like to do this without assuming that there is field history on sObjects or requiring that a custom field is added to sObjects. Upon more thought I'm thinking of doing something in the middle; maybe up to 100 log records per transaction with 6 fields for storing Ids. This helps keep SOQL queries light, and is still quite space efficient, considering most transactions will not be anywhere near 10000 records
    – Questioner
    Commented Dec 30, 2023 at 2:04
  • You plan a log per transaction against a (collection of) record to indicate the change(s) applied? Not all bulk updates update individual records the same way, and they are not always changing a single field. I am concerned that this will not scale, even with trying to minimise the number of log records created, and may lead to unexpected and inappropriate consequence when attempting to "undo" transactional updates (especially if done out of order). I question the use case behind what you have described.
    – Phil W
    Commented Dec 30, 2023 at 8:19

0

You must log in to answer this question.

Browse other questions tagged .