cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ramole
Post Prodigy
Post Prodigy

CountRow Html

Hi Everyone,

I am trying to count how many record for the same person on Html normally  i use two galleries and filter using this formula , Filter(colLoan,Status.Value="Open", Company.Value = GalleryRequests_4.Selected.Comppanyvalue)

but now as i am using on html to count is not letting me do it, can you please guide me the correct formula

Count2.PNG

 

Count.PNG

 

Thanks 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also close the item., please consider giving it Thumbs Up.
2 ACCEPTED SOLUTIONS

Accepted Solutions

@Ramole ,

You have been posting long enough on this forum to know to describe the actual problem in your first post and it would have been highly helpful the include the the gallery code, rather than post what looks like a simple issue and then provide later the complex code that needs firstly parsing before attempting to understand it. In future can you please use Format Text and post the code in a Text box like mine (the </> icon above)
I have parsed it below and included on the end what I believe will guide you in the right direction.
The problem with your HTML Concat is that you refer to colLoan, which one possible content of the gallery, but is the whole gallery. If the invoices are the simple total rows in the NewGroup, then you could do as below. If not, you need to rethink your gallery strategy. You would then need to change the Concat to simply include this column.

AddColumns(
   GroupBy(
      AddColumns(
         If(
            FilterGallery_4.Selected.Filter <> "All" && 
            !IsBlank(TextSearchBox_5.Text),              /*Case1*/
            Filter(
               colLoan,
               Status.Value = FilterGallery_4.Selected.Filter || 
               TextSearchBox_5.Text in LoanAmount || 
               TextSearchBox.Text in InvoiceNumber || 
               TextSearchBox_5.Text in LoanDueDate || 
               TextSearchBox_5.Text in Company.Value || 
               TextSearchBox_5.Text in Payment_x0020_frequency.Value
            ),
            FilterGallery_4.Selected.Filter = "All" && 
            !IsBlank(TextSearchBox.Text),              /*Case2*/
            Filter(
               colLoan,TextSearchBox_5.Text in Status.Value || 
               TextSearchBox_5.Text in InvoiceNumber || 
               TextSearchBox_5.Text in LoanAmount || 
               TextSearchBox.Text in Company.Value || 
               TextSearchBox_5.Text in Payment_x0020_frequency.Value
            ),
            FilterGallery_4.Selected.Filter = "All" && 
            IsBlank(TextSearchBox_5.Text),            /*Case3*/
            colLoan,
            FilterGallery_4.Selected.Filter <> "All" && 
            IsBlank(TextSearchBox_5.Text),           /*Case4*/
            Filter(
               colLoan,
               Status.Value = FilterGallery_4.Selected.Filter
            )
         ),
         "Comppanyvalue",
         Company.Value
      ),
      "Comppanyvalue",
      "NewGroup"
   ),
   "SumShippingFee",
   Sum(NewGroup,LoanAmount),
   "Status",
   First(
      Sort(
         NewGroup, 
         Status.Value, 
         Descending
      )
   ).Status.Value,
   "PhoneNum", 
   First(
      Sort(
         NewGroup,
         Company_x003a_Business_x0020_Pho.Value, 
         Descending
      ).Company_x003a_Business_x0020_Pho
   ),
   "Collect", 
   First(
      Sort(
         NewGroup,
         Payment_x0020_frequency.Value, 
         Descending
      ).Payment_x0020_frequency
   ),
   "OrderDatesFull",
   First(
      Sort(
         NewGroup,
         Created, 
         Descending
      )
   ).Created,
   "Invoice",
   First(
      Sort(
         NewGroup, 
         InvoiceNumber, 
         Descending
      )
   ).InvoiceNumber,
   "InvoiceDueDate",
   First(
      Sort(
         NewGroup, 
         LoanDueDate, 
         Descending
      )
   ).LoanDueDate,
   "TotalBalance",
   Sum(
      NewGroup, 
      Balance
   ),
   "Pending Invoices",
   CountRows(NewGroup)
)

 

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.

 

View solution in original post

@Ramole ,

Yes - you could think about doing a "parallel" collection structured with the same record selection as the gallery, but grouped in a manner that allows you to isolate the required outstanding invoices in a group you can count. Only someone with a good understanding of the logic behind the complexity of your current gallery would be able to do this.

My earlier guidance was to help you understand that you will get better and quicker responses if you set out your issue clearly with all relevant information including properly formatted code (which is much simpler to follow and why I always post answers this way).

Most responders are users who volunteer our spare time and only have a limited amount of it, so the clearer the post the more likely someone will take it on.

View solution in original post

11 REPLIES 11
WarrenBelz
Super User III
Super User III

Hi @Ramole ,

If you put this on a label,do you get a count?

CountRows( 
   Filter(
      colloan,
      Status.Value="Open" && 
      Company.Value = GalleryRequests_4.Selected.Comppanyvalue
   )
)

also how many records are in the list colloan?

 

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.

Hi @WarrenBelz 

 

50 records but the thing i am looking is to see how many records on same person example :  Thomas has a 5 records it should show me those records like 1 Overdue invoice on the html course the            GalleryRequests_4   is a Group By       and    GalleryRequests_7 is to filter and formula using                    Filter(colLoan,Status.Value="Open", Company.Value = GalleryRequests_4.Selected.Comppanyvalue)

 

That works fine but need it on html to do same  here is the formula on the html text 

<table>
<tr>

<th>Customer </th>
<th>Phone </th>
<th>Pending Invoices </th>
<th>Overdue Balance </th>

</tr>
<tr>"&
Concat(GalleryRequests_4.AllItems,


"<td>" & Comppanyvalue& " </td>
<td>" & PhoneNum.Company_x003a_Business_x0020_Pho.Value & " </td>
<td>" & CountRows(
Filter(
colLoan,
Status.Value="Open" &&
Company.Value = GalleryRequests_4.Selected.Comppanyvalue
)
)&" </td>
<td>" &TotalBalance&" </td>

","</tr><tr>") &"
</tr>
</table>

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also close the item., please consider giving it Thumbs Up.

@Ramole ,

Can you please give me the two pieces of information requested.

  • Do you get the correct count when you put that code on a label?
  • How many records are in the list?

Hi @WarrenBelz 

 

Yes i do get records and only the selected gallery record that is what i get 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also close the item., please consider giving it Thumbs Up.

Thanks @Ramole ,

So I make sure I am not misunderstanding you - please confirm.

You put this on a label and get the exact number you are expecting

CountRows( 
   Filter(
      colloan,
      Status.Value="Open" && 
      Company.Value = GalleryRequests_4.Selected.Comppanyvalue
   )
)

You put this is a HTML box and you do not.

<table>
   <tr>
      <th>Customer</th><th>Phone</th><th>Pending Invoices</th><th>Overdue Balance</th>
   </tr>
   <tr>"&
   Concat(
      GalleryRequests_4.AllItems,
      "<td>" & Comppanyvalue& "</td>
      <td>" & PhoneNum.Company_x003a_Business_x0020_Pho.Value & "</td>
      <td>" & 
	  CountRows(
	     Filter(
            colLoan,
            Status.Value="Open" &&
            Company.Value = GalleryRequests_4.Selected.Comppanyvalue
         )
      )&" </td>
      <td>" & TotalBalance &"</td>
      ","</tr><tr>"
   ) &"
   </tr>
</table>

What output do you get from this code? I just tested it at this end and got exactly what was expected

HTMLTestConcat.png

HTMLTest1.png

HTMLTest2.png

 

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.

Hi @WarrenBelz 

 

Thanks 

 

i think you misunderstood me the formula is fine and counting no problems, but this gallery GalleryRequests_4 is GroupBy   now when select the GroupBy gallery it will show the Pending Invoices all data records same as shown on the screenshot , i am trying to get to show each records how many records attached, 

 

Thanks 

 

Capture5.PNG 

 Here my formula on GalleryRequests_4 the GroupBy

 


AddColumns(
GroupBy(AddColumns(
If(
FilterGallery_4.Selected.Filter <> "All" && !IsBlank(TextSearchBox_5.Text),/*Case1*/
Filter(colLoan,Status.Value = FilterGallery_4.Selected.Filter || TextSearchBox_5.Text in LoanAmount || TextSearchBox.Text in InvoiceNumber || TextSearchBox_5.Text in LoanDueDate || TextSearchBox_5.Text in Company.Value || TextSearchBox_5.Text in Payment_x0020_frequency.Value),
FilterGallery_4.Selected.Filter = "All" && !IsBlank(TextSearchBox.Text),/*Case2*/
Filter(colLoan,TextSearchBox_5.Text in Status.Value || TextSearchBox_5.Text in InvoiceNumber || TextSearchBox_5.Text in LoanAmount || TextSearchBox.Text in Company.Value || TextSearchBox_5.Text in Payment_x0020_frequency.Value),
FilterGallery_4.Selected.Filter = "All" && IsBlank(TextSearchBox_5.Text),/*Case3*/
colLoan,
FilterGallery_4.Selected.Filter <> "All" && IsBlank(TextSearchBox_5.Text),/*Case4*/
Filter(colLoan,Status.Value = FilterGallery_4.Selected.Filter)),
"Comppanyvalue",Company.Value),"Comppanyvalue","NewGroup"),"SumShippingFee",Sum(NewGroup,LoanAmount),"Status",
First(Sort(NewGroup, Status.Value, Descending)).Status.Value,"PhoneNum", First(Sort(NewGroup,Company_x003a_Business_x0020_Pho.Value, Descending).Company_x003a_Business_x0020_Pho),"Collect", First(Sort(NewGroup,Payment_x0020_frequency.Value, Descending).Payment_x0020_frequency),"OrderDatesFull",
First(Sort(NewGroup,Created, Descending)).Created,"Invoice",
First(Sort(NewGroup, InvoiceNumber, Descending)).InvoiceNumber,"InvoiceDueDate",
First(Sort(NewGroup, LoanDueDate, Descending)).LoanDueDate,
"TotalBalance",
Sum(NewGroup, Balance))

 

 

here is the formula i use to get records from the GroupBy 
Filter(colLoan,Status.Value="Open", Company.Value = GalleryRequests_4.Selected.Comppanyvalue)

here the difference now is using as a Html text so i can print it and each person on the html to show is own history like example Tom

Order 002

Order 003

 

john 

order 0010

order 0011

order 0012

 

now john has a 3 Pending invoices and tom 2 invoices 

 

Thanks 

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also close the item., please consider giving it Thumbs Up.

@Ramole ,

You have been posting long enough on this forum to know to describe the actual problem in your first post and it would have been highly helpful the include the the gallery code, rather than post what looks like a simple issue and then provide later the complex code that needs firstly parsing before attempting to understand it. In future can you please use Format Text and post the code in a Text box like mine (the </> icon above)
I have parsed it below and included on the end what I believe will guide you in the right direction.
The problem with your HTML Concat is that you refer to colLoan, which one possible content of the gallery, but is the whole gallery. If the invoices are the simple total rows in the NewGroup, then you could do as below. If not, you need to rethink your gallery strategy. You would then need to change the Concat to simply include this column.

AddColumns(
   GroupBy(
      AddColumns(
         If(
            FilterGallery_4.Selected.Filter <> "All" && 
            !IsBlank(TextSearchBox_5.Text),              /*Case1*/
            Filter(
               colLoan,
               Status.Value = FilterGallery_4.Selected.Filter || 
               TextSearchBox_5.Text in LoanAmount || 
               TextSearchBox.Text in InvoiceNumber || 
               TextSearchBox_5.Text in LoanDueDate || 
               TextSearchBox_5.Text in Company.Value || 
               TextSearchBox_5.Text in Payment_x0020_frequency.Value
            ),
            FilterGallery_4.Selected.Filter = "All" && 
            !IsBlank(TextSearchBox.Text),              /*Case2*/
            Filter(
               colLoan,TextSearchBox_5.Text in Status.Value || 
               TextSearchBox_5.Text in InvoiceNumber || 
               TextSearchBox_5.Text in LoanAmount || 
               TextSearchBox.Text in Company.Value || 
               TextSearchBox_5.Text in Payment_x0020_frequency.Value
            ),
            FilterGallery_4.Selected.Filter = "All" && 
            IsBlank(TextSearchBox_5.Text),            /*Case3*/
            colLoan,
            FilterGallery_4.Selected.Filter <> "All" && 
            IsBlank(TextSearchBox_5.Text),           /*Case4*/
            Filter(
               colLoan,
               Status.Value = FilterGallery_4.Selected.Filter
            )
         ),
         "Comppanyvalue",
         Company.Value
      ),
      "Comppanyvalue",
      "NewGroup"
   ),
   "SumShippingFee",
   Sum(NewGroup,LoanAmount),
   "Status",
   First(
      Sort(
         NewGroup, 
         Status.Value, 
         Descending
      )
   ).Status.Value,
   "PhoneNum", 
   First(
      Sort(
         NewGroup,
         Company_x003a_Business_x0020_Pho.Value, 
         Descending
      ).Company_x003a_Business_x0020_Pho
   ),
   "Collect", 
   First(
      Sort(
         NewGroup,
         Payment_x0020_frequency.Value, 
         Descending
      ).Payment_x0020_frequency
   ),
   "OrderDatesFull",
   First(
      Sort(
         NewGroup,
         Created, 
         Descending
      )
   ).Created,
   "Invoice",
   First(
      Sort(
         NewGroup, 
         InvoiceNumber, 
         Descending
      )
   ).InvoiceNumber,
   "InvoiceDueDate",
   First(
      Sort(
         NewGroup, 
         LoanDueDate, 
         Descending
      )
   ).LoanDueDate,
   "TotalBalance",
   Sum(
      NewGroup, 
      Balance
   ),
   "Pending Invoices",
   CountRows(NewGroup)
)

 

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.

 

View solution in original post

Hi @WarrenBelz 

 

Thanks for correcting me but what i am looking to solve is html formula to use total pending invoices for each customer in Html not gallery, my GroupBy formula is working fine 

 

Capture.JPG

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also close the item., please consider giving it Thumbs Up.

@Ramole ,

You did not answer anything I posted on the reasons for the problem.

If the CountRows on the Grouped items (NewGroup) (as I have added) does not work, you may have to re-think your gallery strategy as the number you are looking for has to exist within the gallery structure as it stands if you are wanting to directly Concat this in your HTML.

 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (43,460)