Invalid Mashup Expression using supplied values

[GaganMehrok]

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. 

[faustocapellanj]

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

[GaganMehrok]

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. 

[faustocapellanj · Accepted solution]

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.

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

[GaganMehrok]

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. 

[faustocapellanj]

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

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.

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

I hope this helps.

Regards,

Fausto Capellan, Jr

[GaganMehrok]

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. 

 

OUTPUT

[GaganMehrok]

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. 

[faustocapellanj]

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.

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:

Please let me know if you have any questions.

Regards,

Fausto Capellan, Jr

[GaganMehrok]

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. 

[faustocapellanj]

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

[GaganMehrok]

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?

[faustocapellanj]

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