Running GraphQL API query, how can you return values selected from another board?

monday Apps & Developers
, , ,
1

Hello All!

I have a query running against a Board, it pulls the items_page results. I just finished migrating the code from the 2023-07 version to the 2023-10 version.

But now I noticed that 2 of the fields used to return values in the 2023-07 results, but now return a blank value for the 2023-10 results.

I don’t know if I am using the proper terminology, but it seems as if these fields are setup to have the user choose from other Boards being Linked to this particular field.

For example, the field “Referral Source”, if you click on it to choose a value:
image

The user can select from multiple other boards, to then search in that board of choice for the value they want to appear in this cell as the Referral Source.
image

So my question is, how do you query to bring along these selected values coming from the other boards?
I’m assuming they may be consider Linked Items? But the “linked_items” function does not 100% seem to align in its explanation, or I’m just hoping thats false since it looks like it may only work in an Items query and not a Boards query.

Any help on this matter would be extremely appreciated!

2 
column_values {
  ... on BoardRelationValue {
    display_value
  }
 ... on MirrorValue {
   display_value
 }
}

Connect boards and mirror columns no longer directly return the text value, so you need to use graphql fragments which will only execute on column values of the type specified to return the “display_value” field.

But if you look in the documentation column type reference you will see all the fields for the various column types. For board relation you can get quite fancy returning the actual connected items (and query them for more than names, but also their column values!)

3

So if the body of my query looked like this right now, how might I bring in what you were sharing there? (it may look a little off with extra quotations, that’s because it’s in PowerQuery)

“{”“query”“: ““query { boards(ids: 1111111111111 ) { items_page (limit: 100) { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text } } } } }””}”

4

just within the set of braces after “column_values” just insert that,

“{”“query”“: ““query { boards(ids: 1111111111111 ) { items_page (limit: 100) { cursor, items { name, updated_at, group { title }, columns: column_values { column { title }, text, ... on BoardRelationValue {display_value}, ... on MirrorValue { display_value } } } } } }””}”

Now that I know this is PowerQuery (and I assume Power BI?) I will step out of this. Thats one platform I simply choose not to work with or try to get into because its a mess with things like pagination.

6

I appreciate the help Cody! I’m going to try your query additions today.

Yes I am doing this in Power BI. With a little help from people on this post, combined with YouTube channel of BI Elite (Parker), I pieced together a fully functioning dynamic pagination for the Monday query.

M-code example has been replied into this thread: Using Power BI to retrieve boards using Cursor-based pagination - monday Apps & Developers - monday Community Forum

I’ll respond back with how your query above works out.

7

@anon29275264 Unfortunately, no success. I did at least run without screaming at me about the added code, but still returned all null values.

image

8

The values will be returned as display_value not text so you need to do text ?? display_value in mapping the column to your table. I believe. Not a power BI guy as stated! But I believe thats how you do it, where if text is null then use display_value.

9

Ah, it is a Power BI problem it seems. If I work down 1 record at a time through the return results, without trying to bulk expand in PowerQuery, I got to the records of that Referral Source. It does then produce 2 values, “text” and “display_value”.

I don’t believe it will be as simple as trying to do an IF or CASE type of scenario, but I’ll look into it.

It will have to be when doing the List expansion in PowerQuery.
This part:

Table.FromRecords(Table.TransformRows(#“RecordsToColumns”, each
List.Accumulate([Columns], [
id = [id],
Title = [Title],
UpdateDate = [UpdatedAt],
Group = [Group][title]
], (state, current) => Record.AddField(state, current[column][title], current[text]) )
))

10

Thank you again for your help @anon29275264 this did help me with the Monday API portion :slight_smile:

11

https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-get-transform-null-coalescing-operator/e3e3bd21-ae31-49d2-befa-b552ba4fdd45

try current[text ?? display_value] in the expansion

12

that unfortunately didn’t work. I’m trying to look around as to where I can fit the fix into PowerQuery.

Since you know way more about Monday and this GraphQL, is there no way to do some sort of an IF or OR statement when selecting 2 columns, but also can a column be renamed within the query? Or to return null’s for a column name on records that wouldn’t have it?

In this example, only 3 columns are actually returning a “display_value” from that BoardRelationValue, but then all other columns return just the “text” and no empty “display_value” column at all. This is what is forcing further issues downstream in PowerQuery then, due to the column not existing in the other 20 columns.

I also tried using display_value instead of text. That doesn’t seem to work either.

columns: column_values { column { title }, text, … on BoardRelationValue {display_value}
columns: column_values { column { title }, display_value, … on BoardRelationValue {display_value}

13

Gets ugly.

You have to use fragments for EVERY type of column on your board

... on TextValue {
 text 
}
... on MirrorValue {
 text: display_value
}

you have to specify all of the column types you’re getting from the board this way, and NOT have “text” outside of any fragments. (the {} form heirarchies).

But if you had text outside a fragment it would try to return text every time including on mirror/boardrelation/dependency and you’d get a conflict.

You can put other fields beside text outside the fragments though.

15

Thank you for your help again @anon29275264 .

I wound up doing the extra steps needed in PowerQuery.

After using these Bodies of the API Call to capture those BoardRelationValues:

firstPage = Text.ToBinary("{""query"": ""query { boards(ids: " & BoardNbr_SalesPipeline & ") { items_page (limit: 100) { cursor, items { id, name, updated_at, group { title }, column_values { column { title }, text, ... on BoardRelationValue { display_value }} } } } }""}"),
nextPage = Text.ToBinary("{""query"": ""query { next_items_page (limit: 100, cursor: \""" & continuationCursor & "\"") { cursor, items { id, name, updated_at, group { title }, column_values { column { title }, text, ... on BoardRelationValue { display_value }} } } }""}"),

I then finish with some extra steps that expand the Columns, run a conditional column to bring forward when it finds a “display_value” and then group it all back up to proceed forward as it did before the expand those groupings into actual Columns in PowerQuery.

let
Source = List.Numbers(1,1),
#“Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“First Page” = Table.TransformColumnTypes(Table.RenameColumns(#“Converted to Table”,{{“Column1”, “FirstPage”}}),{{“FirstPage”, Int64.Type}}),
#“Invoked Custom Function” = Table.AddColumn(#“First Page”, “SalesPipelineApiResults”, each GetSalesPipelineCursorPagination2([FirstPage], null, null)),
#“Expanded ListToRows” = Table.ExpandListColumn(#“Invoked Custom Function”, “SalesPipelineApiResults”),
#“Expanded RecordsToColumns” = Table.ExpandRecordColumn(#“Expanded ListToRows”, “SalesPipelineApiResults”, {“id”, “name”, “updated_at”, “group”, “column_values”}, {“ItemId”, “Title”, “UpdatedAt”, “Group”, “Columns”}),
#“Expand Columns to Records” = Table.ExpandListColumn(#“Expanded RecordsToColumns”, “Columns”),
#“Expand Text Values” = Table.ExpandRecordColumn(#“Expand Columns to Records”, “Columns”, {“column”, “display_value”, “text”}, {“column”, “display_value”, “text.original”}),
#“Added Conditional Column” = Table.AddColumn(#“Expand Text Values”, “text”, each if [display_value] <> null then [display_value] else [text.original]),
#“Added Custom” = Table.AddColumn(#“Added Conditional Column”, “columns”, each Record.SelectFields([[column],[text]],{“column”,“text”})),
#“Removed Columns” = Table.SelectColumns(#“Added Custom”,{“ItemId”, “Title”, “UpdatedAt”, “Group”, “columns”}),
#“Grouped Rows” = Table.Group(#“Removed Columns”, {“ItemId”, “Title”, “UpdatedAt”, “Group”}, {{“Columns”, each _[columns] }}),
#“Expanding RecordsAndList” = Table.FromRecords(Table.TransformRows(#“Grouped Rows”, each
List.Accumulate([Columns], [
ItemId = [ItemId],
Title = [Title],
UpdateDate = [UpdatedAt],
Group = [Group][title]
], (state, current) => Record.AddField(state, current[column][title], current[text]) )
)),
#“Changed column type” = Table.TransformColumnTypes(#“Expanding RecordsAndList”, {{“Title”, type text}, {“UpdateDate”, type datetime}, {“Group”, type text}, {“Subitems”, type text}, {“Email”, type text}, {“Phone”, Int64.Type}, {“Territory”, type text}, {“CES”, type text}, {“Address”, type text}, {“Funder”, type text}, {“Status”, type text}, {“Caregiver Type”, type text}, {“Intake Complete”, type text}, {“Date of Intake”, type date}, {“Caregiver Name”, type text}, {“Caregiver Phone”, Int64.Type}, {“Hourly Cost”, type text}, {“Hours per week”, Int64.Type}, {“Payrate”, type number}, {“Deal Size”, type text}, {“Deal Age”, type text}, {“Days to Close”, type text}, {“Reason Lost”, type text}, {“Referral Source”, type text}, {“Supports Coordinator”, type text}, {“Quarter”, type text}, {“Referral Date”, type text}, {“First Shift”, type text}, {“Month of First Shift”, type text}, {“Zip Code”, type text}, {“Link to Events”, type text}, {“Follow Up Date”, type text}}),
#“Replaced value” = Table.ReplaceValue(#“Changed column type”, “,”, “”, Replacer.ReplaceText, {“CES”}),
#“Trimmed text” = Table.TransformColumns(#“Replaced value”, {{“CES”, each Text.Trim(), type text}}),
#“Renamed columns (REMOVE SOON)” = Table.RenameColumns(#“Trimmed text”, {{“Referral Date”, “Start Date”}}),
#“Replaced value 1” = Table.ReplaceValue(#“Renamed columns (REMOVE SOON)”, null, 0, Replacer.ReplaceValue, {“Hours per week”}),
#“Replaced value 2” = Table.ReplaceValue(#“Replaced value 1”, “,”, " ", Replacer.ReplaceText, {“Referral Source”}),
#“Trimmed text 1” = Table.TransformColumns(#“Replaced value 2”, {{“Referral Source”, each Text.Trim(), type text}}),
#“Split Column by Delimiter” = Table.SplitColumn(#“Trimmed text 1”, “Email”, Splitter.SplitTextByEachDelimiter({" - “}, QuoteStyle.Csv, false), {“Email”, “Email.duplicateValue”}),
#“Trimmed Text” = Table.TransformColumns(#“Split Column by Delimiter”,{{“Email”, Text.Trim, type text}}),
#“Removed Other Columns” = Table.SelectColumns(#“Trimmed Text”,{“Title”, “UpdateDate”, “Group”, “Subitems”, “Email”, “Phone”, “CES”, “Territory”, “Address”, “Zip Code”, “Funder”, “Status”, “Caregiver Type”, “Intake Complete”, “Date of Intake”, “Caregiver Name”, “Payrate”, “Caregiver Phone”, “Hourly Cost”, “Hours per week”, “Deal Size”, “Deal Age”, “Days to Close”, “Reason Lost”, “Referral Source”, “Supports Coordinator”, “Quarter”, “Start Date”, “First Shift”, “Month of First Shift”}),
#“Transform columns” = Table.TransformColumnTypes(#“Removed Other Columns”, {{“Title”, type text}, {“UpdateDate”, type text}, {“Group”, type text}, {“Subitems”, type text}, {“Email”, type text}, {“Phone”, type text}, {“Territory”, type text}, {“CES”, type text}, {“Address”, type text}, {“Funder”, type text}, {“Status”, type text}, {“Caregiver Type”, type text}, {“Intake Complete”, type text}, {“Date of Intake”, type text}, {“Caregiver Name”, type text}, {“Payrate”, type text}, {“Caregiver Phone”, type text}, {“Hourly Cost”, type text}, {“Hours per week”, type text}, {“Deal Size”, type text}, {“Deal Age”, type text}, {“Days to Close”, type text}, {“Reason Lost”, type text}, {“Referral Source”, type text}, {“Supports Coordinator”, type text}, {“Quarter”, type text}, {“Start Date”, type text}, {“First Shift”, type text}, {“Month of First Shift”, type text}}),
#“Replace errors” = Table.ReplaceErrorValues(#“Transform columns”, {{“Title”, null}, {“UpdateDate”, null}, {“Group”, null}, {“Subitems”, null}, {“Email”, null}, {“Phone”, null}, {“Territory”, null}, {“CES”, null}, {“Address”, null}, {“Funder”, null}, {“Status”, null}, {“Caregiver Type”, null}, {“Intake Complete”, null}, {“Date of Intake”, null}, {“Caregiver Name”, null}, {“Payrate”, null}, {“Caregiver Phone”, null}, {“Hourly Cost”, null}, {“Hours per week”, null}, {“Deal Size”, null}, {“Deal Age”, null}, {“Days to Close”, null}, {“Reason Lost”, null}, {“Referral Source”, null}, {“Supports Coordinator”, null}, {“Quarter”, null}, {“Start Date”, null}, {“First Shift”, null}, {“Month of First Shift”, null}, {“Zip Code”, null}}),
#“Replaced Value 3” = Table.ReplaceValue(#“Replace errors”,null,”",Replacer.ReplaceValue,{“Subitems”, “Phone”, “Date of Intake”, “Payrate”, “Caregiver Phone”, “Reason Lost”, “Referral Source”, “Supports Coordinator”})
in
#“Replaced Value 3”