Re: Unable to display data in HTML textbox

menonv · ‎04-06-2020

Hi I am new to Powerapps. I have developed an app wherein data is displayed onto a HTML text box, which is within a nested galleries.

I have used nested galleries to organise my data.

The HTML box uses Concat function and picks data from three sharepoint lists. I have used several functions in the HTML box viz. Sum, Distinct, filter etc.

However I am facing some issues in displaying data. The HTML box does not display data on all ocassions. Refer attachments, one of the occassion, data for "Earthworks in Formation" was not displaying.

What could be the issue over here? How do I overcome this inconsistency ?

Thanks,

WarrenBelz · ‎04-06-2020

Hi @menonv ,

This first thing I would be looking at would be the actual data that is being displayed by Concat in the HTML box. A good test would be to put a label on the screen with CountRows(ThatDataSourceName) and see what that is when the HTML is not showing.

Is this a collection or directly filtered data being displayed?

v-xida-msft · ‎04-07-2020

Hi @menonv ,

Could you please share a bit more about the Concat formula you used within your app?

Are there some error message with the Concat function?

Firstly, please make sure you have filtered out these results you want to display within the Html Text control. You could consider ignore the HTML tag, just type the Concat function within the HTML Text control, then check if the expected results could be displayed.

Please also check and see if the following blog or video would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/html-email-reporting-with-tabular-data/

https://www.youtube.com/watch?v=AnERfGIE8gw

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.

WarrenBelz · ‎04-07-2020

Hi @menonv ,

A couple of options here - please tag whichever you wish to continue the thread with.

menonv · ‎04-07-2020

Hi @WarrenBelz ,

Thanks for your reply, I have tried your suggestion of placing a label to countrows. I do not think there is any issue in referring to the data. However the app went even more unreliable. I do not understand why that is happening

I suspect that it is because of complicated formulas that I have used within the HTML box, which could be causing this.

A simplified version of the code is as follows

Concat ( Filter from Datasource 1, Lookup Result in Datasource 1, Lookup Result in Datasource 1, Filter result in Datasource 2 and Sum, Filter Result in Datasource1 and sum, Lookup result in Datasource 3, Filter Result in Datasource 1 and sum, Filter Result in Datasource 1 and sum, Lookup in Datasource 2)

Any Ideas how to resolve this?,

Thanks

Code in HTML textbox

XXXX

Concat(Distinct(Filter(Project_Actual_Data, Actual_Target_Start_Date = Dte_Ref_4.Text, PROJECT_ID = Dropdown1_11.Selected.Result, MA_SEQ = ThisItem.Result, SEC_ID = Label8_3.Text),SA_SEQ),"<table><col width = 100><col width = 210><col width = 100><col width = 100><col width = 125><col width = 100><col width = 120><col width = 110><tr><td>" & Result & "</td><td>" & LookUp(Filter(Project_Actual_Data, Actual_Target_Start_Date = Dte_Ref_4.Text, PROJECT_ID = Dropdown1_11.Selected.Result, MA_SEQ = ThisItem.Result), SA_SEQ = Result,SA_Title)&"</td><td>" & LookUp(Filter(Project_Actual_Data, Actual_Target_Start_Date = Dte_Ref_4.Text, PROJECT_ID = Dropdown1_11.Selected.Result, MA_SEQ = ThisItem.Result), SA_SEQ = Result,SA_Unit)&"</td><td>" & Sum(Filter(Project_Scope_Data, SA_SEQ = Result, PROJECT_ID = Dropdown1_11.Selected.Result, SEC_ID =Label8_3.Text ), Scope_Work_Quantity)&"</td><td>" & Text(Sum(Filter(AddColumns(Filter(Project_Actual_Data, SEC_ID = Label8_3.Text, SA_SEQ = Result, PROJECT_ID = Dropdown1_11.Selected.Result), "S_Month", Month(Actual_Target_Start_Date), "S_Year", Year(Actual_Target_Start_Date),"S_Day", Day(Actual_Target_Start_Date)), Date(S_Year,S_Month,S_Day)<= Dropdown1_10.Selected.Result), Actual_Work_Quantity),"#")

& "</td><td>" & LookUp(Filter(AddColumns(Filter(Project_Agreed_Data, SEC_ID = Label8_3.Text,SA_SEQ = Result),"A_Month", Month(Agreed_Target_Start_Date), "A_Year", Year(Agreed_Target_Start_Date)), A_Year = Year(Dropdown1_10.Selected.Result), A_Month = Month(Dropdown1_10.Selected.Result)), SA_SEQ = Result,Agreed_Work_Quantity) & "</td><td>" & Sum(Filter(Project_Actual_Data, SEC_ID = Label8_3.Text, SA_SEQ = Result, PROJECT_ID = Dropdown1_11.Selected.Result, Actual_Target_Start_Date = Dte_Ref_4.Text), Actual_Work_Quantity) & "</td><td>" &

Sum(Filter(AddColumns(Filter(Project_Actual_Data, SEC_ID = Label8_3.Text, SA_SEQ = Result, PROJECT_ID = Dropdown1_11.Selected.Result), "P_Month", Month(Actual_Target_Start_Date), "P_Year", Year(Actual_Target_Start_Date), "P_Date", Day(Actual_Target_Start_Date)), P_Month = Month(Dropdown1_10.Selected.Result), P_Year = Year(Dropdown1_10.Selected.Result), P_Date <= Day(Dropdown1_10.Selected.Result)),Actual_Work_Quantity) &"</td><td>" &

Text(Day(LookUp(Filter(Project_Scope_Data, SEC_ID = Label8_3.Text), SA_SEQ = Result, PDC_End_Date)),"0#") & "-" &

Text(Month(LookUp(Filter(Project_Scope_Data, SEC_ID = Label8_3.Text), SA_SEQ = Result, PDC_End_Date)),"0#") & "-" &

Right(Text(Year(LookUp(Filter(Project_Scope_Data, SEC_ID = Label8_3.Text), SA_SEQ = Result, PDC_End_Date))),2)

&" </td></tr>")

XXXXX

menonv · ‎04-07-2020

Thanks @v-xida-msft for your reply,

I do not think here is any problem in the formula that I have used, as it has worked on several occasions, it is just that sometimes, for some of the HTML boxes( HTML box in a nested gallery), the data is not being displayed. For eg - refer attached pic. One one occasion data for "Earthworks in Formation" disappeared. after I changed the dates a couple of times, it again reappeared.

Disappearing data in HTML boxes is random, at times all data is displayed, while on other occasions one or two nested galleries do not show data in the HTML text boxes

There are though two warnings in the HTML Box

1. I am delegating filter for dates, even though there is error , the app is working

2. Sum function - there is a warning that it might not work for large data sets - Which is not an issue as I am limiting dataset through filters.

A simplified version of the code is as follows

Concat ( Filter from Datasource 1, Lookup Result in Datasource 1, Lookup Result in Datasource 1, Filter result in Datasource 2 and Sum, Filter Result in Datasource1 and sum, Lookup result in Datasource 3, Filter Result in Datasource 1 and sum, Filter Result in Datasource 1 and sum, Lookup in Datasource 2)

Code in HTML textbox
XXXX
Concat(Distinct(Filter(Project_Actual_Data, Actual_Target_Start_Date = Dte_Ref_4.Text, PROJECT_ID = Dropdown1_11.Selected.Result, MA_SEQ = ThisItem.Result, SEC_ID = Label8_3.Text),SA_SEQ),"<table><col width = 100><col width = 210><col width = 100><col width = 100><col width = 125><col width = 100><col width = 120><col width = 110><tr><td>" & Result & "</td><td>" & LookUp(Filter(Project_Actual_Data, Actual_Target_Start_Date = Dte_Ref_4.Text, PROJECT_ID = Dropdown1_11.Selected.Result, MA_SEQ = ThisItem.Result), SA_SEQ = Result,SA_Title)&"</td><td>" & LookUp(Filter(Project_Actual_Data, Actual_Target_Start_Date = Dte_Ref_4.Text, PROJECT_ID = Dropdown1_11.Selected.Result, MA_SEQ = ThisItem.Result), SA_SEQ = Result,SA_Unit)&"</td><td>" & Sum(Filter(Project_Scope_Data, SA_SEQ = Result, PROJECT_ID = Dropdown1_11.Selected.Result, SEC_ID =Label8_3.Text ), Scope_Work_Quantity)&"</td><td>" & Text(Sum(Filter(AddColumns(Filter(Project_Actual_Data, SEC_ID = Label8_3.Text, SA_SEQ = Result, PROJECT_ID = Dropdown1_11.Selected.Result), "S_Month", Month(Actual_Target_Start_Date), "S_Year", Year(Actual_Target_Start_Date),"S_Day", Day(Actual_Target_Start_Date)), Date(S_Year,S_Month,S_Day)<= Dropdown1_10.Selected.Result), Actual_Work_Quantity),"#")
& "</td><td>" & LookUp(Filter(AddColumns(Filter(Project_Agreed_Data, SEC_ID = Label8_3.Text,SA_SEQ = Result),"A_Month", Month(Agreed_Target_Start_Date), "A_Year", Year(Agreed_Target_Start_Date)), A_Year = Year(Dropdown1_10.Selected.Result), A_Month = Month(Dropdown1_10.Selected.Result)), SA_SEQ = Result,Agreed_Work_Quantity) & "</td><td>" & Sum(Filter(Project_Actual_Data, SEC_ID = Label8_3.Text, SA_SEQ = Result, PROJECT_ID = Dropdown1_11.Selected.Result, Actual_Target_Start_Date = Dte_Ref_4.Text), Actual_Work_Quantity) & "</td><td>" &
Sum(Filter(AddColumns(Filter(Project_Actual_Data, SEC_ID = Label8_3.Text, SA_SEQ = Result, PROJECT_ID = Dropdown1_11.Selected.Result), "P_Month", Month(Actual_Target_Start_Date), "P_Year", Year(Actual_Target_Start_Date), "P_Date", Day(Actual_Target_Start_Date)), P_Month = Month(Dropdown1_10.Selected.Result), P_Year = Year(Dropdown1_10.Selected.Result), P_Date <= Day(Dropdown1_10.Selected.Result)),Actual_Work_Quantity) &"</td><td>" &
Text(Day(LookUp(Filter(Project_Scope_Data, SEC_ID = Label8_3.Text), SA_SEQ = Result, PDC_End_Date)),"0#") & "-" &
Text(Month(LookUp(Filter(Project_Scope_Data, SEC_ID = Label8_3.Text), SA_SEQ = Result, PDC_End_Date)),"0#") & "-" &
Right(Text(Year(LookUp(Filter(Project_Scope_Data, SEC_ID = Label8_3.Text), SA_SEQ = Result, PDC_End_Date))),2)
&" </td></tr>")

XXXXX

WarrenBelz · ‎04-07-2020

Hi @menonv ,

Firstly, I have parsed it below and am in admiration that you actually got this to render, but also astounded that it actually did render in a HTML box.

Seriously, the first thing I would do if you have not already is Collect everything you are referring to and filter the collections. I am a bracket out somewhere below.

Concat(
   Distinct(
      Filter(
         Project_Actual_Data, 
         Actual_Target_Start_Date = Dte_Ref_4.Text, 
         PROJECT_ID = Dropdown1_11.Selected.Result, 
         MA_SEQ = ThisItem.Result, 
         SEC_ID = Label8_3.Text
      ),
      SA_SEQ
   ),
   "<table>
   <col width = 100><col width = 210><col width = 100><col width = 100><col width = 125><col width = 100><col width = 120><col width = 110><tr><td>" & 
   Result & "</td><td>" & 
   LookUp(
      Filter(
         Project_Actual_Data, 
         Actual_Target_Start_Date = Dte_Ref_4.Text, 
         PROJECT_ID = Dropdown1_11.Selected.Result, 
         MA_SEQ = ThisItem.Result
      ), 
      SA_SEQ = Result,
      SA_Title
   )&
   "</td><td>" & 
   LookUp(
       Filter(
          Project_Actual_Data, 
          Actual_Target_Start_Date = Dte_Ref_4.Text, 
          PROJECT_ID = Dropdown1_11.Selected.Result, 
          MA_SEQ = ThisItem.Result
      ), 
       SA_SEQ = Result,
       SA_Unit
   )&
   "</td><td>" & 
   Sum(
      Filter(
         Project_Scope_Data, 
         SA_SEQ = Result, 
         PROJECT_ID = Dropdown1_11.Selected.Result, 
         SEC_ID =Label8_3.Text 
      ), 
      Scope_Work_Quantity
   )&
   "</td><td>" & 
   Text(
      Sum(
         Filter(
            AddColumns(
               Filter(
                  Project_Actual_Data, 
                  SEC_ID = Label8_3.Text, 
                  SA_SEQ = Result, 
                  PROJECT_ID = Dropdown1_11.Selected.Result
                ), 
                "S_Month", 
                Month(
                   Actual_Target_Start_Date
                ), 
                "S_Year", 
                Year(
                   Actual_Target_Start_Date
                ),
                "S_Day", 
                   Day(
                      Actual_Target_Start_Date
                   )
                ), 
                Date(
                   S_Year,
                   S_Month,
                   S_Day
                )<= 
                Dropdown1_10.Selected.Result
             ), 
             Actual_Work_Quantity
          ),
          "#"
       )& 
       "</td><td>" & 
       LookUp(
          Filter(
             AddColumns(
                Filter(
                   Project_Agreed_Data, 
                   SEC_ID = Label8_3.Text,
                   SA_SEQ = Result
                ),
                "A_Month", 
                Month(
                   Agreed_Target_Start_Date
                ), 
                "A_Year", 
                Year(
                   Agreed_Target_Start_Date
                )
             ), 
             A_Year = 
             Year(
                Dropdown1_10.Selected.Result
             ), 
             A_Month = 
             Month(
                Dropdown1_10.Selected.Result
             )
          ), 
          SA_SEQ = Result,
          Agreed_Work_Quantity
       ) & 
       "</td><td>" & 
       Sum(
          Filter(
             Project_Actual_Data, 
             SEC_ID = Label8_3.Text, 
             SA_SEQ = Result, 
             PROJECT_ID = Dropdown1_11.Selected.Result, 
             Actual_Target_Start_Date = Dte_Ref_4.Text
          ), 
          Actual_Work_Quantity
       ) & 
       "</td><td>" &
       Sum(
          Filter(
             AddColumns(
                Filter(
                   Project_Actual_Data, 
                   SEC_ID = Label8_3.Text, 
                   SA_SEQ = Result, 
                   PROJECT_ID = Dropdown1_11.Selected.Result
                ), 
                "P_Month", 
                Month(
                   Actual_Target_Start_Date
                ), 
                "P_Year",
                Year(
                   Actual_Target_Start_Date
                ), 
                "P_Date", 
                Day(
                   Actual_Target_Start_Date
                )
             ), 
             P_Month = 
             Month(
                Dropdown1_10.Selected.Result
             ),
             P_Year =
             Year(
                Dropdown1_10.Selected.Result
             ), 
             P_Date <= 
             Day(
                Dropdown1_10.Selected.Result
             )
          ),
          Actual_Work_Quantity
      ) &
      "</td><td>" &
      Text(
          Day(
             LookUp(
                Filter(
                   Project_Scope_Data, 
                   SEC_ID = Label8_3.Text
                ), 
                SA_SEQ = Result, 
                PDC_End_Date
             )
          ),
          "0#"
      ) & 
      "-" &
      Text(
         Month(
            LookUp(
               Filter(
                  Project_Scope_Data, 
                  SEC_ID = Label8_3.Text
               ), 
               SA_SEQ = Result, 
               PDC_End_Date
            )
         ),
         "0#"
      ) & 
      "-" &
      Right(
         Text(
            Year(
               LookUp(
                  Filter(
                     Project_Scope_Data, 
                     SEC_ID = Label8_3.Text
                  ), 
                  SA_SEQ = Result, 
                  PDC_End_Date
               )
            )
         ),
         2
      )
      &" 
      </td></tr>"
    )

menonv · ‎04-07-2020

Hi @WarrenBelz

To clarify, rendering in HTML box is an issue?

About using collections, the datasources I am using has more than 500 entries. I am using filters to limit the data I am calling from the datasource. For eg the section "S01" and "S02" may have more than 500 entries.

Especially "Total Progress" and "Progress for Month" would have several entries - currently I am limiting them by using filters

( For eg I am filtering out all entries for Earthwork(Filling) and summing it up)

Which formula according to you would be creating the most problems?

Additionally, I am not very sure how to use collections in a nested gallery, and how to use collections when there are large number of entries in the sharepoint list.

Currently I am using the following logic in my app,

1. Use distinct function for the gallery to find all the relevant sections in a "Project" for a "Date'

2. Use a nested gallery and again use distinct function to find all the relevant "Main activities" performed during the day.

3. Use concat within the HTML box to display all the relevant data for each "Sub Activity"

Additional Info : I cannot change the datasource.

Thanks!!!

P.S there is no issue with bracket, must be a copy - paste mistake I made.

WarrenBelz · ‎04-07-2020

Hi @menonv ,

My comments may have been a bit flippant, but were based on a genuine concern.

One thing I have probably assumed but have not asked is your data source - I based my comments on SharePoint.

The speed of rendering queries is generally contingent on the number if individual "server calls" in a query - the number of times that a piece of code has to go back to the data source and ask for information or the capacity of the server-side processing to respond to the query.

Intermittent error such as yours can be caused by a data source not responding with the information requested.

I do a fair bit of HTML concatenated code in both emails and PDF files generated in OneDrive and try as much as possible to keep the Concat bits to collections with generally only one filter level and sort. I do all my "processing" in (at times) several levels of collections until I get the data I need.

Your code is obviously valid for the data you want, you just have to hope it works consistently for the purpose you are using it.

menonv · ‎04-07-2020

Hi @WarrenBelz ,

Thanks a lot for the info. It makes sense.

Let me try and rework the code so that I can move some of my datasources to collections to reduce the server calls. Hope that would resolve the issue.

I will get back to you once I do that.