cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FelipeCaru
Level 8

Coloring gallery row using datacard value and a second criteria

Hello everyone,

I have yet another question if anyone could help me. I have in a datacard a card that has the following code:

If(DateDiff(Today();'Fecha Termino')<0;"Atrasado";DateDiff(Today();'Fecha Termino'))

It doesn't exist in my Sharepoint database, by the way. What this code gives me is the days remaining between today() and the "end date" of the item. If it is lower than zero, then it will say it is late ('atrasado'). This value is only on the detail view and not on the adding one or the gallery itself.

What I want to do is, using that value, color code gallery items. For example, if it is "Atrasado", it will be painted red; if it is between 0 and 2, orange; and so on.

 

To the previous data I also want to add the criteria that if a number somewhere else (that DOES exist in my sharepoint list) is 100 (which is concat with a % sign), the card would be green.

 

In short, color code gallery to green with 100% value (exist in SP), and the other colors according to a value that only exists in a datacard in the detail view. Is this possible?

 

The code for the "100%" seen in gallery is:

Concatenate(Text(ThisItem.'% Avance');"%")

forumColor1.pngforumColor2.png

4 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Coloring gallery row using datacard value and a second criteria

Hola again @FelipeCaru 

 

What you want to do is very possible.  But, think in terms of your gallery, not in your datacard.  The logic you want to deal with will happen in the gallery itself.

In this case we are looking at the TemplateFill property.

 

So, set your gallery TemplateFill property to this:

  If(ThisItem.'% Avance'=100; Green; DateDiff(Today(); ThisItem.'Fecha Termino')<0; Red; White)

 

In this formula we are using the If statement to check two conditions.  First, if the value is 100, return green.  ELSE if the datediff is "late", then return Red.  Else return White (I assumed that color for you).

 

Hoep this helps.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Super User
Super User

Re: Coloring gallery row using datacard value and a second criteria

@FelipeCaruyes this is possible. 

Since this is a Gallery, you have full control over the template of the gallery.

If you want to add images, you can do so.  There are at least two options for that.

One - you can use the built-in icons (from Insert->Icons-> choose the ones you like).  Then on each one, you can set the visible property based on your conditions.  For example, the Smile icon for % Avance=100.  So, add the Icon to your gallery template and set the Visible property to : (ThisItem.'% Avance'=100)

This will be fine, but you'll have to add some other logic to make sure only one is visble (you can imagine, even though it is 100, the calulation for the datediff might cause others to appear that you don't want).

BUT, this is an option.

Two - you can import images into your Media Gallery (View->Media->Browse) and then use them in your app like this...

Example: imgBueno for Avance=100,  imgAtrasado for Atrasado, and imgPronto for days low.

Now, in your gallery template, add an Image control and set the Image property to:

   If(ThisItem.'% Avance'=100; imgBueno; 

      DateDiff(Today(); ThisItem.'Fecha Termino')<0; imgAtrasado; 

      DateDiff(Today(); ThisItem.'Fecha Termino')<10; imgPronto;

      imgDefault)   //you'll either want to have a default image, or set the visible property of the image to false if the above conditions are not met.

 

As for the images - the rules are this.  Make them the size you want - that is the best.  Go too big and its wasted bits.  Go too small and you stretch the image and it looks bad.  Also, name the image (the file name) exactly as you want the "variable" name to be in the App to reference it.  Let's say the above example, I would have created a file named "imgBueno.png" and "imgAtrasado.png" and "imgPronto.png"

 

Hope this is helpful.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Super User
Super User

Re: Coloring gallery row using datacard value and a second criteria

@FelipeCaru

Sure thing...here is a simple solution to that.

First - The goal is to create an additional column that will "flag" the 100's.  Then you will do a sort on two columns, the "flag" and the normal date.

 

So, your Items becomes:

SortByColumns(
 AddColumns(
Filter(demo_project; Título = ListaProyectos.Selected.Value && User().Email in ShowColumns(Encargado;"Email"));
"SortOrder"; If(Avance=100, 1,0));
"SortOrder"; Ascending; "Fecha_x0020_Termino"; If(SortDescending1;Descending;Ascending)
)

This will add the column called SortOrder to the list with the condition of Avance=100 then 1 otherwise 0.

You do a primary sort on SortOrder Ascending (always have the 100's at the bottom) and then a secondary sort on "Fecha Termino" in whatever order you want.

 

Hope this help.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Super User
Super User

Re: Coloring gallery row using datacard value and a second criteria

@FelipeCaru 

So, in our last exercise, we added a Column called SortOrder...All good.

 

Now, you have Forms that have data sources set to your original source (and all its fields).  When you try to pass the Item to it, the item has an additional Field - the SortOrder.  It sees a mismatch with that and will give you this error.

So, one quick way around it is to Drop the Column.  However...DropColumns only works on tables, so we need to pull a little trick on it.  Here is what you want for your Item property:

   First(DropColumns(Table(BrowseGallery1.Selected); "SortOrder"))

 

This will first convert the Selected item in the Gallery to a table then apply the DropColumns to that table to get rid of the column, then return the First record (which there was only ever one anyway).

 

Hope this helps you to your next step.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Coloring gallery row using datacard value and a second criteria

Hola again @FelipeCaru 

What you want is entirely achievable!  However, don't think in terms of your datacard in this case, think about your gallery - because that is where you want the logic to exist.

 

So, you will be looking at the TemplateFill property of the gallery.  

In there you will put this formula:

   If(ThisItem.'% Avance'=100; Green; DateDiff(Today(); ThisItem.'Fecha Termino')<0; Red; White)

In this satement we are using an If function to return the desired result.

If it is 100 it will be filled with Green.  If it is late, it will be filled with Red.  If none of those, then it will be filled with White (I assumed that color for you).

 

Hope this helps.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Coloring gallery row using datacard value and a second criteria

Hola again @FelipeCaru 

 

What you want to do is very possible.  But, think in terms of your gallery, not in your datacard.  The logic you want to deal with will happen in the gallery itself.

In this case we are looking at the TemplateFill property.

 

So, set your gallery TemplateFill property to this:

  If(ThisItem.'% Avance'=100; Green; DateDiff(Today(); ThisItem.'Fecha Termino')<0; Red; White)

 

In this formula we are using the If statement to check two conditions.  First, if the value is 100, return green.  ELSE if the datediff is "late", then return Red.  Else return White (I assumed that color for you).

 

Hoep this helps.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Highlighted
FelipeCaru
Level 8

Re: Coloring gallery row using datacard value and a second criteria

Hey again @RandyHayes 

 

I did something before checking this answer, which resulted in the following code:

If('% Avance'=100;DarkSeaGreen;If('% Avance'<100 && '% Avance'>50;LightGreen;If('% Avance'<=50 && '% Avance'>0;LightSalmon;OrangeRed)))

Which ends giving me a screen as following:

colorCode1_forum.png

 

This was done using the 3rd line in there (body), as % show. However, I saw that it is possible to add small images next to it if it has a condition or not. Going by this, is it possible for my previous code to have added next to the data, to the left of it, an image that uses as condition the field in the datacard I mentioned? Such as, if it is "Atrasado", I will have a "!" image next to it, or if the days remaining from that field is low, some other. In this case, I would have 3 symbols: one for 'late', one for 'close to date' and one for 'ok'.

If that is possible, how can I do so? And, as a subquestion, how to add those images and what size should they have?

 

As always, thanks in advance

Super User
Super User

Re: Coloring gallery row using datacard value and a second criteria

@FelipeCaruyes this is possible. 

Since this is a Gallery, you have full control over the template of the gallery.

If you want to add images, you can do so.  There are at least two options for that.

One - you can use the built-in icons (from Insert->Icons-> choose the ones you like).  Then on each one, you can set the visible property based on your conditions.  For example, the Smile icon for % Avance=100.  So, add the Icon to your gallery template and set the Visible property to : (ThisItem.'% Avance'=100)

This will be fine, but you'll have to add some other logic to make sure only one is visble (you can imagine, even though it is 100, the calulation for the datediff might cause others to appear that you don't want).

BUT, this is an option.

Two - you can import images into your Media Gallery (View->Media->Browse) and then use them in your app like this...

Example: imgBueno for Avance=100,  imgAtrasado for Atrasado, and imgPronto for days low.

Now, in your gallery template, add an Image control and set the Image property to:

   If(ThisItem.'% Avance'=100; imgBueno; 

      DateDiff(Today(); ThisItem.'Fecha Termino')<0; imgAtrasado; 

      DateDiff(Today(); ThisItem.'Fecha Termino')<10; imgPronto;

      imgDefault)   //you'll either want to have a default image, or set the visible property of the image to false if the above conditions are not met.

 

As for the images - the rules are this.  Make them the size you want - that is the best.  Go too big and its wasted bits.  Go too small and you stretch the image and it looks bad.  Also, name the image (the file name) exactly as you want the "variable" name to be in the App to reference it.  Let's say the above example, I would have created a file named "imgBueno.png" and "imgAtrasado.png" and "imgPronto.png"

 

Hope this is helpful.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Community Support Team
Community Support Team

Re: Coloring gallery row using datacard value and a second criteria

Hi @FelipeCaru ,

Have you solved your problem?

I agree with @RandyHayes 's thought almost. Firstly, you could consider insert your three 3 symbols images ( OKImage, LateIamge and 'CloseToDate' Image) into your app via pressing File -> Media -> Images.

More details about adding media data within PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/add-images-pictures-audio-video

 

Then within your Gallery, you could add a Image control. Set the Image property to following formula:

If(
   ThisItem.'% Avance' = 100;
    'OKImage';
   DateDiff(Today(); ThisItem.'Fecha Termino')<0,
    'LateImage';
   DateDiff(Today(); ThisItem.'Fecha Termino')<5;
   'CloseToDateImage';
   'OtherImage'
)

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
FelipeCaru
Level 8

Re: Coloring gallery row using datacard value and a second criteria

@v-xida-msft @RandyHayes 

 

Thank you both. I managed to add the images going by the criteria/formula provided by you both. However, before accepting as solved, have a last question.

My gallery has the following code:

SortByColumns(
Filter(demo_project; Título = ListaProyectos.Selected.Value && User().Email in ShowColumns(Encargado;"Email"));
"Fecha_x0020_Termino";
If(SortDescending1;Descending;Ascending))

And the image gallery ended coded as:

If(ThisItem.'% Avance'=100;okDate;
DateDiff(Today();ThisItem.'Fecha Termino')<0;lateDate;
DateDiff(Today();'Fecha Termino')<=5;closeDate;
defaultDate)

Is it possible to add some way to make it so when ordering/sorting, which uses the 'end date' that shows in gallery there, to make it so those that have the check mark/100% always appear at the end?

sort3_forum.png

As seen in this case, "Tarea2" would always show up at the end of the sort, because it is as "100%"/Check mark

 

Thanks in advance,

Super User
Super User

Re: Coloring gallery row using datacard value and a second criteria

@FelipeCaru

Sure thing...here is a simple solution to that.

First - The goal is to create an additional column that will "flag" the 100's.  Then you will do a sort on two columns, the "flag" and the normal date.

 

So, your Items becomes:

SortByColumns(
 AddColumns(
Filter(demo_project; Título = ListaProyectos.Selected.Value && User().Email in ShowColumns(Encargado;"Email"));
"SortOrder"; If(Avance=100, 1,0));
"SortOrder"; Ascending; "Fecha_x0020_Termino"; If(SortDescending1;Descending;Ascending)
)

This will add the column called SortOrder to the list with the condition of Avance=100 then 1 otherwise 0.

You do a primary sort on SortOrder Ascending (always have the 100's at the bottom) and then a secondary sort on "Fecha Termino" in whatever order you want.

 

Hope this help.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

FelipeCaru
Level 8

Re: Coloring gallery row using datacard value and a second criteria

Thank you again, everyone (:

 

Now it is how I wanted it to be.

FelipeCaru
Level 8

Re: Coloring gallery row using datacard value and a second criteria

@RandyHayes 

 

I think the previous thing, which works perfectly, broke my detail and add new data views. Both have the following error:

error.png

It shows both in the detail and adding a new entry, with the new add on of that order in gallery. If I removed that order in gallery, it doesn't show. What  can I do in this case ?

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (5,203)