Extract an Intercom Custom Attribute

Hi there,

I’ve used the Intercom pack to pull in custom attributes from Intercom users. The Custom Attributes column has a list of custom attributes like this:

custom_attribute_1,custom_attribute_2…

You can hover over each custom attribute and see what data it’s storing:

Key: custom_attribute_1
value: custom_value_1

I think this column is actually a list JSON values like this:

=List(ParseJSON(’{“Key”:“custom_attribute_1”,“Value”:“custom_value_1A,custom_value_1B”}’),ParseJSON(“custom_attribute_1”,“Value”:custom_value_2")

I want to create a separate column that extracts a single value, such as the value of custom_attribute_2. I can’t figure out how to do this.

I tried [Custom Attributes Column].Nth(2).value, but the custom attribute I’m looking for is in a different position in the list in each row, so that won’t work.

If I knew how to search for the numerical position of a list item, I would do something like this:

[Custom Attributes Column].Nth([Custom Attributes Column].SearchForNumericalPositionOf(custom attribute 2))

There’s probably some way to use the Count() function for this, but I don’t know how.

Here’s an example:

1 Like

Hi @David_Busis, welcome to the community!

If you could post a minimalist example of what you are trying to do via Coda’s “share doc” functionality, that would allow myself and other users to dive in and try to help you. Otherwise we are stuck trying to interpret the message (reading formulas is not as easy as interacting with them).

In the process of creating this doc / minimalist example, you may just solve the problem (as I do myself).

Looking forward to the doc so I can dive in!

You can still create an example doc that has all the necessary funcitonality for the task you are trying to do. It seems like a pain at first, but it very help to everyone involved :slight_smile:

Lloyd

Okay, here’s an example table:

Hi @David_Busis,

Thanks for posting the example!

Is this what you are looking for?

Formula:
thisRow.[This Person's Attributes].FormulaMap(If(ParseJSON(ToText(CurrentValue),"#/key")="attribute2",ParseJSON(ToText(CurrentValue),"#/value"),False())).Sort().Last()

The trick you may have been missing I just discovered with some plug-and-play experimentation: toText() converts the item back into JSON so we can use ParseJSON() to access certain attributes. I tried @currentValue in the FormulaMap(), but it wouldn’t let me access key or value.

(Note that this formula doesn’t require that attribute2 exists, rather it will just be blank otherwise.)

PS - False())).Sort().Last() feels like a sin against programming… But I don’t know a better way to filter down to just the one attribute… Someone let me know if there is an easier and more consistent way to do this…

Lloyd

Thanks, Lloyd! The formula works in the example, but in my actual doc, it spits out “false” no matter what I plug in for “attribute2”. I can’t tell what I’m doing wrong, or what I did wrong in the example.

Can you explain your formula in human language?

Does it look through each item in [This Person’s Attributes], check to see if the item has a “key” called “attribute2,” and if so, put the value back into JSON? And…it sorts all those attributes and puts what I want last and returns the last one, or something?

I’m not a programmer :frowning:

Hi @David_Busis,

I’ll break down the formula into pieces. Although without the actual example you are working with I don’t know if perhaps something is different in your real use case instead of the example.

thisRow.[This Person's Attributes].FormulaMap(  # Take the attributes from this person, and apply the following "If" to each element
	If(
		ParseJSON(ToText(CurrentValue),"#/key")="attribute2",  # If the element has the key = attribute2
		ParseJSON(ToText(CurrentValue),"#/value")  # Then we want the value
		,False()  # Otherwise just put "False()"" (This could be anything that sorts at the beginning)
	)
).Sort().Last()  # Finally, we have to sort the list and take the last thing, since there is always only one attribute2

Let me know if you want more indepth help with this. If you want to send me a private message with a link I can personally look into, I can do that. I will then update the public answer. I think there is something wrong with the end of my formula, but it’s hard to tell when it works for the example you gave.

PS - Your example has value2, in it, with the comma after value2. Is that intended? I removed it from my working example above, but either way the formula appears to work.

Lloyd

Hi @David_Busis,

Although I think the problem is perhaps that your example is not exactly what is going on with the real thing, here is an improved formula that I think is less abusive since it does not take advantage of the sorting system.

thisRow.[This Person's Attributes].FormulaMap(If(ParseJSON(ToText(CurrentValue),"#/key")="attribute2",ParseJSON(ToText(CurrentValue),"#/value").First(),"")).Filter(Not(isBlank(CurrentValue)))

And here is it explained:

thisRow.[This Person's Attributes].FormulaMap(  # Take the attributes from this person, and apply the following "If" to each element
	If(
		ParseJSON(ToText(CurrentValue),"#/key")="attribute2",  # If the element has the key = attribute2
		ParseJSON(ToText(CurrentValue),"#/value").First(),  # Then we want the value
		""  # Otherwise we put nothing, signifying that we didn't find attribute2
	)
).Filter(  # Now, we want to filter out those pesky Blanks we put in
	Not(isBlank(CurrentValue))  # We do this by only taking values that are "Not()" "isBlank()"
)

PS - When will the Coda devs give us formatted formulas with multi-lines and inline comments?!?

Lloyd

Lloyd,

I got swept away on a tide of life and forgot to thank you for this. It’s extremely helpful and generous of you. Thank you!

1 Like

For others that might stumble upon this thread -

Another way to extract the Key Value ( without the JSON parsing) for the Intercom pack would be to use a Filter -

Person.CustomAttributes.Filter(CurrentValue.Key=“attribute2” ).Value

Thanks, Mallika! I just came back to this and it works perfectly.

1 Like