cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bri
Level: Powered On

Count all things from one table in another

The Problem:

I have a mdoels table: tableM, and an inventory table: tableT, i want to loop through my models table, tableM, and count how many of each model in tableM exsist in invnetory, tableT. I want to output the count into a collection if possible.

 

All tables are from a SQL database, and i wish i could write queries to do this, but i can not with powerapps 😞

 

I do not want to hard code the models and the countif functions because we are always adding and deleting models.

 

My resulting count collection should just be two columns like this

ModleName        TotalCount

ModelA                23

ModelB                12

ModelC                 48

 

and so on...

2 REPLIES 2
Community Support Team
Community Support Team

Re: Count all things from one table in another

Hi Bri,

 

I am not sure about the Models table. Will you create a new row for each model every time you edit the table? Or will you update the row for each model?

 

I am working on this issue and trying to figure out a workaround.


Best regards,
Mabel Mao

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
irsimeonov
Level: Powered On

Re: Count all things from one table in another

Hi Bri

 

One way to achieve that is though using a gallery to visualise your results and countrows() to calculate the number of model occurences (keep in mind though that the countrows() function is not delegatable to any data sources, which means that if you have more than 500 models for a specific type of model e.g. 550 ModelA models, then the countrows function will only return 500):

 

This solution will not load your table into a collection:

 

1. Create a gallery and set its Items property to tableM datasource

2. Insert a text box into the gallery and set its Text property to tableM.ModelName

3. Insert another text box to calculate the count, set its Text property as

                             CountRows(Filter(tableT, ModelName = ThisItem.ModelName))

Note that the Model name after tableT in the formula above is the name of the column in tableT that contains the model names, while ThisItem.ModelName gives you the current model name from tableM.

 

A better way to visualise the count in the second text box above is by show "more or equal to 500" if the countrows equals to 500, you can use an If() statement for that.

 

If you want to load that as a collection, then one way is to set OnVisible property of the app screen to include the following:

1. AddColumns(ClearCollect(collectionModelName, tableM.ModelName), "Model Count", 

                                                                                                     CountRows(Filter(tableT, ModelName = ModelName))

Note that the first ModelName in the countrows() function will come from tableT while the second ModelName will come from the collectionModelName collection.

THe result of the above should be a collection called collectionModelName that have two columns, the first called ModelName and second - Model Count.

 

Ivan

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 309 members 5,326 guests
Please welcome our newest community members: