cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
venka91
Super User
Super User

Adding Groupby with AddColumns - Formula help - Sharepoint relationship lists

Hi team,

 

I have two lists namely cow master and calf. I am trying to create a gallery based on breed type, it shows the number of male calf, female calf and alive cows and its total.

 

I am writing the formula as below.

 

ClearCollect(varTotalAnimalGallery, GroupBy(AddColumns(Filter(AddColumns(SSS_calf,"cow_details",LookUp(SSS_CowMaster,cow_breed.Value = SSS_calf[@calf_breed])),calf_alive = true),"BreedType",calf_breed.Value,"CowCount",cow_details.cow_gender.Value),"BreedType","BreedGroup")

 

both cow_breed and calf_breed is a choice column. However when i write the formula, i am getting incompatible type to compare.

 

I am not why it is showing the error.

 

venka91_0-1653980684669.png

 

Gallery view :

venka91_1-1653980778943.png

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
venka91
Super User
Super User

Okay,,

 

i have derived the values like this. I do not is there any other straight forward way to do this.

I have created a collection with the rows in each list.

ClearCollect(varTotalAnimalGallery, SSS_calf,SSS_CowMaster);

 

And i have grouped the Collection by Breed Type using the following formula:

ClearCollect(SortedTAG,GroupBy(AddColumns(varTotalAnimalGallery,"AnimalBreed",breed.Value),"AnimalBreed","AnimalBreedGroup"))

 

Now whenever i add new item in Cow list or calf list, gallery shows the value correct. Also even if there is no breed type in any of the list the gallery shows the value accordingly.

 

The Item property of my gallery i gave as SortedTAG

 

I am not sure from performance point of view this is correct or any other simple way to do this.

 

View solution in original post

7 REPLIES 7
WarrenBelz
Super User
Super User

HI @venka91 ,

I will post the first item I can see (which explains the error) - the Lookup returns a record - you need a text value. ( YourCowField in the below)

ClearCollect(
   varTotalAnimalGallery, 
   GroupBy(
      AddColumns(
         Filter(
            AddColumns(
               SSS_calf,
               "cow_details",
               LookUp(
                  SSS_CowMaster,
                  cow_breed.Value = calf_breed
               ).YourCowField
            ),
            calf_alive
         ),
         "BreedType",
         calf_breed.Value, 
         "CowCount",
         cow_details.cow_gender.Value
      ),
      "BreedType",
      "BreedGroup"
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

 

I wrote a formula like this, it did not give me any errors.

 

GroupBy(AddColumns(Filter(AddColumns(SSS_calf,"cow_details",LookUp(SSS_CowMaster,cow_breed.Value = SSS_calf[@calf_breed].Value)),calf_alive = true),"BreedType",calf_breed.Value,"CowCount",cow_details.cow_gender.Value),"BreedType","BreedGroup")

 

However the formula is not giving me the desired result. When i add a cow or add a calf the count should change accordingly in my gallery. But the above formula shows the change in gallery only when i add a new calf.

 

Can you please help me where i am doing wrong ?

My logic i am trying to write is , when a cow or calf added in the respective list, based on the Breed type i am just showing the count of each animals.

 

Basically the lookup formula what i am using, choosing the cow records according to calf records. But i just need to add a column in calf list with all the records in cow list. So i can derive the exact count of animals.

 

HI @venka91 ,

I would need your model to get my mind around all of that completely, but the CowCount is only going to reflect the value in cow_gender in the record produced by the lookup  (added field) cow_details.

Basically i have two lists. cow_master and calf_master

each cow_master and calf_master has a column "Breed". Breed is a choice type column.

 

Now in Gallery I would like to show Grouping by Breed, how many cows, male calf and female calf are there.

the Gallery i would like to show as below.

 

venka91_0-1654061744458.png

 

The formula what i used, not giving the count whenever a new item added in Cow or calf, because it add the columns of cow table with calf table based on the condition

 

LookUp(SSS_CowMaster,cow_breed.Value = SSS_calf[@calf_breed].Value)),calf_alive = true)

 

so only when the calf list has added with new item, it checks the corresponding item in cowmaster and updates the count.

 

By my requirement is, the new item added in cow table or calf table, the gallery should be showing the count, so number cows in farm will be correct.

 

I am not sure how to derive this value. Any ideas please.

@venka91 ,

Sorry, I simply cannot visualise this from your descriptions - I will tag a colleague @EddieE who likes formulas and give him a go.

venka91
Super User
Super User

Okay,,

 

i have derived the values like this. I do not is there any other straight forward way to do this.

I have created a collection with the rows in each list.

ClearCollect(varTotalAnimalGallery, SSS_calf,SSS_CowMaster);

 

And i have grouped the Collection by Breed Type using the following formula:

ClearCollect(SortedTAG,GroupBy(AddColumns(varTotalAnimalGallery,"AnimalBreed",breed.Value),"AnimalBreed","AnimalBreedGroup"))

 

Now whenever i add new item in Cow list or calf list, gallery shows the value correct. Also even if there is no breed type in any of the list the gallery shows the value accordingly.

 

The Item property of my gallery i gave as SortedTAG

 

I am not sure from performance point of view this is correct or any other simple way to do this.

 

@venka91 

If your code is working, there's no need to fix it. As @WarrenBelz has said, your situation is a little complex making it difficult to provide specific help without further details about your data structure eg screen shots of the 2 list with appropriate data.

 

The only performance issue you will face is that ClearCollect() and GroupBy() will only return a MAX of 2000 records but if you'll never need that many it's fine.

 

As I said, if your code is working, mark your answer as the solution and you are done 🙂 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,992)