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:
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
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…
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’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.
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( # 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?!?