cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GaganMehrok
Level: Powered On

Invalid Mashup Expression using supplied values

I am trying to build up my first flow with SQL. To test the connectivity and flow i tried to create a basic flow where if condition is met email shoud trigger. Though i am getting below error. 

 

{
  "status"400,
  "message""Invalid Mashup Expression using supplied values.\r\n     inner exception: The key didn't match any rows in the table.\r\nclientRequestId: e6de1883-594d-4c77-96b4-0caf956d48dd",
  "source""sqlconnectionprovider-westcentralus.cy4-ase-001.p.azurewebsites.net"
}

 

While looking into setup i can see my fields are getting populated from SQL table under Dynamic content. I am selecting one of the fields from available list and using equal to condition with If yes to shoot an email to my contact. 

 

Any help would be appriciated. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Invalid Mashup Expression using supplied values

Hi @GaganMehrok

 

Since you have the column and the value you are filtering on hidden, I can't tell exactly why the array filtering is failing. I created a SQL DB with a table named Departments. This table has the columns UserID, Name and Department, and my "Filter array" action is filtering on Department equals IT. This is working fine.

The screenshot below shows my Flow and the results from the "Filter array" action. Right below the screenshot, you can see the INPUTS and OUTPUTS from the "Filter array" action after the Flow runs.

SQLFiltering-1.JPGSQLFiltering-2.JPG

 

INPUTS
[
  {
    "@odata.etag": "",
    "ItemInternalId": "259a5b7a-c9c8-4aed-99dd-c20e54d004b6",
    "UserID": 1,
    "Name": "Jane Doe",
    "Department": "HR"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "c9aff125-82e8-4ba9-adc2-a00c8510e4b4",
    "UserID": 2,
    "Name": "John Doe",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "2440c04b-4854-41b6-bd2f-256ac7c94a28",
    "UserID": 3,
    "Name": "Mary Jane",
    "Department": "Executive"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "90e6e538-991e-4df8-99ec-1f0f9354d13f",
    "UserID": 4,
    "Name": "A Smith",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "96226f03-2b5b-4b83-aaf0-fad0e7f4c8e7",
    "UserID": 5,
    "Name": "B Carmine",
    "Department": "HR"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "e4cff0f5-e0da-4d61-b3da-60baa898bbf4",
    "UserID": 6,
    "Name": "C David",
    "Department": "Executive"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "40b8a236-8023-4c36-9efe-3f6ff9f41742",
    "UserID": 7,
    "Name": "D Frank",
    "Department": "IT"
  }
]
OUTPUTS
[
  {
    "@odata.etag": "",
    "ItemInternalId": "c9aff125-82e8-4ba9-adc2-a00c8510e4b4",
    "UserID": 2,
    "Name": "John Doe",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "90e6e538-991e-4df8-99ec-1f0f9354d13f",
    "UserID": 4,
    "Name": "A Smith",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "40b8a236-8023-4c36-9efe-3f6ff9f41742",
    "UserID": 7,
    "Name": "D Frank",
    "Department": "IT"
  }
]

Please check your filter and let us know.

 

Regards,

Fausto Capellan, Jr


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


12 REPLIES 12
Super User
Super User

Re: Invalid Mashup Expression using supplied values

Hi @GaganMehrok

 

Can you please post a screenshot of your Flow configuration? This will give us a better idea of your scenario and we can try to re-create it.

 

Regards,

Fausto Capellan, Jr


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


GaganMehrok
Level: Powered On

Re: Invalid Mashup Expression using supplied values

I have solved this one now i am trying to create a simple flow to retrieve rows with a filter array. I am not getting anything though flow is working fine. 

 

Setup as given below. 

Highlighted
Super User
Super User

Re: Invalid Mashup Expression using supplied values

Hi @GaganMehrok

 

Since you have the column and the value you are filtering on hidden, I can't tell exactly why the array filtering is failing. I created a SQL DB with a table named Departments. This table has the columns UserID, Name and Department, and my "Filter array" action is filtering on Department equals IT. This is working fine.

The screenshot below shows my Flow and the results from the "Filter array" action. Right below the screenshot, you can see the INPUTS and OUTPUTS from the "Filter array" action after the Flow runs.

SQLFiltering-1.JPGSQLFiltering-2.JPG

 

INPUTS
[
  {
    "@odata.etag": "",
    "ItemInternalId": "259a5b7a-c9c8-4aed-99dd-c20e54d004b6",
    "UserID": 1,
    "Name": "Jane Doe",
    "Department": "HR"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "c9aff125-82e8-4ba9-adc2-a00c8510e4b4",
    "UserID": 2,
    "Name": "John Doe",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "2440c04b-4854-41b6-bd2f-256ac7c94a28",
    "UserID": 3,
    "Name": "Mary Jane",
    "Department": "Executive"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "90e6e538-991e-4df8-99ec-1f0f9354d13f",
    "UserID": 4,
    "Name": "A Smith",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "96226f03-2b5b-4b83-aaf0-fad0e7f4c8e7",
    "UserID": 5,
    "Name": "B Carmine",
    "Department": "HR"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "e4cff0f5-e0da-4d61-b3da-60baa898bbf4",
    "UserID": 6,
    "Name": "C David",
    "Department": "Executive"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "40b8a236-8023-4c36-9efe-3f6ff9f41742",
    "UserID": 7,
    "Name": "D Frank",
    "Department": "IT"
  }
]
OUTPUTS
[
  {
    "@odata.etag": "",
    "ItemInternalId": "c9aff125-82e8-4ba9-adc2-a00c8510e4b4",
    "UserID": 2,
    "Name": "John Doe",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "90e6e538-991e-4df8-99ec-1f0f9354d13f",
    "UserID": 4,
    "Name": "A Smith",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "40b8a236-8023-4c36-9efe-3f6ff9f41742",
    "UserID": 7,
    "Name": "D Frank",
    "Department": "IT"
  }
]

Please check your filter and let us know.

 

Regards,

Fausto Capellan, Jr


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


GaganMehrok
Level: Powered On

Re: Invalid Mashup Expression using supplied values

Hi Thanks, i got it working through one of the existing SQL template though it is similar to mehtod you have shared. So i assume it would work as well. 

 

Now second question. 

 

My output results were also similar to the one you have shared. 

 

How can i put it in a better display, may be table format and also don't want to show the values which are generated by FLOW system such as 

    "@odata.etag": "",
    "ItemInternalId": "c9aff125-82e8-4ba9-adc2-a00c8510e4b4",

 

OUTPUTS
[
  {
    "@odata.etag": "",
    "ItemInternalId": "c9aff125-82e8-4ba9-adc2-a00c8510e4b4",
    "UserID": 2,
    "Name": "John Doe",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "90e6e538-991e-4df8-99ec-1f0f9354d13f",
    "UserID": 4,
    "Name": "A Smith",
    "Department": "IT"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "40b8a236-8023-4c36-9efe-3f6ff9f41742",
    "UserID": 7,
    "Name": "D Frank",
    "Department": "IT"
  }
]

 

Thanks in advance. 

Super User
Super User

Re: Invalid Mashup Expression using supplied values

Hi @GaganMehrok

 

Apologies for the delay. I couldn't work on the Flow because my DB is located in Azure and I couldn't connect to it at work. Anyways, in order to get the data in a nice format such as HTML, you have to add 2 more actions:

  1. Data Operations - Select
  2. Data Operation - Create HTML table

The Data Operations - Select will be used to select the column you need from the Data Operations - Filter array action. When you add the Data Operations - Select action, add the Body output from the Filter array. In the Map section, enter UserID, Name, and Department in the key column. In the value column, select the Expression tab from the Dynamic content and build the expression for each of the previous key columns: item()?['UserID'], item()?['Name'], item()?['Department']. 

SQLFiltering-3.JPG

 

Next, add the Data Operations - Create HTML table. In the From field, select the Output from the Data Operations - Select action. As for the remaining fields, set Include headers to Yes and Columns to Automatic.
SQLFiltering-4.JPG

 

The screenshots below have the output from both the Data Operations - Select and the Data Operations - Create HTML table actions.

SQLFiltering-5.JPGSQLFiltering-6.JPG

 

I hope this helps.

 

Regards,

Fausto Capellan, Jr

 

 

 


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


GaganMehrok
Level: Powered On

Re: Invalid Mashup Expression using supplied values

Hi Fausto ( I assume that's your name)

 

First of all i would like to thank you for helping me out. I did as you suggested. Please my setup screenshot and output that i got on email. Please suggest what is problem with my setup at current stage. 

 download.png

 

 

OUTPUT

 

 

download2.PNG

GaganMehrok
Level: Powered On

Re: Invalid Mashup Expression using supplied values

Please ignore my last post, i tried agian and carefuly read your suggestions. Finally i got tabluar out put in flow, but it it limited within the flow. As i want the results to be emailed to selected party therefore as soon as i am adding send email function it is showing html coding in email body. Also due to Apply to each i am getting one email for each row results. As i have set top 20 limti in SQL Get Rows therefore it stopped after 20. Please suggest how i can port same table to email body and get one email for the results of that run. I want complete table to be created within the body of email rather sending seperate email for each record matched. 

 

email.png

Super User
Super User

Re: Invalid Mashup Expression using supplied values

Hi @GaganMehrok

 

If you follow my steps, you shouldn't have an "Apply to each" in your flow. I made sure that wouldn't come up for what you're trying to achieve. Since I forgot to add a "Send an email" action to my flow, I just added it and here's what it looks like.
SQLFiltering-7.JPG

 

If you look at the screenshot, it is important to set the "Is HTML" to Yes so the email body is in HTML format. Once I receive the email from flow, this is what my email looks like:

SQLFiltering-8.JPG

Please let me know if you have any questions.

 

Regards,

Fausto Capellan, Jr


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


GaganMehrok
Level: Powered On

Re: Invalid Mashup Expression using supplied values

Hi Thanks for sharing solution. Though after putting it here i searched on internet and now i am able to get results in clean table format on email. 

 

What i am trying to achieve is automate notifcation from a table of a sql server. So my next step is to create measure to calculate fields from the data and than if possible try to create a pivot based table before putting it in email send it over. 

 

I searched to check available Math functions but not able to find much, also tried looking for some SQL function like DistinctCount to apply on some columns. Is it something available in Flow or i am asking too much of Flow right now. 

Super User
Super User

Re: Invalid Mashup Expression using supplied values

Unfortunately, I haven't gone as deep as doing math functions in Flow. Now, what I can suggest is you create another post in the forum specific to the calculations you are looking for. That way, the Flow staff can chime in on that and hopefully provide you with a solution. Additionally, if you are familiar with SQL queries, you can execute one within flow against your database table. Maybe with that you can retrieve the data the way you want it.

 

Regards,

Fausto Capellan, Jr


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


GaganMehrok
Level: Powered On

Re: Invalid Mashup Expression using supplied values

Thanks for suggestion, I will try adding a new post. Get ROW doesn't allow to write complete query. Is there any other action in which custom query is allowed?

Super User
Super User

Re: Invalid Mashup Expression using supplied values

There's an action called "Execute a SQL Query". The screenshot below shows all the actions available under SQL Server:

SQLFiltering-9.JPG


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 134 members 4,464 guests
Please welcome our newest community members: