Question 103016 in Azure Question posted in
The official documentation to get you started can be found here.

Azure Logic Apps – SQL tables to a single Email – How To

I have a stored procedure that I’m pulling data sets into Azure Logic Apps Designer. no matter what I try – nothing wants to plug into the send email function. some things that seem like a no brainer => DataOperations => Compose (making a full html table), or Create HTML Table, or Create CSV Table – All create a Foreach loop around the object, then will not in bed into the email – which is HTML code capable.

I can just in bed the body – which is a monstrosity of data, but that’s not going to work.

How can I format the SQL data into tables, and send them in a single email. Being able to add a custom entry for a Click me would also be a must.

most of the above was not saving due to error, or would not run. the Data Operations of objects is what I had been working with so far, but I need a working table in an email from the SQL data.

here are the results of the 2 sets of objects that it can spit out, body just being a higher version of result sets it seems. also note this is a single result:

BODY:

{"ResultSets":{"Table1":[{"Title":"Test #3556 - legal","ID":176,"ExecutionDate":"2023-04-27T00:00:00","Division":"Banking","Program":"Banking","ContractType":"Invitation for Bid ("IFB")","Name":"BedRock Concrete"}]},"ReturnCode":0,"OutputParameters":{}}





resultsets:
{"Table1":[{"Title":"Test #3556 - legal","ID":176,"ExecutionDate":"2023-04-27T00:00:00","Division":"Banking","Program":"Banking","ContractType":"Invitation for Bid ("IFB")","Name":"BedRock Concrete"}]}

so i missed a type of return object –

BODY & Resultsets: gave too much information since it looked like there was a possibility of additional tables to come back. i only called for a single table, so at the bottom of my list I found "Table1" – after using it – it was giving me the results I wanted – a single table representation. This will also allow me to break up the data and give specific header under the CREATE HTML TABLE option.

my end logic app will be Recurance => Execute Stored Procedure => Create HTML Table Feeding in "Table1" objects data => Send Email feeding in CreateHTMLTable.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    so i missed a type of return object -

    BODY & Resultsets: gave too much information since it looked like there was a possibility of additional tables to come back. i only called for a single table, so at the bottom of my list I found "Table1" - after using it - it was giving me the results I wanted - a single table representation. This will also allow me to break up the data and give specific header under the CREATE HTML TABLE option.

    my end logic app will be Recurance => Execute Stored Procedure => Create HTML Table Feeding in "Table1" objects data => Send Email feeding in CreateHTMLTable.


  2. 0

    Azure Logic Apps – SQL tables to a single Email – How To

    I have reproduced in my environment got expected results:

    I have used For Each and faced similar issues, so i alternatively used another method:

    enter image description here

    Output:
    enter image description here

    enter image description here
    Logic app code:

    {
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Create_HTML_table": {
                "inputs": {
                    "format": "HTML",
                    "from": "@body('Execute_a_SQL_query_(V2)')?['resultsets']?['Table1']"
                },
                "runAfter": {
                    "Execute_a_SQL_query_(V2)": [
                        "Succeeded"
                    ]
                },
                "type": "Table"
            },
            "Create_HTML_table_2": {
                "inputs": {
                    "format": "HTML",
                    "from": "@body('Execute_a_SQL_query_(V2)')?['resultsets']?['Table2']"
                },
                "runAfter": {
                    "Execute_a_SQL_query_(V2)": [
                        "Succeeded"
                    ]
                },
                "type": "Table"
            },
            "Execute_a_SQL_query_(V2)": {
                "inputs": {
                    "body": {
                        "query": "SELECT TOP (1000) * FROM [dbo].[emo1]nSELECT TOP (1000) * FROM [dbo].[emo]"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['sql']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/query/sql"
                },
                "runAfter": {
                    "Initialize_variable": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            },
            "Initialize_variable": {
                "inputs": {
                    "variables": [
                        {
                            "name": "ammo",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            },
            "Send_an_email_(V2)": {
                "inputs": {
                    "body": {
                        "Body": "<p>Tables:<br>n@{body('Create_HTML_table')}<br>n<br>n@{body('Create_HTML_table_2')}</p>",
                        "Importance": "Normal",
                        "Subject": "TABLES",
                        "To": "[email protected]"
                    },
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['office365']['connectionId']"
                        }
                    },
                    "method": "post",
                    "path": "/v2/Mail"
                },
                "runAfter": {
                    "Create_HTML_table": [
                        "Succeeded"
                    ],
                    "Create_HTML_table_2": [
                        "Succeeded"
                    ]
                },
                "type": "ApiConnection"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {}
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "office365": {
                    "connectionId": "/subscriptions/b823f/resourceGroups/rbojja/providers/Microsoft.Web/connections/office365",
                    "connectionName": "office365",
                    "id": "/subscriptions/b83c1ed/providers/Microsoft.Web/locations/eastus/managedApis/office365"
                },
                "sql": {
                    "connectionId": "/subscriptions/b83c1ed3f/resourceGroups/rbojja/providers/Microsoft.Web/connections/sql-3",
                    "connectionName": "sql-3",
                    "id": "/subscriptions/b83c1ed3-c5b/providers/Microsoft.Web/locations/eastus/managedApis/sql"
                }
            }
        }
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search