Gallery 1 is used for Gallery 2 filter

[Anonymous]

Hello all,

I have a problem with two gallery filtering, where one item selected should show only its items on the second one, however, it is showing all. I did try a solution in a different case (https://powerusers.microsoft.com/t5/General-Discussion/Gallery-default-for-input-from-another-galler...), however it did not work.

In my data, I have as source an Excel file as following:

On this, my first screen is showing only the first column, "project". First gallery is coded as:

GroupBy(Filter(Consolidado_Piloto, Or(Lower(User().Email)=Lower('PowerApp Admin'), Lower(User().Email)=Lower('Responsable ARQ'), Lower(User().Email)=Lower('Responsable ING')) && Len('Nombre Tarea')>0),"Nombre_x0020_Proyecto","Proyectos")

And by selecting one, it should show only those with the corresponding project. However, when selecting "SBA Longavi" for example, it shows both "ejecución" and "aprobación", where the second one is only for other project. It also shows in count as ALL the "ejecución" of both projects, instead of only counting the ones corresponding to that project.

The code for the second gallery is:

GroupBy(Filter(Consolidado_Piloto, Or(Lower(User().Email)=Lower('PowerApp Admin'), Lower(User().Email)=Lower('Responsable ARQ'), Lower(User().Email)=Lower('Responsable ING')) && 'Nombre Proyecto'=galleryListaProyecto.Selected.Nombre_x0020_Proyecto && Len('Nombre Tarea')>0),"Nombre_x0020_Tarea","Tareas")

Where the filter I tried adding was:

&& 'Nombre Proyecto'=galleryListaProyecto.Selected.Nombre_x0020_Proyecto

Where it showed error: "this rule creates a circular reference between properties, which is not allowed. A property cannot reference itself or other properties affected by its value."

What can I do? Any help would be greatly appreciated

[Anonymous]

@RandyHayes 

2 questions:

First:

While that does work, the problem I have is that I do not want to count -all- of the items, rather- all the 'unique' subtask the item might or might not have. I decided to try instead adding how many "depending on" (or sub item) an item has in Excel, for example, project 1 has -2- "depending on" below it, on column "Dependiente". How would I call that, from the group by? If the gallery formula (of project) is:

GroupBy(Filter(Consolidado_Piloto, Or(Lower(User().Email)=Lower('PowerApp Admin'), Lower(User().Email)=Lower('Responsable ARQ'), Lower(User().Email)=Lower('Responsable ING')) && Len('Nombre Tarea')>0),"Nombre_x0020_Proyecto","Proyectos")

How would I be able to 'call'/show the grouped column of 'depend on'? I tried with:

ThisItem.Proyectos.Dependientes

However that gives the error of: "property expects text values, but this rule produces incompatible Table values"

----

Second question:

With further gallery sorted as:

GroupBy(galleryListaProyecto.Selected.Proyectos,"Nombre_x0020_Tarea","Tareas")

if I want/need to add a new, different filter such as "len(subtask)>0", how would I go doing that? Since it would be a new filter.

[RandyHayes]

@Anonymous 

I am looking at your example picture:

I'm a little confused on the grouping you have because those column names are cut off.
You have the first project that appears to have 24 Tareas, but they all look like (in the picture) to be the same name.  Are they, in fact, different?

As for the first question. Yes, you will get an error with that formula as you are trying to produce a text value from a table.  I'm not entirely sure what you are aiming at for the Dependiente, but, the Proyectos is a table of records, it does not have a property of Dependientes, however every record in it does. So, you could sum them if you want.

Sum(ThisItem.Proyectos, Dependientes)

For the Second question - if you want to exclude any subtask items that are blank (len(subtask)>0 - which is clearer with !IsBlank(subtask)) the question is, do you want to exclude them all together, or just from the second gallery?

If you want to exclude them all together, then you can put that in your first gallery

GroupBy(Filter(Consolidado_Piloto, 
        (Lower(User().Email)=Lower('PowerApp Admin') || Lower(User().Email)=Lower('Responsable ARQ') || Lower(User().Email)=Lower('Responsable ING')) && !IsBlank('Nombre Tarea') && !IsBlank('Nombre Subtarea'),
       "Nombre_x0020_Proyecto","Proyectos")

However, in the case of your example in the picture, it's really not necessary as I saw one example where the Tarea was blank...that entire record would be excluded from the !IsBlank('Nobre Tarea'), so adding the additional check is not needed.  But, if you have other records where there might be a name for the Tarea, but none for the Subtarea, then yes, just add it in as above.

If, however, you want to just exclude from the second, then it would be this in your galleryListaTareas:

GroupBy(Filter(galleryListaProyecto.Selected.Proyectos, !IsBlank('Nombre Subtarea)) ,"Nombre_x0020_Tarea","Tareas")

[Anonymous]

@RandyHayes 

I think there might be seen better. You are right regarding the repeated name of task or subtask (column B and C), since that is the way I have the Excel file as to know what info a line has. For example, "Aprobación" has the 16 records in the red square, hence the 16 on the first "Aprobación" line, showing it has 16 "below it" in hierarchy. However, those below it have 0.

Below, on the orange box, "Mecánica de suelos" has 0 below it, hence, zero; however, the "Compra de Terreno" has 3, which are shown in the column D at the end. Does this make it clearer? Each line is a different item, with different dates and info.

In the gallery, which was previously said, I have it as Title/Subtitle, with Title being the "Nombre proyecto" and I want the subtitle now to have the numbe of records it has, for example, in this case it would have 2, which are "Aprobación" and "Ejecución". That number is stored on the first line, column E. Is there a way to get that number from the groupedBy gallery?

Regarding the second part, will test that later on and will get back at you

[RandyHayes]

@Anonymous 

Always clearer in pictures! 😉

Your data is fine.  You don't need to create a Dependientes column though, you can get all of this directly in PowerApps.

Let me digest this for a moment and I'll shoot you an App with it all nailed down for you.

[RandyHayes · Accepted solution]

@Anonymous 

Okay, thought it would be easier to take your data and throw together an App to demonstrate this.

Please look at the attached App and you will see all the Galleries listed with their appropriate items, correct counts and proper grouping.  No Dependientes columns needed.

Look at the OnVisible of the Screen to see the Data structure as you had in your picture.  Obviously I used a collection for this sample rather than an excel file.

Look at the Gallery Items properties and the labels properties to see the formulas needed.

Take a look and let me know if you still have questions.

[Anonymous]

@RandyHayes 

Quick question: to see that, I need to upload that as a package in web.powerapps?

[RandyHayes]

Yes, just go into any app and then go to file-open and then open that file.

[Anonymous]

@RandyHayes 

Thank you so much. That 'demo' so to speak you did helped me through it and works perfectly. Many, many thanks (:

[timl]

Hi @RandyHayes 

I'm really impressed at how much effort you've put into answering this, plus a demo app in Spanish! Seriously well done!

[RandyHayes]

@timl 

Thanks much!  I got to the point where it seemed easier to provide a quick solution for @Anonymous rather than tie him up with the back and forth messages.  Seems like it hit the spot!