Filter and GroupBy with Lookup column doesn't work

[Manarjomar]

Hi all,

I have some lists linked by lookup columns.

List "Artículos" (Items in English), with the field "ItemName" and "UnidadMedida" (Unity, M, M2, M3...), and other things...

List "Gestión de Almacén" (Warehouse Management), with many fields but for now only is important:

- The Lookup field "Artículo" from the list "Artículos"

- "NSeguimiento" (is like Tracking Number)

- "Qty"

- "Un" automatic lookup by the item name to get the "UnidadMedida" value.

With my list "Gestión de Almacén" I can record the entries and withdrawls of material with serials and # references.

My issue is that I want filter by "NSeguimiento" that match with a field on form, and Group By "Artículo" and show all in a table with the fields:

Item Name / Qty / Un.

The "Qty" must be a Sum that get all records in that "NSeguimiento" an for each Item, and the "Un" must be get the "UnidadMedida"

I try but I have the error: "Artículo" is not a field.

This is my original code for the table:

GroupBy(Filter('Gestión de Almacén';NSeguimiento=TextInput1.Text);"Artículo";"Nombre Artículo")

Later, I tried with this:

GroupBy(Filter('Gestión de Almacén';NSeguimiento=TextInput1.Text);Artículo.Value;"Nombre Artículo")



GroupBy(Filter('Gestión de Almacén';NSeguimiento=TextInput1.Text);Value('Gestión de Almacén'.Artículo);"Nombre Artículo")

And show in the datafield on the other list, tell me the name for "Artículo" is "Art_x00ed_culo", and I try with that name diferent things:

GroupBy(Filter('Gestión de Almacén';NSeguimiento=TextInput1.Text);"Art_x00ed_culo";"Nombre Artículo")

GroupBy(Filter('Gestión de Almacén';NSeguimiento=TextInput1.Text);Art_x00ed_culo.Value;"Nombre Artículo")

Thank you.

[iAm_ManCat]

Hi @Manarjomar 

"Nombre Artículo" will probably have a different name on SharePoint, so you will need to follow the steps I outlined in this other post to get the correct SharePoint field name:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Sortbycolumn-not-working/m-p/537864/highligh...

Then use that instead of  "Nombre Artículo" - the reason being that SharePoint does not handle accented characters well and will have replaced those in the naming.

Please let me know if you run into any other issues while doing this,

Cheers,

Sancho

[Manarjomar]

@iAm_ManCatThank you,

As I understand the function "GroupBy", the last argument, is only a personal text for the group name, so "Nombre Artículo", I think that is not the problem.

The problem is that PowerApps don't recognize the colum "Artículo", however I change the name to "ItemName", and "Nomrbe Artículo" to "Name" and doesn't work neither.

GroupBy(Filter('Gestión de Almacén';NSeguimiento=TextInput1.Text);"ItemName";"Nombre")

I don't know why, but I don't have problem to make reference to this field with other functions.

Could you tell me more options to find where is my mistake?

Thank you.

[iAm_ManCat]

Could you check the SharePoint settings that I mentioned in that other post for that field - renaming a field unfortunately does not change the base name that it was given by SharePoint when created, and this is what the Sort and Group is looking for, that is why it is in " "  and not in ' '

[Manarjomar]

Hi @iAm_ManCat ,

Yes, I checked and I saw that the field main name didn't change. So I deleted it and created new field with the name "ItemName" and ensure that the name of this field is "ItemName", but I have the same problem.

Howeaver if I change the field for grouping, i.e. "serial", the function works. "Serial" is a normal textinput field, but is not that I'm looking for.

Regards.

[iAm_ManCat · Accepted solution]

Hi @Manarjomar 

Could you try something similar to this?

GroupBy(
        Filter(
               AddColumns(
               'Gestión de Almacén',
               "ItemNameList",
               ItemName.Value
               )
               ;
               NSeguimiento=TextInput1.Text
        )
        ;
        "ItemNameList"
        ;
        "Nombre"
)

Here we are adding a column using the value of the choice column, then grouping by that new column which should contain the choice values.

Let me know if that works for your situation,

Cheers,

Sancho

[Manarjomar]

Hi @iAm_ManCat ,

Works perfect!!

Now I can see in your example a different structure to join the functions.

First Add new column, and later GroupBy that column and filter...

Finally, my funtion is this one:

AddColumns(
GroupBy(
        Filter(
               AddColumns(
               'Gestión de Almacén'; 	/*Origen de datos*/
			   "ItemNameList";			/*Nombre de la nueva columna que estamos agregando*/
               ItemName.Value			/*Origen del valor de la nueva columna*/
               );
			  NSeguimiento=TextInput1.Text /*Condición para realizar el filtro*/
        )
        ;
        "ItemNameList";		/*Nombre de la columna por la que agrupar y que hemos creado antes*/
        "Ud";				/*Columna que añadimos*/
	    "Nombre"			/*Nombre de la agrupación*/
);

    "Cantidad";			/*Nombre que damos a la columna para presentar el resultado de la suma*/
    Sum(
        Nombre;			/*Nombre de la columna para la agrupación que hemos hecho antes*/
        Qty				/*Columna que se usará para la suma*/
    )
)

I added a Sum column by that name of group and the Ud of that item.

Thank you very much.