Multiple invoices for the same item

Hi Sam

Your list of tables is a good start but  you need a few more to complete the picture.

Because a customer can own more than one pet, and a pet can be owned by more than one customer you need a join table, call it Owner, to allow this many to many relationship.

Similarly, you also need a join table to allow you invoices to have more than one item, call it Line.

I would have a table, called Batch, to hold a list of pets and the items their owners are going to be invoiced with. The data model then looks like this

Once you have set up your customers and pets, and the share of ownership in the Owner table you would start the invoicing process from Batch. Add a list of pets who's owners are going to be invoiced. Add the invoice lines in BatchLine. Add a button that then loops through all the owners creating an invoice for each one.

let a := this;
for b in BatchPet.Pet.Owner do
    let c := (create Invoice);
    c.(Customer := b.Customer);
    for d in a.BatchLine do
        (create Line).(
            Invoice := c;
            Item := d.Item
        )
    end
end

You may want to track which pet the invoice relates to, and you may want just one invoice per customer for all their pets, not one per customer per pet.

If you want this fleshed out a bit more, let me know.

Regards John

Hey  Sam ,

Here's a complete demo that does everything you need to!