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

Need help with filter and formula on either a gallery or a table

I'm trying to work through the best method of setting up a reporting screen for a SPO list.

This list has a variety of text columns and number columns. Its a Print Request list.

I would like to be able to filter either a table or a gallery and distinctly show each entity to be billed and the total pages printed, total folds made by using the number of pages printed if folding was selected in a drop down list, total cuts which is just an entered number, and bulk mail cost which is also an entered currency number.

Field names are as follows:

Billto

TotalPagesPrinted

BasicFinishingOptions (Drop down list where "Fold" is a selection)

CutQty

BulkMailCost

 

In a month i might have multiple print requests for each entity. So i want to be able to filter a table or a gallery to only show distinct entities and the totals for the previous month for each entity for the columns mentioned above. 

 

I'm struggling with how this should all look. Anyone have suggestions for the filter and other possible needed formulas?

6 REPLIES 6
Community Support Team
Community Support Team

Re: Need help with filter and formula on either a gallery or a table

Hi @SeanTambling ,

Could you please share a bit more about your scenario? 

Do you want to display distinct entities within a Gallery , and the Totals for previous month for column mentioned above?

 

Based on the needs that you mentioned, I think the Gallery, GroupBy function and AddColumns function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

1. Add a Gallery in your app, set the Items property to following:

AddColumns(
          GroupBy(
                  Filter('Print Request list', Year(Created)=Year(Today()), Month(Created) = Month(Today())-1),  /* <-- Filter previous month's records */
                  "Billto",
                  "GroupData"
          ),
          "SumTotalsPages",
          Sum(GroupData, TotalPagesPrinted),
          "SumFoldOption",
          CountRows(Filter(GroupData, BasicFinishingOptions.Value = "Fold")),
          "SumCutQty",
          Sum(GroupData, CutQty),
          "SumBuilMailCost",
          Sum(GroupData, BulkMailCost)
)

2. Add some Label controls within above Gallery, then set the Text property to following:

ThisItem.Billto
ThisItem.SumTotalsPages
ThisItem.SumFoldOption
ThisItem.SumCutQty
ThisItem.SumBuilMailCost

After that, the Totals for columns mentioned above for each Entity would be displayed in above Gallery.

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

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

Re: Need help with filter and formula on either a gallery or a table

Hi Kris,

Thank you for your quick reply. Sorry for my delay, been sick today.

I've added the gallery and the formula you provided to the items control for it. There are no errors, but there is also no data showing. I do have test data in list. I would assume the gallery is blank because the formula is looking for date created as last month. I have a column in the list labeled CompletedDate. How would i substitute CompletedDate into the formula you've provided?

Community Support Team
Community Support Team

Re: Need help with filter and formula on either a gallery or a table

Hi @SeanTambling ,

Do you want to filter your Gallery Items based on the CompletedDate column?

 

Please consider modify your formula as below:

 Add a Gallery in your app, set the Items property to following:

AddColumns(
          GroupBy(
                  Filter('Print Request list', Year(CompletedDate)=Year(Today()), Month(CompletedDate) = Month(Today())-1),  /* <-- Filter previous month's records based on CompletedDate column */
                  "Billto",
                  "GroupData"
          ),
          "SumTotalsPages",
          Sum(GroupData, TotalPagesPrinted),
          "SumFoldOption",
          CountRows(Filter(GroupData, BasicFinishingOptions.Value = "Fold")),
          "SumCutQty",
          Sum(GroupData, CutQty),
          "SumBuilMailCost",
          Sum(GroupData, BulkMailCost)
)

 

If you want to filter your Gallery Items based on CompletedDate column value which is in current Month (rather than previous month), please modify above formula as below:

AddColumns(
          GroupBy(
                  Filter('Print Request list', Year(CompletedDate)=Year(Today()), Month(CompletedDate) = Month(Today())),  /* <-- Filter current month's records based on CompletedDate column */
                  "Billto",
                  "GroupData"
          ),
          "SumTotalsPages",
          Sum(GroupData, TotalPagesPrinted),
          "SumFoldOption",
          CountRows(Filter(GroupData, BasicFinishingOptions.Value = "Fold")),
          "SumCutQty",
          Sum(GroupData, CutQty),
          "SumBuilMailCost",
          Sum(GroupData, BulkMailCost)
)

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

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

Re: Need help with filter and formula on either a gallery or a table

Hi Kris,

Thank you so much for your help. I didn't realize it yesterday but i was getting 1 error in the formula, and I'm terrible with diagnosing formula errors when it comes to using an = (equal sign). The error occurs here:

 

 CountRows(Filter(GroupData, BasicFinishingOptions.Value = "Fold")),

The error says its an invalid argument. Is it because "Fold" is a selection from a dropdown box? If so, how do you fix it? 

Community Support Team
Community Support Team

Re: Need help with filter and formula on either a gallery or a table

Hi @SeanTambling ,

Could you please share a bit more about the "BasicFinishingOptions" field in your SP List? Is it a Choice type column?

Could you please share a bit more about the "BasicFinishingOptions" Dropdown box in your app?

 

If the "BasicFinishingOptions" field is a Choice type column in your SP List, I think the formula I provided above is right. If the  "BasicFinishingOptions" field is a Text type column in your SP List, please consider modify the formula as below:

CountRows(Filter(GroupData, BasicFinishingOptions = "Fold"))

Note: The "BasicFinishingOptions" represents the field column name in your SP List rather than the name of the Dropdown box control in your app.

 

Best regards,

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

Re: Need help with filter and formula on either a gallery or a table

@v-xida-msft ,

Thank you for sticking with me on this. It is a choice field in SPO.

 

BFO-ScreenShot.png

 

I still get the red squiggly under the equal regardless of with .Value or without.

 

BFO-ScreenShot2.png

 

BFO-ScreenShot3.png

 

If you have any more suggestions, I'm all ears!

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 (Last 30 Days)
Users online (5,067)