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.
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
}
}
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
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.
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 Kudos to you!
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.
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.