Hi, How do I surface a table with only a specific (in this case, “species”)column from a JSON that is a result of a LOOKUP Query. I thought of nesting it into a FLATTEN() function but I don’t know how to implement it.
I also thought of parsing the JSON using a “Formula” type column but the formulas were not updating on the Sheets unless I click Save and Validate
Hi,
We have a function called - STRINGTOJSON() which parses the resultant JSON from a LOOKUP JSON column. You can use this function in Datastore and you will be able to get the desired key.
Please note, for such a setup, you will also have to use a special function called as MAPKEYS() to define the key names. Using this you will be able to get the datastore suggestions in other controls
All you need to do is -
MAPKEYS(STRINGTOJSON(LOOKUP([regen_cover_crop.covercropgroup],regen_cover_crop.plan_name="Clover Barley Plan")),"speciesfamily:speciesfamily,species:species,plantingratelbsac:plantingratelbsac")
Also please ensure, the result of the LOOKUP is 1 record/row that can be passed to STRINGTOJSON().
Hope this helps.
@chirag1 I tried putting the formula you gave (+making sure the lookup result is 1 row) to a list variable on a PDF (in Drona PDFCreator). The List Variable is inside a dynamic table but I could not get it to show up on the pdf.
Hi,
You will have to bind the formula ( MAPKEYS(STRINGTOJSON(LOOKUP())) ) in a datastore and then use the datastore’s reference to an input list type control, for instance - multiselect. Map the multiselect control to List Variable of the PDF task to show it on PDF.
Hope this helps.
