Count items without making auxiliary gallery

[Anonymous]

Hello all,

I am having troubles with a part of my app. I am not sure if it is even possible. What I currently have so far is that the user selects an item in the gallery, and next screen it shows all the items that are according to the 'title' of the item selected in the previous screen, filtered by unique names. What I need now, however, is to have a CountRows() of all the items in a gallery, by using the criteria of the "title" of the gallery. For example, I have in screen 1:

• Approvation
• Execution
• Go date

If I select the first one, next screen will show 17 items that are filed under 'approvation' (which is a filter). What I need now, however, is that in the screen that shows those 3 options, add in the subtitle under it a formula that will count how many registers it has, ending like:

• Approvation
  • 17 items
• Execution
  • 83 items
• Go date
  • 0 items

How can I do this? Is it even possible?

I did come with a manual workaround, however it implies making MANY additional screens that will only have a gallery with the one I want to count, which is undoable since it will definitely make the app slow. For example, I did 3 auxiliary screens that have all the items in a gallery, of each one. Meaning, a gallery with 17, one with 83 and one with 0 elements on it, since it is filtered by the item name (approvation, execution, go date). Then, on the subtitle of the gallery of screen 1, I used a CountRows() inputting the auxiliary gallery. If can not do this solution again, since, for example, in "execution", the 83 elements have a sub element, which would mean additional 83 screens as auxiliary, which, again, it is undoable. Any help?

EDIT:

Something I just thought but I am also not sure it is possible: have another Excel table as database connected, that has the total rows counted on it for each item. However, the problem would be when adding a new entry to the database1, since the number of table2 wouldn't count, unless, however, that I can both submit the form to database1 AND also add +1 to whatever item counter it was added.

[RandyHayes]

@Anonymous  Welcome to GroupBy!!  Learn the concept now and it will be your absolute friend for what you are doing.

Consider your formula you mentioned:

GroupBy(Filter(DBExcelProjectTable, User().Email=Lower(Responsable) && Len('Nombre Tarea')>0), "Nombre_x0020_Tarea", "Tareas")

You have to examine what is going on in this statement...

GroupBy will (for simplicity) create a list of all the "distinct" items in "Nombre_x0020_Tarea".  It will then also create an additional Column called "Tareas" that will have ALL of the items that match that "Nombre_x0020_Tarea".

So, you will have a table then with 2 columns - One is the "Nombre_x0020_Tarea" column with names of all the items (distinct) and the second is a column with the table of all the other items that go along with that in it.

They are "Grouped"

So - from your Gallery perspective, you only have one Field to choose from.  But you also have all of the sub-items in the grouped column (Tareas) to work with.

In your gallery you could have (for example) a label that shows Nombre_x0020_Tarea and another label that has CountRows(ThisItem.Tareas).  This will give you a count of the total items that are in that Group.

So, in your situation, go to the SubTitle label and change its text to this:  CountRows(ThisItem.Tareas) & " sub items"  and you will have exactly what you were looking for.

Hope this give some fuel for thought.  And..by the way, you can group on multiple columns as well - but don't confuse yourself on that until you get the basic GroupBy down.

[Anonymous]

@RandyHayes 

I have questions about it since I dont fully get it. I get it that GroupBy will leave me with the 3 unique items, but counting that wouldnt give me total of 3? When what I want is counting the subitems of it (that are not blank)

[RandyHayes · Accepted solution]

@Anonymous 

So let's take your example data..

• Approvation
  • 17 items  (have the column "Nombre_x0020_Tarea" with the value "Approvation")
• Execution
  • 83 items  (have the column "Nombre_x0020_Tarea" with the value "Execution")
• Go date
  • 0 items  (have the column "Nombre_x0020_Tarea" with the value "Go date")

I'll assume that the names above will apply to the "Nombre_x0020_Tarea" column and that the data is in the DBExcelProjectTable and what is returned from your filter.

If we use the following formula:

GroupBy(Filter(DBExcelProjectTable, User().Email=Lower(Responsable) && Len('Nombre Tarea')>0), "Nombre_x0020_Tarea", "Tareas")

We will have a Table now with 2 columns and 3 rows.  The first column will be the "Nombre_x0020_Tarea" results.  In the second column we will have another table with items that are "grouped" by the "Nombre_x0020_Tarea" column value.

So, your results will be as follows:

      Nombre_x0020_Tarea      Tareas
      Approvation             Table (with 17 rows)
      Execution               Table  (with 83 rows)
      Go date                 Table (with 0 rows)

If we have a Gallery and use the above Formula for Items, we would have the following:

Row 1 -  ThisItem.Nombre_x0020_Tarea = "Approvation"
         CountRows(ThisItem.Tareas) = 17
Row 2 -  ThisItem.Nombre_x0020_Tarea = "Execution"
         CountRows(ThisItem.Tareas) = 83
Row 3 -  ThisItem.Nombre_x0020_Tarea = "Go date"
         CountRows(ThisItem.Tareas) = 0

At this point we can count rows, perform calculations, and do many other things with the "sub table" that is in the Tareas column.  In a sense, the sub table is really a filter on top of the filter you first applied (i.e. your filter plus filter by the name in the first column) BUT, we get this then for each and every row in the primary column (Nombre_x0020_Tareas).

Hope this is a bit clearer and helps get you to the next step.

[Anonymous]

@RandyHayes 

Testing it now and it does work! Thank you (:
Have a question tho: In all cases, it is as following:

Data

Data / blank space

Data / subdata / blank space

Data / subdata / sub subdata / blank space

With / marking a new column. In this case, since there is always a blank space, it will show it in gallery as an item. Is it better to, in the CountRows, add a " -1 " to remove that extra, or is there a way to filter it?

[RandyHayes]

@Anonymous 

I'm not following what you mean by blank space.  That would seem to indicate something with the way you are display information rather than what the information contains.

Perhaps share a screenshot of what you are seeing to make it clearer. 

[Anonymous]

@RandyHayes 

task	subtask
aprobacion
aprobacion	agenda
aprobacion	alta proyecto
aprobacion	call

In Excel I have, for example, that table and data. As you can see, the first line will always have a blank space, since it is from a hierarchy I got from a Project file. Then, in a gallery, if I for example show only those with "task=aprobacion":

Same "blank" one. That is why I was asking if it is possible to make the count only if has data, or easier to just do a "minus 1" to the counts, since it always has that empty space, for each "new" one when it has a level down.

[RandyHayes]

@Anonymous Ah...a picture is worth a thousand words!

So, if you ALWAYS know that the item would be blank and feel comfortable with that assumption, then you could certainly minus 1 it.

If not, you could put this in place to check that neither Tarea or Tareas are blank.

GroupBy(Filter(DBExcelProjectTable, User().Email=Lower(Responsable) && !IsBlank('Nombre Tarea') && !IsBlank('Tareas')), "Nombre_x0020_Tarea", "Tareas")

Hope that helps!

[Anonymous]

@RandyHayes 

I think that would work, tested and did work, kinda, because it also filtered one I do need to see. Even if it is zero, still need to see those for details (like start/end date of overall task)

In this case, the add on of the !isBlank() filters the 3rd row, which I do need to see

[RandyHayes]

@Anonymous 

So in that case, I'd go back to the original filter you had and look at the CountA function
That you can use to count any rows that are not blank.