Groupby by a Lookup field - giving error "Expected a text, numeric, boolean, or date/time value - even when I use Text() in my lookup

[Z_Lukowski]

Hello Power Users,

I have a Gallery in which I have a Lookup column ("DogColumn"),

ThisItem.'Dog'.'DogName'

Where 'Dog' is a Lookup column in my Table Shelter(which is source of my Gallery btw)  to Table Dogs, and 'DogName' is primary column there

I want to Groupby my items by 'DogName'

So I've created:

Groupby(Gallery_Dogs, "DogColumn", "AnyNameWIlldo") - which should group my records based on DogName

but I get error:

"Expected a text, numeric, boolean, or date/time value"

When Ive changed "DogColumn" to Text(ThisItem.'Dog'.'DogName') it did not help, my datatype is text and yet it expects text value in error message:

Of course my Gallery is displaying properly and all the dog names are there, they are correct, based on Lookup field, so the issue is not there.

@RandyHayes  - created my new topic following your advice,

Would appreciate any kind of help, and wish you good day to all kind Power Users out there 🙂

Regards,

Zbigniew

[RandyHayes · Accepted solution]

@Z_Lukowski 

Thanks for posting a new thread...it will get more attention.

You will not be able to group by a lookup column without a couple steps.

I am assuming (since you mentioned Gallery_Dogs in your formula sample) that you want to Group By the items in your Gallery

Your formula should be as follows:

GroupBy(
    AddColumns(
        Gallery_Dogs.AllItems,
        "_dogName", Dog.DogName
    ),
    "_dogName",
    "records"
)
    

This will return a table of grouped dog names and their associated records.

I hope this is helpful for you. 

[Z_Lukowski]

You @RandyHayes are truly my saviour, big online hug 🤗  If you ever in Warsaw leave me a message on forum, and I owe you a round,drink, smoothie or whatever you like. 

[RandyHayes]

@Z_Lukowski 

Happy to help!!  I will certainly keep your invite in mind should I be in the area 😁

[Z_Lukowski]

@RandyHayes And what If I would like to count those dog names based on my groupby and Filterout results with Records <>2, example on screenshot, is this even possible?

Best Regards

Zbigniew

[RandyHayes]

@Z_Lukowski 

Sure thing, you can add more columns to contain that information:

AddColumns(
    GroupBy(
        AddColumns(
            Gallery_Dogs.AllItems,
            "_dogName", Dog.DogName
        ),
        "_dogName",
        "records"
    ),
    "_dogCount", CountRows(records)
)

_dogCount will have the count of the dog names of that group.