Big Object field types workarounds

If you have taken a look at Big Objects, you will have noticed that we can only create fields of the following types (information valid for Winter 18 release):

  • Text
  • Number
  • Datetime
  • Lookup to Standard or Custom objects

Big Objects can be used for multiple purposes, but typically, archiving old records is one of the use cases. What can you do if you need to archive a custom object which has field types that are not supported, as for example picklist or master-detail? What if you want to create lookups to Big Objects? Read this post and you will find a solution.

1) Checkbox fields workaround:

You can create a text formula field in the source custom object, that converts the checkbox information to a text, in the next way: IF(IsProcessed__c == true, “true”, “false”). Then, you will be able to copy the result into a text field on the target Big Object.

Captura de pantalla 2017-11-19 a las 13.11.03

Another option could be doing the same, but using a number formula field on the source custom object, and a number field on the destination Big Object. IF(IsProcessed__c == true, 1, 0):

Captura de pantalla 2017-11-19 a las 13.11.07

2) Picklist workaround:

In the same way than with the checkbox field, You can create a text formula field in the source custom object, that converts the picklist information to a text. Then, you will be able to copy the result into a text field on the target Big Object.

Captura de pantalla 2017-11-19 a las 13.11.14

3) Master-Detail workaround:

In this case, you can directly copy the Id into a lookup field on the target Big Object. You won’t have the particularities of master-detail fields in this case, but at least you will have them related to your Custom Objects and you will be able to implement missing functionality. Remember that Standard UI is not available for Big Objects, so, if you want to show a related list of the child Big Objects on the parent Custom Object, you will need to develop your own UI.

Captura de pantalla 2017-11-19 a las 13.11.19

4) Lookups to Big Objects workaround:

Lookups can be created on Big Objects, but they can point only to Standard or Custom Objects. If you want to relate two Big Objects, there is a solution that you can adopt.

Remember that from API 40.0 indexes on Big Objects are compulsory. This index can be compound, of up to 5 fields, and it will be the primary key or unique identifier for the records on the database.

As the index identifies uniquely the records of the source Big Object, what we can do is to create fields on the target Big Object that reference the source Big Object index fields, as we know that the combination of those fields is going to be unique per record on the database.

Captura de pantalla 2017-11-19 a las 13.11.26

Do you want to start using using Big Objects? Don’t wait more! They are GA from Winter 18 and you can start playing with them right now. If you need more field types to support your solution, here you have some workarounds that will help you.

If you want to know more about Big Objects, check this post I recently published, and also this fantastic post from Agustina García.

12 thoughts on “Big Object field types workarounds

Add yours

  1. I was trying fetch index field (Of big object) definition using tooling API query. But the behaviour is very strange when I check the schema using setup it does shows index field correctly but tooling API did not returned same results.

    What I had observed was “IsIndexed” flag itself was showing wrong values, then how come same big object schema shows correct index via setup.

    sample query#
    Returns correct results –
    SELECT QualifiedApiName, IsIndexed FROM FieldDefinition WHERE IsIndexed = true
    AND EntityDefinition.QualifiedApiName = ‘Account’

    Returns wrong results
    SELECT QualifiedApiName, IsIndexed FROM FieldDefinition WHERE IsIndexed = true
    AND EntityDefinition.QualifiedApiName = ‘CustomeBigObject__b’

    ID, false //but this is marked as index field in big object
    Name, false //but this is marked as index field in big object
    Comments, false
    e.g. If there is only one index field then above query returns zero records; If three fields were marked as index then it returns first index field info. But same tooling API query for standard objects returns correct data.

    Like

  2. I believe that may happen because the infrastructure under big objects is completely different from the infrastructure under standard / custom objects. The database and technologies used are completely different, so I guess the IsIndexed field is only meaningful for standard / custom objects (although I am not sure about this). You can try to check the Index metadata type instead (https://developer.salesforce.com/docs/atlas.en-us.bigobjects.meta/bigobjects/meta_index.htm)?

    Like

    1. During testing when I had setup three index field the query does return one field out of three but rest of two fields flag was false.

      This is very wired. Try the query I had provided on your environment and see the behaviour.
      SELECT QualifiedApiName, IsIndexed FROM FieldDefinition WHERE IsIndexed = true
      AND EntityDefinition.QualifiedApiName = ‘’

      Like

Leave a Reply to Numaan Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: