Set up Multiple Vendor Invoice Totals per Job

Hi,

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)
Clients
Vendors

On the Orders table, each job includes the following fields:
DOS
Client Name
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
Juliet

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.

Thank you for the suggestion.

I’ll work on figuring out how to configure the junction table.

Based on your first point it sounds like it would be pretty simple, with the following fields:

  • Formula field for the primary field, just concatenating the Order and Vendor so each record gets a unique name
  • Link to Orders
  • Link to Vendors
  • Currency Field for Invoice Amount
1 Like

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.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.