CIP Coordination Solution - Project Expenditures Arcade Expression

[LeahBowyer]

I was wondering if something was broken in our solution setup or if we are using the "Spending" related table in the way the application is configured. 

The arcade expression built does not total the expenditures in the pop-up, from what I can tell... The bar representing "Expenditures to date:" does not represent the total of all expenditures (Spending related table). I've copied the arcade expression I believe needs to be adjusted, but I'm not confident enough in my Arcade to edit and risk reconfiguring the solution.

If this ISN'T intended to be used with multiple entries for expenditure, are there ways to configure to support? We would like to use this related table to note contract pay outs categorized by design phase (category field) / vendor (description).

Thanks in advance!

//colors
var estCostColor ='#00734C'

//budget values
var expendituresFS = FeatureSetByRelationshipName($feature, 'Spending');
var mostRecentExpend = first(OrderBy(expendituresFS, 'datespent DESC'));
var actcost = IIF(IsEmpty(mostRecentExpend), 0, mostRecentExpend.costamount);

var estCost = $feature.estcost;
var totalSpending = 0+ actcost
var budgetRemaining = estcost-actcost

//labels
var formattedCost = text(estCost, '$#,###')
var formattedBudgetRemaining = text(budgetRemaining, '$#,###');
var formattedSpent = Text(totalSpending, "$#,###")

//bar width % values
var fullwidth = estCost;
var costWidth = (estCost / fullwidth)*100;
var spentWidth = (totalSpending/fullwidth) *100
var remainingwidth = (budgetRemaining/fullwidth)*100


var b1
var b2
var b3
var spentDisplay

if(totalSpending > estCost){
  //Money Spent over budget
  if(totalSpending > fullwidth){
    spentDisplay = `<td style="background-color:#C12E00;width:${costWidth}%; border-right: 3px solid black"  title="Spent: ${formattedCost}"></td>`
   
    b1 = `${costWidth}%;border-right:3px solid black`
   



  }else{
    //Money Spent Over budget
   
    spentDisplay = `<td style="background-color:${estCostColor};width:${costWidth}%"  title="Spent: ${formattedCost}"></td>`

    b1 = `${costWidth}%`
  }

}else{
    if(totalSpending == 0 || IsEmpty(totalSpending)){
    //No Money spent
      spentDisplay = `<td style="background-color:${estCostColor};width:${costWidth}%;"  title="Remaining: ${formattedCost}"></td>`
    }else{
    //Money Spent, not over budget
      spentDisplay = `<td style="background-color:#C12E00;width:${spentWidth}%;border-right: 3px solid black"  title="Spent: ${formattedSpent}"></td>
            <td style="background-color:${estCostColor};width:${remainingwidth}%;"  title="Remaining: ${formattedBudgetRemaining}"></td>`
      b1 = `${spentWidth}%;border-right: 3px solid black`
      b2 = `${remainingwidth}%`
    }
}


var final = {type : 'text',
text :`<font style="font-size:18px;color:#000000"><b>Estimated budget:</b> ${formattedCost}</font></br>
<font style="font-size:18px;"><b>Expenditures to date:</b> ${formattedSpent}</font></br>
<table style="width:100%;border-collapse:collapse">
  <tr style="height:10px">
    <td style="background-color:white;width:${b1}"></td>
    <td style="background-color:white;width:${b2}"></td>
    <td style="background-color:white;width:${b3}"></td>
  </tr>
  <tr style="height:25px">
    ${spentDisplay}
  </tr>
  <tr style="height:10px">
    <td style="background-color:white;width:${b1}"></td>
    <td style="background-color:white;width:${b2}"></td>
    <td style="background-color:white;width:${b3}"></td>

  </tr>
</table>`
}

return final

[KenBuja · Accepted solution]

It looks like you'd just need to get the sum of the costamounts field in expendituresFS instead of using the most recent expenditure record.

//budget values
var expendituresFS = FeatureSetByRelationshipName($feature, "Spending");
var actcost = Sum(expendituresFS , 'costamount')

var estCost = $feature.estcost;
var totalSpending = 0+ actcost //not sure why you need "0+" in this line 
var budgetRemaining = estcost-actcost

 If you're going to post code, please use the Insert/Edit code sample button

[LeahBowyer]

Thank you, @KenBuja. I will try your suggestion.

RE: Code Comments. This code was created as part of the ArcGIS Solution by their team, so I'm not sure of the origin or purpose.

I didn't see Arcade as an option in the code insert. What language option would you suggest I use?

[KenBuja]

Use Javascript, since Arcade is based on that language.

[Joshua-Young · Accepted solution]

We are working through implementing this solution as well, and we also found that the way the solution is designed to work is you enter the running total in the spending table each time you have a payout. We did not want it to work that way either and you have to change the actcost to use the Sum function in each of the different maps that has the Spending/Expenditures information.

You can also update the embedded HTML in Arcade to update the table element to show the category field. Here is one example from the Capital Project Tracker web map.

if (count(related)>0){

  final = `<span style="font-size:18px;"><b>Expenditures</b></span><br><table style="width:100%;border-spacing: .5rem;"><tr><td><table style="width:100%;border-collapse: collapse;"> 
  <tr style="border-collapse: collapse; font-size:15px;"><th style="text-align:left;width:30%">Date</th><th style="text-align:center">Category</th><th style="text-align:right">Amount</th></tr>
  `
  var counter =0
  var x = 0
  for (var i in related) {
      var amount = Text(i.costamount,'###,###,###')
      var color = iif(x % 2==0,"#D6D6D6","#FFFFFF")
      var spentDate = text(i.datespent, "MM/DD/YYYY")
      var category = i.category
      final += `<tr style="border-collapse: collapse;"><td style="text-align:left;width:30%;background-color:${color}">${spentDate}</td><td style="text-align:center;background-color:${color}">${category}</td><td style="text-align:right;background-color:${color}">$${amount}</td></tr>`
      x++
      counter++
  }
  final += '</table></td></tr></table>';
}

[LeahBowyer]

This is great feedback, @Joshua-Young !

I might ping you if I come across any other oddities. I'm trying my best to breadcrumb my way through these changes so I can reproduce or edit in the future without too much confusion. 

[MikeDagle]

Hi @LeahBowyer,

As designed, the Cap Project Coordination Solution is configured to store and return the total expenditures to date, rather than individual pay requests. We configured it in this way for simplicity, but the Solution can certainly be extended to track individual expenditures/pay requests. Based on the comments, you're on your way to doing just that.