cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

SharePoint Get List Items with Loop in PowerApps

HI

I have custom list with following fields

 

Title – String

Impacted Sites – Choice field with Multi Selection Enabled  {Site1, Site2…Site16 }

Status – Choice { Red, Amber , Green}

 

I have to show the 16 tiles (Site1, Site2…Site16) in PowerApps screen. By default background color of the each tile is Green.

In case any item added  in the above custom list with Red or Amber, I need to change  the background color of  respective tile.

 

Ex: New Item added in the list and if I selected Site1, Site 3 in the “Impacted Sites” choice filed, I need to change the background color the Site1 and Site 3.

 

 

Red Priority :

 

If have two items added in the list like

                Item 1 – Site 4 and Site 6 selected and Status is Amber

                Item 2 – Site 6 and Site 8 Selected  and Status is Red

I need show the Site 6 tile background color in Red. Since Red is the first priority. 

 

I can able to write he script to achieve this functionality in SPFX.  But I have restricted use only PowerApps with MS Flow to achieve this requirement.                                                                                                                                                                                                                                            

 

Can you please help me how to do this reequipment in PowerApps.

 

The script I written to get the open items(Red and Amber)  is below.

 

private async  getOpenItem()

  {

   

        let myMap = new Map();

 

    var results = await pnp.sp.web.lists.getByTitle("List")

                            .items.filter(`Status ne 'Green'`)

                            .select("ID","Title","Description",

                            "Status","ImpactedSites")

                            .get();

                            // .then( results =>

                            //   {

 

                            

                           

                              if(results != null)

                              {

                                for(var i=0;i<results.length;i++){

                                  var stringArray = results[i].ImpactedSites.splice(',');

                                  for(var j=0;j <stringArray.length;j++)

                                  {

                                   

                                    if(myMap.has(stringArray[j]))

                                    {

                                      if(results[i].Status =="Red")

                                     {

                                          var status =myMap.get(stringArray[j]);

                                          if(status ="Amber")

                                          {

                                            myMap.set(stringArray[j],results[i].Status);

                                          }

                                     }

                                    }

                                  else

                                    {

                                      myMap.set(stringArray[j],results[i].Status);

                                    }

                                    

                                  }

                                }

                                if(myMap.size>0 )

                                {

                                  this.FormTiles(myMap);

                                }

                              }                       

                     

 

   

  }

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

I have done implemented like below 

 

If(CountRows(Filter( 'List',lblsiteName.Text in 'Impacted Sites'.Value,Status.Value="Open -  Red"))>0,Red,

CountRows(Filter('IList',lblsiteName.Text in 'Impacted Sites'.Value, Status.Value="Open - Amber"))>0,Orange,

Green)

View solution in original post

6 REPLIES 6
Solution Supplier
Solution Supplier

hi @azeesdinu 

 

Could you please share your screen shot of powerapps form where you can able to see the tiles?

 

by reading your query, i think you can use simple if condition to acheive that.

 

share your screen for better understanding.

Hi panand99, 

 

I have used balnk tablet layout for the screen. I have used Rectancle icon for showing as tile view. 

I feel this littel complex since the Status filed is Multi selection. 

 

Hi @azeesdinu ,

Do you want to display the Rectangle's color according to the data in list and the order is from Red, Amber to Green?

If so, try this formula:

1、set Rectangle1's Fill

If("Red" in Distinct(Filter(listname,"Site1" in Impacted Sites.Value),Status.Value,Red,
"Amber" in Distinct(Filter(listname,"Site1" in Impacted Sites.Value),Status.Value,Amber,
"Green" in Distinct(Filter(listname,"Site1" in Impacted Sites.Value),Status.Value,Green,)

2、set Rectangle2's Fill

If("Red" in Distinct(Filter(listname,"Site2" in Impacted Sites.Value),Status.Value,Red,
   "Amber" in Distinct(Filter(listname,"Site2" in Impacted Sites.Value),Status.Value,Amber,
   "Green" in Distinct(Filter(listname,"Site2" in Impacted Sites.Value),Status.Value,Green,)

Others are similar to these formulas. Just modify the sitename.

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi v-yutliu-msft, 

 I have sixteen rectangle with default background color as "GREEN". As I already mentioned, I have "Impacted Sites" dropdown field. Its contains those sixteen sites name like (Site 1, Site 2 .... Site 16). 

We are trying to create health dashboard view for my 16 sites. If any one or more then sites down, I will create/update the item custom list.

Ex:

If I created new item in list with following values 

Title -- Some text 

Impacted Sites -- Site 2 and Site 5 

Status -- Red

Then Background color for Site 2 and Site 5 should be red. Later  If I modified Status to Amber then background color for Site 2 and Site 5 should be Amber. 

Once the site is run & up, I will change the Status to Green so the background of the rectangle should be in green.

So Whenever I do the Add/Update the items in custom list that should reflect in PowerApps screen. 

I have done implemented like below 

 

If(CountRows(Filter( 'List',lblsiteName.Text in 'Impacted Sites'.Value,Status.Value="Open -  Red"))>0,Red,

CountRows(Filter('IList',lblsiteName.Text in 'Impacted Sites'.Value, Status.Value="Open - Amber"))>0,Orange,

Green)

View solution in original post


@v-yutliu-msft wrote:

Hi @azeesdinu ,

Do you want to display the Rectangle's color according to the data in list and the order is from Red, Amber to Green?

If so, try this formula:

1、set Rectangle1's Fill

If("Red" in Distinct(Filter(listname,"Site1" in Impacted Sites.Value),Status.Value,Red,
"Amber" in Distinct(Filter(listname,"Site1" in Impacted Sites.Value),Status.Value,Amber,
"Green" in Distinct(Filter(listname,"Site1" in Impacted Sites.Value),Status.Value,Green,)

2、set Rectangle2's Fill

If("Red" in Distinct(Filter(listname,"Site2" in Impacted Sites.Value),Status.Value,Red,
   "Amber" in Distinct(Filter(listname,"Site2" in Impacted Sites.Value),Status.Value,Amber,
   "Green" in Distinct(Filter(listname,"Site2" in Impacted Sites.Value),Status.Value,Green,)

Others are similar to these formulas. Just modify the sitename.

 

Best regards,

Community Support Team _ Phoebe Liu



Hi, 

I have used below formula to fill the color. I have 16 rectangle like this. This formular is saying " Delegation Warning". 

Do you knwo how to avoid delegation warning. 

 

If(CountRows(Filter( 'List',lblsiteName.Text in 'Impacted Sites'.Value,Status.Value="Open -  Red"))>0,Red,

CountRows(Filter('IList',lblsiteName.Text in 'Impacted Sites'.Value, Status.Value="Open - Amber"))>0,Orange,

Green)

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (39,379)