Animation in PowerApps

In this post I’m going to put some light on the topic of animation in PowerApps. Animation is what probably most of PowerApps devs have never tried to use in their application and you may also think now: “That’s true, because hey, why should they? PowerApps platform is for business, not games”. So let me give you couple examples:

  • Mastering timer control – Timer control is heavily used in animations but you can also use it in different ways like app behavior delays (change screen after 5 seconds), counters (time limited surveys) and statistics (time per page)
  • Awesome loading screen or eye catchy busy indicator – just to amuse your users
  • Improve your business apps – sometimes simple highlight or
    animated illustration can really improve UX of your solution
  • Understand PowerApps platform even better – animations exists on cross line between possibilities and performance
  • Cool games – because a kid is inside each of us
  • Inspire yourself – any learning create a “dots” in your mind. Every idea is a connections of different dots all together but you never know when a new dot make a real difference. So collect them to not narrow your options

From my experience playing with PowerApps animations significantly improved my proficiency in programming in declarative paradigm language (I have C# programming backgrounds which is an imperative paradigm language), helped in understanding of what and how I can implement in PowerApps and also boost my confidence in my PowerApps skills.

DISCLAIMER:

This post was supposed to be much shorter. However the more I spent time on it the more I was convinced that showing whole process without making shortcuts like “ok, we know what we want to achieve so here you have final code and let’s briefly describe it” will give much more value to readers who come here not only to look for a solution but also to understand the process and learn. Furthermore I did not split this article for a separate 2-3 post series because that will ruin the whole workflow and the context of work (What are we doing now? Why are we doing it? Hold on, I got to remind the last part…?).

As the result the following article stays in one piece bringing the most value of it, in my humble opinion.

Different ways of creating animation in PowerApps

To add an animation to your PowerApp app you can use different ways. You can:

  • Add a video file – simple as that. In PowerApps you can switch controls off and auto play it so for a user it will look just like an animation. However be aware that if your video contains any sound you cannot autoplay it in Chrome. Also video cannot interact with user or react on screen changes. Like below:
  • Add a gif file – this method is really similar to a video file without a sounds and is also limited in how it can interact with user or other controls. However it’s ideal for i.e. busy indicator.
  • Constrain timer, controls (i.e. images) with functions – this method is the most powerful, interactable and flexible but also the one that needs some knowledge about animations, programming and platform specifics (like functions in PowerApps) for which you want to create an animation.

In this blog post I want to focus only on the last of above list.

Briefly about animation

Before we start with animation implementation in PowerApps it’s good to know what the animation really is. Especially that no matter which platform you pick the main concepts are always the same.

So what is animation? Short answer may be not really helpful: it’s a short sequence of a film. So what film is? To understand that let’s go back to 1872 when there was an ongoing debate among people interested in horses: “whether all four feet of a horse were off the ground at the same time while trotting?”. Today you will probably google that or just record a horse with your mobile and play video really slow (or should I say frame by frame…but let’s not overtake the facts 😉 ) but back then common opinion was that horse has always at least one foot on the ground while at a trot (and this beliefs shared most paint artists that time). Anyway, one of the race-horse owners named Leland Stanford wanted to end debate once for all and felt that relatively young and still imperfect technology, a photography, might help him. So that he hired Eadweard Muybridge – 38 years old imigrant from England who was world – famous for his large photographs of Yosemite Valley – for some photographic studies. Muybridge began to experiment with an array of 12 cameras photographing a galloping horse in a sequence of shots. Those shots were far from perfect but one negative proved that trotting horse is fully airborne. In 1878 Muybridge published 12 frames of trotting horse:

Source: wikipedia

Once you quickly display pictures one-by-one (like in a flip book animation) you will see something like this (frame 12 is not used).

Source: wikipedia

To summary all above: animation is picture change in time. This brings us towards conclusion we need if we want to make an animation:

  • a pictures of the object in different positions
  • a timer to “animate” (quickly change displayed pictures)

And commonly that will be it if we want to make an animation.
– “Em…so now I have to make picture of my airplane image in each position on the screen and animate it using timer? Wow, that will be a number!” – of course not, this would be insane :D. Fortunately in computer programming there are many situations where actually you can use only 1 picture and on timer tick (smallest portion of time in which timer counts the time) simulates specific behavior of the image (like movement, rotation, size etc) by modifying image properties using functions. And this is exactly what we’re going to use:

  • single image
  • a timer
  • functions to compute property values (describe relations)

Create an app with animated airplanes

If you don’t know where to start it’s always good to breakdown your task to smaller steps. Do it until you will know how to do the task.

  • Create an app with animated airplanes
    • Add screen
      • Add header
      • Add Reset & Start/Stop controls
      • Add board for airplanes
    • Add airplane animation
      • Add image
      • Make airplane “fly”
        • Move plane down the board
        • Bounce plane from board boarders
        • Move plane nose towards its direction
      • Add speed control
      • Add rotation on U-turns

I hope it makes sense but if you think the list should looks differently just remember – that’s fine. The final result is what matters – not the path you follow.

Adding screen and its components

This task along with its sub-tasks is pretty straight-forward. For header we use simple label. Next for controls we use buttons: reset button will puts all components to their starting positions, and start&stop button will animate our objects. For the animation board I used rectangle shape just with blue borders and white filling (transparent filling is also ok).

Because for now we don’t need to put any logic inside controls, I think simple screen will be enough for you to implement this part of our demo app.

Add airplane animation

Add image

This will be our object that we’ll animate. Easiest way is to google some .png icon that is free to use (I used icon from here and rotate it in paint.net to face it upwards) and import it to app assets.

To add image click file > Media > Images > Browse > pick your file and hit ok

To your app screen an image control (1) and point added file in Image property of the control (2)

Make airplane “fly”

By “flying” I mean moving back and forth bouncing from board edges. And as it has been already mentioned in “Different ways of creating animation in PowerApps” section move is a defined change in time. And there are 2 ways of achieving that: imperative-alike and declarative-alike. For now don’t bother that, I’ll explain it later, what is important now is that we’ll start with imperative-alike way. Reason: this was natural way of implementing dependent logic for me since on the beginning of my professional career I was starting as C# developer.

So what we need is a timer that will produce ticks. Each tick triggers a change for registered object by calling its update function. In PowerApps for update function we’ll use OnSelect() event and this approach has 2 main advantages:

  1. Execution of object’s OnSelect() event can be done by using Select(<object>) function
  2. Debugging of an object on-tick behavior cannot be simpler – by clicking on an object with your mouse button (or finger) you can fire OnSelect() event

So without further hesitation let’s get back to PowerApps.

  1. Add a timer (I replaced Start/Stop button) and configure it in following way:
    • Duration: 50 – This means that between each tick (a “frame”) 50ms pass which gives 20fps. Unfortunately this is the minimal value for timer ticks which limits the smoothness of our animation. Why limits? Because, as popular opinion says, human eye sees ~30fps which is equivalent to 33.3ms per timer tick (on the margin: I’ve found here an opinion that 50% of the population can see in 45+ FPS and trained fighter pilots can even see in 255 frames per seconds. I found no serious research that will prove that opinion except this reasearch from Uppsala University that only concludes “The minimum acceptable framerates is 60 frames per second”)
    • Repeat: true – this provides repeating nature of the timer ticks
    • Auto start: false – we’ll control that using our buttons
    • Auto pause: true – just to switch off timer in case of navigation to another screen if any
    • OnTimerEnd – see below code snippet. I use it as objects register
Select(imgPlane)

As I mentioned: whole logic of airplane move and bounce will be place inside OnSelect() function. Let’s start with something simple:

//object properties
Set(Vspeed,30);
//move behaviour
Set(planeY,planeY+Vspeed)

I hope above code is self explanatory but just in case:

  • Vspeed is vertical change in position of our plane
  • planeY stores plane Y position. Of course to make it work we also need to put placeY variable in imgPlane.Y property value

Once you hit “Play” your plane should start falling like this:

WOOHOO! That’s one small code step for us, one giant leap for our animation skills :). As you can see our plane is now moving downwards and dissapears under the screen bottom.

To restart animation (place plane in starting position) add below code to reset button OnSelect code:

Set(planeY,BoardBorders.Y);

Ok, now let’s move to next part: “Bounce plane from board boarders“. Here small math part comes to play – in every tick we’ll check if the top/bottom border of the plane image is above/under board border.

First let’s mark plane nose direction PDirection. For its value I’m using a number (0 = up, 90 = right, 180 = down, 270 = left) so it will be easier to make all the direction and rotation checks later on. Depends on the plane’s direction we will add or subtract VSpeed:

//we substract to fly up and add to fly down because Y-axis is inverted relative to the Cartesian axis
If(PDirection=0,
Set(P1Y,P1Y-Vspeed), 
Set(P1Y,P1Y+Vspeed)); 
//if we're flying top and cross the top boarder -> go down
If(PDirection = 0&&planeY<=BoardBorders.Y,Set(PDirection,180)); 
//if we're flying down and cross the bottom boarder -> go up. 
If(PDirection = 180&&planeY+imgPlane.Height>=BoardBorders.Y+BoardBorders.Height,Set(PDirection,0)) 

Probably you will see and error – PDirection hasn’t been initialized. Add following code line to Reset button OnClick

Set(PDirection,0)

Play the animation. Your plane should bounce now!

Labels on the left shows value of: imgPlane.Y, BoardBoarders.Y, PDirection

Looks quite good but can you see something awkward? See how the tail (for some configurations it may be the nose or the both) pass the border before the plane change its direction?

Any ideas what is causing that?

Give yourself a minute and try to answer this question by yourself.


Answer: this happens because we have STATIC VSpeed and always check for the borders collision after making the step. If we would like to keep the animation speed the best way will be change VSpeed if expected step will cross the borders and if so reduce it to the maximum of border distance. Equivalent code can look like below:

//Near-edge behaviour
Set(Dist2Top, planeY-BoardBorders.Y);
Set(Dist2Bottom, BoardBorders.Y+BoardBorders.Height-planeY-imgPlane.Height);
If(PDirection=180, Set(VSpeed,Min(VSpeed,Dist2Bottom)),
PDirection=0, Set(VSpeed,Min(VSpeed,Dist2Top)));

Now the bouncing should looks as it should to.

Your imgPlane OnSelect() function should be similar to below (notice I’ve refactored Bounce behavior code a bit):

Set(VSpeed,30);

//Near-edge behaviour
Set(Dist2Top, planeY-BoardBorders.Y);
Set(Dist2Bottom, BoardBorders.Y+BoardBorders.Height-planeY-imgPlane.Height);
If(PDirection=180, Set(VSpeed,Min(VSpeed,Dist2Bottom)),
PDirection=0, Set(VSpeed,Min(VSpeed,Dist2Top)));

//Move behaviour
If(PDirection=0,
Set(planeY,planeY-VSpeed),
Set(planeY,planeY+VSpeed));

//Bounce behavior
If(Dist2Top = 0,Set(PDirection,180));
If(Dist2Bottom = 0,Set(PDirection,0))

Last part for this sub task is to make plane’s nose to point the direction plane is flight. To do that we make use of image property called ImageRotation

Result:

Add speed control

For a speed control I use slider control but of course you can use whatever you like: number text field, rate control or even text recognition property of the pen input field ;). PowerApps is very flexible and so you should be.

To add slider control select Controls > Slider

Integrating our new shiny control with our plane will be unexpectedly easy. Modify first line of imgPlane OnSelect() function to this (SpeedSlider is the name of my slider):

Set(VSpeed,SpeedSlider.Value);

Play with it to test it and to congratulate yourself. You’ve just did piece of good job. Well done!

You deserve a break, use it: fresh your mind, eat some peanuts (good for your brain) and once you ready come back to continue animation project – there is still some work to be done.

Add rotation on U-turns

So far what we did is the simple animation of a plain flying up and down. However adding u-turn once a plane reach borders requires to make some changes to what we’ve done. Because you see – how to rotate image in PowerApps? My first thought was to use ImageRotation property. But unfortunately ImageRotation accepts only ImageRotation enum object that has 4 flags: None, Rotate90, Rotate180, Rotate270. Auch 🙁

Using gif or video controls slipped through my mind but I quickly kill those ideas – I wanted to have control over rotation speed of the plane which both controls cannot provide.

Using pure math to compute movement of the image by the circle was also not an option because it will only simulate the movement and not the rotation.

Finally (why the best ideas are always the last one?) I thought that maybe I can use HTML control with img tag in it and rotate the image using html and css manipulation.

But before we start implementing, let’s plan our work – it’s very important to follow the plan.

  1. Add HTML plane img to screen
  2. Change plane rotation manually just to understand html/css rotation logic
  3. Add rotation logic to the plane OnSelect()

Step 1: Add HTML plane img to screen

Click Text > HTML Text

Set its paddings to zero, size to 80 x 80 (I want the image to be of that size and also that the size of the whole control was the same as the size of the image) and in HTMLText property write following code:

"<Img src='<your img URL here>' style='width:80px;height:80px;'>"

Please note that not every kind of URL you can put as <your img URL here>. For the src attribute value of the Img tag in HTML Text control remember following:

  • Provide absolute URL to your resource
  • resource must be publicly accessible
  • Redirections are not supported
  • Authentication is supported

Below URLs WON’T work:

  • appres://resources/plane-icon
  • https://contoso.sharepoint.com/:w:/g/SADQWEDRmfJIh5AdjoFBgWoBV9pJ_uvIX-3Vw2Ag8y90Mw?e=fTuaxu

Below URLs WILL work:

  • https://contoso.pl/wp-content/images/plane-icon.png
  • https://contoso.sharepoint.com/SiteAssets/plane-icon.png

If you put proper URL for your img you should see something like this

To remove a slider that you can see on the right side of the HTML Text control just add to style attribute “position:absolute”

"<Img src='https://mgtrainings.sharepoint.com/SiteAssets/plane-icon.png' style='width:80px;height:80px;position:absolute;'>"

Step 2: Change plane rotation manually just to understand html/css rotation logic

We have our HTML Text control that displays image but how to rotate it. In CSS there is a property called transform that allows for rotating, skewing and scaling the object. Let’s try something simple now and make our image to rotate 90 degrees:

Hm, that is some progress but as you can see the image rotates relative to its center. We will deal with that in a second but first let’s take care of one more thing – make the rotation dependent on a variable value.

To do that we want break the HTML code string in the place of degrees numeric value and replace that numeric value with our variable. Following code shows how to do it:

"<Img src='https://mgtrainings.sharepoint.com/SiteAssets/plane-icon.png' style='width:80px;height:80px;position:absolute;transform: rotate("&PRotation&"deg)'>"

Remember to initialize PRotation variable (using Set() function ie. in Reset button). In below gif you can see the plane rotation dependent on a slider value.

Now we can get back to problem of rotation relative to the center of the image. What we want is that the airplane image moves by the circle and rotate accordingly to its position relative to the center of that circle path. One way will be to use mathematical formulas for movement computations. Although it should be absolutely achievable, the synchronization of the movement and rotation sounds like a juggling with too many balls at the same time to me. I don’t want to stuck in one problem for days – at least not for now :). Is there another way of solving that problem? Of course there is and once again we can use CSS for that!

In CSS there is another cool property called transform-origin which move the center relative to which the rotation will take place. We want rotation to take place relative to end of the right wing of the plane. Let’s implement below code and observe outcomes

"<Img src='https://mgtrainings.sharepoint.com/SiteAssets/plane-icon.png' style='width:80px;height:80px;position:absolute;transform: rotate("&PRotation&"deg);transform-origin: right'>"

Ah, rotation is happening but our plane move off the HTML Text control borders. Let’s change its size to 160 x 160 (double the size of the image)

Hm, not bad, but plane still disappears above top border. So let’s move it away from the top border by 40px (half of its size)

"<Img src='https://mgtrainings.sharepoint.com/SiteAssets/plane-icon.png' style='width:80px;height:80px;position:absolute;transform: rotate("&PRotation&"deg);transform-origin: right;top:40px'>"

GREAT! Now it’s rotating! Perfec…oh, hold on a second. See how tips of the wings are cut off by HTML Text control borders on rotation?

Do you have any idea why it’s happening? Think for a second before continue reading.

Ready?

So this cutting off happens because of the relation between square shaped image, the center of the circle path and its radius. The easiest form of explanation will to draw these relations:

Red square is double the size of plane image and illustrated the HTML Text control borders we have currently implemented. Red circle radius indicates the most far point of the image that will be visible at any time of the rotation. You can clearly see the cutting there on the tip of the planes wing. In such situation ideally will be to enlarge HTML Text size up to the green square size (math formula: a*sqrt(5)/2, where ‘a’ is the size of the side of the square). However in our case we need only few more pixels. So let’s do following:

  • Add 5px padding on each side of the HTML Text control
  • Add 10px to the HTML Text control width (170 now)
  • Add 10px to the img style property (top:50px)
  • Add 20px to the HTML Text control height (180 now)

Result:

HAHA! Awesome! Now take a break, go run, eat some fruits and come back. Last part of the animation development process ahead!

Step 3: Add rotation logic to the plane OnSelect()

Now we need to combine above rotation animation with the whole plain movement.

Open up your plane’s OnSelect() function to remind you its logic: near-edge behavior, move and bounce logic. Now we want following changes to take place:

  • when plane hits border stop moving and start rotation procedure
  • Until the plane makes 180 rotation total do the following:
    • don’t move (of course)
    • rotate for a value of SpeedSlider
    • stop rotating once reach 180 degrees or 360 (watch out for unwanted crossover – don’t repeat the mistake from movement logic. Learn 🙂 )
  • Continue movement

Changes in the code we’ll begin from creating rotation logic. It will be very similar to the move logic so the only lines that requires explanation is the second line and the last one. Add below code right after first line of code where you set VSpeed variable.

Set(Rspeed,SpeedSlider.Value);
If(isRotating&&Mod(PRotation,180)<>0,
//ROTATION
//Protection from crossing over the full U-turn
Set(Rspeed,Min(180-Mod(PRotation,180),Rspeed));
//Rotate
Set(PRotation,PRotation+Rspeed),
//Continue movement
Set(isRotating,false));

Variable named isRotating is a flag that indicates if the plane is rotating or not. If it’s ‘true’ it’ll keep rotating. Once we finish rotation we set our flag to ‘false’. How do we know when we finish? This is why
Mod(PRotation,180)<>0 condition is for. Function Mod returns the remainder after a number is divided by a divisor. If the PRotation value is equals to 180 or its multiplications, the function will return 0 and the condition will be false. With one simple function we cover 2 u-turns. I like such code 🙂

All the remaining code we can safely put inside If(!isRotating,(…)) block.

If(!isRotating,
Set(Dist2Top, planeY-BoardBorders.Y);
Set(Dist2Bottom, BoardBorders.Y+BoardBorders.Height-planeY-imgPlane.Height);
//Near-edge behaviour
If(PDirection=180, Set(VSpeed,Min(VSpeed,Dist2Bottom)),
PDirection=0, Set(VSpeed,Min(VSpeed,Dist2Top)));
//Move 
If(PDirection=0,
Set(planeY,planeY-VSpeed),
Set(planeY,planeY+VSpeed));
//Bounce behavior
If(Dist2Top = 0,Set(PDirection,180));
If(Dist2Bottom = 0,Set(PDirection,0)))

You may ask yourself now – “why didn’t we just throw away this isRotating variable and just put the whole move logic on false result of the first if condition (in the place of where Set(isRotating,false) line is)?”. We can! But for the cost of code readability in my opinion. I like to keep code that is self explanatory so me or someone else understand it even after months of not reading it. From my perspective comments are not always enough and such flag (and extra if condition) perfectly do the job. But of course – do as you like :).

Last thing that left is to adjust bounce behaviour. We need to set isRotating flag and give a plane first rotation push (otherwise Mod(PRotation,180)<>0 will never be true). Swap last 2 lines of your code as follow:

If(Dist2Top = 0 && PDirection = 0,Set(PDirection,180);Set(isRotating,true);Set(PRotation,PRotation+Rspeed));
If(Dist2Bottom=0&&PDirection = 180,Set(PDirection,0);Set(isRotating,true);Set(PRotation,PRotation+Rspeed)))

The “&& PDirection = 0” extra condition is to secure our plane from entering If block twice – when the rotation start and just after it ends (when the plane should continue moving forward).

Your whole code should looks like similar to this:

Set(VSpeed,SpeedSlider.Value);
Set(Rspeed,SpeedSlider.Value);

If(isRotating&&Mod(PRotation,180)<>0,
//ROTATION
//Protection from crossing over the full U-turn
Set(Rspeed,Min(180-Mod(PRotation,180),Rspeed));
//Rotate
Set(PRotation,PRotation+Rspeed),
//Continue movement
Set(isRotating,false));

//MOVEMENT

If(!isRotating,
Set(Dist2Top, planeY-BoardBorders.Y);
Set(Dist2Bottom, BoardBorders.Y+BoardBorders.Height-planeY-imgPlane.Height);
//Near-edge behaviour
If(PDirection=180, Set(VSpeed,Min(VSpeed,Dist2Bottom)),
PDirection=0, Set(VSpeed,Min(VSpeed,Dist2Top)));
//Move 
If(PDirection=0,
Set(planeY,planeY-VSpeed),
Set(planeY,planeY+VSpeed));
//Bounce behavior
If(Dist2Top = 0&&PDirection = 0,Set(PDirection,180);Set(isRotating,true);Set(PRotation,PRotation+Rspeed));
If(Dist2Bottom=0&&PDirection = 180,Set(PDirection,0);Set(isRotating,true);Set(PRotation,PRotation+Rspeed)))
Completed plane animation with u-turns

WOW! That was hell of the road but we made it! You made it! If you follow all the steps up to here I’m really really thankful and proud of you. You are awesome!

Here you can download my project with finished planes animation with some extras! I’ve added a screen with multiple planes and movable
boarder bottom border. When you play it and start to move bottom border you’ll observe interesting thing – an animation slows down and sometimes even lag spikes

If you have enough energy you can read next section…or just read it later 🙂

Performance in animations

So you probably you already know that PowerApps is using declarative code. Declarative means that the code expects results instead of giving instructions (orders) of how to do something (this is imperative). But I feel that in PowerApps declarative also means that it doesn’t really like a controller alike pattern where one object activates other object(s) (like we did in timer control which select plane on timer end).

I’ve googled a bit looking for some solution and found this video where Brian (a.k.a. @8bitclassroom) built a simple animation of shooting space ship. He used observer pattern where each projectile object (kept in gallery) has it’s own timer and object behavior depends on it in a way like projectile Y property uses timer.value/timer.duration for its position calculations. So I’ve built copy of his approach and must admit that his method was very effective and even tens of shots did not slow down the whole animation (note that no object is removing once reach top)

Oh, as a side note – feel free to download also this app with comparison from here.

To verify my theory I’ve build the same app but this time I use my method (each projectile was a gallery item and each item has its own timer and image. On every timer end the projectile was selected). Additionally once the projectile reach the top I removed it from collection so I can be sure that it’s not the ongoing timer that is slowing down animation. And as you can see my app is still lag spiking

Taking 20 shots comparison

You can clearly see that’s even though the total number of shots in my method is smaller the animation is still slowing down. It must be the Select() method and the architecture of my app where one object calls another.

So how to fix the plane animation app? Honestly – I’m not really sure. You see, declarative dependency for projectiles is generally easy to do – one direction, straight line, simple computation. Unfortunately when looping movement comes to play, especially such with u-turns I feel a bit confused how to implement that. Put all the code in Y property function? Then how/where to set the rotation value?

If you have any idea please share it in comments below – I will gladly check it.

Phew, I know, this was a loooong blog post. Nevertheless I hope you enjoyed it, took your value from it, learn something new and now know PowerApps even better!

Have a great day and happy PowerApps coding 🙂

How to overcome 500 items limit in PowerApps

I planned to publish another topic for today but I decided that this one will fits better to the post I’ve published a week ago.

So if you work with PowerApps connected to ANY data source you had to experienced or at least read about 500 items limitation. In this post I will show you 6 ways how you can exceed that limitation.

Limit? What limit?

If you’re one of those happy devs that have never heard of such limit I’ll make quick reminder:

In PowerApps every data source (SharePoint, Common Data Service, OneDrive) is under limitation of 500 items. It means you cannot get more that 500 items from a data source but even more than that – PowerApps won’t even “be aware” of any rows above 500.

Imagine simple case: On your OneDrive for Business you have an excel file with ~600 rows. Even though you need only last 10 rows you will get rows 490-500 instead of 590-600. As I told you in previous paragraph – it’s not the limit of total fetched items. It’s the limit of items that PowerApps knows about.

But before you think that’s a killer for PowerApps and “oh those architects, they’ve never use their tools so how they can design something that actually works” let’s imagine another example:

You have an excel with 2mln rows (yep, I saw over 2GB excel file in one company from financial sector). One of app users opens your app and…probably in an eyeblink you will get tons of emails that your app is not working (but of course it is working but the loading time is huge). That’s why this limitation make sense.

Learn by reading is great. Learn by practicing is even better. Download my Delegation Learning App and start practice delegation now!

Anyway there are many occasions where you will need more than 500 items. Maybe not 2mln but a simple task list for a company with ~1000 employees can easily generate around 50000 rows in total. So how you can overcome this 500 items limitation in PowerApps? In following sections I describe 6 options in the order from easiest/fastest to the most powerful (but also time consuming to setup):

  1. Increase the total limit items you can fetch
  2. Use static data
  3. OneDrive for Business connector specifics
  4. Use delegation
  5. Use delegation + iterative function
  6. Combine PowerApps with Flow

1. Increase the total limit items you can fetch

Okey so this method is the easiest and really quick to setup. Click file (top left corner) > App settings > Advanced settings > set value for non-delegable queries.

This method has one hard limit (limitation of the limitation) – 2000 is a maximum value you can set which means you can’t get more that 2000 items on the same rules as for 500 items (PowerApps won’t know about 2001 item). So if this method does not work for you let’s move to option 2.

2. Use static data

In some specific cases static data may be the best solution. Such data can be imported to your app and will be kept within assets of your app. You can have 10000 rows and still users will have access to all rows. However remember following notes:

  1. Static data are static – you cannot modify it from within PowerApp. But for some scenarios it may still do the job i.e. you build company travel app and you want to have index of all countries in the world (192) along with bigger cities (~30 x 192=5760) so your users can search and select to which city they’re traveling to. Countries and cities typically don’t change dynamically so we can freely import them as static data instead of using SQL database (for which you have to spend extra cash).
  2. Static data are attached to your app which means they’re enlarging total size of app which may affect app loading time.

If you still think static data is a best choice for you here is how you can add it:

  1. View > Data Sources > Add data source
  2. Click Import from Excel
  3. Select excel file and a its table that you want to import

Ok, now, what if this option also doesn’t suit to your needs: Excel is good but you need to be able to modify it from within app so all app users have access to same data. So you googled and decided to use OneDrive for Business data source connector. It uses excel file as a container which data can be modified, sounds perfect, right? So here is the deal…

3. OneDrive for Business connector specifics

Accessing Excel files using OneDrive for Business connector does not support delegations (I’m explaining delegations in section 4). Long story short here are the implications:

  • You can’t get more than 2000 rows
  • OneDriveForBusiness connector treats each table in excel file as separate connection. The 2000 limit is related only to a connection – not the source.

So if you know you may need more than 2000 rows stored in one data source you should switch from OneDrive for Business to any other like SharePoint, Common Data Service, SQL etc. and take use from delegable queries. What are these? Let’s see.

4. Use delegation

Delegation is a mechanism to access all data from a data source in a performance friendly manner.

Speaking a bit more clearly it’s a situation when your PowerApp app says to a data source:
“Ok, you know what, I need items that match these conditions but hey, can you do all the computations by yourself? I need to use my network bandwidth, memory and CPU power for something else…I just need results.”

Now, there are 3 catches:

  1. All results are fetched in a maximum of 100 items bundles. The next bundle is being fetched once a user scroll to the end of a gallery/table list (check out delegation demo gif down below)…
  2. …which means if you need to fetch more than 100 items (! not process but fetch. You can process 10000 of items but fetch only 15 of them as result) you can only use gallery or table. Collections are not supported and works under non-delegable queries limitation (Collect() or ClearCollect() functions breaks delegation!)
  3. Your query need to be supported by the data source. Figuratively speaking the data source must understand what PowerApps app is saying to it (check out example queries on the image down below). To complicate it a bit more not every data source supports all functions and operations – each data source connector documentation outlines delegable support (i.e. here you can find SharePoint Online delegation support). For better understanding of delegations in PowerApps I recommend you to read delegation documentation.
Delegation demo. Left gallery is sourced by OneDrive for Business (which is non-delegable). Right gallery is sourced by SPO list with 500+ items.
Look what happens once I scrolled to the bottom of the SPO items gallery.
Example delegable and non-delegable queries in PowerApps

Ok but what if need more than 2000 items here and now? There are 2 options for doing that. But before you read them please consider below:

Following approaches should be treated as potentially bad practices and should be used only under specific circumstances and with proper caution since they may have bad impact on your application performance or other O365 tenant services. It’s like with medicines – they can solve your problems but harm you in wrong dosage or used inadequate to needs. So in 95% of cases delegation will perfectly do the job. It may requires read & learn a bit but trust me – it’s easier than struggling with performance issues that may appeared if you misuse options 5 and 6.

Ok, so now we can safely move to options for those 5% of cases 🙂

5. Use delegation + iterative function

General idea is like this: for a delegable query build a loop and in each iteration filter data chunks. I.e. in 1st iteration you get rows 1-500, in 2nd 501-1000 and so on.

I won’t explain it in detail or show you code snippets because this method is not mine. Its author is MS employee Brian a.k.a Mr.Dang() and I would feel bad if I get his credits for this workaround. Read it and just in case it won’t meet your needs…

There is also another way of which I’m the author. Option 6. Here it comes.

6. Combine PowerApps with Flow

The last option is to use Flow as a middle man that was asked this:
“Dear Flow, since your data source connectors don’t care about this whole delegation stuff, can you please do me a favor, get all rows, join them in 1 big string and send me back please?”

A simple demo with excel and outlook email may look like this:

To make this work for more than 256 results I had to make few configuration tweaks of the “List rows present in a table” action:

Fetching 682 rows took 3 seconds.

Fetching 2101 rows took 11 seconds so that may be an issue.

However if you use SharePoint as a data source I have a good news for you – I’ve made a demo that fetches 1000 items in 2 seconds. In this blog post I describe step by step how to:

  • Build a PowerApp from scratch
    • Add input fields
    • Pass input fields values to Flow
    • Parse results from flow
    • Display all results in a gallery
  • Build a Flow from scratch
    • Get parameters from PowerApps
    • Integrate Flow with SharePoint
    • Send all results back to PowerApps

And that’s it! I hope you enjoy this blog post. If so please let me know in the comments down below. If not or maybe you know a better solution – let me know as well!

Performance – is Flow faster than PowerApps?

In this post I’ve showed you how you can create PowerApps app that utilize SharePoint Search for your business. I used there PowerApps for parsing big string containing our results from SharePoint Search.

But one o my readers asked me an interesting question

“Mike, wouldn’t it be easier and faster if we parse results in Flow and pass to PowerApps just raw field values?”

Jacek M.

Hm….at that time I didn’t know the correct answer. Neither I could find it in the PowerApps Canvas App Coding Standards and Guidelines . That’s why I’ve decided to make quick test on my own and publish its results on my blog.

Time measurement 1: parsing on PowerApps app side

It’s pretty straight forward to measure parsing time for the application we’ve built in my previous post. Just add timer control, start the timer before firing flow, fetch results from flow, parse it and stop the timer right after it. It will look like this:


I’ve setup my test to fetch 10 results. Result of it: 1 second! 1 second is the time that PowerApps need to fetch 10 results from flow and extracting required field values (Title in this case). But that was not enough to me. I thought: “let’s see how long will it takes to get 100 results and extract 4 fields”. The results surprised me. 1 second again!

3 of 100 fetched items from SharePoint Search via Flow. PowerApps extracted 4 fields from received string: Title, URL, Ranking Sum and UID of the item.

It will be very hard to beat it but let’s give it a shot! Let’s check how Microsoft Flow will manage to extracting field values.

Time measurement 2: parsing on Flow side

Disclaimer: John Liu in this tweet proved I was wrong. My double loop in flow was quite a bad idea..both from conceptual and performance perspective. He’s built a flow which was 15x faster than mine! This means PowerApps is not 30x but “only” 2x faster than Flow…and that also I need to take some flow building lesson 😉

To cheer myself up I can only say:
those who do nothing makes no mistakes.

First let’s start from small test that is fetching 10 items and only Title field. The modification of the flow is simple: I will iterate through each row of the SP search results, then through each cell in a row (each row contains multiple cells) and then once I find the cell I’m looking for (Title in this case) I’ll append cell value to an array variable. Modified flow will look like this:

Parse Cell action allows for using Key, Value, ValueType attributes in farther actions. The Join action that is in the bottom of the screenshot is used to convert array to text value (because PowerApps does not accepts arrays 🙁 ).

For start I will just take 10 results and extract only Title field. Let’s test this configuration:

WHAT?! 32 seconds!? I must admit – I expected that Flow will be slower that PowerApps but not ~30 times (it’s obvious why it’s a common pattern to use your end user CPU time, via browser or mobile memory, rather than your own) . That’s huge difference and strong argument for using PowerApps for all kind of parsing and any CPU expensive calculations and not using Flow for that.

Further testing (more fields, more items) has no point. Let’s jump right to the conclutions.

Conclusions

Personally I find this small test really informative.

First: It’s more efficient to use PowerApps for calculations that Flow. It’s worth to tell it even if most of us know that common good practice is to distribute calculations and delegate it to user device.

Second: In the light of above note, consider making more than 1 call to Flow and back to make most of the expensive calculation on the PowerApps side for the sake of whole process time consumption. E.g.:

  1. PowerApps asks Flow to get results from SPO Search
  2. Flow get results and pass it back to the app
  3. PowerApps extract some data (specific fields, values range, values sum etc) and pass it back to Flow
  4. Flow gets additional data for selected items or fields and pass it back to PowerApps again
  5. PowerApps display results to user
Check how I can help you or contact me.

What do you think about that? Does this test was also informative for you? Or maybe I missed something that may change whole results – let me know!

Search in SharePoint from PowerApps

Inspired by this post I realized that built-in connectors does not allow to utilize SharePoint Search. So theoretically it is not possible to use SharePoint search from PowerApps. However using Flow as a middle layer you can call SharePoint Search and parse results for your needs. Let’s see how to do that.

Why you may find SharePoint Search useful in your app?

Let’s consider is it worth to have such possibility as using SharePoint Search from your PowerApps app. To answer this question let’s see what we can and cannot do using built-in connectors.

You can:

  • Get content of any list or library in a single SharePoint site
  • Read, Update, Delete elements of list or library
  • Use specific subsets of items (filtering, sorting) in delegate manner
  • Get content of Office 365 Groups
  • Get OneDrive content (only excel files)

You cannot:

  • Find all items of a specific Content Type across whole tenant
  • Get elements from list or library in a site – even if the list or library was created after the connector has been declared
  • List SP sites in your tenant
  • Get OneDrive for Business content (all kind of)

All above are limitation coming from OOB SharePoint connectors. So if you want workaround them this post will tell you how to achieve it.

PowerApps SharePoint Search Architecture

We need 3 components:

  • PowerApps: acts as front-end, provides screen and controls to user and also display results,
  • Flow: acts as back-end, call SharePoint search (also keeps SPO url) and pass results to PowerApps
  • SharePoint: contains all the data of course

Those components will communicate each other in following manner:

  1. PowerApps uses Flow connector to make a call to it
  2. Flow uses “Send an HTTP request to SharePoint” and then…
  3. …parse returned result to JSON
  4. Flow join all rows as string variable and pass it as to PowerApps as respond
  5. PowerApps filters out the data it needs

Let’s start building it! To not make this post too much long I’ll focus only on most important part skipping parts like header creation or pagination functionality.

Follow this link to download complete demo app (remember that url to SPO is in the flow)

Step 1: Create a PowerApp

First we need to create an app, add screen to it and add controls to the screen (if you don’t know how to do any of those please check out this post). Some parts that may need additional explanation:

  • numRowLimitNumber – number of items to return
  • btnCallFlow – this is invisible button that call Flow on OnSelect event. Other controls (like previous/next page buttons) can fire it using Select() function instead of duplicating code in it.
  • galSPSearchResultItems – gallery displaying results items
    • imgOpenInNewWindow – clicking it will open SharePoint item in browser
    • lblSPItemTitle – displays item Title
    • lblSPItemUniqueId – displays item UniqueId
    • lblSPItemRank – displays item ranking sum. Because why not 😉

Now let’s configure behavior of our controls.

  • On OnSelect action of the “Search” button put as follow:
Set(gblItemsToSkip,0);
Set(gblPaginationVisibility,false);
Select(btnCallFlow)
  • As you can see it fires btnCallFlow. On OnSelect action of that button we will call the flow
Clear(colSPSearchResultItems);
Set(
    gblRowLimitNumber,
    numRowLimitNumber.Text
);
Set(
    gblSearchResults,
    Searchforitemsintenant.Run(
        txtSearchPhrase.Text,
        numRowLimitNumber.Text,
        Text(gblItemsToSkip)
    )
);
<HERE WE WILL PUT CODE TO PARSE SP SEARCH RESULTS>
Set(
    gblPaginationVisibility,
    true
)

Now let’s add flow to our app. To do that click:
Action tab -> Flows button -> “Create a new flow”

Step 2: Create a Flow

On Microsoft Flow side add variables that will keep input parameters from PowerApps. Then add “Send and HTTP request to SharePoint”.

The Uri attribute contains function that replace apostrophe (‘) to empty sign in case user put ‘ in the search phrase (accidentally or intentionally) which will break the query:

_api/search/query?querytext='@{replace(variables('SearchQuery'),'''','')}'&clienttype='ContentSearchRegular'&selectproperties='UniqueId, Title, OriginalPath'&rowlimit=@{variables('RowLimit')}&startrow=@{variables('RowsToSkip')}

Next add Parse JSON with Body output in Content field and a Schema.

The schema however is a bit tricky because using sample (i.e. from search REST call using browser or Postman) may not include all variations of results (that was my case). So after quick debugging I’ve successfully created following schema – feel free to copy-paste it.

{
    "type": "object",
    "properties": {
        "odata.metadata": {
            "type": "string"
        },
        "ElapsedTime": {
            "type": "integer"
        },
        "PrimaryQueryResult": {
            "type": "object",
            "properties": {
                "CustomResults": {
                    "type": "array"
                },
                "QueryId": {
                    "type": "string"
                },
                "QueryRuleId": {
                    "type": "string"
                },
                "RefinementResults": {},
                "RelevantResults": {
                    "type": "object",
                    "properties": {
                        "GroupTemplateId": {},
                        "ItemTemplateId": {},
                        "Properties": {
                            "type": "array",
                            "items": {
                                "type": "object",
                                "properties": {
                                    "Key": {
                                        "type": "string"
                                    },
                                    "Value": {
                                        "type": "string"
                                    },
                                    "ValueType": {
                                        "type": "string"
                                    }
                                },
                                "required": [
                                    "Key",
                                    "Value",
                                    "ValueType"
                                ]
                            }
                        },
                        "ResultTitle": {},
                        "ResultTitleUrl": {},
                        "RowCount": {
                            "type": "integer"
                        },
                        "Table": {
                            "type": "object",
                            "properties": {
                                "Rows": {
                                    "type": "array",
                                    "items": {
                                        "type": "object",
                                        "properties": {
                                            "Cells": {
                                                "type": "array",
                                                "items": {
                                                    "type": "object",
                                                    "properties": {
                                                        "Key": {
                                                            "type": "string"
                                                        },
                                                        "Value": {
                                                            "type": [
                                                                "null",
                                                                "string"
                                                            ]
                                                        },
                                                        "ValueType": {
                                                            "type": [
                                                                "null",
                                                                "string"
                                                            ]
                                                        }
                                                    },
                                                    "required": [
                                                        "Key",
                                                        "Value",
                                                        "ValueType"
                                                    ]
                                                }
                                            }
                                        },
                                        "required": [
                                            "Cells"
                                        ]
                                    }
                                }
                            }
                        },
                        "TotalRows": {
                            "type": "integer"
                        },
                        "TotalRowsIncludingDuplicates": {
                            "type": "integer"
                        }
                    }
                },
                "SpecialTermResults": {}
            }
        },
        "Properties": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "Key": {
                        "type": "string"
                    },
                    "Value": {
                        "type": "string"
                    },
                    "ValueType": {
                        "type": "string"
                    }
                },
                "required": [
                    "Key",
                    "Value",
                    "ValueType"
                ]
            }
        },
        "SecondaryQueryResults": {
            "type": "array"
        },
        "SpellingSuggestion": {
            "type": "string"
        },
        "TriggeredRules": {
            "type": "array"
        }
    }
}

At the end, we just have to join rows (using some special separator like “/n” which is unique enough) and pass it back to PowerApps along with totalRows number

If you did everything properly your Flow should look like this

Now we can get back to PowerApps and parse the results.

Step 3: Parsing results in the PowerApps

Flow pass result to PowerApps in a string field (called “results”). But we cannot use it in a gallery right away. Just look at it:

{"Cells":[{"Key":"Rank","Value":"17.1283779144287","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17594717461461","ValueType":"Edm.Int64"},{"Key":"Title","Value":"Michał Guzowski Team Site","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595142086123","ValueType":"Edm.Int64"},{"Key":"Title","Value":"pl","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595383574480","ValueType":"Edm.Int64"},{"Key":"Title","Value":"en","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595390074495","ValueType":"Edm.Int64"},{"Key":"Title","Value":"fr","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595412154536","ValueType":"Edm.Int64"},{"Key":"Title","Value":"da","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595412284536","ValueType":"Edm.Int64"},{"Key":"Title","Value":"de","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595451564436","ValueType":"Edm.Int64"},{"Key":"Title","Value":"sv","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595498094501","ValueType":"Edm.Int64"},{"Key":"Title","Value":"lv","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595513184435","ValueType":"Edm.Int64"},{"Key":"Title","Value":"ro","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0754928588867","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17594717461464","ValueType":"Edm.Int64"},{"Key":"Title","Value":"How To Use This Library","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}

How to extract specific parts of that?

  • Split string with our special delimiter: “/n”
    • For each string part we split it once again using following scheme:
      • “_FieldName_”,”Value”:
      • I.e. for Title field it will looks like this “Title”,”Value”:
      • Also we need to encode quote mark (“) so example after encoding looks like this: Char(34)&”Title”&Char(34)&”,”&Char(34)&”Value”&Char(34)&Char(58)&Char(34)
    • We take second element (above split always returns only 2 elements) and split one more time using following scheme:
      • “,”ValueType
      • After encoding it look like this: Char(34)&”,”&Char(34)&”ValueType”
    • We take first element (above split always returns 2 elements) and add it as value of a specific column.

Wow, felt a little dizzy? Don’t worry. Just copy below code, paste it in your app and replace name “Title” with field name you want to extract.

ClearCollect(
    sposearchitems,
    AddColumns(
        Split(
            Items,
            "/n"
        ),
        "Title",        First(Split(Last(Split(Result,Char(34)&"Title"&Char(34)&","&Char(34)&"Value"&Char(34)&Char(58)&Char(34))).Result,Char(34)&","&Char(34)&"ValueType")).Result
    )
)

In my case (for fields UniqueId, Title, OriginalPath and Rank) my parse operation looks like this:

Step 4: Display results

Finally! We made up to this part. Just set the values of labels in the gallery and enjoy the results!

For me the final result looks like below but your can be totally different (and better than mine ;] )

THAT’S IT! Wasn’t that hard, was it? 😉 If it was, remember that you can download my Demo SharePoint Search PowerApps application to compare or just copy paste some parts – just click here!

Hope you find this post useful. If so please share it and comment if you like.

Thanks and have a great coding 🙂

Update 22.02.2019 – If you’re curious if you can use Flow for parse computations I’ve done such comparison in my next blog post.

26.02.2019 – O365 User Group PL Warszawa #1

Breaking news: I will be a speaker on the first O365 User Group PL Warszawa meeting. Woohoo!

I will tell about how to build smart app that enthused MVPs and Microsoft employees around the world (also known as “the story of 1 tweet” 😉). But I’m just a shadow of the others: hosts Michał Słowikowski and Kamil Bączyk and another speaker: Microsoft Teams Product Group representative: Jace Moreno !

O365 User Group PL Warszawa #1

Tuesday, Feb 26, 2019, 6:00 PM

Microsoft Polska
al. Jerozolimskie 195A Warszawa, PL

62 Członkowie Went

Zapraszamy na inauguracyjne spotkanie grupy Office 365 w Warszawie. Jako, że mamy wyjątkową okazję to sesje i agenda również takie będą. Podczas tego spotkania poruszymy tematy związane z platformami Teams oraz PowerApps. Agenda: 18:00 – 18:15 Intro 18:15 – 19:05 Unleashing the Power of the Microsoft Teams Platform 19:05 – 19:15 Przerwa 19:15 – 20:…

Check out this Meetup →

Enable Whistleblowing in your Office365

PowerApps can participate in organization modernization in many ways. And I’d like to show you some example. Imagine following scenarios:

  • You’ve seen how a colleague sends confidential documents to his private email. Or…
  • By accident, you heard two colleagues talking about “dorabianiu” at the expense of the health of patients by ordering unnecessary radiological examinations. Or…
  • In the network, one of your colleagues has published an offensive text that hits the brand of the company in which you work. Or…
  • You and a few colleagues from work are the object of mobbing and microaggressive behaviors

I hope you don’t know what I’m writing about but unfortunately many of the above situations really happened: example1, example2, example3.

Such incidents may put significant questions marks on your organization reputation, reliability and honesty followed by financial penalties depending on the seriousness of the offense. So to protect your organization Microsoft provides multiple different tools such as Azure RMS (Azure Rights Management), DLP (data loss prevention) and retention policies. But non of those tools gives you an easily accessible way to pass on information concerning wrongdoing in safe and anonymous manner. That inspired me to create a solution that will fill the gap.

Enable whistleblowing

72 per cent of Canadian survey respondents recognize cyber crime as a risk, many still don’t fully understand the potential impact a cyber breach can have on the business

Financial Post article

The original article of the above quote also noticed that enabling whistleblowing allow for early identification of issues and is critical for ability to manage risk. That inspired me to create a solution integrated with O365 that will respect user anonymity – at least on the data access level.

I’m a huge fun of PowerApps but unfortunately they do not allow for guest access (at least not yet! 😉) and I was wondering if that’s hard limitation. Maybe there is some workaround? There must be. And with a small help of Microsoft Flow I was able to create a solution that:

  • Allow for anonymized creation of new submission
  • View, Edit ones submissions in anonymized manner
  • Correspond with assigned admin in anonymized manner
  • As Admin you can access all submissions as well as filter submissions by status (i.e. only those submissions that waits for your action)

Power Whistleblowing app – user view

This is animated view – If gif is not animating click here

Another view (this time just a screenshot)

Power Whistleblowing app has also an admin view

This is animated view – If gif is not animating click here

Power Whistleblowing architecture

The architecture of above solution is simple:

  1. PowerApp gets information from a user and pass to flow. On this stage everything is personalized. We know who send what.
  2. Flow pass over HTTP request to another flow with parameters of newly created item (for submission it’s: Title, Description, Category; for Comment it’s: Author GUID, Submission GUID, Comment). This is the moment where we lost all context information (except data that are essential for the business logic) and imply anonimization
  3. For newly created submission we need to generate Author GUID (I’ll explain later what is its role) and pass it back to first Flow.
  4. For newly created submissions the Flow expects the Author GUID and pass it back to PowerApps app
  5. Both for new submission and new comment all information are saved in SharePoint Online impersonated as Service Account. We don’t know who is original creator of the record, we only have some Author GUID

Why do we need Author GUID?

Author GUID is the new credentials for submitter to:

  • Check status of his submissions
  • View all his submissions
  • Leave a comment in any of the submissions (to correspond with the admin)

I find this solution really useful for an organization – what do you think? Leave me a comment! Oh and also feel free to ask freely on any other topics i.e.:

  • How to create HTTP connected Flows
  • Is it possible to build admin panel with vertical tabs (yep, it’s tricky 😉)
  • How to build Regular Expression to check GUID cohesion

I don’t bite but do drink beer. You can also catch me on my fb, twitter, linkedIn or PowerUsers forum.

Just in case you want to:

  • Deploy this solution on your environment
  • Customize it for your own needs
  • Create new solution based on this one

Contact me and I will help you

…Oh, and I have a small gift for all of you that read until now – you can download this solution here. Sharing is Caring. Enjoy!

To CDS or not to CDS: that is the question

Common Data Services is an interesting service that is in the hands of Flow, PowerApps and PowerBI. Today I read an interesting blog post by Pieter Veenstra that inspired me to write this post.

What is Common Data Service?

CDS is a service that allows you to store data in a relational model integrated with Dynamics365 (because it comes from it), but also (and maybe most of all) PowerApps, Flow and PowerBI. This service allows you to securely store and manage business application data from the cloud. You can read more about CDS here.

For me, however, CDS is a defective relational database. In fact, we can connect to it from the level of the above platforms, but its management is unfortunately very limited:

  1. No access to the server on which this database stands. So we can not add resources to make it run more efficiently, as we can do for SQL server (adding space / CPU / memory) or DTU for Azure SQL Db.
  2. No access to data container. So, we can not, for example in the case of the SQL database, optimize its operation by changing the auto-shrink setting, reorganize the indexes in tables, etc.
  3. Limited access to the created OOB entities. That is, we can do everything (CRUD) with Custom entities and Custom fields, but with Standard entities / fields it is not so easy. We can expand them, but you can not remove them.
  4. Many functionalities are accessible only through API. So if you have not coded in C # or WebAPI or have already forgotten how to do it, you will not configure functions such as auditing entities or fields unfortunately. However, if it does not scare you, I recommend this part of the documentation explaining how to work with the API.
  5. CDS does not convince to itself in terms of efficiency. At the beginning of this post I referred to the Pieter Veestra’s post , who made a very interesting comparison of the performance of the Read-Write operation between the CDS and the SharePoint list on his blog. In this comparison CDS not only was slower, but also had a limit limited to 5,000 items on the list.

It’s worth to use CDS anyway!

Reading the above 5 points one can gets the impression that it is best to quickly forget about Common Data Services and focus on other data sources. But it does not have to be that way. What is on the one hand a nuisance to CDS is simply its characteristic feature on the other.

CDS is available OOB and is not intended to be another data source that requires configuration. It’s here, ready to be used just like that. Now. As it is. Simple and handy relational database(s).

And what about its performance? Remember that GA of the Common Data Services took place in November 2016. It was then a very basic product (I would say the beta of the MVP of the product) without the possibility of creating complex relationships between entities or defining option sets (which is possible from January this year). In this light, CDS is still a relatively young product. Observing the way of launching new products by companies such as Microsoft, and also having myself some experience on the startup market, I know that no serious company will take risk of making complete and final product from the start, and the process of maturing the solution takes at least a few years. The requirement to indicate the direction of development of a new product, repair its defects (eg efficiency) and confirmation of its actual usefulness is … using it by customers!

Use it consciously

Right, “use it consciously”. But what if the product is still not mature and its configuration is uncomfortable or impossible? Answer is: Use it wisely. Use the Common Data Service taking into account its advantages and disadvantages. Use wherever it is possible (and there is customer approval for extra licenses – Plan 1 for applications using CDS, Plan 2 for managing CDS entities). Only in this way can we influence its development. CDS will certainly be further developed, and I know from some sources (although this is not declarations or assurances) that even to the point of possibility to install CDS on more controlled environments (eg Azure web service instance).

If, however, someone still had doubts about the use of CDS as a relational database, stay tuned – I’m going to write few more blog posts about advantages of CDS, use cases and show some examples.

Oh and btw, just in case I’ll remind you that “SharePoint is not a relational database”;)

Do you want to know more about Common Data Service?

Follow my blog – there will be more about CDS. You can also check how I can help you or you can simply contact me!