InvalidTemplate error using select action in flow

[amitlathia]

Hi Community,

I have been building a flow, which up until recently worked. All of a sudden I am receiving the following error...

InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '...The template language expression 'item()['Babukaka_x0020_Hall_x0020_Start_']' cannot be evaluated because property 'Babukaka_x0020_Hall_x0020_Start_' doesn't exist... 

From what I can see it is saying that the selected column from sharepoint does not exist - but I have selected this from the get items output, and I can also confirm its not a required field in sharepoint so even blank values should not impact this from working. The column concerned is a simple date/time field.

Has anyone had a similar problem or able to suggest a solution. My flow is designed to select certain columns from a sharepoint list and create a CSV table which will then get emailed out on a regular basis.

Many thanks for your help in advance

Amit

[v-yamao-msft]

Hi @amitlathia,

I assume that there might be blanks within the Date column. Please make sure all the fields under the Date column are filled with value.

I have made a test on my side. When there is a blank value under the Date column, the flow would be failed with the error you mentioned. While if all the fields under the Date column are filled with value, it will run succeed.  If you test with other types of columns, you will get the same result.

Please try to fill in all the date fields then test it again.

Best regards,

Mabel Mao

[amitlathia]

thanks @v-yamao-msft Mabel,

Is this not a bug then... In a normal use of sharepoint lists there maybe instances where date/time field is not completed. In my application for example, there are fields which only get updated at certain points for example confirming reciept of payment, or only entering a time/date if a room has been blocked.

Also I removed the date/time fields from my flow and found that even non-date fields are coming up with the same error. It cannot surely be the case that all fields must be completed for the flow to work? I have previously had this flow working but it has stopped all of a sudden, so the data would not be the issue because even in previous versions there were blank fields.

Please advise any alternative work around as blank fields are likely to be necessasary on this sharepoint list.

Thanks

Amit

[amitlathia]

Hi Mabel @v-yamao-msft and community,

I would really appreciate if someone could have a look at this issue for me - I tried to recreate on another sharepoint list and have the same issues. Blank fields may be the issue, but I really need a work around as the sharepoint list is designed to have blank fields. Is there any way of telling flow to either ignore the fact that a field is blank and therefore still provide the rest of the data from that item or any other work around so I can get the CSV table of the various sharepoint columns?

Really appreciate your support in advance.

Amit

[Lexn]

Thank you!! Had similar issue, indeed due to missing value. I whould think if a value is empty, it would just show blank.

[Brian13]

Hello

I have exactly the same problem with 'get items' and 'select'

the error message is the same:

InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "SAIBT Entry ": "@item()['Title']", "Pathway Year": "@item()['Pathway_x0020_Year']", "UniSA Division ": "@item()['UniSA_x0020_Division']", "SAIBT Pathway Program ": "@item()['SAIBT_x0020_Pathway_x0020_Progra']", "Requirement on Exit ": "@item()['Requirement_x0020_on_x0020_Exit']", "GPA Requirement ": "@item()['GPA_x0020_Requirement']", "UniSA Program Code ": "@item()['UniSA_x0020_Program_x0020_Code']", "Stream Code ": "@item()['Stream_x0020_Code']", "UniSA Program Plan Description ": "@item()['UniSA_x0020_Program_x0020_Plan_x']", "Note Code ": "@item()['Note_x0020_Code']", "Credit Granted": "@item()['Credit_x0020_Granted']", "Yrs at UniSA": "@item()['Yrs_x0020_at_x0020_UniSA']", "Cricos Code": "@item()['Cricos_x0020_Code']", "Annual Fee": "@item()['Annual_x0020_Fee']", "Total Course Fee to use on CoEs": "@item()['Total_x0020_Course_x0020_Fee_x00']", "UniSA Intake": "@item()['UniSA_x0020_Intake']", "SAIBT Stream": "@item()['SAIBT_x0020_Stream']", "NOTES": "@item()['NOTES']" }' failed: 'The template language expression 'item()['Stream_x0020_Code']' cannot be evaluated because property 'Stream_x0020_Code' doesn't exist, available properties are '@odata.etag, ItemInternalId, ID, Title, Pathway_x0020_Year, UniSA_x0020_Division,

There are no date fields in my list but there are a lot of empty fields

anyone get a fix to this?

[WillPage]

I have the same issue. I expect it can be fixed with some sorrt of if(isblank([column name]), "placeholder text", [column name]) type expression, but I'm yet to embark on trying to resolve this. I'll update this thread if I figure it out.

[WillPage]

@Brian13 and @amitlathia if you're still looking for an answer to this, here it is:

In the select operation, set the value to an expression like this:

if(empty(item()?['Column']), '-', item()?['Column'])

This example replaces bank fields in the column called Column with a dash character. Edit as you see fit for you purpose.

[Brian13]

Thank you

[Balachandan]

@WillPage Thank you for pointing us in the right direction. But the If(empty.. condition didnot work for me. 

Changing your code to the below worked!

if(equals(item()?['Column'],null), '-', item()?['Column'])

[WillPage]

@Balachandan  good to know it worked for you. If I was solving the same problem now I would use the coalesce function instead of if(equals(something,null), something else, something) I'd do coalesce(something, something else) to achieve the same.

[muralikrishna12]

@WillPage Really appreciate your solution, using I can get some first-level details/values 

But what if I want to get second-level values from the JSON.

Assume I have the below schema to get "Title" and Name from the Permission JSON.

{
  "type": "object",
  "properties": {
    "d": {
      "type": "object",
      "properties": {
        "results": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": {
              "__metadata": {
                "type": "object",
                "properties": {
                  "id": {
                    "type": "string"
                  },
                  "uri": {
                    "type": "string"
                  },
                  "type": {
                    "type": "string"
                  }
                }
              },
              "Member": {
                "type": "object",
                "properties": {
                  "__metadata": {
                    "type": "object",
                    "properties": {
                      "id": {
                        "type": "string"
                      },
                      "uri": {
                        "type": "string"
                      },
                      "type": {
                        "type": "string"
                      }
                    }
                  },
                  "Alerts": {
                    "type": "object",
                    "properties": {
                      "__deferred": {
                        "type": "object",
                        "properties": {
                          "uri": {
                            "type": "string"
                          }
                        }
                      }
                    }
                  },
                  "Groups": {
                    "type": "object",
                    "properties": {
                      "__deferred": {
                        "type": "object",
                        "properties": {
                          "uri": {
                            "type": "string"
                          }
                        }
                      }
                    }
                  },
                  "Id": {
                    "type": "integer"
                  },
                  "IsHiddenInUI": {
                    "type": "boolean"
                  },
                  "LoginName": {
                    "type": "string"
                  },
                  "Title": {
                    "type": "string"
                  },
                  "PrincipalType": {
                    "type": "integer"
                  },
                  "Email": {
                    "type": "string"
                  },
                  "Expiration": {
                    "type": "string"
                  },
                  "IsEmailAuthenticationGuestUser": {
                    "type": "boolean"
                  },
                  "IsShareByEmailGuestUser": {
                    "type": "boolean"
                  },
                  "IsSiteAdmin": {
                    "type": "boolean"
                  },
                  "UserId": {
                    "type": "object",
                    "properties": {
                      "__metadata": {
                        "type": "object",
                        "properties": {
                          "type": {
                            "type": "string"
                          }
                        }
                      },
                      "NameId": {
                        "type": "string"
                      },
                      "NameIdIssuer": {
                        "type": "string"
                      }
                    }
                  },
                  "UserPrincipalName": {
                    "type": "string"
                  }
                }
              },
              "RoleDefinitionBindings": {
                "type": "object",
                "properties": {
                  "results": {
                    "type": "array",
                    "items": {
                      "type": "object",
                      "properties": {
                        "__metadata": {
                          "type": "object",
                          "properties": {
                            "id": {
                              "type": "string"
                            },
                            "uri": {
                              "type": "string"
                            },
                            "type": {
                              "type": "string"
                            }
                          }
                        },
                        "BasePermissions": {
                          "type": "object",
                          "properties": {
                            "__metadata": {
                              "type": "object",
                              "properties": {
                                "type": {
                                  "type": "string"
                                }
                              }
                            },
                            "High": {
                              "type": "string"
                            },
                            "Low": {
                              "type": "string"
                            }
                          }
                        },
                        "Description": {
                          "type": "string"
                        },
                        "Hidden": {
                          "type": "boolean"
                        },
                        "Id": {
                          "type": "integer"
                        },
                        "Name": {
                          "type": "string"
                        },
                        "Order": {
                          "type": "integer"
                        },
                        "RoleTypeKind": {
                          "type": "integer"
                        }
                      },
                      "required": [
                        "__metadata",
                        "BasePermissions",
                        "Description",
                        "Hidden",
                        "Id",
                        "Name",
                        "Order",
                        "RoleTypeKind"
                      ]
                    }
                  }
                }
              },
              "PrincipalId": {
                "type": "integer"
              }
            },
            "required": [
              "__metadata",
              "Member",
              "RoleDefinitionBindings",
              "PrincipalId"
            ]
          }
        }
      }
    }
  }
}

from the above code, i can able to get Title property values 

if(equals(item()?['Member']?['Title'],null), '-', item()?['Member']?['Title'])
but we would like to get Name property which is under RoleDefinitionBindings
if(equals(item()?['RoleDefinitionBindings']?['Name'],null), '-', item()?['RoleDefinitionBindings']?['Name'])
if(equals(item()?['Member']['RoleDefinitionBindings']?['Name'],null), '-', item()?['Member']['RoleDefinitionBindings']?['Name'])
if(equals(item()?['RoleDefinitionBindings']['results']?['Name'],null), '-', item()?['RoleDefinitionBindings']['results']?['Name'])

if(equals(item()?['d']['results']['RoleDefinitionBindings']['results']?['Name'],null), '-', item()?['d']['results']['RoleDefinitionBindings']['results']?['Name'])

but no luck 

Can you assist me how can I get Name property value from RoleDefinitionBindings

[WillPage]

Looking at your schema, the whole thing is an object which contains another object called d and that contains an array called results. The results array contains objects of which RoleDefinitionBindings is a property. That property is in turn an object which contains a property called results which is an array.

So you have several dimensions of the array to work through here. The first layer can you can reference the individual "results" array items by their index number, e.g body('Get_items')?['d']?['results']?[0] for the first one, body('Get_items')?['d']?['results']?[1] for the second one etc, or pass body('Get_items')?['d']?['results'] into an Apply to each loop.

If you pass it into an apply to each you can then reference the inner "results" array like this:

items('Apply_to_each')?['RoleDefinitionBindings']?['results']?[0], items('Apply_to_each')?['RoleDefinitionBindings']?['results']?[1] etc. Again, you can have another apply to each within here using items('Apply_to_each')?['RoleDefinitionBindings']?['results'] as the input and select the Name property inside the inner Apply to each with items('Apply_to_each_2')?['Name']

So you really need to know what it is you're trying to select. Which item of the outer results array contains the item, and within that, which item of the inner results array contains the Name you're looking for? You can achieve this with Filter array actions using the same inputs as the Apply to each loops and a filter condition that you know will only return one result, and select it with the [0] array index selector above (no Apply to each). I have no idea about your data structure or use case so hard to advise on the specifics here. 

[muralikrishna12]

@WillPage 

First of all, thank you for your response and reply.

Yes you are right the schema is in the form of an array, object, and array
Finally from a youtube video, I understand how to call/refer array elements within the array.

Here is the formula as of now I am getting and which is working as expected.
item()?['RoleDefinitionBindings']?['results']?[0]['Name']

you can update/Share other than this if any 

Thank you.