Sometimes you try to solve a problem and you can’t find a solution, or not one you’re satisfied with. Once you do find a good solution, you might want to share that with others. This is exactly why this section Tips & Tricks could be helpful to you and others.
This page is a first draft, so it will grow and change. You can add tips yourself.
Cloud flow / Power Automate
Get only one record based on specific criteria (OData query or FetchXML) without a loop
Use this expression to get the first record directly:
first(outputs('List_Quotes')?['body/value'])?['quoteid']if the criteria don’t match any record the result will be null
So you can check if there are records with this criteria by getting the length of the List to use it in condition:
length(outputs('List_records')?['body/value']) > 0Get today in a specific format, use this expression
formatDateTime(utcNow(),'dd/MM/yyyy')Get use mail and his manager’s mail in one step (Expand query)
Use (Get row) action and make the fields like that:
Select Columns: parentsystemuserid,internalemailaddress
Expand Query: parentsystemuserid($select=internalemailaddress)
To use this parent mail in another action:
outputs('Get_Manager_Email')?['body/parentsystemuserid/systemuserid']Use the `and()` expression in Power Automate
and(equals(item()?[‘Status’], ‘blocked’), equals(item()?[‘Assigned’], ‘John Wonder’))For more expressions check this article: https://docs.microsoft.com/en-us/power-automate/use-expressions-in-conditions#use-the-and-expression
Generate date from Unix timestamp
If you want to get only the date Time
addseconds('1970-1-1', triggerBody()['number'],'yyyy-MM-dd hh:mm:ss')If you want to get only the date:
addseconds('1970-1-1', triggerBody()['number'],'yyyy-MM-dd')Convert data to Unix timestamp and vice versa
Convert date.Now to UnixTimestamp in seconds:
div(sub(ticks(utcNow()),ticks('1970-01-01')), 10000000)Convert Collection date to UnixTimestamp in milliseconds:
div(sub(ticks(outputs('Get_Payment')?['body/trx_collectiondate']),ticks('1970-01-01')), 10000)you can use this site to test the results: https://www.epochconverter.com
Check if a property exists in an object
empty(outputs('Apply_to_each')?['_new_location_value@OData.Community.Display.V1.FormattedValue'])Get last day of the month
subtractFromTime(startOfMonth(addToTime(utcNow(),1,'month')),1,'day')
Power Apps
Differences between Filter, Search and LookUp functions
Filter
Find a set of records in a table that satisfies a formula. Use Filter to find a set of records that match one or more criteria.
Filter(IceCream, Quantity + OnOrder > 225)
Filter(IceCream, "chocolate" in Lower(Flavor ))
Filter(IceCream, Quantity < 10 && OnOrder < 20))Search
Find a set of records that match string text in specific columns.
Search(IceCream, "choc", "Flavor")
Search(Customers, "Mo", "Name", "Company") //search on column Name or company
Search(IceCream, "", "Flavor") //Because the search term is empty, all records are returned.)LookUp
Find the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria.
LookUp(IceCream, Flavor = "Chocolate", Quantity)
LookUp(IceCream, Quantity > 150, Quantity + OnOrder)
LookUp(IceCream, Flavor = "Vanilla") // Searches for a record with Flavor equal to "Vanilla", of which there is one. Since no reduction formula was supplied, the entire record is returned.)Note: If no records are found, Filter and Search return an empty table, and LookUp returns blank.
For More info:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup