we've discussed parts of this before. Are you sure that you should have a separate invoice just to record the deposit? That's always struck me as an odd way to do this. I'd be inclined to log the deposit as the first payment in your payments table and keep the same invoice record.
How does the data in products determine the size of the deposit? I need that info before I can suggest much in detail.
From what I understand at this point, the system determines a minimum amount--the deposit required and the customer can then pay either the deposit or a larger payment up to the full cost of the items listed on the invoice plus taxes. If so, you need two fields, one for calculating the deposit and one for the customer's actual payment. Scripts or validation rules can flag the payment if it's less than the minimum.
And a script can enter the deposit as the default payment amount into this payment field. (Can't use an auto-entered calculation here as it won't update correctly when you add/delete/modify lineitems records.)
I completely agree with your first point - But over here in the Uk, a deposit has to have VAT applied to it so it needs to be it's own invoice. Otherwise it would just be an advance payment which app is different to a deposit... But Let me double check that I cant do it the wasy you suggest - The db I am replacing does it this way.The size of thje deposit is ascertsained at the point of order. We always charge a minimum of £200 but sometimes this may be more or less depending on the client. Or no deposit at all!
So I understand your third paragraph, about the two fields...but which table should they go into? Invoice details? Lines?Products?
I will attach the ERD to remind you...
I had forgottend about the tax issue here.
However, you may be ables to still use the same invoice, just update fields to show the amount of deposit and whether or not it has been paid before re-issuing it to the customer. (and taxes can be collected against a record in payments, if it is a payment of type "deposit".)
You may want to consult with your accountants/bookkeepers/auditors to see what they need to best comply with the laws and regulations.
The two fields would go in invoice details.
What I'm still requesting is how, on paper, you would compute the deposit. Right now it sounds like the deposit is one of two values--£200 or a percentage of the total cost of the items ordered.
Is that correct?
Is it the same percentage for every item, every customer or does it vary for every product and/or different customers? (say customer orders from another part of the world and you want larger deposits from them...)
The depost amount bares no relation to the order amount. We just do a standard £200. or more or less! But this incliudes the 20% vat. So the Net deposit amount (in this case about £166.67p) should come off the main invoice...
Does that make sense? The deposit amount is really whatever is typed in to the invoice details::Deposit field
Hmmm, but you said: "The deposit amount comes from the products table that is accessed via a Lines join table."
That's what had me thinking that you had a percentage figured in here--one that might differ with different products.
So for order amounts where Totalcost * 1.2 < 200, it's the full order amount, else it's £200?
Then your calculation field could be:
Min ( Sum ( LineItems::Cost ) * ( 1 + Globals::gVAT ) ; 200 ) )
Note that I am using a global field for the VAT rate stored as a decimal. This can be loaded with the current Value from a table when your file opens (assuming you have a multi-user system). By using a field, it's easy to change the percentage should the goverment modify the tax rate.
An OnObjectExit script trigger can be specified for your line items portal to perform a script to put this value into your deposit field as the default deposit amount.
Sorry - you are right> I did say that.
Orginally I did have the depost fields in the invoice details table. However, when I wanted to creat a button on the order layout (based on invoice details) I wrote a script that would automatically create a deposit invoice. As the invoice layout has a portal to the Lines table (linked to Orders) then I thought I would have to add the deposit as an item in products so that I would appear on the portal and be treated as a seperate invoice. But I had trouble autofilling the the Lines::Price field to be the same as the Invoice details::Deposit field and also cacling it to be less VAT as the price field in Products are all net! Very confusing.
I think I am not explaing how the user arrives at the amount of deposit paid. Normally, every customer pays a deposit. Most of the time this is £200. If it is a know client then the we may trust them enough not to take a deposit. Sometimes clients from abroad wish to pay half the totak cost of the order. What ever the mechanism to work out how much they pay, the user woudl still manually type in the amount in the deposit field on the orders layout. I expect the field would automatcially show 200 as this is the most common amount taken as a deposit.
So this is problem 1 - Creating a deposit invoice from the deposit field - I assume I would need a new deposit invoice layout with no portal to Lines etc, but just having the deposit fields and the total at the bottom. (I assume the deposit fields should take off the 20% VAT and the totals recaluclulate it ?)
Problem 2 would be the VAT issue outlined vaguely above
PS also regarding your global VAT field - I have created a seperate VAT table that allows the rate to be changed when needed. However, I dont want to make it global as this will knock on all the old records i think? We had this problem before (which is when we were informed about VAT needing to be added on invoices) as we had issued invoices in November at 17.5 VAT and then the rate changed to 20% a few months later. so there were invoices of different rates going to the same client for the same product!
You are providing more info that clarifies things, but the calculation expression I posted still works for this. You can reference a table for the tax rate in this calculation if you want, but keep in mind that this expression is used to copy the calculated amount via a set field script step so it won't change when you change the VAT rate. (The fact that the rate is in a global field--which can make the value more accessible for use in calculations, isn't what caused your earlier problem, it's the fact that you had a calculation field and thus it updated each time you changed the rate--something that can happen with your value in a related table just as it would with a global field.)
You will need some mechanism in place that permits a "known customer" to not have to enter the specified deposit amount. And the calculated amount represents a minimum payment, there's no reason it has to prevent a user from entering a larger payment.
I assume I would need a new deposit invoice layout
I see no reason for such a layout. As far as I can tell from here, you should be able to manage all of this from an existing layout.
On the other hand, I haven't been thinking in terms of a web site for data entry. Will this use IWP to make it accessible to your customers? If so, script triggers aren't an option and a button for entering a deposit that opens a layout (and that computes the deposit amount) may be a very good idea.
Thank you for all that.I shall have a tinker about with it all. The DB will not use IWP at all so at least that makes things a little easier!
I just need a few things clarifying - I have attached an image of the "Order" Layout. If you see, you will notice the deposit field, into which I have entered 200.
Normally the user would then click the image next to it of a printer - this triggers the 'Create and Print invoice deposit" script. The script, amongst other things, Creates the new invoice and autofills the Lines join table to show the Deposit as I have listed the deposit as a record in product table. Is it in this script that I would implement what you suggest? I cant really alter the Lines::price field to have a calculation to take off the vat (if I add set field from the deposit field from the order layout to the new invoice record) as it will alter all other entrys in the lines table and they are already Nett. And once the invoicve is created I do not wanbt any filed to be edited in browse mode as it is an invoice...Do you see what I mean?
I have attached my create deposit invoice script as an Att. as well
I would not put an entry in Lines for the deposit. I'd keep it as a field in Invoice Details so it can be accessible from Invoices when you go to print the invoice.
I can see now why your were suggesting creating a special layout for deposit invoices as this makes it possible to print out an invoice that more clearly documents that this is for the deposit, but still lists the ordered items from Lines.
perfect- thought i needed to something along those lines. Wil have a go in sunday as it bit busy until then...
thank you again
Have managed to get a few hours to do some work - I have set up the deposit invoice and it is working (the crearte deposit invoice script etc).
The only issue I think I will have on this is when it comes to the Make payment script I have on the Invoice layout. I have attached it below. The calcs involved all link to the lines table and other fields, not the new seperate deposit fields. Should I create a special script for deposit payments?