Calculate linked subitems only

Hey guys,
hopefully someone who has experience with my issue would be able to help me with it.

Use case:
project management boards to track Income, Expenses, and Subcontractors payments.

this is how the boards are built:

Board - Projects:
           item - project one 
           item - project two
Board - Subcontractors:
           item - Plumber
                    subitem - project one | agreement cost: 2000$  | paid: ""
                    subitem - project two | agreement cost: 4000$  | paid: ""
           item - Electrician
                    subitem1 - etc.
Board - Expenses:
           item - project's name1 | linked project="project one"
                    subitem1 - Material | amount: 100$ | linked boards: subcontractors=
                    subitem2 - Plumber | amount: 200$ | linked boards: subcontractors="Plumber"
           item - project two
                    subitem1 etc.

In short:
when entering in the Expenses subitems subitem2 - Plumber | amount: 200$ | linked boards: subcontractors='Plumber'
the amount 200$ should be added to the the Subcontractors Board and add the value 200$ in the “paid” column.

In length:
in the Expenses Board I’d like to distinguish between a regular expense such as material expenses and expenses that would go to subcontractors (The way that I’m doing it now, is by linking the row with the subcontractor via “connect board”) I hoped that this way I can keep track of what the agreement cost between me and the subcontractor was and how much I ended up paying them, unfortunately this is not to my liking.

How could I make a formula that can distinguish between a linked subitem from another board and calculate only the subitems that are each linked to a subcontractor in the current project (the project is linked to the parent item, the subitem is linking to the subcontractor).
The end goal is to add the amount entered in the Expenses board linked specifically its subcontractor to the subcontractor’s “paid” column so I can keep track the amount paid VS the agreement cost we had.

I hope I didn’t get too many of you confused.

thanks in advance

Hey Ben,

Thanks so much for the insight here!!

I’d really like to engage in some testing on my end and replicate some of your set-up… by any chance is it possible for you to share a couple of screenshots to just get a more clear understanding of your column set up across the boards? This will just help me gather some visual context and better understand how we can integrate this into a formula to achieve your end goal here?

Thanks so much in advance :pray:

sure here’s some I created just so you could get an idea:
Board - Projects:

Board - Subcontractors:

Board - Expenses:

We’re you able to take a look at the boards I sent you?

Hey Ben!

I am so sorry, I did completely miss your reply - thank you for chasing me on this :frowning:

Please see my response in the 2 part looms below :pray:

Hopefully this makes sense!!!

thank you very much for your time helping me with this matter.

it does seem to be working once applying these settings.
However, since we are working with a lot of Subcontractors this is very frustrating and does not really makes things easy to operate.
Moreover since it does require an entry for each subcontractor, it would raise the risk of accidents when entering information manually.

Unfortunately for now this is not very useful for our team, but hopefully this will be solved in the future so we can integrate this function into your system.

Best regards,