Nesting Functions in Power Apps

I love Excel and sometimes I wish I could use all of the same Excel functions in Power Apps but I can’t so I nest (enclose one function within another function) a lot of functions instead.

In my example Technology Loaner Request app, users are able to request laptops and phones. There are several loaner devices available at any given time and I need to be able to tell users how many devices, if any, are available.

Armed with this information about my data I start building. I like to build code statements by starting with the basic thing I need to do and then adding to it.

To display the number of laptops or phones available, I started with CountIf. First, I get a count of all the laptops or phones like this:

CountIf('Technology Loaner Request',"Laptop" in DeviceType)

This code counts the number of records in my data source, the SharePoint list named Technology Loaner Request when the term Laptop is found in the DeviceType column in the data source. This way, I isolate the number of laptops from the number of phones available. Feel free to copy/paste the code above and substitute the names of my data source, columns and terms for yours.

There were 8 total laptops so the code above returns the number 8 as the result but there are not 8 laptops available. Two have already been checked out by users.

I’ll add to my existing code to account for devices already checked out like this:

CountIf('Technology Loaner Request',"Laptop" in DeviceType, IsBlank(CheckedOutBy))

This code does what it did before but CountIf has the inherent ability to evaluate multiple conditions like CountIfs in Excel. I just remove the original ending ) and replace it with a comma and lengthen the statement. The new statement counts the number of records in the data source when the term Laptop is found in the DeviceType column and the same record has nothing in the CheckedOutBy column.

There are 6 total laptops not checked out and available. Excellent.

Make it user-friendly

If all of the laptops or phones are checked out a zero appears as the number of available laptops. Sometimes users call or email the department wondering if there really are no available devices because they aren’t sure if the 0 is accurate or if there is a problem with the app. I realized that it is better to display some text and not the number 0 when there are no laptops or phones so I added a bit more to the existing code to address the issue like this:

If(CountIf('Technology Loaner Request',"Laptop" in DeviceType,IsBlank(CheckedOutBy))=0,"There are no available laptops.",CountIf('Technology Loaner Request',"Laptop" in DeviceType,IsBlank(CheckedOutBy)))

In the code statement above, I wrapped the entire original statement within and If statement. This statement checks to see if the result of the original CountIf statement is equal to 0 and if it is the text There are no available laptops is displayed (instead of a 0). If the original code statement is not equal to 0 then I want the whatever the value is to appear. That is why the original statement appears a second time. It is what should appear if the answer to the If statement is false.

Make this solution even better by having this screen automatically refresh every few minutes. Check out my AutoRefresh in PowerApps post to get it done!