topic Re: CIP Coordination Solution - Project Expenditures Arcade Expression in ArcGIS Solutions Questions

CIP Coordination Solution - Project Expenditures Arcade Expression

LeahBowyer — Fri, 06 Jun 2025 20:39:12 GMT

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

Re: CIP Coordination Solution - Project Expenditures Arcade Expression

KenBuja — Fri, 06 Jun 2025 19:53:50 GMT

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

Re: CIP Coordination Solution - Project Expenditures Arcade Expression

LeahBowyer — Fri, 06 Jun 2025 20:24:34 GMT

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?

Re: CIP Coordination Solution - Project Expenditures Arcade Expression

KenBuja — Fri, 06 Jun 2025 20:32:03 GMT

Use Javascript, since Arcade is based on that language.

Re: CIP Coordination Solution - Project Expenditures Arcade Expression

Joshua-Young — Mon, 09 Jun 2025 13:46:42 GMT

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

Re: CIP Coordination Solution - Project Expenditures Arcade Expression

MikeDagle — Mon, 09 Jun 2025 13:49:44 GMT

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.

Re: CIP Coordination Solution - Project Expenditures Arcade Expression

LeahBowyer — Mon, 09 Jun 2025 14:00:01 GMT

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.

Re: CIP Coordination Solution - Project Expenditures Arcade Expression

LeahBowyer — Tue, 28 Oct 2025 13:29:34 GMT

Hi Josh,

I'm running into some limitations on my actcost field calculations CPC (Capital Project Coordination) Solution Related Table(s) Limitations?. Do you mind sharing if you're also running into this issue? Attempting to troubleshoot at the moment if it's a function or table limitation. Thanks!