Jun 20, 2022 01:11 PM
We’ve been using AT for a couple years and would like to see the total amount paid to vendors per job.
Currently, we have the following tables within a Base:
Orders (1 record per job)
On the Orders table, each job includes the following fields:
Vendor(s) [multiple select field] (each job can have 1 to many vendors depending on scope)
Client Invoice # (manually entered)
Vendor Invoice amount Paid (manually entered and separated by / if more than one vendor hired)
I would like to have a field that adds up the total vendor payments per record/job. This can be done by entering Vendor Invoice info into a separate table (Vendor Payments) or some other way that is simple/effective.
My goal is for our AR person view our Invoice No. and all vendors associated with that Invoice No. in separate records in order to enter their invoice amount per record/Job.
Thanks in advance
Jun 20, 2022 02:03 PM
It sounds like you should go ahead with adding a junction table for “Vendor Payments” .
Instead of linking Vendors to Orders, your Vendor Payments table would have one row per pair of vendors/orders. That way you could have a field in that table for Invoice Amount Paid and the Orders table would have a Rollup that sums up that field.
Jun 20, 2022 02:38 PM
Thank you for the suggestion.
I’ll work on figuring out how to configure the junction table.
Jun 20, 2022 02:41 PM
Based on your first point it sounds like it would be pretty simple, with the following fields:
Jun 20, 2022 03:05 PM
I set up the junction table, but I’m unable to figure out how to separate multiple vendors with same Job/Invoice no.
The vendor field is linked to the Vendors Table per your suggestion.