Background 

I work at a dealership/repair shop and I'm working on a Solution to manage money received, paid, and anticipated as well as other things from customer management to inventory management. I'm a beginner learning from researching as I go, I have a table to input Invoices we receive from vendors so we can reconcile at the end of the week or month we will print a report showing the invoices that where received and entered in the table to the payments paid on the company credit card.

Issue

I ran thru a Invoice validation issue we have more then 50 vendors and receive a lot of invoices I'm trying to prevent employees from entering duplicate invoices so I thought about making the Invoice number a unique value but at the same time it is possible to run into an issue where the same invoice number could be used by more then one vendor but not twice by the same vendor .

How can I prevent this from happening?

I thought of validating it this way but I don't know how to accomplish it if <<Vendors::Company Name>> has an existing <<Invoices::Invoice Number>> that matched the <<Invoices::Invoice Number>> that is being inputted --> Message [ The invoice your trying to input already exists for <<Vendors::Company Name>> received on <<Invoices::Invoice Date>> for <<Vehicle::Description>> and the amount is $<<Invoices::Invoice Amount>>, Please Verify.]

unless there is a better way to go about this please advise.

Create a data entry form use some global fields when you hit the button to add new invoice to the vendor you set the VendorID to a global field then have the user enter the invoice number in another global field

use these fields as predicates to the invoice table and do a test to see if  Count ( invoice::uuid ) ≠ 1 then it would be safe to add this invoice.

The goal here is enter the values in to globals and validate before you actually create the record.  

alternatively you could create a concatenated field VendorID & "-" & InvoiceNo   and then GlobalVendorID &"-"& GlobalInvoiceNo then relate these fields and single equal join link between them 

 

thank you.