flow data types

[Anonymous]

I am working on a project in which the IT admin is going to use flow and the MySQL data connector to push email information into a MySQL table. From, To, Subject...

Where can I find the data types that are required, pushed by flow, so I can build the table in MySQL to accept the data properly?

[manuelstgomes]

Hey @Anonymous 

Here's the Outlook reference with the field information you need:

https://docs.microsoft.com/en-us/connectors/outlook/#clientreceivemessage

I think you can easily map these into SQL Server datatypes.

Is this what you need?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[Anonymous]

Thank you for your reply!  It is helpful, but the information is still vague.

The links gives general data types, but with no details.  a String, fine, but of what length?!

Also a data type of email (for To, From, …), is what exactly?  I've never seen or heard of this, can find it mentioned in any info relating to SQL Server or MySQL.  I'm thinking this is my current issue, but could be wrong.

When I try to insert the flow info using the MySQL connector I get 

We can't update the value. The operation or its arguments aren't supported by the value.
inner exception: We can't update the value. The operation or its arguments aren't supported by the value.

Not too helpful!  Which field, which value?  Any way of getting more detail error logging?

Can flow do data conversions prior to trying to perform the Insert? Perhaps I could try converting the email to string and see if that works any better.

[Anonymous]

I'm also wondering if it could be a Collation issue, but there again, I can't seem to find any information on the subject.

[manuelstgomes]

HI @Anonymous 

Let's take this into steps

Data Types

From, To, Subject are varchars(X) - this means that they will have an indeterminate number of characters. You need to map, and Power Automation will do the conversion for you.

Everything that you want to store as integer maps to numeric in SQL Server. 

For boolean types use BIT

For the attachments, I recommend using BLOBS

I think, for datatypes, this is what you need:

Error Log

Indeed that's not very helpful. I recommend adding the fields one by one and see what the one that's failing is

Conversion

Technically the connector would do the conversions for you. But we need to understand what is the one that is failing so that we can figure out how to convert it.

Debug

Like mentioned before, make all columns allowing null values and add one field each time, in your Power Automation. Then, when it fails, you know what field is not inserting correctly, and we can understand what's wrong.

Does all of this make sense?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[Anonymous]

Okay, so I finally figured it out, well partially, and would need some help to address the issue.

It has to do with Flow requiring the use of a PK to push data.  So I can manually assign a PK value and then other field values and it works.  The question being now, how can I retrieve the last PK value (so the current greatest value) and +1 to it as the new insert value?

[Anonymous]

Okay, I now have an ugly, but functional flow.  I'd love someone to tell me there is a much better way of handling this!

Basically, I added a GetRows action before my Insert to retrieve the last value in the EmailId Column which I sorted DESC.  Then I use that value+1 in my Insert action  

add(items('Apply_to_each')?['EmailId'],1)

I don't get why I can't just use a NULL like I would normally do in MySQL...?

If you have any better approaches I'd love to hear, so please share.

[manuelstgomes]

hi @Anonymous 

Regarding the PK, let the database handle that. 

You can check here the details, but the PK column can be configured so that it autoincrements. Doing by yourself can lead to issues like, if multiple inserts are going at the same time, the "last ID" collected can be the same, and the insert will fail.

This way, you can build a scalable solution.

Does it make sense?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[Anonymous]

The column is set to auto increment, always has been. But if I don't include it in my flow, flow errs. I can perform an insert in MySQL, or other platforms, without the Pk and it works just fine. It is only with flow that I seem obliged to supply the Pk and I don't get why.

[Anonymous]

I've tried passing 'null' to the PK, then the Flow errs.

I've tried not inputting the EmailId column altogether, but the Flow complains stating that it is required.

I know that I shouldn't need to supply it, so what is the proper way of doing this?