In this part of the introduction to Microsoft Flow, I will provide some tips on making the Flow readable for yourself and others and how to handle decimal, integer and date fields using the Excel connectors.
If you are creating some larger Flows, with a lot of loops, conditions and multiple actions, the Flow might look messy. You might get lost in some spaghetti. You can use descriptions for the actions to make the Flow understandable and easy to follow. Even for smaller Flows, it then acts like an inline documentation. If you paid attention in my previous blogs, I just assembled the Flows usually without paying attention to the descriptions. If you have to create new products, you would need to insert multiple records in several entities. If you don’t change any description, the Flow will look like the next picture.
In this example, you don’t know which create action will take care of what table. It is possible to change the description directly when you added the new action. To do so, you can click the ellipsis (…) menu and choose to rename the action. The description will become editable and you can start typing a meaningful description. In that way, you can make notes for yourself and others. It acts also as inline documentation.
Possibly, you are in the need for more groupings. For that purpose, you can use the Scope action. This action is a container which can hold multiple actions. Have a look at the next example.
how to Handle decimal, integer and date fields
When you select a decimal or integer field, you will notice that no Excel column will be visible for selection in your Flow field mappings. To be able to work with this type of fields, you have to use Functions in Expressions. The function float(value) will convert input to a decimal value and the function int(value) is the one to use for integer values. The value parameter must be filled with an items value like items(‘action name’)?[‘element name’]. The action name is the description of the Excel loop action. The element name is the Excel column name. As example, you can use a decimal conversion float(items(‘For_each_Excel_row’)?[‘Delivery date’]). To get additional help, you can hover the mouse above an existing Excel mapping to get a tooltip with the item value.
When you try to map a date field, it is possible to select the Excel column from the Dynamic content, but you will get a runtime error like this:
An error has occurred. Exception has been thrown by the target of an invocation. Cannot convert the literal ‘43631’ to the expected type ‘Edm.DateTimeOffset’.
The issue is that Excel is storing the date as an decimal field calculated from a certain start date. As workaround, you can try to save the Excel cell as text, but that would not be a consistent solution as users will probably update the Excel cells and it might be a date field again. You can actually use a function to calculate the correct date with the given start date and add the number of days which is equal to the Excel value. You can use the next function in the Expression:
That’s all for now. Till next time!