cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Manarjomar
Resolver I
Resolver I

Filter and GroupBy with Lookup column doesn't work

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")

 

 

Screenshot_1.pngScreenshot_2.png

 

Thank you.

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

6 REPLIES 6
iAm_ManCat
Super User
Super User

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

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


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

 

 

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 ' '

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


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.

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

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


View solution in original post

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.

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,953)