Coloring gallery row using datacard value and a second criteria

[Anonymous]

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');"%")

[RandyHayes]

Hola again @Anonymous 

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.

[RandyHayes · Accepted solution]

Hola again @Anonymous 

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.

[Anonymous]

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:

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

[RandyHayes · Accepted solution]

@Anonymousyes 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.

[v-xida-msft]

Hi @Anonymous ,

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,

[Anonymous]

@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?

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,

[RandyHayes · Accepted solution]

@Anonymous

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.

[Anonymous]

Thank you again, everyone (:

Now it is how I wanted it to be.

[Anonymous]

@RandyHayes 

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

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 ?