The method we had in your old solution should also apply here.
Relate the added table for invoice numbers to the PK of the invoice table. Define the invoice number field as an auto-entered serial number in the new table and enable "allow creation..." in the new table.
Set Field [NewTable::Fkfield ; InvoiceTable::pkfield]
will then create a new record in the new table and generate an invoice number for it.
Then, just add the invoice number field from the new table to any Invoice table based layout where you you need to display the invoice number.
I have got my DB up to where I was previously. I have spent the last week or so tinkering about with the relationships on my old friend, the payments table!
The Core 2 payments layout looks just like my one and has a portal that shows related open invoices for the selected customer. These portal lines (invoices) canthenbe ticked to apply the payment amount. However, As mentioned before, the Core 2 invoices table is deisgned to hold one invoice's details. My db contains the invoice details that are the same whether a quote/order/invoice. I want the portal to filter/view records from my invoice table and not the Invoice details table that core 2 uses as it will show all quotes etc and I dont want it to. I habe altered the relationship diagram where I think neccessary but still cant get it to work.. I thought this bit wold be simople but it is anything but! Does this make sense? If you need more info on the ERD let me know but I think you shoudl be bale to make sense of it...
Attached is the ERD, at least the bits where it is relevent I think.
Sorted it - Have put a filter on the portal - this works and also seems to work on the running balances of the inoivces too without including the totals for the orders and quotes as well. ALthough I wil need to do some more testing to confirm this..
How should I go about adding a credit style addition to this Database? (see above ERD) . At the moment, receiving and distrubuting payments for invoices works fine, but when it comes to giving a credit to a cusotomer, it cannot do this (doesnt accept negative payments as it is set up this way which is a good thing). I would have thought I need a new Credits table with a pk and fk. I am just not sure what else i need to do - I want it to work in the same way the payment system works....Are you aware of the core 2 payments system? (it has a method of initiating a search for customers and then applying payments against open invoices)
I would also need to replicate the payments script to be the credits script and just adjust the related fields/tables within...
THis may take a bit of work so I appreciate your help! I can send over the design report if you want a proper look over the fields/scripts etc...
Business procedures will have a lot of impact here.
What should happen with that Credit once it is created and recorded in the database?
A related table of credits is definitely an option, and one of your payment methods could be to apply an existing credit much like you currently can use check, cash, credit card, etc.
This would be much like handling a "coupon" for x $$ off a purchase, but the coupon is specific to a particular customer.
Well, the way the business works is a more simple way - we rarely issue credits - we only do this if an invoice is incorrect or a cusomter has over paid etc..But I am really unsure on how to adapt this to my database without mucking the rest of it up!
Should I just create new Credits table and Credit items table (join). And link this in excatly the same way the existing payments and payment items link to the other table, then copy the exising payments script but substitute the fields to the new ones? IF this is the dczse, then how do I then link the two so that the totals correlate and can work out the final balance..?
P.S. at the moment, one of my layouts is a payments list -this shows all payments. I would like the credits to appear here to I think. If not I will design a credits table that looks the same as the payments table but coloured text maybe...
IS this what you meant by yor first question?
Set up a Credits table where you specify the amount, the customerID and, eventually, an invoiceID or a link to an Invoice_Credits join table. (Given their rarity and probable small values, you may find that you never need to link a credit to more than one invoice.)
This gives you a list of credits for a given customer that you can use to generate matching payment records when paying off invoices. You'd update them to link to an invoice so that you can document how it was used in case a customer tries to apply the same credit twice.
Note that both cases where you are issuing a credit, the result is that you have a payment with a portion of the payment not assigned to any invoice. Thus, you can treat any record in payments where there is such a remaining balance as your "credit". Thus, you may not need any separate table, just a way to find and use/document the cases where a Payment exceeds the sum of the amounts assigned to one or more invoices.
I see - I shall give option 1 a whirl and let you know...
Bit of a nightmare!
Just to refresh your memory of the process involved with my invoice creation - I shall then move on to the credit raising situtation! One thing I have not added to the process yet, is the deposit formation. This may prove tricky as well but will go on about that when I have sorted this aspect.
The table Invoice details holds all the relevent fields for the quote/order/invoice. The ID numbers for each of these are created from the related tables Invcoie Detais::Invoice_PK<---->Quote fk/order fk/invoice fk. When these numbers are created, I have also added into the script a set field so that the invoice details table also holds a record of the quote number (and date) as well as the order and invoice numbers abnd dates.
I have now added a credit note table which contains the same fields and links to invoice details as the tables for quotes and orders and invoices. ( and the same fk fields in invoice details).
I do habve script in my ssytem to duplicate an invoice which works fine and provides a new Invoice_details_pk number (i havent altered it to use my own invoice:invoice pk number yet as an invoice number). I would have thought that to create a credit note, I would need to use these scipt somehow - I have been looking at the way the business raises these at present and the user is presented with three options -
1/ Credit entire invoice. In this case a credit note is raised which is identical to the original invoice yet is in'credit' so the balance owed by the customer returns to 0. THe credit note has its own ID number and has a field that shows the related invoice number. This credit note is normally printed out and sent to the cusotmer along with the original invoice to show the 0 balance.
2/ Credit part of an invoice - inthis case the user creates a new credit note (same layout/look as the invoice layout, except with credit number/date as opposed to invoice number). User can then add lines to the credit note to create a negative balance that goes against the customer's balance.
3/ Not really a credit note but similar to above - this is when a supplementery invoice needs to be created. As each order has a unique Job number, this new invoice must relate to this original job number...
I have created (by copying) the original payment tables and relationships to new Credit/credit items etc tables. and also the credit note table that gives the credit note a uniwue number. So there are crdit payments tables, and credit invoices table (and layouts) if you follow me...
The tricky bit is, that as my orginal payments system is so comlicated (core 2) that I doubt I can get the credits system to work as easily. But then again It doesnt really need to be as complicated as is not a commonly used feature - but when it works, it needs to work properly!
Do I need to have a seperate credit payments table? Can I just use negative payments and link these to the new credit notes table to creat a proper running balance?
There are situations in the business (i have just been told) where we send out an invoice that includes delivery in the total. The client will sometimes decide that they will collect instead of having the item delivered. And they will not pay until they collect. This means we have to credit the amount of the delivery and raise a credit note for that amount.
This does get rather confusing. What do you suggest I do? I have sent you a pm with a link...Is you can see for yourself. I have sent the DB before I have messed around duplicating the payments tables...
I am taking a train to Scotland on Saturday morning so hope to have a crack at what ever suggestions you may have....
Thank you Phil!
The key is to treat the credit note as simply another form of payment just like cash, credit card, etc. The difference being that you will want to link the credit to the invoice that caused the creation of the credit note as well as the invoice to which it was used as a payment. In many cases, this will be one and the same. The only real use for a separate Credit table (I've been thinking this through a bit more myself), would be to document the creation of the credit note and you may not even need the added table for that if add an "orginating InvoiceID" field or if credits are always applied back to the invoice from which it was created, you may not even need that field.
So, I would add 'credit' to my value list. And so when I payment is made and applied to a certain invoice, the amount in the payment amount field will go as credit..? Would I enter in a negative number? Also, do I set up a script trigger that if credit is in the payment method, then a credit number will be created?
I will need the ability to print out the 'credit note' as well, showing the credit note number and related invoice number.
But this will only work if the whole invoice is to be credited - what about i only need to credit part of the invoice, such as 'delivery'?
The another annoying thing about this core 2 solution is that it is riddled with Quikbooks intergration (if a plug in is purchased) and so there are lots of references to this in the erd, table amd fields as well as scripts but I daren't delete any in case of a ripple effect throughout the database! Plus, I will also need to export various reports into Sage so the tables etc may come in handy later...maybe..
A payment is a paymen is a payment. If the user is credited $200 on an invoice, create a payment record for $200 dollars and label it as a "Credit" type of payment instead of cash, check, credit card, etc.
There are only two things different:
- The need to link it to the invoice that originated it--add a field for recording the InvoiceID of the originating invoice.
- Printing a Credit Note--create a layout based on the payments table. Perform a find or use Go to Related record to find the correct Credit record, use the added field from 1. in a relationship to access any needed info about the originating invoice. Now print or Save As PDF and you have your credit note.
I can also think of an alternative method, but leave it up to you to decide if it makes sense for your business policies and practices: Log credits on specific invoices by adding negative amount line items to the invoice.
The another annoying thing about this core 2 solution...
I strongly recommend that you make lots and lots of backups. This allows you to recover from mistakes and "developers remorse" (Darn that doesn't work like I thought, the previous approach works better than this...).
Here's a thread on automating such a process (and apologies if I have previously suggested it.) Saving Sequential Back Ups During Development
Thank you - yes, that does make sense - I shall adapt and see what happens.
With regard to your suggestion of back ups - I have about a million old versions - when I remember of course..and the issue with what to call them too. I shall read your post. No need at all to apologise -