sql server table - count & exclude some records?

[Anonymous]

hi,

just started using powerapps today so please be gentle.

so far i have setup a gateway and successfully connected to a local db.

I have a table which can have up to 2m rows in it at any one time. We currently have the following query that counts these records and groups them into country code for us, to show us how many records per country are in the table at that time. We also exclude any records which have a date in the future (fieldname : fromdate)

query we use is:

Select COUNT(companyid) QueueCount, companyid
from Table name
where fromdate < GETDATE()
group by companyid

which basically returns something like:

20,000 / UK

10,000 / DE

378 / NL

120,000 / FR

4000 / ES

I can connect powerapps and view the table but at the moment i just see all records.

is there anyway in powerapps to create a screen that would show the user the results in a similar fashion to the results of the query?

Sorry if this is a really dumb question.

many thanks

Dave

[Maxwell]

Hey Dave,

Welcome to the PowerApps community!

I think I've got a solution that will accomplish what you want. Ultimately, you'll end up setting the "Items" property of a gallery to something similar to the following:

DropColumns(AddColumns(GroupBy(Filter(Companies, DateTimeValue(Time) < Now()), "Location", "CompaniesInLocation"), "Total", CountIf(CompaniesInLocation, true)),"CompaniesInLocation")

Now, that statement is pretty inscrutable crammed onto a single line.

Here's an example that will break down what we're doing:

First, I've set up some data that I think approximates the information you're working with:

ClearCollect(Companies, {Name: "Foo Inc", Location: "UK", Time:"3/23/2016 5:55:55 PM"}, {Name: "Foo Inc", Location: "UK", Time:"3/23/2018 5:55:55 PM"},{Name: "Foo Inc", Location: "DE", Time:"3/23/2018 5:55:55 PM"},{Name: "Foo Inc", Location: "UK", Time:"3/23/2018 5:55:55 PM"},{Name: "Foo Inc", Location: "NL", Time:"3/23/2016 5:55:55 PM"},{Name: "Foo Inc", Location: "NL", Time:"3/23/2016 5:55:55 PM"},{Name: "Foo Inc", Location: "NL", Time:"3/23/2018 5:55:55 PM"},{Name: "Foo Inc", Location: "FR", Time:"3/23/2016 5:55:55 PM"},{Name: "Foo Inc", Location: "FR", Time:"3/23/2018 5:55:55 PM"},{Name: "Foo Inc", Location: "ES", Time:"3/23/2018 5:55:55 PM"})

Basically, I've set up a collection called "Companies" whose entries have the following information:

• "Name" - The name of the company (this is largely irrelevant to the example)
• "Location" - A two letter code corresponding to the country that the company is located in
• "Time" - The time that the entry was created (these are all either a year in the future or a year in the past)

Next, I've set up a button which runs the following when clicked:

ClearCollect(NonFutureCompanies, Filter(Companies, DateTimeValue(Time) < Now()));
ClearCollect(NonFutureCompaniesByLocation, GroupBy(NonFutureCompanies, "Location", "CompaniesInLocation"));
ClearCollect(CompanyCountByLocation, AddColumns(NonFutureCompaniesByLocation, "Total", CountIf(CompaniesInLocation, true)))

These commands are the individual parts of that massive command I posted up near the top. Let's break down what each of these commands is doing.

ClearCollect(NonFutureCompanies, Filter(Companies, DateTimeValue(Time) < Now()))

Here, we're creating a new collection called "NonFutureCompanies". We're setting it equal to a subset of the "Companies" collection by using the "Filter()" function. Specifically, we're removing any records that have a "Time" field in the future.

ClearCollect(NonFutureCompaniesByLocation, GroupBy(NonFutureCompanies, "Location", "CompaniesInLocation"))

Next, we're taking all of the "Companies" records that aren't in the future and grouping them by "Location". We're storing this new table into the collection "NonFutureCompaniesByLocation". This table will have two columns: "Location" and "CompaniesInLocation". This table will also have a number of rows equal to the number of distinct "Location" values. The information stored in "CompaniesInLocation" will be a table whose columns are the same as the original table, sans the "Location" column, which we are grouping on.

ClearCollect(CompanyCountByLocation, DropColumns(AddColumns(NonFutureCompaniesByLocation, "Total", CountIf(CompaniesInLocation, true)),"CompaniesInLocation"))

Finally, we create a collection, "CompanyCountByLocation", that has only the information we need. We take a copy of the "NonFutureCompaniesByLocation" collection we made in the last step and add a new column to it, "Total". We populate the "Total" field in each record by counting the number of records in its "CompaniesInLocation" field:

Concatenate(ThisItem.Total, " / ", ThisItem.Location)

Then, we remove the "CompaniesInLocation" column, since we don't want to display this information:

DropColumns(X, "CompaniesInLocation")

At this point, all we have to do is make and configure a text gallery. Set the gallery's "Item" property to "CompanyCountByLocation" and set its display properties ("Body", "Heading", "Subtitle") to suit your needs. If you wanted the information to be formatted identically to your initial post, you could use the following:

Concatenate(ThisItem.Total, " / ", ThisItem.Location)

Let me know if you have any questions about this example,

  Maxwell, PowerApps-Staff

 PS. Here are some links that detail the functions I used above:

• Filter: https://powerapps.microsoft.com/en-us/tutorials/function-filter-lookup/
• GroupBy: https://powerapps.microsoft.com/en-us/tutorials/function-groupby/
• Count: https://powerapps.microsoft.com/en-us/tutorials/function-table-counts/
• AddColumns/DropColumns: https://powerapps.microsoft.com/en-us/tutorials/function-table-shaping/
• General Table Info: https://powerapps.microsoft.com/en-us/tutorials/working-with-tables/

[Anonymous]

good morning from the uk!

i can only thank you maxwell for a superb response.

I will go through it all and read up on the various functions and report back , no doubt with further questions 🙂

many thanks again!