Though it is ever shifting sands, there seems to be a sort of semi-coalescing within the Azure/Power/Fabric space. It is useful for me to pretend so, at least. Here are a handful of Tips and Techniques that I have found indispensible. Sometimes I'm kind of surprised they aren't built-in. Mostly this was accumulated from a PowerAutomate-into-* point of view, but probably will expand over time.
-
-
Save jrwarwick/77f0df931ad036f9dd46a52b131d24a1 to your computer and use it in GitHub Desktop.
Dynamics 365 cloud-edition has some (or maybe all?) tables exposed in DataVerse. In some cases, when using the DataVerse connector, writing a record to a table, there are some columns whose types are kind of foreing-key/picker-relation columns. When inserting invalid values, and in some cases even valid (but naked values), you will get (not completely clear) errors on insert (e.g.?). They require you to insert not just the surrogate GUID, but an expression that represents a lookup on that GUID. The lookup "function" name is the pluralized word form of the entity type name.
hints:
- for research of lookup types (lists available endpoints): https://yourorg.api.crm.dynamics.com/api/data/v9.1/
concat('msdyn_timeentrysettings(',outputs('List_rows_-_Time_Sources')?['body/value'][0]?['msdyn_timeentrysettingid'],')')
If you have access to PowerApps/solutions interface, the Dataverse>Tables resources grids include a right-click>advanced kind of option for finding the "Logcial Name" of the table. That is the one that will be resolvable inside of direct REST calls and advanced PowerAutomate processing.
Choices are different from Lookups. Lookups are fk-relational, "Choices" are similar but custom per-column things, and lack the lookup expressions. You need to just supply pre-validated raw key values. https://YOUR-DYNAMICS-URL.crmX.dynamics.com/api/data/v9.2/EntityDefinitions(LogicalName='TABLE-NAME')/Attributes(LogicalName='COLUMN-NAME')/Microsoft.Dynamics.CRM.PicklistAttributeMetadata?$select=LogicalName,SchemaName,AttributeTypeName&$expand=OptionSet($select=Options)
ref: https://www.ameyholden.com/articles/dataverse-choice-power-automate-dynamic-no-switch
msdyn_duration usually appears in fancy pickers (e.g., in Time Entry where it is a required field despite the begin and end timestamps already being supplied), but under the hood its "just an integer"; but what UoM? "msdyn_duration (Integer): The total duration of the requirement in minutes"
hints: A handy expression to take a stypical shift endTime - beginTime datediff string (hh:mm) and convert to a single integer total quantity of minutes (note the funny prefix of a space char in the format specifier to formatDateTime; without it, formatDateTime thinks it is an invalid full-format code):
add(
mul(float(formatDateTime(dateDifference(items('Apply_to_each')?['start'], items('Apply_to_each')?['end']),' h')),60),
float(formatDateTime(dateDifference(items('Apply_to_each')?['start'], items('Apply_to_each')?['end']),' m'))
)
div(sub(ticks(__TARGET_DATE__), ticks('1970-01-01Z00:00:00')), 10000000)
# sec since epoch (classical)div(sub(ticks(__TARGET_DATE__), ticks('1970-01-01Z00:00:00')), 10000)
# millisec since epoch ("modern")
e.g., for the date 60 days ago:
div(sub(ticks(addDays(utcNow(), -60)), ticks('1970-01-01Z00:00:00')), 10000000)
#sec since epoch (classical)div(sub(ticks(addDays(utcNow(), -60)), ticks('1970-01-01Z00:00:00')), 10000)
#millisec since epoch ("modern")
addSeconds('1970-01-01Z00:00:00', __TARGET_MILLISEC_SINCE_EPOCH__, 'yyyy-MM-dd')
addSeconds('1970-01-01Z00:00:00', div(__TARGET_MILLISEC_SINCE_EPOCH__,1000), 'yyyy-MM-dd')
formatDateTime(utcNow(),'yyyyMMddHHmmss')
addDays(formatDateTime(utcNow(),'MM-dd-yyyy'),add(dayOfWeek(formatDateTime(utcNow(),'MM-dd-yyyy')),-9))
workflow()['tags']['flowDisplayName']
https://sharepains.com/2019/05/06/one-liner-expressions-power-automate/