Assign Query Result to Cascader Structure Property

Queries and Resources
1

I have a query ("myQuery") that returns results like this:

TRACKNAME POSNAME
21Shed 1
21Shed 2
21Shed 3
22Dock North
22Dock South

I'm trying to get that into a cascader's structure property. I've tried putting variants of the following into it such as {{myQuery.data}}, {{myQuery.dataArray}}, {{myQuery.dataArray[0]}}.

I've also tried to use Transformer and Javascript to no avail. Any ideas? Thanks in advance!

2

You probably have the data as an array of objects (assuming the result comes from a query) that you need to convert into an array of arrays.

Try this as your cascader source: {{query1.data.map(obj => Object.values(obj))}} (replace query1 with whatever the name of your query is).

Worked for me...

image
image779×618 47.9 KB

3

Hi jg80! I get this error when I try that: "TypeError: getTrackPositions.data.map is not a function. (In 'getTrackPositions.data.map(obj => Object.values(obj))', 'getTrackPositions.data.map' is undefined)"

4

My query is a normal postgresql query:
select t."Name" as "TrackName", p."Name" as "PosName"
from "CT_Tracks" t join "CT_TrackPositions" p on t.id = p."TrackID"
order by t."Name", p."Order"

5

Try this: {{getTrackPositions.dataArray.map(obj => [String(obj.TRACKNAME), obj.POSNAME])}}

6

Thanks!

This almost works:
{{getTrackPositions.dataArray.map(obj => [String(obj.TrackName), String(obj.PosName)])}}

I found that the String() are not really needed since these are text fields anyway. But the problem I'm seeing now is that it does populate the Structure property but it doesn't recognize the data as individual array objects. All of it is essentially concatenated into one item. I've attached what it shows when I click on the pull down:

cascader1
cascader11836×226 17.3 KB

7

Long post short, the right code should be:
{{getTrackPositions.dataArray[0].TRACKNAME.map((track, index) => [String(track), getTrackPositions.dataArray[0].POSNAME[index]])}}

Long post long...you want an array of arrays like this:

[
  ["21","Shed 1"],
  ["21","Shed 2"],
  ["21","Shed 3"],
  ["22","Dock North"],
  ["22","Dock South"]
]

For the initial suggested code provided, I loaded and parsed sample data as a (very) simple CSV through a file input component, then ran a "Query JSON with SQL" (select * from {{fileDropzone1.parsedValue[0]}}) and took the data from there, which is this array of objects:

[
  {
    "TRACKNAME": "21",
    "POSNAME": "Shed 1"
  },
  {
    "TRACKNAME": "21",
    "POSNAME": "Shed 2"
  },
  {
    "TRACKNAME": "21",
    "POSNAME": "Shed 3"
  },
  {
    "TRACKNAME": "22",
    "POSNAME": "Dock North"
  },
  {
    "TRACKNAME": "22",
    "POSNAME": "Dock South"
  }
]

And this can be manipulated into the right for with {{query1.data.map(obj => Object.values(obj))}}

...but clearly my mockup data isn't reflecting your real life data...so switching to importing the CSV into the Retool DB and a straightforward SQL query, I get the following dataArray:

[
  {
    "id": [
      1,
      2,
      3,
      4,
      5
    ],
    "POSNAME": [
      "Shed 1",
      "Shed 2",
      "Shed 3",
      "Dock North",
      "Dock South"
    ],
    "TRACKNAME": [
      21,
      21,
      21,
      22,
      22
    ]
  }
]

Which can be converted with {{query2.dataArray[0].TRACKNAME.map((track, index) => [String(track), query2.dataArray[0].POSNAME[index]])}}.

If your data is still not working with this new code, please paste in the value of getTrackPositions.data and getTrackPositions.dataArray and I'm sure we can figure it out...

1 Like
8

Awesome! That worked. Thank you so much!

9

Hello jg80. Is it possible to incorporate the database id with the PosName items so that when a user selects a TrackName>PosName I can get the id associated with that selection? And a related question, if I am displaying an existing record, how would I have the Cascader component display the existing record's PosName given the PosName id from that record?

10

I've found an easier way to deal with populating the cascader, which should have been obvious in the first place: {{[Object.values(getTrackPositions.data).flat()]}}. Using this aproach, if you add a third (or nth...) item to the query, this should continue to expand in the cascader. Using this approach, you can simply add the database ID to the query and the user can ultimately select that at the end of the cascader.

If you don't want the extra step of selecting an ID, there might be something analogous to a standard select component where there is a Value and a Label component for each item, but I'm not aware of how. The docs indicate there is a Value and a displayValue property for cascader components, so maybe there is something there.

In regard to setting the value based on what record you are displaying (assuming the record is selected independently of the cascader), did you try cascader.SetValue ? If that isn't working, I'd start up a new topic on what you've tried and what isn't working focused on that which might bring someone into the conversation with more experience using the component.

11

While fiddling with a Cascader, I found this excellent post that clarifies how to set the value and label for the cascader levels. Leaving it here for any future explorers plumbing the depths of using the component.

1 Like