Adding Groupby with AddColumns - Formula help - Sharepoint relationship lists

(0) Share

Report

Posted on by venka91

1,119

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.

Gallery view :

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
venka91 1,119 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
venka91 1,119 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

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.

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@venka91 ,

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

Was this reply helpful? Yes No
Verified answer

venka91 1,119 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
EddieE 4,650 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No