Creating Collections Manually and Using Them

PowerApps mainly focuses on lookups and variables that are linked to data sources that reside elsewhere.  In fact, it is REALLY GOOD at this.  However, what isn't really clearly documented in a single location are the formatting requirements around manual population of the various data sources within PowerApps.

If you're just getting started with static data and are importing it from Excel then read my post on this first and then come back.

A simple "List" for Drop Down, List boxes and more

Let's start by adding a drop down to your PowerApp via Insert | Controls | Drop down.  Click on the new drop down box.  To the right, the menu lists various settings for this control.  Find the Items entry (just under DATA).  

For example: Items = ["MD", "RN"]

The value Items takes a "list" in the following format: ["string1","string2",...,stringX"].  That's two square brackets and a series of string variables separated by commas.  

It *almost* makes sense

You could also do a series of numbers (e.g. 1, 2, 3.14, etc.).  What you cannot do is mix strings and numbers unless you make the numbers a string by putting them within quotes.

Pick a lane idiot.

You can also do a multi-column source.  This is where things start to get "weird"(-er).

Single-column lists are sorta-Collections? (but formatted 100% differently)

In my above screenshot I've used a simple list ["Item1", "Item2", "Item3"].  This is, commonly referred to in general terms a single column of data.  

If you're an Excel person, imagine the above list of values from first to last residing in A1, A2, A3.  That's column A and row 1, 2 & 3.  But what if we perhaps want to do a couple of things w/ our list of data and only one of them is having an item shown in a drop down?  I want to include A1, B1, A2, B2, A3, & B3!  That's where multi-column lists come in and you'll see references to the word "Collections" littered about the PowerApps documentation and forums.

It looks much easier in Excel

HOWEVER, what is not clear is that the PowerApps documentation doesn't state what (if anything) the original "single-column" type of "variable" this cobbled together series of characters actually is.  Is it a List?  A Collection?  A Gigglesnort?  How can I even search for something online if I don't know what it is called?!  It actually is a Collection...eventually.

Essentially Microsoft is making it "easy" on you by giving you a short-hand version of feeding the Items field a single column list of data that the control already knows how to convert into what it needs (i.e. a Collection).  However, if you try to take this trick with you and use it elsewhere, you might run into problems.

For example, if you tried to create a "Collection" by assigning a similarly formatted string to a local context variable...

• UpdateContext({myCollection:["Item1","Item2","Item3"]}) 

it would fail.  If PowerApps knows that list of characters is a single-column Collection when you're in these fields, then why doesn't this work?

It actually comes down to how PowerApps (ultimately) treats Collections.  Collections are always global so this means that attempting to put this into a local "context variable" (via UpdateContext) will not work.  HOWEVER, you can use what you already know about global variables to do something similar.  The following statement would work instead:

• Set (myCollection,["Item1","Item2","Item3"])

While this works, it isn't generally how we create/alter Collections.  As well, it breaks down when we try multi-column lists.  There are a whole series of commands around Collections that should be used instead.

There are a few instances of where you still might wind up using the [] notation.  One is when you try to use the in Operator.

• If(myField.Text in ["help", "sos"], UpdateContext(popupHelp:true), UpdateContext(popupHelp:false));

This checks to see if the value that the user put into myField is contained in a single-column "list" (i.e. Collection) and sets a variable to true if so.  And yes, if you're being persnickety, then you could have also written it this way:

• UpdateContext(popupHelp:myField.Text in ["help", "sos"])

Single or multi-column lists are PowerApps variables called "Collections"

The basic commands we use with Collections are outlined here.  In most scenarios, using ClearCollect() is the right choice.

The classic example that Microsoft leans (heavily) on is populating a Collection from a SharePoint list (although it could come from any linked data source):

ClearCollect(MySPCollection, ListName)

That's a function that clears and creates (if necessary) a "Collection" named "MySPCollection" and it gets the data from a SharePoint (or anywhere really) list data source you named "ListName".  Everything in that particular list (within reason) gets pulled into a variable inside the application.  Again, the data doesn't have to come from SharePoint of course, but it's a common example they give.

NOTE: Doing things this way means that if the SharePoint data changes during that time, your application will not have the current data.  Only do this for reasonably static information (e.g. menus, language text, etc.)

So to "create" a Collection identical to my example above, you'd actually have to create it somewhere before you get to your Drop down or List box control.  That can be at your application's start, when a user clicks a button, when a screen is shown, etc.

To create an identical Collection to my first example (that has a single column of data), you could use the following command tied to an event (e.g. ScreenVisible, ButtonClick, AppStart, etc.) prior to when you want to use it.  For now, drop a Button on your form and change the OnSelect code to be the following:

ClearCollect(myCollection,["Item1","Item2","Item3"])

or alternatively

ClearCollect(myCollection,{ Value: "Item1"}, { Value: "Item2"}, {Value: "Item3"})

Hold down the Alt key and click on your button (this runs the above code and creates the Collection).

This results in is the exact same thing as we originally put into our Items field for our Dropdown initially.  We could reference that variable name (e.g. myCollection) in a Drop down or List box exactly as before w/o any difference.  Alter your Drop down control Items property to be myCollection and everything would appear identically to the original example.

Once you click the button (or load the screen, the app, etc.), the variable gets populated and the list as well.  Clicking the button multiple times just clears and reloads the list.  You really would only ever need to do this once unless your list changed for some reason.

What you might notice in the second and more exact command above is that we've now created something akin to a JSON (or similar) formatted series of Name/Value pairs.  This will become important as we continue below.  

HOW DO I CREATE MULTI-VALUE COLLECTIONS?

Remember above I said trying this w/ the Set() function breaks w/ multicolumn lists?  This is where you have to use the Collection commands to create/populate/alter them.  So this works:

ClearCollect(myNewCollection,{ Value: "Item1", Quantity: 10 }, { Value: "Item2", Quantity: 200 }, {Value: "Item3", Quantity:0})

While this doesn't:

Set(myNewCollection,[{ Value: "Item1", Quantity: 10 }, { Value: "Item2", Quantity: 200 }, {Value: "Item3", Quantity:0}])

The first (and working) command creates a collection similar to the first one, but each item/row has two distinct columns tied to it (e.g. Value and Quantity).  These names (Value and Quantity) are just made up for this example.  These are commonly referred to as "name/value pairs" that you can make up on your own.  Just make them relevant/understandable to you.

It is simpler to see how things are clustered if you put the working command above into a field in PowerApps and then click on the Format Text button below your formula.  Then the above looks like the following:

ClearCollect(
    myNewCollection,
    {
        Value: "Item1",
        Quantity: 10
    },
    {
        Value: "Item2",
        Quantity: 200
    },
    {
        Value: "Item3",
        Quantity: 0
    }

)

That's much easier to read.

Each "row" in the "table" is separated by curly brackets {} while "columns" within the "row" are separated by commas.  Rows are also separated by commas between the curly brackets.  Notice the complete lack of square brackets anywhere and that what you learned before has zero value.  

Thanks PowerApps!

So which column appears inside the control?  The good news is that PowerApps is actually slightly smart about this and generally will pick the first column in your Collection as the default value to display. 

Change the button code to the above ClearCollection code snippet (note the variable name change for clarity).  Now edit your Drop down control so that the Items field points to the new Collection myNewCollection.

Change the Value field for whatever you want to be visible to the user

Note that no values will be displayed because you haven't run your code to populate the variable yet.  If that's bothering you, hold the Alt button on your keyboard and click the button control you just edited (again, you magically just "ran" this part of your application).

Now look just below the Items field on your Drop down control.  There is a field named Value which tells PowerApps which column should appear for users inside your control.  By default it is set to "Value" (same as our first column) and shows what that column contains.

Referencing secondary columns (and beyond) of selected items.

The whole reason you used a multi-column list is so that you could reference some other value elsewhere in your application.  This is fairly easily done via the Selected (Drop down boxes) or SelectedItems (List box) functions.

For Drop down boxes where only one item can be selected it is fairly easy.  We could reference "Quantity" in our above example by using: 

Dropdown2.Selected.Quantity 

Just change the orange text to reflect the name of your Drop down control.  For example, create a new text label on your form and edit the Text value to be similar to the value above.  When you select items in your drop down, the text in that field will change reflecting the "Quantity" column from the row you selected.

List boxes are slightly different in that you can potentially allow multi-select.  The function SelectedItems returns a table of all the selected rows.  As an example, to get the first selected row from a List box would be:

First(ListBox1.SelectedItems).Quantity

Again, you would replace the  orange text with the name of your control.

Removing Items from a Collection

The Remove() and RemoveIf() functions are laid out here.

Essentially, if you have an item that someone has selected (say from a ListBox of items they can choose to add to an order), then you can use these commands to remove one or more items from a Collection programmatically.  Imagine the following command run against our Collection from above:

RemoveIf(myNewCollection, Quantity<=0)

This would alter the Collection and remove any items from the list where the Quantity name/value pair was <= Zero.

NOTE: The Remove() and RemoveIf() functions directly modify the list you are operating on, vs. the AddColumns() command I detail below.

Gallery controls use Collections as an input

Now that you understand a Collection is just an old school rows/columns table, it makes sense that Gallery controls use the same kind of variable as an input.  These fancy-schmansy controls look fairly slick in PowerApps but aren't obvious on how to use them at first glance.  

Default links to a sample Collection

Part of the problem is that their "example" controls they include (e.g. Gallery | Vertical) is populated by a sample Collection HOWEVER the images displayed are just pointing to the same image "SampleImage".  In other words, every item gets the same image.  There is no way you would do this on a real world application.

Every item's image is the hard-coded to the same non-Collection static image.

So in a nutshell, their example shows a non-standard use case.

Lame.

You can have images in Collections and they're referenced similarly to the other fields.  However, you of course cannot manually create a Collection that includes an image, BUT, you can type in a reference for an image you already imported into your app (i.e. View | Media | Browse | Open).  So in that way, you can "add images" to your Collection AFTER you have imported them.

NOTE: You could have an SVG (which is just a string if you ever open one w/ notepad) assigned to a string value and cheat your way into some more dynamic graphics, or binary HTML that is shown in an HTML control

However, let's skip that for the moment and just focus on referencing items within the list.  In particular, what if we want to conditionally display something based on a value within a Collection inside a Gallery?

In such a scenario, if we had imported an image into our application (View | Media | Browse | Open) named SoldOut, then we could select the Image item that was created by default as a part of the Gallery (left side of screen, expand the Gallery control you added to your screen, select the image) and alter its Image setting from the default value of SampleImage to be:

If(ThisItem.Quantity=0,SoldOut)

This would mean that for all items that had a non-zero value no image would be displayed but for our last item, that has a Quantity=0, we get the following:

Where can I fulfill my Item3 fix?!

Merging or Joining Collections 

A really common data function is the SQL JOIN function.  There isn't an exact match within PowerApps, but you can get pretty close to the functionality w/ some fancy coding. 

Since Collections are essentially Tables, you'd expect that we will eventually need to combine all or part of two Records from two different Tables where they share a matching Field.  In PowerApps, the common matching field where Users are concerned is the Email address.  So let's take a look at an example of how to get this done.

Assume we have two collections: List1 and List2.  These two share a field that have unique names (this is important - but you can get around it) but common values.  In the example below the Fields are: Email and Email2.  To build these Collections run the following:

ClearCollect(

    List1,

    {

        FirstName: "John",

        Email: "test@test.tst"

    },

    {

        FirstName: "Bob",

        Email: "test2@test.tst"

    }

);

ClearCollect(

    List2,

    {

        LastName: "Smith",

        Email2: "test@test.tst"

    }

);

This gets us two sample Collections.  List1 contains the person's First Name and Email while List 2 contains their Last Name and Email.  Since the names for the Email field are unique (Email and Email2), we can run the following to combine them into a new list named mergedList:

ClearCollect(

    mergedList,

    AddColumns(

        List1,

        "LastName",

        LookUp(

            List2,

            Email = Email2,

            LastName

        )

    )

)

The results of us running all of these commands winds up giving us a Collection named mergedList that consists of an Email Address (Email), a first name (FirstName), and a last name (LastName):

Simple?

But I made it easy on you.  What if the two fields were IDENTICALLY NAMED?!  So if List1 and List2 actually both had fields named Email?  That's where the RenameColumns() function comes in.

NOTE: Microsoft does have an alternate method to allow you to directly reference individual Datasources/Collections (e.g. List1.Email & List2.Email) via the As operator.

Now normally, I'd tell you how to do this wrong and then correct you for taking the obvious path.  But sometimes I'm lazy.  Just know that the functions RenameColumns, AddColumns, DropColumns, and ShowColumns DO NOT ACTUALLY MODIFY THE TABLE YOU GIVE IT!!!  So AddColumns(myTable...) doesn't modify myTable.  It returns a temporary table as an output that contains the modification that results from you running any of these functions.

Maybe I'll do another blog post on this because it will trip you up many MANY times.

Ultimately, the final chunk of code in the above scenario for identically named Email fields looks like:

ClearCollect(

    mergedList,

    AddColumns(

        List1,

        "LastName",

        LookUp(

            RenameColumns(List2,"Email","Email2"),

            Email = Email2,

            LastName

        )

    )

)

Sometimes, this all reminds me of math class soooo many decades ago when those old HP calculators used Reverse Polish Notation.  You had to kind of work backwards from what you wanted to do in some respects.  In the case of these kinds of scenarios you are sometimes working from the innermost section of the code to the outermost.

Sorta.  But then not really.  I don't know man.  Sometimes it is just a dumpster fire of code.

Copying SharePoint data: DropColumns vs. ShowColumns

Now that you're getting cocky w/ understanding a bit more about how to add/rename columns, let's talk about Remove or Drop vs. Show.  The overview of AddColumns, RenameColumns, DropColumns, and ShowColumns is here.  But let's get to a real-world use-case where you'll do the exact wrong thing.

If you decide at some point you want to copy entries from one SharePoint List to another, then you might head down the road of grabbing an entire Record from SharePoint:

ClearCollect(recordToCopy, Filter(mySPList, ID=2));

What you wind up with is the entire SP record including all of the auto-generated SP fields (e.g. Author, Created, etc.).  You cannot copy these fields into another SharePoint list because those fields are auto-populated by SharePoint.

Since often times there is no problem in grabbing these fields (and many are useful) you might then just decide to use DropColumns to remove all of the extra stuff.  

ClearCollect(recordToCopy, DropColumns(Filter(mySPList, ID=2),{Attachments}, {...

You'll go through and remove every single one of them, triple-check, get an extra cup of coffee, check to see if one of your co-workers is messing w/ you, and then perhaps browse the interTubes for jobs w/ the -PowerApps and -SharePoint search tags.

Why?  Because you'll never be able to copy that record to another SharePoint list.  There simply is no way for you to remove certain fields (I'm looking at you Author#Claims).  So how do you do this?  Well, instead of removing/dropping fields, you add/show them.

Here is the correct way to handle this kind of situation: use ShowColumns.

ClearCollect(recordToCopy,ShowColumns(Filter(mySPList, ID=2), firstName, lastName...

ShowColumns only adds the specific columns you want vs. dropping the columns you don't want.  Again, because of some quirk of SharePoint/PowerApps, you cannot Drop certain columns (that don't even appear when you look at the fields).  

Now of course, if you also want to keep some of the original automatic columns like ID, Created, or Author, then you're going to want to pair the above command w/ a RenameColumns command and put them into something different than the default SharePoint fields.

Merging Collections into Strings

One of the items that you will run into time and again will be dealing w/ Collections generated by multi-select Combo Boxes.  You will need to put the values selected "somewhere" and sometimes that is within a simple comma-delimited string for ease of reporting.  While sure it might be nice to build our a relational database to link tables and whatnot, we commonly need to get close to "good enough".

Fortunately, there is a very simple way to merge a series of Collection values into a single delimited string and that is via the CONCAT() command.  In the case of combo boxes, it looks similar to Concat(myComboBox.SelectedItems,valueToCombine,",").  This would take a ComboBox named myComboBox, then take all the items selected within it, then munge the column valueToCombine (not necessarily the raw text of what appeared in the ComboBox mind you) all into a String to use in some other way (i.e. save to SharePoint, write to a variable, display in a label, etc.)

Here's an example based upon the list from the earlier section:

ClearCollect(

    List1,

    {

        FirstName: "John",

        Email: "test@test.tst"

    },

    {

        FirstName: "Bob",

        Email: "test2@test.tst"

    }

);

Set(

    csvEmails,

    Concat(

        List1,

        Email,

        ","

    )

)

The results of this would be a variable named csvEmails that contains: test@Test.tst,test2@test.tst

This makes it easy for you to drop in a multi-select combobox and still support your original data fields in SharePoint or otherwise w/ a formatted string.  Sometimes this is a good compromise per readability vs. the more structured JSON string.

Turning/Merging simple CSV lists BACK into Collections

This is another annoying aspect of simple data storage.  Since we don't have a function to import JSON into a Collection yet, you might wind up storing multiple values within a single field.  And while it is tempting to do this w/ JSON() (and you should if you're going to work w/ Power Automate), in many cases all you need is a simple comma-delimited list.  

For example, let's say you've had an end-user select a group of people and their lunch preferences inside your app.  However, you only want to store this in a single record (e.g. Marketing's Lunch Orders).  So you use the above CONCAT() command to split your series of users into a comma-delimited list, and you split their individual lunch orders into a comma-delimited list as well.  

Now you want to give users the ability to edit/change their order, but you realize it is annoying to try and pull these two distinct comma-delimited lists into a single Collection.  Well, here's how to make that happen.

Imagine we have two fields storing comman-delimited strings that holds the following data:

• lunchPeople = Janice, Uday, Xiao
• lunchOrdlers = PB&J, Fish, Tofu

The items line up and each item is stored in order.  So Janice's lunch is PB&J and so forth.

To merge all of this back into a Collection within our PowerApp via the following:

Clear(mergedList);

ForAll(MatchAll(lunchPeople, "[^\s,]+" ).FullMatch,

    Collect(mergedList,

        {

            column1: Last(FirstN(Split(lunchPeople,","),CountRows(mergedList)+1)).Result,

            column2: Last(FirstN(Split(lunchOrders,","),CountRows(mergedList)+1)).Result

        }

    )

)

To walk through this, we first Clear() a Collection where we'll eventually store our data.  Then, we use the ForAll() command to roll through each item and append it to our new Collection.

You might also notice that I was a little adventurous by using a MatchAll() function.  That allows you to do pattern-matching similar to a REGEX expression.  That single line (MatchAll(lunchPeople "[^\s,]+" ).FullMatch) is creating a Collection that we aren't saving, but are using merely as an input for our ForAll() command to iterate through.

As the ForAll() command iterates through each item in the original list, we add a new item to the end of our mergedList Collection.  It is worth mentioning that we don't actually DO anything to the original list we created via the MatchAll() function.  This actually represents a fairly common use case for ForAll().

It ultimately doesn't even matter which of the two lists we had in our ForAll() block as a starting point.  If the first line were instead:

MatchAll(lunchOrders "[^\s,]+" ).FullMatch

There would be no difference at all in the results.  If you just think of it as an old school "counter" variable then that's how ForAll() should mainly be used.  In plainer English, we've got 3 items in our list, so I need you do do the below code 3 times.

In the end, what we get out of this is a Collection named mergedList that has each row/column lined up as we'd expect:

I hope there isn't a mixup

Updating Values in a Collection

OK, this is the Holy Grail of what is just confusing inside PowerApps currently.  I owe you all this and will (eventually) get back to this and update it.  Just know that the following commands might look like they works almost identically, but how they handle writing to an existing record is where things get really messy really fast.

• Patch()
• Update()
• UpdateIf()

The shorthand here is that the Patch() function is my go-to for almost everything...just not this.  UpdateIf() is what you should use and I'll explain it eventually.

This sure looks/feels a lot like JSON

It should.

Collections can be easily converted into JSON using the JSON() function.  However, at present you cannot do the reverse and import JSON directly into PowerApps and convert it to a Collection without a mildly annoying amount of work.  You CAN read/process JSON more easily (if you know me then you know how funny that last word was) in Power Automate.  So if you need to process something that is encoded JSON, just know that you can hack your way via that tool into putting it into a more structured data store (i.e. SharePoint List or similar) which can then be read into PowerApps.

One trick that you would be surprised works is that you can use the Set() command to actually directly assign it a static blob of JSON and it will work so long as you put it all inside a string, using double quotes, and convert all double quotes to singles inside of the JSON.  However, this must be hard coded.  You cannot look this up from elsewhere (e.g. SharePoint).  This is because Collections must be defined at design time.  You cannot dynamically add new columns that will be created from the source.  Also, the language gets a little confused as you try to access values within the JSON that are Collections/Lists themselves.  So while it works, it is of limited use.  Just a curious thing the platform almost does.

NOTE: If you caught it, I did kind of lie to you near the beginning of the article on the Set() command not being able to create multi column lists.  It can, but the use case is funky and extremely limited.  YMMV

Final thoughts

So is a single-column list we originally created a "Collection"?  Yes.  However, once you start dealing w/ real "lists of data" you should stop using simple variable commands and instead use the commands associated with "Collections".

PowerApps isn't really designed to have hard-coded data mainly because that's just bad practice programming-wise.  However, sometimes you want to test some things and just see how everything functions before you pull in outside data.  When you do this, sometimes PowerApps is...annoying.

There are certainly use-cases for importing semi-static data (e.g. Excel tables) that a field team might work with and update while they are "offline" which they then later want to submit back to "something" to save the changes.  Since so many organizations are starting from some fairly elaborate Excel files, it is reasonable to understand that at some point you're going to pull in an Excel table and have to deal w/ a Collection as outlined above.

NOTE: There now (Dec-2019) exists a method to read/write in real-time to Excel files that exist on OneDrive.  Just because you can do a thing...

Sometimes the best thing you can do is to drop in a Data Table control, link it to your Collection, Display the Columns, and review the Collection's contents to ensure you have the data you think you have.  Other times, the quick view in the expanded code window drop down at the top of the screen is enough.

You can allow SharePoint and other data sources to create Collections for you automatically, but doing this manually a few times will help you understand what is going on when these things just...don't...work.  

When that happens, you might need to figure these things out very quickly and on a live environment.  So playing with manual population is worth considering to expand your knowledge.

See Microsoft's documentation for more (or less) about Collections.