Billing Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help me!!! I've labored over this for weeks and am stumped at how to
accomplish this.

I've set up a database to do quotes and invoicing. When we invoice a
customer, I would like to have the ability to bill a certain percentage of
each line item. (Ex. Line Item 1 $100, bill customer 90% = $90 on the
invoice). That's the first issue.

The second issue is because there will be multiple invoices per project, I
would like the next invoice I create to automatically show the percentage of
the line item that I haven't already billed. (Ex. Line Item 1 $100, bill
customer 10% = $10). And if I want to change this, to say bill for only 5% on
this invoice, I want the next invoice to show what's left to bill.

Am I totally insane, or is this possible? Or both :-)

Thanks in advance for any help you can offer.
 
What you'll probably have to do is to reimagine how the process works.
First, you would put the line items onto the Rate Quote by adding
records to a table named tblProjectDetail. The records would contain the
line item information for each item including description, amount,
quantity etc. When you need to invoice an item, either fully or
partially, you would add a record to a second table perhaps
tblInvoiceDetail that includes the line item detail, the total amount of
the item $1000.00, the percentage being billed 10%, the percentage
remaining 90% and then the percentage amount being billed $100. From
there you would generate an invoice based on the tblInvoiceDetail
records. From a user-interface perspective, I would approach it from
this stand point. The user pulls up a Form that shows the Rate Quote.
This form would show the line item records, the total amount for each
item, the total invoiced to date, and the total remaining to be
invoiced. The user then double-clicks on the detail section of the form
where the line item information is displayed (I'm assuming continuous
forms). This action displays a pop-up form reiterating the information
that I've mentioned. The form would have two sections - invoice absolute
amount and invoice percentage. This would allow you to bill a specific
amount and then calculate the percentage backward or vise versa. You'll
probably also want to have something in place that totals the
tblInvoiceDetail records since the total of ALL of the records should
balance to the total on the RATE QUOTE. The other validation sums would
be total invoiced and total remaining.

David H
 
Thanks David, that made things much clearer. You had me right up until the
part where the user sees the pop up form with "invoice absolute amount and
invoice percentage". I'm not clear on your vision or how to code the form for
the calculations and to create the invoice. Thanks again!
 
The idea is that the form is used to capture the information from the
user as to how much to invoice. Its possible that in some situations,
you may want to invoice an absolute amount ($50) as opposed to a
percentage. If you invoice an absolute amount, the form would calculate
the appropriate percentage invoiced. For example, if the remaining
amount to be invoiced is $434 and you invoiced an absolute amount of
$50, the form would calculate that your invoicing 11.52%. Naturally, if
you want to invoice a percentage, the form would caluclate the
appropriate amount.

All you have to do is to have three controls.

Amount Due % to Invoice $ to Invoice
$434.00

In the AfterUpdate event of the % to Invoice control you would write
code that updates the Invoice Percentage.
Me!invoiceAmount = Me!AmountDue * Me!InvoicePercentage

For the Invoice Amount..
Me!invoicePercentage = Me!InvoiceAmount/Me!AmountDue

You can also go so far as to add code which insures that the % to
Invoice does not exceed 100% and that the $ to Invoice does not exceed
the Amount Due. The defaults for both would be 100% and Amount Due
respectively. You will need to make sure that you round the Invoice
Amount and figure out how you want the percentage to appear .11 or 11%
or 11.25%.

David H
 
Back
Top