Set up Multiple Vendor Invoice Totals per Job

[Juliet_S]

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

[Kamille_Parks]

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.

[Juliet_S]

Thank you for the suggestion.

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

[Kamille_Parks]

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

[Juliet_S]

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.