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

joyoung_kerrvilletx

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>';
}