How to validate the Date control in Power Apps to only allow selection of Tuesday and Thursday?

Summary

I needed to restrict the date selection in the Power Apps form to only allow Tuesday and Thursday.

Step By Step Solution

I assume you know how to create Power Apps form app.

Step # 1 I the BorderColor property of the Date control (in my case DateValue2) put the following formula. The Weekday function will provide the number for Tue and Thu.

// if parent error is blank or
//    selected date is Tue or Thu the border color is normal
If(
    Coalesce(
        (Weekday(
            DateValue2.SelectedDate,
            StartOfWeek.Sunday
        ) = 3) ||
        (Weekday(
            DateValue2.SelectedDate,
            StartOfWeek.Sunday
        ) = 5),
        IsBlank(Parent.Error)

    ),
    Parent.BorderColor,
    Color.Red
)
BorderColor

Step # 2 In the Text property of the ErrorMessage Control of the DataCard add the following formula.

Coalesce(
    Parent.Error,
    If(
        ((Weekday(
            DateValue2.SelectedDate,
            StartOfWeek.Sunday
        ) <> 5) && (Weekday(
            DateValue2.SelectedDate,
            StartOfWeek.Sunday
        ) <> 3)),
        "Date must be for Tuesday or Thursday only"
    )
)
Text property

Conclusion

Currently, the out of the box date control does not provide any validation, this is an alternative approach to validate and restrict the days. I hope it is useful to you for your scenario.

Posted in Power Apps | Leave a comment

How to get List-Field’s information such as display name and static name map?

Summary

During the development of PowerShell Script or Power Automate Flow with REST calls you will need a Static Names of the List. The Display name of the list is not same as static name.

Please read here for more information about the Display Name Vs Static Name.

Step By Step Solution

To simplify let’s create a simple Manually Triggered flow. Using the REST API make a call to the get Fields information in JSON. Map using the Select action to get only required field properties. After that apply the Create HTML action to create nice tabular data to see the map.

Flow to extract the Static Name information
https://github.com/pankajsurti/PowerAutomateSamples/blob/main/Export-ListFields-Information_20210704172208.zip

You can download the sample Power Automate from the above URL or by clicking here.

Conclusion

I use this flow to get the information of the list to get the static names. I hope it is useful for you as well.

Posted in Power Automate | Leave a comment

How to get a list of all SharePoint list names that were Customized with PowerApps?

Summary

The SharePoint List can be customized. It is as shown here. But question is how to find out all the list names which are customized for PowerApps?

Answer

It is very simple, I saw multiple places and I want to documented here.

Using the PnP.PowerShell connect to the site using Connect-PnPOnline.

Execute the following command to get the PowerApps customized list names.

Get-PnPList -Includes RootFolder.Properties | ? { $_.Hidden -eq $false } | ? {$_.RootFolder.Properties.FieldValues.Keys.Contains('PowerAppFormProperties')}

Conclusion

Posted in Uncategorized | Leave a comment

How to limit number of checkbox selections in a gallery?

Summary

This is text version of the video by Daniel Christian.

#1 Add a Gallery control Gallery1

#2 Add a Check Box control in the Gallary

#3 Remove the border of the checkbox

#4 Add a Label control name it “CountLabel”. Add Text property formula.

// The formula will get all items in the galley and filter by checked checkboxes.
CountRows(Filter(Gallery1.AllItems,Checkbox1.value))

#5 Add DisplayMode formula as

If(Self.Value || (Value(CountLabel.Text) = 0), DisplayMode.Edit, Disabled)
Posted in Power Apps | Leave a comment

How to take an action in Power Automate only when certain field’s value have changed?

Summary

Here is the scenario, there is a list named “OrderList” with the following five fields. The end users change any of these fields except Order Status. The Order Status field is changed externally.

Field NameField Type
OrderIDNumber
Order Name Single line of text
Order StatusChoice (New, Approved, Shipped, Completed)
QuantityNumber
Order DescriptionMultiple lines of text
OrderList fields

The need here is when the Order Status change from one state to other the Power Automate Flow has some business logic. The question is how to check only the Order Status has changed from old value to new value. For example, Approved to Shipped status has multiple business actions and emails to be sent.

The solution to such need is to enable the “versions” in SharePoint List.

Step By Step Solution

Note: Please make sure you have enabled versions for your SharePoint List.

Step # 1 Create “When an item is created or modified” trigger flow

In this step select Site Address and the List Name.

Flow Trigger

Step # 2 Make REST call using HTTP to get top 2 versions of the item

Make a REST call using Send HTTP Method to get last two versions
# copy the following in Uri, make sure you change list name
_api/web/lists/GetByTitle('OrderList')/Items(@{triggerOutputs()?['body/ID']})/versions?$top=2

# copy the following values in Headers

{
  "accept": "application/json;odata=nometadata"
}

The above call will provide the results in the following format. The value JSON array has the data we need to extract the old and new values for the order status. The first value is the New value and second item in the array is an old value of the Order Status. e.g. the below data clearly shows that the Order Status was changed from New to Approved.

{
  "value": [
    {

      ... removed other data for the brevity
      "VersionId": 3584,
      "VersionLabel": "7.0",
      "ID": 1,
      "OrderName": "Laptop",
      "OrderStatus": "Approved",
      "Quantity": 11,
    },
    {

      ... removed other data for the brevity
      "VersionId": 3072,
      "VersionLabel": "6.0",
      "ID": 1,
      "OrderName": "Laptop",
      "OrderStatus": "New",
      "Quantity": 11,
    }
  ]
}

Step # 3 Extract the field values using Select operation

Us Select to extract only the OrderStatus field’s last top 2 versions we need.
# use the following formula in the From.
outputs('REST-GetTop2Versions')?['body']?['value']
# use the following formula in the Map
item()?['OrderStatus']

Step # 4 Store the OrderStaus’s New and Old value to variables

Storing Order Status New and Old values

# for New OrderStatus value us the following formula
first(body('Select'))?['OrderStatus']


# for Old OrderStatus value us the following formula
last(body('Select'))?['OrderStatus']



Step # 5 Using the New and Old variables do the comparison to take business action.

The final step is now up to your business actions. Basically you can use the above two old and new variables to do the comparison if the value have changed, or if values have changed.

Conclusion

There is no out of the box trigger to Tigger on the field. This is a simple ways to find out field values changing.

Posted in Uncategorized | Leave a comment

How to fix an infinite trigger loop in Power Automate?

Summary

Have you seen the following error in your Power Automate?

“Actions in this flow may result in an infinite trigger loop. Please ensure you add appropriate conditional checks to prevent this flow from triggering itself.

If your answer is yes, then please keep reading…

As you can see the warning is telling you “it may” and you need to “add appropriate condition checks”.

So to demonstrate that, I am going to create a scenario to get this warning and address it. I will create a simple custom list (named InfiniteLoop) with a default ‘Title’ field. Then I will create a flow with a trigger “When an item is created or modified”. In this flow, I will use a random number generator function rand to generate a random number from 1 to 75. The flow will append the generated random number to the Title field and call Update item.

After all that, it will show me a warning in the Flow checker as seen below.

# code for the compose is
concat ( ' ' , rand(1,75))

# code for the Update 'Title' is

concat ( triggerOutputs()?['body/Title'], outputs('Compose'))

Step By Step Solution

There are multiple ways to solve this infinite loop issue.

You need to add a trigger condition to your flow so it only fires when condition is met and does not fire back again after update item in your flow.

Solution # 1 : Utilize ‘Modified By’ field as the trigger condition.

Basically, what you want here is to set a trigger condition to check “Modified By” Email is NOT equals to a user’s email(flow author’s email). In my scenario BobK created the flow.

You can simply copy paste the following formula to the Trigger Condition, and change the email address appropriately.

# First modify by check email is null, 
#             if yes, return true
#             if no,  convert to lower case and compare to flow author
# Make sure to change the email address after copy and paste

@if(equals(triggerOutputs()?['body/Editor/Email'],null), true, not(equals(toLower(triggerOutputs()?['body/Editor/Email']),'bobk@gov963094.onmicrosoft.com')))
Settings Dialog for “When an item is created or modified” trigger.

NOTE: Please make a note, if the Flow Author user makes a change to the list item, the flow will not trigger. This is a downside of this technique. If you are a flow author and you will not be end user to update the list items this technique will work great for you.

Solution # 2 : Create a Copy of the field to do compare.

In this technique for above summarized scenario. I will create a new field e.g. “Title_Copy”. This field will hold the copy of the data from the “Title” field. The new Title_Copy field will be hidden from the user. The copy will be made in the flow with update item.

The list showing the Title and Title_Copy fields.

The flow will have a trigger condition to check if the “Title” and “Title_Copy” are NOT equal. If they are not equal that means the user has made the change to the Title field. So allow flow trigger to fire.

If they are equal that means the change is made by the flow at the time of the update. So do not allow trigger to fire.

# To fire the trigger, check if the Title and Title_Copy are NOT equal.

@not(equals(triggerOutputs()?['body/Title'],triggerOutputs()?['body/Title_Copy']))
The flow with Title and Title_Copy

Solution # 3 : Create a Copy of the field to do compare for more than one fields.

So far ok if you were only interested in the one field user makes the modification. What if you have more than one field which your flow is interested to check whenever a user makes a modification. The above #2 technique may need to be adjusted.

Basically, you will make a master Copy filed with multi text type. This field will be hidden similar to the #2 solution. This field also will be updated in the flow.

But update will be concatenation of the all required fields your flow want to trigger on.

To be continued… (I will complete this later as I get time)

Conclusion

As you can see there are multiple ways you can avoid infinite loop. Please let me know your comments.

Posted in Power Automate | 1 Comment

How to get User Profile Properties and ODfB information?

Summary

I want to find out the User’s OneDrive for Business url or personal site. I also want to find more such user properties for the user with UPN, such as when the personal site created, if not created what is the state of the personal site etc. You will find all this information here.

Step By Step Solution

It is very simple to do but before you run the PnP.PowerShell command make sure you follow prerequisite for connecting to the new tenant. This is a good article.

In short, you need to call Register-PnPManagementShellAccess and accept the consent. This will create the Azure AD app Service Principal in your tenant.

After that you can call the following scripts to get all User’s OneDrive URL.

Connect-PnPOnline -url https://gov963094-admin.sharepoint.com/ 

$p = Get-PnPUserProfileProperty -Account 'admin@GOV963094.onmicrosoft.com'

$p.PersonalUrl

### OUT PUT is ###
### https://gov963094-my.sharepoint.com/personal/admin_gov963094_onmicrosoft_com/

$p.UserProfileProperties

### OUTPUT of above will provide all the properties.

If you notice there are properties for the Personal Site with information such as what and when the personal sites were created or enqueued.

Property-Name
AboutMe
AccountName
ADGuid
Assistant
CellPhone
DelveFlags
Department
Fax
FirstName
HomePhone
LastName
Manager
msOnline-ObjectId
Office
OfficeGraphEnabled
PersonalSpace
PictureURL
PreferredName
PublicSiteRedirect
PulseMRUPeople
QuickLinks
SID
SPS-AdjustHijriDays
SPS-AltCalendarType
SPS-Birthday
SPS-CalendarType
SPS-ClaimID
SPS-ClaimProviderID
SPS-ClaimProviderType
SPS-ContentLanguages
SPS-DataSource
SPS-Department
SPS-DisplayOrder
SPS-DistinguishedName
SPS-DontSuggestList
SPS-Dotted-line
SPS-EmailOptin
SPS-FeedIdentifier
SPS-FirstDayOfWeek
SPS-FirstWeekOfYear
SPS-HashTags
SPS-HideFromAddressLists
SPS-HireDate
SPS-Interests
SPS-JobTitle
SPS-LastColleagueAdded
SPS-LastKeywordAdded
SPS-Locale
SPS-Location
SPS-MasterAccountName
SPS-MemberOf
SPS-MUILanguages
SPS-MultiGeoFlags
SPS-MySiteUpgrade
SPS-O15FirstRunExperience
SPS-ObjectExists
SPS-OWAUrl
SPS-PastProjects
SPS-Peers
SPS-PersonalSiteCapabilities
SPS-PersonalSiteFirstCreationError
SPS-PersonalSiteFirstCreationTime
SPS-PersonalSiteInstantiationState
SPS-PersonalSiteLastCreationTime
SPS-PersonalSiteNumberOfRetries
SPS-PhoneticDisplayName
SPS-PhoneticFirstName
SPS-PhoneticLastName
SPS-PictureExchangeSyncState
SPS-PicturePlaceholderState
SPS-PictureTimestamp
SPS-PointPublishingUrl
SPS-PrivacyActivity
SPS-PrivacyPeople
SPS-ProxyAddresses
SPS-RecipientTypeDetails
SPS-RefreshToken
SPS-RegionalSettings-FollowWeb
SPS-RegionalSettings-Initialized
SPS-ResourceAccountName
SPS-ResourceSID
SPS-Responsibility
SPS-SavedAccountName
SPS-SavedSID
SPS-School
SPS-SharePointHomeExperienceState
SPS-ShowWeeks
SPS-SipAddress
SPS-Skills
SPS-SourceObjectDN
SPS-StatusNotes
SPS-TenantInstanceId
SPS-Time24
SPS-TimeZone
SPS-UserPrincipalName
SPS-UserType
SPS-WorkDayEndHour
SPS-WorkDays
SPS-WorkDayStartHour
Title
UserName
UserProfile_GUID
VideoUserPopup
WebSite
WorkEmail
WorkPhone

Conclusion

It is pretty simple to get all these information for the user profile using PnP.PowerShell.

Posted in PnP.PowerShell | Leave a comment

How to find all associated Power Automate Flows for a SharePoint list?

Summary

A customer showed a working Power Automate for an item created trigger. There was an approval action in the flow so technically only one email should be sent for a new item. But she noticed multiple approvals emails were sent. This was no way possible.

Our suspicion was there may more than one flow associated to the list. What if, this flow was duplicated by someone and that also started running for an item created event.

The question is, how do we find out how many flows are associated to the list?

The answer is to make a call to the SyncFlowInstances to the list to find all associated Power Automate Flow.

Step By Step solution

Step #1 First, create a Manually Triggered Power Automate Flow.

Step #2 Add “Sent an HTTP request to SharePoint” action

Make a call to SyncFlowInstances

Copy paste values to your ” Sent an HTTP request to SharePoint” action. Make sure to change the list name below, my list name is MasterSiteInventory yours may be different.

Site Address: [Your Site]

Method: POST

Uri:

_api/web/lists/GetByTitle('MasterSiteInventory')/SyncFlowInstances

Headers:

{
  "accept": "application/json;odata=verbose",
  "content-type": "application/json;odata=verbose"
}

Body: EMPTY

The above call returns a response in the FlowSynchronizationResult. The intent now will be to get the data from the FlowSynchronizationResult.SynchronizationData property. This property stores value as JSON string as seen in the below sample data.

{
  "d": {
    "__metadata": {
      "id": "https://BLAHBLAH.sharepoint.com/sites/siteprovisioning-preprod/_api/web/lists/GetByTitle('MasterSiteInventory')/SyncFlowInstances",
      "uri": "https://BLAHBLAH.sharepoint.com/sites/siteprovisioning-preprod/_api/web/lists/GetByTitle('MasterSiteInventory')/SyncFlowInstances",
      "type": "SP.FlowSynchronizationResult"
    },
    "SynchronizationData": "{\"value\":[{\"name\":\"1a7", REMOVED FOR BREVITY"
    "SynchronizationStatus": 0
  }
}

Step # 3 Now parse and convert the above SynchronizationData JSON string to HTML

Add a Compose action with the following formula. As you can see the data is converted first to JSON and then we access the value property. The value property is a JSON array.

json(outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['d']?['SynchronizationData'])?['value']

Step # 4 Now iterate the value JSON array to select name and display name.

From

outputs(‘Compose’)

Map

{
  "ID": {@item()?['name']},
  "Name": {@item()?['properties']?['displayName']}
}

Step # 5 Now, just take the output of select and pass to Create HTML

Conclusion

Using the above method you can quickly find out how many flows are associated on your list.

Here was my output for the above flow.

My inspiration to write this blog is from the below article.

How to get a list of Flow instances attached to a SharePoint List?

Posted in Power Apps, Power Apps, Power Automate | 2 Comments

How to create Site Columns using field schema xml in Power Automate?

Summary

This is a very simple blog to create site column using Schema XML. The Schema XML can be extracted with PnP.PowerShell. First manually create the Site Column on your site. After you are happy with the created fields you can extract the information using the Save-PnPSiteTemplate. Anyhow, this post is to create the site columns in consistent way to various sites.

Step By Step

#1 Create a Manually Triggered Power Automate flow.

#2 Create Compose action, rename to something like ComposeSchemaXML. Add the following xml in the “Inputs” of compose action.

Field Schema Definition

<Field Type="Text" DisplayName="Description" Description="A Sample description field." Required="FALSE" EnforceUniqueValues="FALSE" Indexed="FALSE" MaxLength="255" Group="MyCustomFields" StaticName="reqDescription" Name="reqDescription"></Field>

#3 Call the “Send an HTTP request to SharePoint” action with the following information.

Method: POST

Uri: _api/web/fields/createfieldasxml

Headers:

{
   "accept": "application/json;odata=verbose",
   "content-type": "application/json;odata=verbose"
}

Body

{
  "parameters": {
    "__metadata": { "type": "SP.XmlSchemaFieldCreationInformation" },
    "SchemaXml": '@{outputs('ComposeSchemaXML')}'
  }
}

Conclusion

This post may a simple and short post for you. It may give you a quick ways to create the multiple site columns in a consistent way.

Please make a note:

To check if the site column exists or not you can make GET call with the following uri

_api/web/fields/GetByInternalNameOrTitle(‘reqDescription ‘)

To delete the existing site column you make a POST call with above uri with headers as following.



{
   "accept": "application/json;odata=verbose",
   "content-type": "application/json;odata=verbose",
   "IF-MATCH":"*",
   "X-HTTP-METHOD":"DELETE"
}

Posted in Power Automate, Uncategorized | Leave a comment

How to setup certificate in MAG Azure Function App for PnP.PowerShell?

Summary

The PnP.PowerShell supports PowerShell Core. There are few things you must do to make the PowerShell Code to work in Azure Function App. In this article, I will go through the steps by step all pre requisites.

Step by Step Process

Step # 1 Create the certificate files or acquire commercial certificate

# Change your tenant name

Register-PnPAzureADApp -ApplicationName "MyPnPApplication" -Tenant "GOV963094.onmicrosoft.com" -CertificatePassword (ConvertTo-SecureString -String "password" -AsPlainText -Force) -Username admin@GOV963094.onmicrosoft.com -DeviceLogin

# Note: make a note of PFX file and password you will need in step 3.

The above command will provide the DEVICE code to log in to the Azure Portal. You will be asked to consent the requested permissions for “Group.readWrite.All”, “User.ReadWrite.All”, “AllSites.FullControl”, “Site.FullControl.All” & “User.ReadWrite.All.

Once the consent is provided the command will create the Azure AD application and returns the application id. The command also creates the two certificate filed <Name of App>.PFX and <Name of App>.CER.

Consent dialog

Step # 2 Create and configure the Azure Function App

Go to Azure Portal and create the Azure Function App. Select PowerShell Core as runtime stack.

Create Azure Function App with PowerShell Core runtime stack

Create a Timer Function

  1. Click on functions
  2. Click Add
  3. Select Timer Trigger
  4. Click Add button.
Create a Timer Azure function

Configure the profile.ps1

Click on “App Files” -> Select profile.ps1

Add the EnvironmentName for your scenario. If it is commercial you do not need to pass the Environment Variable. Please refer here for more information.

Connect-AzAccount -EnvironmentName AzureUSGovernment -Identity
Configure profile.ps1

Configure requirements.ps1

Click on “App Files” -> Select requirments.psd1

Add the following line for PnP.PowerShell.

# This file enables modules to be automatically managed by the Functions service.
# See https://aka.ms/functionsmanageddependency for additional information.
#
@{
    # For latest supported version, go to 'https://www.powershellgallery.com/packages/Az'. 
    # To use the Az module in your function app, please uncomment the line below.
    'Az' = '6.*'
    'PnP.PowerShell' = '1.*'
}
Configure requirements.ps1

Step # 3 Upload the certificate on Azure Function App

Click on “TLS/SSL Settings” -> “Private Key Certificates (.pfx)

Click on “Upload Certificate”

Select the PFX file created in the Step # 1 and provide the password as you have used in the Step # 1.

Upload PFX to the Azure function App.

Step # 4 Add the WEBSITE_LOAD_CERTIFICATES configuration parameter

Click on the “Configuration”

Add WEBSITE_LOAD_CERTIFICATES with value of “*”.

Modify the configuration.

Step # 5 Import the certificate to Azure Key Vault

On Azure Portal for the Azure Key Vault

Click on “Certificates” -> “Generate/Import”

On the next dialog select Import and select the certificate created in the Step # 1.

Finally click on Create. This will add the certificate in the Azure Vault.

Step # 6 Create Azure Function App’s System assigned Identity

On the Azure portal got the Azure Function App.

Click on the “Identity” under Settings -> “System Assigned”

Turn the Status to On

Make a note of the “Object ID” GUID. Copy to the clipboard you will need it.

Step # 6 Provide Access Policy to Azure Function App in Azure Key Vault

On the Azure Portal, navigate the Azure Key Vault.

Click on “Access Policies” under Settings -> “Vault access policy” radio button for the permission model.

Click on the “Add Access Policy” link.

For the “Add Access Policy” dialog

Select “Certificate Management”

Select “Get and List” for the Certificate permissions.

Select “Secret Management”

Select “Get and List” for the Secret permissions.

Finally select the “Select Principal”

In the select permission paste the GUID copied from the earlier step for the system assigned identity.

Click on Select Button and accept all the dialog by clicking Add.

This will add the Access Policy for the the Azure Key Vault. This gives an access to the Certificate in Azure Vault for the Azure Function App using managed identity.

Access Policy

Step # 7 Final step to access the and connect to SPO admin size using certificate.

Please make a note, the import thing in the code is to get the certificate base encode. Once we get it from the Azure Key Vault it is now next step to use the connect command to connect to the any site or admin site.

# Input bindings are passed in via param block.
param($Timer)

$tenant             = "GOV963094";
$RequestWebUrl      = $("https://{0}-admin.sharepoint.com/" -f $tenant)
$GRAPH_APP_ID       = "7c244c08-9875-4ffe-b39d-34f9b6853f6b"
$KeyVaultName       = "my-spo-key-vault"
$KeyVaultSecretName = "storedcertificate"

# get the PFX secret from the key vault
$kvSecret = Get-AzKeyVaultSecret -VaultName $KeyVaultName -Name $KeyVaultSecretName
$certificateBase64Encode = '';
$ssPtr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($kvSecret.SecretValue)
try {
	$certificateBase64Encode = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($ssPtr)
} finally {
	[System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($ssPtr)
}
# Using Splat to convert
$HashArguments = @{
	Url                      = $RequestWebUrl
	ClientId                 = $GRAPH_APP_ID
	CertificateBase64Encoded = $certificateBase64Encode
	Tenant                   = $("{0}.onmicrosoft.com" -f  $tenant)
}
$RequestSitesConnection = Connect-PnPOnline @HashArguments  -ReturnConnection

###
### DO SOME WORK 
###
Disconnect-PnPOnline -Connection $RequestSitesConnection





Conclusion

There are many steps but it is listed here step by step for the reference. For any reason you miss the step please refer it back.

Posted in MS Graph, PnP.PowerShell | 3 Comments