cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amitlathia
Frequent Visitor

InvalidTemplate error using select action in flow

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... 

 

error.PNG

 

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

14 REPLIES 14
v-yamao-msft
Community Support
Community Support

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

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

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

 

Anonymous
Not applicable

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?
 

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.

@Anonymous 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.
Anonymous
Not applicable

Thank you

 

Anonymous
Not applicable

@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'])

@Anonymous  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.

@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

 

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. 

@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.

 

KoketsoM
New Member

Hi , 
For me the column had a different name! Thanks for this!

Helpful resources

Announcements

Power Platform Connections - Episode 7 | March 30, 2023

Episode Seven of Power Platform Connections sees David Warner and Hugo Bernier talk to Microsoft MVP Dian Taylor, alongside the latest news, product reviews, and community blogs.     Use the hashtag #PowerPlatformConnects on social media for a chance to have your work featured on the show!      Show schedule in this episode:    0:00 Cold Open 00:30 Show Intro 01:02 Dian Taylor Interview 18:03 Blogs & Articles 26:55 Outro & Bloopers    Check out the blogs and articles featured in this week’s episode:    https://francomusso.com/create-a-drag-and-drop-experience-to-upload-case-attachments @crmbizcoach https://www.youtube.com/watch?v=G3522H834Ro​/  @pranavkhuranauk https://github.com/pnp/powerapps-designtoolkit/tree/main/materialdesign%20components @MMe2K​ https://2die4it.com/2023/03/27/populate-a-dynamic-microsoft-word-template-in-power-automate-flow/ @StefanS365 https://d365goddess.com/viva-sales-administrator-settings/ @D365Goddess https://marketplace.visualstudio.com/items?itemName=megel.mme2k-powerapps-helper#Visualize_Dataverse_Environments @MMe2K    Action requested:  Feel free to provide feedback on how we can make our community more inclusive and diverse.    This episode premiered live on our YouTube at 12pm PST on Thursday 30th March 2023.    Video series available at Power Platform Community YouTube channel.    Upcoming events:  Business Applications Launch – April 4th – Free and Virtual! M365 Conference - May 1-5th - Las Vegas Power Apps Developers Summit – May 19-20th - London European Power Platform conference – Jun. 20-22nd - Dublin Microsoft Power Platform Conference – Oct. 3-5th - Las Vegas    Join our Communities:  Power Apps Community Power Automate Community Power Virtual Agents Community Power Pages Community    If you’d like to hear from a specific community member in an upcoming recording and/or have specific questions for the Power Platform Connections team, please let us know. We will do our best to address all your requests or questions.       

Announcing | Super Users - 2023 Season 1

Super Users – 2023 Season 1    We are excited to kick off the Power Users Super User Program for 2023 - Season 1.  The Power Platform Super Users have done an amazing job in keeping the Power Platform communities helpful, accurate and responsive. We would like to send these amazing folks a big THANK YOU for their efforts.      Super User Season 1 | Contributions July 1, 2022 – December 31, 2022  Super User Season 2 | Contributions January 1, 2023 – June 30, 2023    Curious what a Super User is? Super Users are especially active community members who are eager to help others with their community questions. There are 2 Super User seasons in a year, and we monitor the community for new potential Super Users at the end of each season. Super Users are recognized in the community with both a rank name and icon next to their username, and a seasonal badge on their profile.  Power Apps  Power Automate  Power Virtual Agents  Power Pages  Pstork1*  Pstork1*  Pstork1*  OliverRodrigues  BCBuizer  Expiscornovus*  Expiscornovus*  ragavanrajan  AhmedSalih  grantjenkins  renatoromao    Mira_Ghaly*  Mira_Ghaly*      Sundeep_Malik*  Sundeep_Malik*      SudeepGhatakNZ*  SudeepGhatakNZ*      StretchFredrik*  StretchFredrik*      365-Assist*  365-Assist*      cha_cha  ekarim2020      timl  Hardesh15      iAm_ManCat  annajhaveri      SebS  Rhiassuring      LaurensM  abm      TheRobRush  Ankesh_49      WiZey  lbendlin      Nogueira1306  Kaif_Siddique      victorcp  RobElliott      dpoggemann  srduval      SBax  CFernandes      Roverandom  schwibach      Akser  CraigStewart      PowerRanger  MichaelAnnis      subsguts  David_MA      EricRegnier  edgonzales      zmansuri  GeorgiosG      ChrisPiasecki  ryule      AmDev  fchopo      phipps0218  tom_riha      theapurva  takolota     Akash17  momlo     BCLS776  Shuvam-rpa     rampprakash  ScottShearer     Rusk  ChristianAbata     cchannon  Koen5     a33ik  Heartholme     AaronKnox  okeks      Matren   David_MA     Alex_10        Jeff_Thorpe        poweractivate        Ramole        DianaBirkelbach        DavidZoon        AJ_Z        PriyankaGeethik        BrianS        StalinPonnusamy        HamidBee        CNT        Anonymous_Hippo        Anchov        KeithAtherton        alaabitar        Tolu_Victor        KRider        sperry1625        IPC_ahaas      zuurg    rubin_boer   cwebb365   Dorrinda   G1124   Gabibalaban   Manan-Malhotra   jcfDaniel   WarrenBelz   Waegemma   drrickryp   GuidoPreite    If an * is at the end of a user's name this means they are a Multi Super User, in more than one community. Please note this is not the final list, as we are pending a few acceptances.  Once they are received the list will be updated. 

Register now for the Business Applications Launch Event | Tuesday, April 4, 2023

Join us for an in-depth look into the latest updates across Microsoft Dynamics 365 and Microsoft Power Platform that are helping businesses overcome their biggest challenges today.   Find out about new features, capabilities, and best practices for connecting data to deliver exceptional customer experiences, collaborating, and creating using AI-powered capabilities, driving productivity with automation—and building towards future growth with today’s leading technology.   Microsoft leaders and experts will guide you through the full 2023 release wave 1 and how these advancements will help you: Expand visibility, reduce time, and enhance creativity in your departments and teams with unified, AI-powered capabilities.Empower your employees to focus on revenue-generating tasks while automating repetitive tasks.Connect people, data, and processes across your organization with modern collaboration tools.Innovate without limits using the latest in low-code development, including new GPT-powered capabilities.    Click Here to Register Today!    

Check out the new Power Platform Communities Front Door Experience!

We are excited to share the ‘Power Platform Communities Front Door’ experience with you!   Front Door brings together content from all the Power Platform communities into a single place for our community members, customers and low-code, no-code enthusiasts to learn, share and engage with peers, advocates, community program managers and our product team members. There are a host of features and new capabilities now available on Power Platform Communities Front Door to make content more discoverable for all power product community users which includes ForumsUser GroupsEventsCommunity highlightsCommunity by numbersLinks to all communities Users can see top discussions from across all the Power Platform communities and easily navigate to the latest or trending posts for further interaction. Additionally, they can filter to individual products as well.   Users can filter and browse the user group events from all power platform products with feature parity to existing community user group experience and added filtering capabilities.     Users can now explore user groups on the Power Platform Front Door landing page with capability to view all products in Power Platform.      Explore Power Platform Communities Front Door today. Visit Power Platform Community Front door to easily navigate to the different product communities, view a roll up of user groups, events and forums.

Microsoft Power Platform Conference | Registration Open | Oct. 3-5 2023

We are so excited to see you for the Microsoft Power Platform Conference in Las Vegas October 3-5 2023! But first, let's take a look back at some fun moments and the best community in tech from MPPC 2022 in Orlando, Florida.   Featuring guest speakers such as Charles Lamanna, Heather Cook, Julie Strauss, Nirav Shah, Ryan Cunningham, Sangya Singh, Stephen Siciliano, Hugo Bernier and many more.   Register today: https://www.powerplatformconf.com/   

Users online (1,772)