I’m currently on an adventure into my first GCC (government) tenant. When I went to https://flow.microsoft.com in this new GCC tenant, I got the following error:
“That didn’t work. Your org doesn’t allow Microsoft Flow. You’ll need to use your personal email to sign up and get access.”
I was in the habit of using that URL for quick access. Turns out the GCC tenant uses a different Flow. If you use the app launcher in your tenant to get there, you’ll notice the correct URL to memorize is https://gov.flow.microsoft.us (or your locality).
If that doesn’t work for you, and you’ve tried to get to Flow through the App Launcher (waffle icon in the upper left of Office online or SharePoint), it could very well be that your organization doesn’t allow Flow.
One major difference I’ve noticed so far is that Flow for government O365 tenants only has around ~85 connectors currently where as Flow for commercial O365 tenants has around ~275 connectors.
The following are the available connectors currently in GCC tenants:
The following was my solution for correcting a time zone issue in which the original data’s date/time column was 5 hours ahead.
=[#"Date/Time"] - #duration(0, 5, 0, 0)
That example subtracts 5 hours from a column called Date/Time. You can change the column name to your own, of course. Also update the numeric values in the #duration part to add or subtract days, hours, minutes, and seconds, respectively.
#duration(days, hours, minutes, seconds)
And just change the – to + to add instead of subtract.
This Power Query M function reference guide is super helpful for learning more about available functions to use in your M formulas in Power Query Editor.
I’m working on digitizing a form to improve the user experience and our data collection and availability efforts. I decided to do this one in PowerApps as we begin to pivot organizationally toward the modern experience.
During building this particular form, I needed to hide fields unless the user indicated “Yes” to corresponding dropdown fields. Here’s the end-result:
Click to enlarge
To get this to work we have to do three steps:
Initialize variables for visibility
Set the conditional fields’ visibility to the new variables
Set the dropdown to toggle the related variable
Initialize variables for visibility
First we need to tell our app that we’ll be using true/false variables to indicate the visibility of our conditional fields. We do this from the OnVisible property (2) of the Form Screen (1).
Click to enlarge
The formula itself should be as follows (3). Use a semi-colon between each UpdateContext function to add additional variables. I’m using aVisible, bVisible, etc.
So in your documentation you might note something like:
Variable
Default state
Changed by
Hides/Shows
aVisible
false (hidden)
BAARequired (dropdown)
BAAEffectiveDate
bVisible
false (hidden)
ConflictsOfInterest (dropdown)
Conflicts…Completed
cVisible
false (hidden)
DataSecurityRisk (dropdown)
DataSec…Completed
Note: You can use the same variable more than once to show/hide multiple fields based on one dropdown.
Set the conditional fields’ visibility to the new variables
Now we need to assign these variables to the DataCards for each field we wish to hide.
From the Tree View panel, select the DataCard (not the DataCardValue within/beneath it) for the field you wish to hide (1). Then go to its Visible property (2). Finally, set the property’s function to the variable you initialized for it (3). In this example I decided I would use “cVisible” for “DataSecurityRiskCompleted”.
Click to enlarge
Repeat the steps above, assigning variables to each field you want to hide until a dropdown is changed to the triggering value.
Set the dropdown to toggle the related variable
The last step is to tell the variables we established to change based on a dropdown.
First select the DataCardValue (not the DataCard) within the data card (1).
Then select the OnChange property (2) and set the function to the following (3):
Repeat for each dropdown that should serve as a trigger for conditional fields. Now, when I publish my app, changing “Data Security Risk” to “Yes” will change the default false visibility of DataSecurityRiskCompleted to true. If I change the dropdown to “No” again, it will hide the related field again.
I’m by no means an HTTP request expert, but the requirement of being able to integrate business applications is common and, in this case, important. I took some time to figure out how we could implement Alertus integration and am sharing what worked here.
Background
Alertus is a program we use at LMH Health to push mass-notification desktop and mobile alerts to the organization during times of severe weather, evacuation procedures, or just software downtime.
Image from alertus.com
We use SharePoint as a supplement for “breaking news” by adding a banner to the top of our site during times of urgency. We wanted to integrate Alertus with SharePoint so that when an alert fired, it created a breaking news banner in SharePoint as well. This makes sure users who aren’t on a managed device, such as working from a home computer, are still able to get important news if they happen to be on the intranet.
In times of emergency, it’s best to utilize as many channels as possible to inform your users. Integration of these two services meant there’d be minimal delay in broadcasting a single message through all available channels.
How it works
To send the Alertus alert data to SharePoint, we needed something capable of receiving an HTTP request so Flow came to mind first. Once configured, your Alertus administrators will just send alerts as usual, being sure to include SharePoint in the alert profile.
Create and send an alert in Alertus
Flow receives HTTP request with alert data
Flow creates item in SharePoint list used for breaking news
And, of course, with Flow we could then do any number of actions including additional HTTP actions, sending to email or phone, etc.
Note: This works for SharePoint server and SharePoint online. If using server, you’ll need the on-premises data gateway so that Flow can connect to your environment.
Setting it up
We need to do a few things to get this working:
1. Start creating a Flow (to get the HTTP POST URL) 2. Create the Alertus Service for “SharePoint” 3. Add the Alertus Service “SharePoint” to any Alert profiles for which you want to include SharePoint for distribution 4. Finish the Flow 5. Test
Start creating a Flow
1. Create a new Flow with the When a HTTP request is received trigger
2. Paste the following into the Request Body JSON Schema
Add the service to alert profiles and/or preset alerts (optional)
Your service is now available to be added to alert profiles (pre-selected sets of services to notify under certain circumstances, such as an evacuation) and preset alerts (customized alerts and notification groups).
Alert profile example of adding the new SharePoint service we created
Preset alert example of adding the new SharePoint service as a delivery method for the alert
You could, alternatively, choose not to include it in anything preset and only use it manually upon creation/configuration of an alert.
Finish the Flow
1. Go back to the Flow you started.
2. Add a HTTP Response step and make sure the Status Code is set to 200. This lets Alertus know the request was received and prevents an error from occurring on the Alertus side.
3. Add a SharePoint: Create item step and connect to the site and list for which you want to create an item from the alert details.
Test
From Alertus, do a Custom Activation of a test message just for the SharePoint service/delivery method.
In the example below, I also threw in an email alert as a step but you can see the whole process in Flow’s run history still only took 2 seconds.
Check in SharePoint that the item was added. In my case, we use custom script to display the most recent list item as an active alert.
Additional Configuration (optional)
Now that you’ve done the basics, you may wish to make adjustments.
Check out Alertus’ Knowledgebase (must be a customer with login credentials) at https://my.alertus.com/ .
Search for HTTP Request (Documentation) where you’ll find additional placeholder variables you could use in the Activation Body and JSON Schema fields to send additional details.
Since Excel doesn’t have the same set of robust text transformation tools as Word, we need creative ways to get the same effects sometimes. When it comes to auto-capitalizing whole words or first letters of words, there are two functions you can use:
=UPPER() will capitalize the entire cell contents
=PROPER() will capitalize first letters of each word in a cell
In the GIFs below, you’ll notice I demonstrate both. The steps for both are exactly the same, so just pick the formula/function you need.
Since nobody wants to add another column to a sheet unnecessarily, we’ll just temporarily create a column for the calculation, copy the result, then delete it. You may, alternatively, wish to just hide it for later use or keep it alongside the original for a purpose.
Insert column and formula
Insert column
Place formula of choice in top cell of column and drag down (lower right-hand box/handle of cell) or press enter if in table format as seen below
Click to enlarge
Copy new values over originals and delete temporary column
Next we’ll use the “Paste values” feature to remove the formula dependency of the resulting data and keep just the newly formatted text.
“Paste Values” icon when pasting in Excel. This option copies just the values resulting from a formula so you don’t “break” your data/calculations when moving/deleting.
Copy values
Paste (special) values only over original column values to replace them with the correct capitalization version
This morning I pasted a list of values into Excel in which the pattern consisted of a “good value” followed by that same value with a minor adjustment. The second, near-duplicate value wasn’t useful to me so I wanted to filter out all even rows so I’d just have the first of each near-duplicate.
So to filter odds and evens, we’ll add a new column to the right of our data and set the formula to:
=ISEVEN(ROW())
This evaluates the data’s row number as even or odd and will return TRUE if the number is even or FALSE if not. Double-click the handle in the lower right of the cell to fill down.
Then we filter the column for just TRUE (evens) or FALSE (odds). You can right-click the value you want to keep > Filter > Filter by Selected Cell’s Value
A simple trick in Excel allows you to remove duplicate values from a column in Excel. Depending on your version of Excel, yours may look a bit differently but the process is nearly the same. Below, I’m using Excel 2016.
1. Select the column, or the values from which you’re removing duplicates. Note: You could also just start with step 2, and then select the relevant column(s) from the resulting dialog. 2. Go to Data > Remove duplicates (in the Data Tools panel)
3. Confirm if your column has a header (column title or not) and click OK.
4. Excel will confirm the number of duplicates found and how many remain. Click OK.
But adjusting the same widths in datasheet mode (quick edit, for example) is a bit different. After a bit of fiddling around, I found an answer that will allow you to adjust column widths for both standard and datasheet view types using just CSS.
The difference is in how you reference the column names in the css:
Standard (catches both filterable and non-filterable columns such as multi-line text): th.ms-vh2-nofilter div[DisplayName=’Column1′],th.ms-vh2 div[DisplayName=’Column1′]{…
Datasheet: th[Title=’Column1′] {…
Everything that follows that first line is the same in both types of views, fixing a minimum and regular width property for the column(s).
I’m honored to be among many fantastic presenters for SharePoint Fest Seattle. When first learning SharePoint, SharePoint Fest Seattle was the first big conference I attended. I was an administrative associate at the time. I remember attending a workshop on SharePoint Designer workflows by Vlad Catrinescu and thinking it was the coolest thing I’d ever seen.
Each night of the conference, I’d stay up late working on things I’d learned or ideas I’d gotten during the day. After the conference, I dove all in and ended up working full-time in SharePoint. My job transformed into a SharePoint Business Analyst role. Fast forward, now I’m a Microsoft MVP, Microsoft 365 Certified Enterprise Administrator Expert, author, blogger, user group leader, and a SharePoint Systems Engineer at LMH Health.
I’m continually wowed and re-energized by all that’s coming out of Microsoft. And I’m excited to be on the other side of the podium for a few sessions this year, sharing some of my favorite topics. And, as always, I’m looking forward to sitting in the audience as well for what is certainly one of the year’s best professional development opportunities.
Come discover how SharePoint Fest Seattle can expedite your career growth through professional development and inspiration. Register today at https://www.sharepointfest.com/Seattle/ and use code Chamberlain100 to save $100 off your pass.
I’ll be presenting these three sessions, as well as sitting at the “Ask the Experts” booth a couple times. I hope I’ll see you there!
INT102 – Building the Intranet of the Future: Using SharePoint to Empower Collaboration
Automation, organization, incredible search capabilities and user interactivity; all things your organization can have in choosing to build an intranet with SharePoint.
We’ll discuss avoiding File Dump 2.0 by exploring best practices in internal communications and collaboration via intranets, rethinking forms and minimizing busy work by utilizing automation capabilities in SPD workflows and Microsoft Flow and designing intuitive navigation.
CM202 – SharePoint wizardry for content management, archiving & retention
Are your muggles muddying up your servers and site storage with duplicate files, pictures from a 2008 staff party and files named “April” in folders called “Jane’s stuff”?
Learn how to most effectively destroy the 8th horcrux using out-of-the-box functionality in SharePoint brewed with bits of governance, content strategy and just a little help from SharePoint Designer to help automate processes. It’s just like magic!
BV303 – You’re the one, OneNote!
Are you looking for more productivity in your life? Sticky notes leaving you…unsatisfied?
Sit back and enjoy this journey through the sweetest productivity story ever told, where you’ll be inspired to take “note” of new ways of working more efficiently day-to-day with OneNote. When it comes to working smarter and not harder, OneNote is the one.
