PowerApps Currency Formatting and Trailing Zeros

It seems like it would be so easy….but alas it was not for me.  So, in case anyone else is having trouble with this issue, here’s the deal:

I kept formatting currency like this:

Text(ThisItem.Check_x0020_Amount, “[$-en-US]###,###.00”)

And it would look like this:

I was looking for a way to get “$325.40.”  Finally I found the answer.  Format currency like this instead, putting a dollar sign in front of the ###,###.00, like this:

Text(ThisItem.Check_x0020_Amount, “[$-en-US]$###,###.00″)

Now it looks like this:

Yay!!  Something that seems so easy was a little tricky to figure out.  Hope it helps!

Set the default value of a Drop Down in PowerApps

I’m not sure why this is so hard to find but here it is.  I have a Sharepoint list with a “Choice” field, where the user can choose one of two values.  Let’s say “ValueA” and “ValueB.”

In PowerApps I want to set the default value of a drop down to be “ValueA.”  Here’s how:

  1. Select the data card for the field (Note:  the data card for the field includes both the label and the drop down control)
  2. Set the Default property of the data card to be:
    • If(SharePointForm1.Mode = New, {
      Value:”Check Request“}, 
  3. Replace “SharepointForm1” with the name of your form (not in quotes like mine above in the code example), replace the value “Check Request” which the value you want for your drop down (in quotes like mine above in the code example), and then replace “Request_x0020_Type” with the name of your field (not in quotes just like mine above in the code example).

And that’s it!

Below you can see I’ve got the entire data card selected, I’ve set the Default property, and the drop down is showing the value I specified:

PowerApps – How to set a People Picker field default to the Current User

This took me a while to find so I thought I’d document how to set a People Picker field to default to the current user name.  It’s easy once you have the code and you put it in the right place!

Here’s how:

Click on the DATA CARD for the field….not the actual people picker field, but the entire data card for the field.  See below where I’ve got the entire CARD identified in RED:





Copy the code below:

If(NewForm.Mode = New,{‘@odata.type’:”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser”,Claims:Concatenate(“i:0#.f|membership|”,User().Email),DisplayName:User().FullName,Email:User().Email},ThisItem.Project_x0020_Requestor)

Paste the code into the “Default” property for the DATA CARD.  Replace two things in the code:  First Replace “NewForm” with the name of your form, and then replace “Project_x0020_Requestor” with the name of your DATA CARD.

And viola!  Your name appears in the people picker.

Customizing the Content Query Web Part (CQWP) to add Additional Columns from a Custom List

I am using the Content Query Web Part to display some items from a list. Sounds pretty simple right? Not so much if you’re not a developer or familiar with this web part.

The Content Query Web Part (I will call it CQWP) is such a great web part but the out of the box options are pretty limiting. I have a list and I want to display an image, the Title (which will link to the item), and two additional fields, one of which is a rich text field.

Upon reviewing my options to style my list in the CQWP, I see one that matches what I’d like to do – the “Image on the Left” sytle:

Great, now I’ve got the image on the left and I want my three additional fields to the right of the image. But OH NO, Sharepoint does not make it so easy. In the area where I would pick the “Fields to Display” in the web part I only have the ability to add TWO additional columns instead of the three I need:

How do I add an additional field/column?

Well….after a lot of reading and researching and digging, I found out how to do it. The CQWP is actually made up of TWO parts: the “Query” and the style or “Presentation” of the query. The Query queries the list (or whatever you specify), and then the style sheet (Presentation) displays the results of the query in your web part. Here are the two parts that make up the CQWP properties:

So you might think we have two customizations to make – but we only have one.  The query is already set to return ALL the columns of the Custom List along with any filters you’ve specified.  (I don’t know about you, but I’ve been using Sharepoint and CQWPs for many years and I’ve never seen it explained so simply!!)

First set your query “Source” to “Show items from the following list:” and browse to select your list.  I also selected “Additional Filters.”  These were the only fields I needed to set on my query…but if you need more or different settings for your query, set them in the “Query” section of the CQWP properties.















Now, since your query is already returning all your columns from the list we just have to customize the style or “Presentation” piece.  To do this we’ll need to customize the style file that shows items from a list.  Open Sharepoint Designer to the SITE COLLECTION where your site is located.  In the left pane select “All Files” under Site Objects:  Scroll towards the bottom and select the “Style Library”:  

Click on the folder for “XSL Style Sheets”  and within this folder you will see all the style sheets for various items.  The CQWP uses the “ItemStyle.xsl” file to display items so we’ll need to customize this file.  Right-click the file and select “Edit the File in Advanced Mode.”  You will receive a prompt indicating the file is under source control – you will need to answer “Yes” to check the file out before customizing it.

The file will look a bit complicated, but there’s only a few things we need to know for our purposes.  Hang in there with me.  Remember back when we were setting the properties on our CQWP and one of the settings we could choose was “Item Style”?  (See the very top of this post, the first two images for a reminder…also, here’s a screen shot of the various item styles:

The “ItemStyle.xsl” file contains a template for each of the item styles listed.  Notice the “template” tag for each of the Item styles listed?  (Note:  since “Image on left” is the default Item style selected within the CQWP, the template name in the ItemStyle.xsl is “Default” – see image:  

If you scroll down the file you will see the other templates or Item styles within the file (here you can see the closing tag for a template, followed by the next template which is the template for “NoImage” – which is the template for the “Title and Description”  Item style). 

NOTE:  The names of the templates in the ItemStyle.xsl file do not match the names of the selections in the “Item style” drop down in the CQWP properties.  OH NO this would be too easy!  BUT they are in the same order so:

“Default” template in ItemStyle.xsl = “Image on left” Item style on CQWP properties
“NoImage” template in ItemStyle.xsl = “Title and Description” Item style on CQWP properties

…and so on.

For my purposes I need the “Default” template…BUT I do use this template in other places within my site collection so I am NOT going to customize anything within this template.  Instead I am going to copy the Default template contents and then paste them directly below the closing tag of the Default template.  In the example below you can see I’ve selected an entire template from the opening template tag to the closing template tag:

Copy and paste an instance of the template directly below the closing tag of the template.  Now you’ll want to change the “name” of the Template instance you just copied.  Change the first line of the template to the “name” you’d like – my new template will be called “CustomTemplate”:

Now we get to add our field!  Within your template scroll down until you see the div class for “description”:

Add your fields to this div – in the example below I’ve added the “Quarter” and “Reason” fields:

Now my “Reason” field is a rich text field so I will need to add a little more – add disable-output-escaping=”yes” as you see below if you need it for a rich text field:

Now Save and close the ItemStyle.xsl file.  Check the file in as a minor version first so only admins can see the changes….once you are sure you like your changes then check in the ItemStyle.xsl as a major version (Published) or no one else will see your changes.

Open the page with your CQWP and open the web part properties.  Click the Item style drop down and select your template.  You will see the additional labels you added and you can add your field names to the additional text boxes!!

When you like what you’ve got you can publish the ItemStyle.xsl file.  Your new item template style will be available to all sites within the site collection.  If you navigate to another site collection and look for it in CQWP properties you won’t see it unless you do the same to the ItemStyle.xsl file within that site collection.

Microsoft Announces Replacements for MS InfoPath 2013 and Sharepoint Designer 2013

If you’ve got Sharepoint Online (O365) RockPoint Technologies has you covered!!  We’ve been using MS PowerApps and MS Flow in Beta for several months now.  Microsoft has just announced these two products are now being released for general availability, and are meant to be the successors to MS InfoPath 2013 and MS Sharepoint Designer 2013.

While neither of these products is mature enough yet to replace complex application and workflow solutions developed in InfoPath or Designer, we have at least had the official word from Microsoft on the replacements!  These new solutions are also optimized for mobile use (iOS and Android), which is pretty exciting.

Of course, be aware these new solutions also come with different “flavors” available at different licensing costs and plans based on your current subscriptions (where InfoPath and Designer are “free,” PowerApps and Flow may have costs associated with them depending on your organization’s use).

Read more about it here, from an article in MCPMag:

“Microsoft’s SharePoint development team seems to have embraced PowerApps and Flow in a big way. For instance, Microsoft’s SharePoint announcement today explained that PowerApps and Flow “are the successors to InfoPath and SharePoint Designer for many common business scenarios, especially custom forms used on SharePoint lists.”