So ... I've been learning a lot!  I've got most of my Certification Invoice (custom object in a custom app called Certification) for processing payments working perfectly, thanks to the help of the Community and Premier Support.  Here's what it currently looks like:



The Amount Due consists of:

Subtotal - consisting of a rolling summary of all line items;

Delivery charges - a picklist value;

Previous Balance/Credit - formula that looks for the company's most recent previous invoice and if the Remaining Balance is not zero, brings in the Remaining Balance amount into the Previous Balance/Credit field;

Total Amount Due - formula that adds the Subtotal, Delivery Charges, and previous Balance/Credit;

Total Amount Paid - manually entered by the user;

Remaining Balance - formulta that subtracts the Total Amount Paid from the Total Amount Due.

Here's the challenge:

The Previous Balance/Credit is currently referencing the Remaining Balance of a Previous Invoice, but not specific to the company on this current invoice.  The user would need to scroll through lots of previous invoices to find one for the current company, and then plug in the invoice number.  That's not a workable solution, because the users won't have time to do that while the client is waiting for them to certify their documents.

So, the Previous Balance/Credit needs to auto-populate in one of three ways:

1) If there is an Exporter company listed on the current invoice but no Third Party company (such as a courier service), the Previous Balance/Credit would come from the Exporter's most recent invoice Remaining Balance.

2) If there is a Third Party company listed on the current invoice but no Exporter, the Previous Balance/Credit would come from the Third Party company's most recent invoice Remaining Balance.

3) If there is an Exporter company AND a Third Party company listed on the current invoice, the Previous Balance/Credit would come from the Exporter's most recent invoice Remaining Balance.

I have set up a Certification Date on each invoice ({YYYY}-{MM}-{DD}-{0}), which has both a date and a unique autonumber (at least in theory), so this, combined with the name of the Exporter or Third Party company, should be able to identify which invoice is the most current previous invoice to use for pulling out a Remaining Balance.

Would anyone know how to write an APEX code to make this happen?

Many thanks,

Cynthia