Extracting SubItems

I am new to monday.com, but have been asked to extract some details so that it can be reported on from within Power BI. I am using Postman to initially workout what I can get returned.

This is working for most of our board using the query:
query
{
boards
(ids: {{boardId}} ) {
id
name
items {
name
id
updated_at
group { title id }
columns: column_values { title text }
}
}
}

However, I am having issues with a board with subitems. It returns some subitems within column_values (as a comma separated list, causing me more issues as we have commas in the text!) but some of the fields are not returned at all.

From the image the [owner], [start date], [end date], [Approved budget], and [Forcast Spend] are not being returned.

Thanks in advance for any assistance.

Hey @Steven ,

check this out :

Greetings

3 Likes

@TMNXT-Dev

Thanks so much for helping out here! :slight_smile: That’s amazing community spirit.

-Alex

@TMNXT-Dev- thank you for the reply.

I have viewed the other post and details provided, but I am still struggling to return all of the columns from the subitem. I can’t understand why the fields [owner], [start date], [end date], [Approved budget], and [Forcast Spend] are not being returned. When the others are?

I have even tried by looking at a specific item:
query {
boards (ids: {{boardId}}) {
columns{
title,
id
}
items (ids: {{subitemId}})
{
name
column_values (ids: “subitems”){
id
value
text
}
}

This only returns:
{
“data”: {
“boards”: [
{
“columns”: [
{
“title”: “Name”,
“id”: “name”
},
{
“title”: “Subitems”,
“id”: “subitems”
},
{
“title”: “Budget Status”,
“id”: “subitems_budget_status”
},
{
“title”: “Timescale Status”,
“id”: “subitems_timescale_status”
},
{
“title”: “Benefit Status”,
“id”: “subitems_benefit_status”
},
{
“title”: “Milestone Status”,
“id”: “subitems_milestone_status”
}
],
“items”:
}
]
},
“account_id”: 000000
}

Just to clarify, is there any chance those columns are mirrored from another board, or are any of those columns a Formula column by any chance? If so, those columns will not show in your results, I’m afraid, due to those being calculated client-side.

I’ve also asked my teammates to take a closer look at how this works to see if they can provide any other guidance here. We are working on investigating this more closely and as soon as we’ll be closer to finding a solution, we’ll get back to you here :slight_smile:

-Alex

Thanks for the update.

It does look like the sub items are on a separate board.

We have removed the boardid parameter from the request and brought through the details of all boards. This allowed me to see a board named “subitems of …”. I don’t have access to the front end so not sure if this was created by default or by the team.

This subitems board provides all the details broken out and the additional columns that had been missing.

Steven

Thank you for following up on this!

To be transparent with you, sub-items are indeed developed in a way where they’re hosted in a separate board on the back-end that is not visible to users by default on the front-end. We’ve also placed some limitations inside the platform to prevent users from opening those sub-item boards by accident, or ending up on the sub-item boards via link.

Having said that, I’m glad you’ve been able to find a way to get around this roadblock to get the data you were looking for even quicker than we were able to come up with one :slight_smile: Kudos to you!

Is there anything else we can help you with here?

-Alex

As a final note, when extracting a board which is a sub-items board it would be nice if one of the metrics could be that of the board and item it is connected related to above.

This can be achieved in t-sql, but will break if you change the creation of a sub-item board to start with 'Subitems of '. Creating a self join to the extracted data on:
replace([extract_1].[boards name], 'Subitems of ', ‘’) = [extract_2].[boards name]
and [extract_2].Subitems like ‘%’ + [extract_1].[Items name] + ‘%’
and [extract_1].[boards id] <> [extract_2].[boards id]

Overall, the output you are producing is great- really glad you have the API and it is working really nicely for our requirements. :+1:

1 Like

Doesn’t work for me.
Some subitems are excluded from the response, this happens on some parent items but not for all. FYI

The only way I was able to get a reliable response is using the same API the frontend uses (after some reverse engineering)

Hi @amitozalvo,

sorry that this didn’t work for you.

Any chance you are able to post the details of the reverse engineered API string you used? This could likely be useful for myself and others, I personally don’t have access to the front end so was never able to look into doing it this way.

Thanks

Steven