Parallel serials in LineItems?

[LaRetta]

We have standard Contacts to Invoices to LineItems. Payments, credit memos and misc charges are all stored in LineItems. Business request is two-fold:

1) Allow partial disbursement of a credit memo. A major chain with 250 stores now wants to take 30% of a credit memo with each payment (geesh). Credit Memos are free-floating and can be applied against any invoice with a balance (for any store within that chain), reimbursed via credit card directly to the store or be deducted from 1 or many check payments. We need to track these disbursements like a small sub-table. I THINK the attached will work for the sub-table. I see no need to attach another table nor do I want to change the original credit amount nor script adjusting its balance after each payment. Also, pulling this piece out of my LineItems would be a pretty big change in process and I want to avoid it. But I am open to ideas on this piece as well.

2) Decrease the length of credit memo numbers. The business thinks it looks like we have to bring back tons of product whereas (in truth), I just cheat and use the LineItem serial as part of the Credit Memo number (blush) which is CM-0589327. But I'm unsure if (by keeping credit memos in LineItems), I can SAFELY create a parallel set of serials or whether I can somehow MODIFY the 7-character text serial from LineItems into something very short and serialized (to use in the CM number). There can even be gaps in the numbers. But it must be safe in multi-user. It would be simple if I pulled this piece out of LineItems and let it serialize in its own table. But 1) that would mean a major change (I think?) and 2) I'm not convinced it is necessary. If I can use the existing LineItems serial, there should not be the possibility of duplication, right? The ORIG Credit Memo will have been created as a LineItem - I won't be looking outside this record for a different number. So can I take that 7-character and make a new (short) serial whenever I create an ORIG item and have it be unique as well?

Mod ( serial ; 100 ) APPEARS to work (please see attached for sub-table as well). But is this a safe approach? These numbers WILL be used to hold this sub-relationship together so I can't have it break. Assistance/input on either piece will be greatly appreciated. :wink2:

SubTable.zip

[comment]

1. You are a much better accountant than I'll ever be, so... Still, I'm puzzled by this: isn't disbursement a method of payment - like a check?

2. Mod ( serial ; 100 ) will definitely NOT work. It will cycle perpetually between 0 and 99. Say a credit memo has a serial number "0525667", then you have 98 line items, and the next credit memo is "0525767". Now you have two consecutive credit memos numbered "67".

The only RELIABLE method I know of is auto-entered serials - which means another table. Fortunately, you can make this work entirely without scripts and transparent to the user by having a one-to-one relationship between the tables, and creating both records simply by filling a field in each table.

[LaRetta]

isn't disbursement a method of payment - like a check?

This is a mix-point between AR and AP. I'm unsure how term originated in this business but loosely it is authorization for payment and (many times) a check is cut or credit card refunded when CM is issued. Credit Memos (in their entirety) back out of Sales income (and commissions) in the fiscal-month created. The difference between Sales less credit memos less payments is outstanding AR. If these disbursements were counted as (reversed) AR payments then they would offset the credit memo and reflect the original sale figure. Eventually (varies business to business), any unused credits are converted to income and reconciled. I prefer non-profit books.

Okay, another table it is. I would prefer the disbursements be in the other table but that wouldn't help me serialize the Credit Memo number. I suppose I could create the Credit Memo in the new table as the first unused disbursement with 0 disbursed. Then I would have the CM number to add the CM in LineItems (but that feels cart-before-horse). I prefer CM in LineItems because, other than disbursements, it IS my ledger and reporting/summaries work wonderfully (from LineItems table).

But if Disbursements were the second table, it STILL wouldn't serialize only on the first disbursement of each Credit Memo, would it? Incremental serials are all or nothing, right? Well I suppose that would be okay - it would just mean that my Credit Memos would have holes in the numbers (when additional disbursements were created). I suppose I could go with two additional tables (CM and Disbursements) and just write the CM back to LineItems. I have much to consider here ... I don't want redundancy. But maybe if the CM table only contained the serial (and LineItem serial)?

Thank you for helping me, Michael. :wink2:

[comment]

See, I told ya - I have no idea what you said in the first paragraph there.

But maybe if the CM table only contained the serial (and LineItem serial)?

That's the basic idea, but then you will need a script. OTOH, if you have even a single field in the CM table that needs to be filled by the user, then BOTH records will be created. Or, turning it around, you can have the user fill all the CM details in the CM table - but the amount is written directly to a field from LineItems.

BTW, I think people who worry about serial numbers should be round up and forced to volunteer at the nearest hospital or something - so that they can get some sense of REAL problems. I mean, who cares? The number on the last bill from my ISP is 200620201479. Does anyone over there think I am going to analyze this? Ooh, they have too many customers, they cannot provide good service - I'm going to change my provider! Really...

Edited September 9, 2006 by Guest
Added a rant

[LaRetta]

Oh, I liked your rant! Well, the only reason it matters is that, when credit memos are deducted from payments received, hopefully they have listed the CM number as reference. Then the person entering the check can apply the CORRECT CM against the payment. If it is the exact amount it is obvious - but it won't always be. And stores don't always list the CM number on their stub - this drives me nuts. But a CM number preserves our sanity if we need to discuss unpaid balances (or track specifically how payments were applied). It's only a reference issue - [color:green]I agree that the size of the serial shouldn't matter at all. But it now will be important to us because it will tie a relationship together (between the CM and its disbursements). The DisburseID is Invoice Number (if they took the amount off an invoice), Receipt# (if they deducted it from the bottom of their check) or the Transaction # (if we reinbursed via credit card). This DisburseID then is filtered to include with reporting for deposits, credit card transactions and such.

I am not an avid double-entry believer ... Luca Pacioli did much for the accounting world but they didn't have computers which could think (HA HA). But that means that the Developer must think the debit/credit piece carefully or they'll ... ummm ... hit these types of situations (such as AR/AP overlap). LineItems works wonderfully for ledger and handles this piece quite well (without debit/credit columns). Splitting disbursements out (only so we can have smaller CM numbers) will be major adjustment (backward compatibility through posted ledger makes me shudder). I could simply tell them that the CM number generation can't be changed or I might try using a CM serial generation table (only). IIRC, you created just such a demo but I'm unsure if it would work in this instance. I will try to find it.

Currently, Credit Memos are event scripted (for many reasons). It seems more logical to modify that script than to change portal and statement displays (of LineItems which currently display disbursements as well for history tracking). How about this:

LineItems::serial = CM::LineItemSerial (with Allow Creation).

Add to script (which creates CM record in LineItems now)

Set Field [ CM: any field but LineItemSerial ; anyValue ] (I'd probably just insert a 1)

Commit Records/Requests

Set Field [ LineItems::CMnumber ; CM::CMnumber ]

It is very similar to your thinking - only backwards. Does that sound feasible?

[color:green]Unless you think the above is possible, I'll tell them I can't change the numbers. But I hate saying no.

Edited September 9, 2006 by Guest
Added green

[comment]

I think this should suffice:

Set Field [ CM: LineItemSerial ; LineItems::LineItemSerial ]

Commit Records/Requests

Now you have your CM serial in the CM table, with a one-to-one relationship - no need to keep it in LineItems as well.

[LaRetta]

OMG! That works perfectly! And so simple! And I don't have to restructure a thing! I'm rockin' now! Bless ya!

:laugh2:

[LaRetta]

Why was I under the impression that you couldn't set a key directly when Allow Creation was on! I always thought you had to set a non-key field. This just sent me into brain-spin. Or maybe I'm just due for another break. :crazy2:

[comment]

Maybe it's because it wouldn't be very practical in a one-to-many situation - which is more common.

[LaRetta]

Ah. I have no other 1:1 relationships (although I've considered it to lighten the burdeon on my LineItems) and decrease number of fields I have to scroll through. :giggle: