Salesforce and other SMB Solutions are coming soon. ×

# Adding up numbers from two join tables from a certain field

## Recommended Posts

Hey everyone,

I know this may sound simple, but I'm having an issue implementing it. We have a computer table and a parts table. We can assign parts to a computer. There is an initial quantity of the part (in a field) in the parts table. Each join record has a quantity that is assigned to a computer (in a field). I want to be able to add up the quantity values for each of the join records from both join tables (one from computers, one from projects) and have that number subtracted from the initial quantity to show in a field on the parts layout how many we have left available to assign.

so for example, we may have a project and a computer who need computer mice. (They are separate entities)

Say we initially have 10 mice. We have assigned one record in the join table for the project with 3 mice in the quantity field and one record in the join table for the computers with one mouse in the quantity field. So I want to have a calculation that adds up the quantities of the assigned records (which would be 4) and subtract it from the initialquantity (10) to get the actual amount remaining to be assigned (6)...

I know, I know, it sounds like an elementary math equation... (two trains are heading toward each other, one leaves at 6 am.... etc)

Can anyone help? The "Sum" function isn't working for me...

Thanks again!

-Justin

##### Share on other sites

Hi Justin

Rather than explaining this long-windedly, I have attached an example file. See how this goes.

##### Share on other sites

Just in case a part is assigned to more than one computer, or to more than one project, I would make the AssignedCount field =

Sum ( CompPart::Quantity ) + Sum ( ProjPart::Quantity )

##### Share on other sites

Good point comment.

Cheers, David

##### Share on other sites

Sorry guys, I'm apparently lacking enough coffee or something. I had a filter on the relationship that was preventing the calc from doing what it was supposed to do. Sorry again for wasting your time

• ### Images

• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.6k
• Total Posts
141.4k
×
×
• Create New...