Sum values from Related SP List

[RJF61]

Looking for some guidance here.  I have a Gallery/DataForm (based on a SP List "ShortFormII") where I am collecting general information relative to projects.  I have created a second SP List "ProjectCosts" where I import spend data.  These two lists have a common field/column named WONo.  While in Power Apps, I would like to select the ShortFormII project and have two Text Labels (ie., "Labor" and "Material") that display the sums of the ProjectCosts based on the common WONo and a secondary field named Title.  If the value in Title is between 500000 and 799000, this is Material.  If greater than or equal to 800000, costs are Labor.

Following is a pic example...

Labor = sum of ProjectCosts.Field1 where Title is gt 500000 and lt 800000

Material = sum of ProjectCosts.Field1 where Title is gt 800000

What is the best method to achieve?

[WarrenBelz]

Hi @RJF61 ,

I may be oversimplifying this, and am a little unclear on the actual column containing the value (Field1 or Title) but would it not be

Sum(
   Filter(
      ProjectCosts,
      WONo = YourValueHere &&
      Field1 > 500000
   ),
   Field1
)
      

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[RJF61]

Hello @WarrenBelz Thanks for the response.  Although the following appears to work correctly for materials, I am receiving a delegation warning in my code.

Sum(

     Filter(

          ShortFormII_ACstData,

          Order = DataCardValue35.Text &&

          Value(Text('Cost Element')) < 800000

     ),

     'Val.in rep.cur.'

)

Also, for Labor (where the 'Cost Element' value is >= 800000) I am receiving no records

Sum(

     Filter(

          ShortFormII_ACstData,

          Order = DataCardValue35.Text &&

          Value(Text('Cost Element')) >= 800000

     ),

     'Val.in rep.cur.'

)

[WarrenBelz]

@RJF61 ,

To fix the Delegation issue (probably as the top filter still needs to return record numbers under your limit)

With(
   {
      _Data;
      Filter(
         ShortFormII_ACstData,
         Order = DataCardValue35.Text
      )
   },
   Sum(
      Filter(
         _Data,
         Value(Text('Cost Element')) < 800000
      ),
     'Val.in rep.cur.'
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[RJF61]

@WarrenBelz I am not getting an error saying "Expected Table Value"

[WarrenBelz]

@RJF61 ,

I am using your code here - if this works

Sum(
   Filter(
      ShortFormII_ACstData,
      Order = DataCardValue35.Text &&
      Value(Text('Cost Element')) < 800000
  ),
  'Val.in rep.cur.'
)

then this should also

With(
   {
      _Data;
      Filter(
         ShortFormII_ACstData,
         Order = DataCardValue35.Text
      )
   },
   Sum(
      Filter(
         _Data,
         Value(Text('Cost Element')) < 800000
      ),
     'Val.in rep.cur.'
   )
)

assuming Order is a Text field, and Val.in rep.cur.' is a Numeric field. I am wondering however why you are using this Value(Text('Cost Element')) < 800000 converting a value to text and back to a number.

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[RJF61]

@WarrenBelz So, to your comment, I am receiving an error.  I have the code above in a Text label, but am receiving...

This rule creates a circular reference between properties, which is not allowed.  A property cannot reference itself or other properties affected by its value.  This is the shortest circular reference: 'Label29'.'Text' -> 'DataCardValue39'.'Default' -> 'Label29'.'text

To answer your question, 'Cost Element' is a text field (with numeric content...i.e., 522025).  From my original post 'Cost Element' < 800000 are materials.  'Cost Element' >= 800000 are labor.  I was attempting to convert the 'Cost Element' to a value for comparison to 800000.

[WarrenBelz]

@RJF61 ,

If ‘Cost Element’ is Text you do not need to state Text again.

Value('Cost Element) < 80000

but this is not your issue. What is in DataCardValue39 Default and what type of control is it ?

[RJF61]

@WarrenBelz So this is what is currently displayed...

The source is a SP List field "WorkOrder" which is text

[WarrenBelz · Accepted solution]

@RJF61 ,

DatacardValue39 is the issue (not 35). Also I need the Default code (the one you have displayed is calculated as well).

The issue here is that you have two control values referring to each other, either directly or in a "circle" (A=B=C=A).