Summarising database data within checkbox component

[rjb2000]

Excuse the beginner question

Goal is to present counts based on conditions from my Retool DB within a Checkbox Group component.

I'm building an input and checking system for a clothing item database.

An aspect is a checkbox group that allows users to choose which item_type to display.

I want to present a summary of how many of these items there are to check in each category with the intention being this:

Jeans (3)
Jacket (7)
T-shirt (12)

etc.

So this is displaying different item_type items with the status of "Lister Updated" or :"Checker Skipped"

---

Currently I have an SQL request as follows:

SELECT item_type, status, COUNT(*) as total
FROM listings
GROUP BY item_type, status

and this to Transform Results:

const LABELS = {
"t-shirt": "T-shirt",
"jeans": "Jeans",
"jacket": "Jacket",
"button-up shirt": "Button-up Shirt",
"sweatshirt": "Sweatshirt",
"shorts": "Shorts",
"trousers": "Trousers",
"hoodie": "Hoodie",
"sweater": "Sweater",
"cardigan": "Cardigan",
"knitwear": "Knitwear"
};

const col = data;
const result = {};
const length = col.item_type.length;

for (let i = 0; i < length; i++) {
const raw = (col.item_type[i] || "").trim().toLowerCase();
const type = LABELS[raw] || raw;
const status = col.status[i] || "Unknown";
const total = parseInt(col.total[i], 10);

if (!result[type]) {
result[type] = { "Lister Updated": 0, "Checker Skipped": 0 };
}

result[type][status] = total;
}

return result;

The output from this looks about right:

I'm really struggling to get this to display in the goal format, within the checkbox component or even a text component.

I have this in a text component and it doesn't return anything:

{{ count_listings_by_type["Jacket"]?.["Lister Updated"] || 0 }}

Any help much appreciated, sure it's something simple I'm missing!

Thanks

[dcartlidge]

First of all, no need to apologise for asking a sensible question, everyone is a beginner at some point.

Second, your code and output looks fine, it looks to me like you need to specify the data or value that your code is returning.

eg if I have a db query called getMyResults then I access the data through the data property ie {{ getMyResults.data }}
For a transformer or variable then it's the value property, e.g. {{ formatMyResults.value }}

So for your case you might want to try {{ count_listings_by_type.data["Jacket"]?.["Lister Updated"] || 0 }}

[rjb2000]

That worked! Thanks so much, I knew it would be something simple like that.

If I wanted to input into the checkbox component, where would I input the code?

To achieve this formatting:

Jeans (3)
Jacket (7)
T-shirt (12)

The (number) being the return of this: {{ count_listings_by_type.data["Jacket"]?.["Lister Updated"] || 0 }}

Thanks

[dcartlidge]

There are a few ways to achieve that, as with most things in Retool.
The best way for you will depend on knowing things like, if the list only needs to show those 3 items or only specific ones, or the list of items can change, if ones with zero should be hidden/disabled etc.

Here's an example of how to set the labels of a checkbox group to use the values from a variable, but it could just as easily come from a query or other data source.

[rjb2000]

Got it!

thanks a lot, really helpful