LineItem to Inventory Logic

[LaRetta]

Products sold (as appearing in LineItems) are listed as a positive number (of course). Returns are listed as a minus. To write our sales to Inventory, I use a join on: ProductID = ProductID AND date = date and MoveType = MoveType. I use Set Field [ Inventory::Quantity ] directly (which creates a new Inventory line if Product/dateMoveType doesn't exist). Originally written, I had become confused on converting these numbers in my script, 'adding together' multiple positive (actual minus) numbers and 'adding subtracting' multiple negative numbers. I feel like a multiple-dork for finding this confusing. I'm reviewing my write process and I can use clarity in my thinking please.

Currently, I use Auto-Enter (Replace) to determine whether a quantity is positive/0/negative. If Quantity is minus, MoveType becomes IN; if Quantity is plus, MoveType becomes OUT and if Quantity is 0, there is no MoveType (thus it doesn't write to Inventory). Once in Inventory, the OUTs need to be converted to a minus (and the INs, which add together and end up with total minus quantity) are converted to plus! This is driving me nuts.

These lines must still be reversed because now I use a running summary portal of Inventory in Products to give our Inventory balances. And I want to track both INs (returns) and OUTs (sales) separately for each day. But I KNOW I'm missing some obvious logic in it and I should be able to handle it during the Set Field[] script-step. Ideas to straighten out my thinking on it? I'm in the middle of a re-write and now's the time to fix it. Thank you!!

LaRetta

[Søren Dyhr]

Might it be just me whos silly enough to think that both summaries as wells as aggregate functions not quite up to huge numbers of records, how many inserts on earlier days are likely to break the chain.

Business practices exists though where the head of the warehouse keeps the documents to him/her self, and only arrives in the office when bad conciseness forces him/her to hand them over ...when it comes to it must such behaviours be avoided by other means.

So when ever a shipment arrives or leaves should good data disipline make this approach considerably better than anything else, I mean it has been in use since the late 1490 when the the bookkeepers tool was an abacus and a pen - even before in the arabic world!!! (google yourself to Luca di Pacioli)

http://www.filemakerpros.com/LULAST.zip

--sd

[LaRetta]

My apologies, Soren, I'm unclear on what you're suggesting! I have many dynamic views into our LineItems and Inventory, as well as Summaries and they are quite fast in fact. Are you suggesting that I keep our purchases in LineItems as well? Or are you suggesting I change my Inventory table to write balance to static field?

You seem to be addressing the issue of what to do with quantities once they're in my Inventory ... but they'll still have to be converted to get there! Our Inventory is written to permanent record and the balance brought forward quarterly (as per our accounting firm's request) and it also keeps file size down so summaries are quick in our portals.

BTW, you said " how many inserts on earlier days are likely to break the chain." No chain breaking allowed. Once written they are not changed because the LineItems are flagged as written and are not changed. But I need the PLUS quantities in LineItems to become MINUS quantities in Inventory, that's all. And I thought a script could write and add/subtract instead of waiting until the write process was done to then loop through them and reverse them.

I may not be able to change the underlying structure but I'll consider it. But I have management rules and accountant rules to live by as well. And I thought everyone was familiar with the concept of writing to an Inventory table. Not so? I'll give it up in a heartbeat if there is a better way. Can you give me a clearer perspective on what you're suggesting please? :wink2:

[LaRetta]

Here is the Set Field{ } script step I'm using:

Set Field[ If(RightWords(LineItems:_KeyInventory; 1) = "In"; InventoryWrite::Quantity - LineItems::Quantity; InventoryWrite::Quantity + LineItems::Quantity)

And of course I write the MoveType, Date and ProductID when Auto-Create makes the record. But it seems that Auto-Enter (Replace) in Inventory could calculate (add or subtract) then reverse whatever number were passed to it I just don't know how to add negative numbers and such. And I Also think the trigger and the math can be cleaner and more pure. I just don't see it.

Should I not be writing my LineItems to Inventory? I'm open to all approaches; screw old school. I'll make it look and perform however they wish. It's like the old Dennis The Menace cartoon; BTW my alltime favorite:

Dennis is sitting on his dunce chair in the corner (like the teach always made him do for something or other). He's facing the wall and his thought is, "I may be sitting down on the outside ... but I'm STANDING UP on the inside." I always adored that philosophy. So the inside will be mine AND I will adher to their Management decisions. I will simply do both.

So write or no write? And if write ... what's the best way. When Inventory is entered (Purchases, Adjustments, Transfers, etc.), Users always want to type a - on outgoing; which is logical. If I use Auto-Enter (Replace) to reverse the numbers, it makes - - which pluses. It breaks. So if I write, it needs to be good; if I use Auto-Enter, it needs to account for reversing the polarity properly.

LaRetta

[Søren Dyhr]

You seem to be addressing the issue of what to do with quantities once they're in my Inventory ... but they'll still have to be converted to get there! Our Inventory is written to permanent record and the balance brought forward quarterly (as per our accounting firm's request) and it also keeps file size down so summaries are quick in our portals.

Sorry for continuing in the same groove (what is the contemporary expression here, when turning to CD's???) if you phone you accoundant will probably say that all "accounts" not just the inventory should be designed this way.

He's facing the wall and his thought is, "I may be sitting down on the outside ... but I'm STANDING UP on the inside." I always adored that philosophy.

Well then he and Comment are the right candidates for this:

http://www.americanscientist.org/template/AssetDetail/assetid/45938

....roll, pitch and yaw away, to your hearts desires!!!

--sd

[comment]

I am not quite sure what I have to do with either Dennis or that link.

Don't even try to explain. Instead, could you explain in a few simple words what you meant by:

when ever a shipment arrives or leaves should good data disipline make this approach considerably better than anything else

What exactly is "this approach"? You hint like we're all suposed to know. Could you spell it out?

[Søren Dyhr]

Lookup(Last) ...ledgers that don't need to be summed from start to end on demand say 5000 records - but instead by placing a ruler somewhere in the mainbook, just by looking at the line above could the fact of how large the stock/fortune/debt etc. was at that particular moment, be established.

Attempted descriped here: http://www.responsive.co.nz/theory.html

and here:

http://www.nationmaster.com/encyclopedia/Double_entry-book_keeping

This is a pretty established if not the one and only way of handling such issues, allthough there have been numerous attempts to move some disguised liabilities to the assets side of the equation (Think of Kenny Lay)

My point is that we have to ignore what the tool might give us all kinds of nifty ways to present data with various credibility ...but instead use the established way of gaining the KNOWLEDGE of the state of affairs.

We have some expressions in danish that says: "Even a dogs excrement's looks like gold in moonlight" and the other one I came to think of was "It is with statistics like lampposts in the street, they're good to lean against - but gives poor light"

--sd

[comment]

Lookup(Last) is good in a flat file. It does not answer the problem of a transaction in one table generating a transaction in another (such as sale -> inventory).

So what is the method of updating the inventory as a result of a sale? Do you script it so that the same transaction is registered in both tables, or do you make a separate entry when the goods are actually taken out of the warehouse? Neither method sounds 100% reliable to me.

[Søren Dyhr]

you make a separate entry when the goods are actually taken out of the warehouse?

Thats how it's supposed to be done isn't it?? We need an accounting specialist on this one!!

But an initial thought is that it really isn't about the sign, but rather to make each itemline a be ledgerline as well, when the item then is shipped - will the ledgerline recieve by autoenter two account (due to the document it was created in) numbers one for the account the debit and the other the account to credit, this will cause the two(four) previous figures will be pulled into the record as well - via the lookup Last.

So the relation is a star!!! And by the look of it not scripted at all!!

--sd

Edited August 25, 2005 by Guest
I came to think about it!!!

[comment]

I am not sure. Capt. Murphy says that the warehouse entry will have a different quantity and/or different product code from the invoice. And where is the link from the warehouse entry to the invoice, so that such eror can be hunted down?

Surely, even a "double-entry" accounting application does not require the user to actually enter the same transaction twice?

[Søren Dyhr]

Surely, even a "double-entry" accounting application does not require the user to actually enter the same transaction twice?

If you read my change to my previous post is all it takes a checkbox in the shipping document that pulls the two account numbers and trigs the LULast's ...what the warehouse acually calls each thingy in the itemlines of the shipping document is a matter of presentation.

--sd

[comment]

I am not sure I understand. Auto-enter is NOT auto-create. Are you suggesting that EVERYTHING (invoice line items, purchases, shipments and what else) be entered into the same file?

[Søren Dyhr]

Yes the ledger, that is not file but instead table! The creation of the line stems from all kind of documents Invoices, financial movements and purchase_arrival documents. The document they stem from dictates what set of account numbers to be used.

Each of these documents have a script to enter a line or the allow creation of related records checked. So what is shown in the portal is a version of the data in the ledger.

--sd

Edited August 25, 2005 by Guest

[comment]

That is an interesting (and complex) approach. Not that I have a better one. But the problem is that some of these entry types MUST have their own consecutive serial numbers.

[Søren Dyhr]

That is an interesting (and complex) approach.

Well it have since occured to me that the invoice in itself is a ledgerline, so the Itemlines in the invoice's portal is actually a selfjoin ...so it's indeed a complex solution.

But as I have though of it until now, havn't the need to script occured yet! Thers is some interesting details in the prosponement of the committing the invoice, and fields locking.

I wonder how well these thoughts will survive the nexts weeks bombardments with data (attending Devcon) ...but could indeed be an interesting thing to make into a template.

--sd

[comment]

the invoice in itself is a ledgerline, so the Itemlines in the invoice's portal is actually a selfjoin

This cure seems worse than the disease. I would rather do the equivalent of closing a page in a ledger (or rather pages in ledgers). I believe that's what my bank does: every so-snd-so, they produce a statement and transfer the total to the beginning of a new page. So the dynamic summary is limited to the currently viewed page.