Runninig Total-- Counts and Allocation

[malia]

I'm new to the forums, and I am seeking help because I've had this issue that I can't figure out.

To simplify, let's say I have three tables:

1- Orders

2- Line items in those orders

3- Products

The line items contain the quantity of an item ordered.

The products database calculates how many items are left in inventory by taking the starting inventory and deducting the sum of the quantities for the line items, that all works fine.

However, this is the tricky part.

Say item 1234 has 4 units in stock

Order 1- has qty 1 unit

Order 2- has qty 2 units

Order 3- has qty 2 units

the system correctly calculates that there is -1 in stock now, which is what I want it to do.

But NOW I need it to allocate those units to an item so that it knows the following

Order 1- has qty 1 unit-- this is OK, there are 4 in stock

Order 2- has qty 2 units-- this is ok, there are 3 in stock (after processing order 1)

Order 3- has qty 2 units-- this is the order that cannot ship because there is only 1 left in stock after processing orders 1 and 2)

And I cannot figure out the logic or calculation or method to do this.

If anyone can help, I'd be eternally grateful.

Thanks!

Malia

[mr_vodka]

Why dont you put in a check to see if the qty is avaiable prior to creating the line item in the order or you can flag the line item as an invalid line item.

[malia]

I can't do that becuase the orders aren't manually entered, one at a time, they are imported from a website's export feature.

[mr_vodka]

Okay then if you are importing them from a third party source, then I am assuming that you do some kind of processing for the shipping, no? Why dont you take your imported records and then run a loop through it so that it indicates if the order items is available for shipping or not.

[malia]

Even if I used the loop, the loop would still have to perform the same function of being able to count off or allocate quantity to know if the item was available for shipping, so we're right back at square one, which is actually having a way of counting off or allocating the quantity.

Also, having a constant look wouldn't be good, because, when inventory is received, the count needs to be refreshed and looping each time would be a lot of scripts.

In searching this site some more, I found a solution which does this feature,

http://www.nightwing.com.au/FileMaker/demos8/demo807.html

And they call it calculate/carry down (across records), which is essentially what I need. Does anyone have any idea how to restrict the carrying to records with the same item #?

Would this be done with a fond/sort script or an if statement in the calculation?

For example

item # ordred quantity remaining stock

1234 1 3

1234 2 1

1234 2 -1

1235 (resets counting down to not continue from previous value)

[mr_vodka]

If you have a list of imports, I still do not understand why you just dont mark each record with a boolean that it is okay to be shipped. With a loop, you can check to see if there is stock and if so for each new record that wants that item you can subtract from the qty on hand. If your stock gets low to the point where it can not cover a particular order qty, then you can flag it as pending instead of shipped.

[malia]

Mr. Vodka,

I appreciate your help, but it seems now you are getting into arguing business processes rather than taking the problem or question at face value.

This is the way it needs to be done.

Yes, at order import, I can write a script that loops through and checks inventory, however, that still does not solve the problem of updating that inventory availability status when new stock is received. There are also other conditions that affect inventory status, such as another order being cancelled before shipping, thus releasing the stock to become availabile. Or another order being returned, thus adding to the inventory and availability. Or inventory being counted and finding out there was more or less in stock than originally in the database...I could go on.

Thus your way, while effective for import, is ONLY effective for import, unless the script is going to run every time an adjustment is made, which would be too much scripting.

And now I am back to square one. I have actually spent posts going back and forth with you about why I need to do it this way instead of you just taking my word for it that it needs to be done this way.

For anyone else looking for a solution to this problem who finds this after me, the link I posted above works beautifully for this type of count. Using a subsummary value, it counts off quantity by the value you use for subsummary.

Thus, I have been able to find a solution to the problem, and it works as inventory quantities fluctuates or chages, it still counts off correctly. So hopefully this can help someonoe else.

The only script involved is ensuring that the layout that containts the counting fields are appropriately sorted (to get the subsummary data) and refreshing a lookup field to return "in stock" or "backorder" or what not to the line item in the order.

[mr_vodka]

I appreciate your help, but it seems now you are getting into arguing business processes rather than taking the problem or question at face value.

and what IS the problem at face value? You post vauge information about a basic 3 table orders setup and wanted to know how to prevent a line item from shipping if the qty was low. That was the gist of it.

Then after I probed you about why you can not put in a check as you go, you only responded that it was imported from a website's export feature.

Do not lecture me about Business Practices. I never talked to you about how you should run your business. I spoke on how technically you can flag a certain line item that there is not enough inventory by running a loop on the imported line items. BASED OFF THE INFO YOU GAVE.

Yes, at order import, I can write a script that loops through and checks inventory, however, that still does not solve the problem of updating that inventory availability status when new stock is received. There are also other conditions that affect inventory status, such as another order being cancelled before shipping, thus releasing the stock to become availabile. Or another order being returned, thus adding to the inventory and availability. Or inventory being counted and finding out there was more or less in stock than originally in the database...I could go on.

What does this have to do with running a loop on your imported records??? This doesnt change how you can add more stock. You original issue was that you wanted to prevent an item to go into negative qty.

I have actually spent posts going back and forth with you about why I need to do it this way instead of you just taking my word for it that it needs to be done this way.

Indeed... I wish I had those wasted minutes of my life back.

[malia]

and what IS the problem at face value? You post vauge information about a basic 3 table orders setup and wanted to know how to prevent a line item from shipping if the qty was low. That was the gist of it.

I didn't want to know how to prevent a line item from shipping, I wanted the database to only INDICATE which items could not ship. Just to return a value indicating the stock status of that particular item.

This was my initial question

But NOW I need it to allocate those units to an item so that it knows the following

Then after I probed you about why you can not put in a check as you go, you only responded that it was imported from a website's export feature

Correct, but revealing that the data was imported was only relevant because you figured that it should be done differently, not because you actually looked at what I asked. Only because you said "why don't you do it this way" did it become necessary to explain how the data got in the databse in the first place. I could have gone on about reasons it wouldn't be prudent to do that at data entry (for example, if you allow people to order backordered items), but again, this was only an issue because you assumed I wanted something to be prevented from shipping when I only wanted an indication if it was in stock for that order.

This doesnt change how you can add more stock. You original issue was that you wanted to prevent an item to go into negative qty.

Again, no. Again, the issue was only giving the information on the stock status, not preventing anything. That was an assumption you made.

I have re read my posts and it seems as though I tried to make it clear that I just wanted an allocation of items, to show if the items were available for an order. You assumed that meant I wanted to prevent something from shipping or prevent negative line items and thus came up with the loop script.

But that wasn't the issue.

Again, thank you for your help.

[mr_vodka]

But NOW I need it to allocate those units to an item so that it knows the following

Order 1- has qty 1 unit-- this is OK, there are 4 in stock

Order 2- has qty 2 units-- this is ok, there are 3 in stock (after processing order 1)

Order 3- has qty 2 units-- this is the order that cannot ship because there is only 1 left in stock after processing orders 1 and 2)

Then perhaps you should not have written it in such a way.

I didn't want to know how to prevent a line item from shipping, I wanted the database to only INDICATE which items could not ship. Just to return a value indicating the stock status of that particular item.

Whether you used it to prevent or indicate, it still does the same. It marks.

If you have a list of imports, I still do not understand why you just dont mark each record with a boolean that it is okay to be shipped.

Why dont you take your imported records and then run a loop through it so that it indicates if the order items is available for shipping or not.

[malia]

I could have been more clear, yes, lesson learned.

But the fact still remains I asked how to do something specific and you continued to question why I would not or could not do it a different way.

Again, thank you for your help. Your suggestions are appreciated and may be useful in the future, they just were not useful for this particular application because I needed something to do exactly what I asked about (for various reason).

Again, thank you.

[LaRetta]

The products database calculates how many items are left in inventory by taking the starting inventory and deducting the sum of the quantities for the line items, that all works fine.

Well. Yes and no. 1) the aggregate calculations will take longer and longer to process and 2) you cannot account for manual inventory adjustments etc.

...that still does not solve the problem of updating that inventory availability status when new stock is received. There are also other conditions that affect inventory status (minus aggregate 'unshipped' orders), such as another order being cancelled before shipping, thus releasing the stock to become availabile. Or another order being returned, thus adding to the inventory and availability. Or inventory being counted and finding out there was more or less in stock than originally in the database...

I would suggest an Inventory table. I've tried all methods and that still works best. Yes, you may deal with manually marking records but you will anyway. By using an Inventory table, you can 1) look at orders unshipped against your STATIC inventory balance (which has been adjusted with returns and stock in) and tell a customer ahead of time whether there is enough stock, 2) on the day of shipping, you can loop as John suggested and decrease your inventory.

You simply join ship date and ProductID from LineITems to Inventory table with Allow Creation on. Then Set Field [ Inventory::Qty ; Inventory::Qty - LineItem::Qty ]. If there is a problem (power loss etc), you can easily re-create your day's inventory by deleting that day from all Inventory records (with Type=Sale so it will leave manual adjustments that day) and re-run your shipment for the day.

If you are keeping one 'On Hand' figure in your Product table, and you don't wish for an Inventory table, then you will never have an audit trail of stock movement. Your accountant will want that, ie, returns are NOT business loss if they are put back into inventory for resale, etc.

You are delving into a difficult subject; just remember that John has the background to properly assist you. It's just that you were unclear (within yourself) what you needed, thus it is difficult to ask it correctly. We understand. I suggest you also draw the stock movement on paper before ever coding it. It is amazing how many details can get overlooked in a process as complicated as this one (which, BTW, is second only in complexity to a full accounting module).

LaRetta :wink2:

Edited April 19, 2008 by Guest

[malia]

Hi Laretta!

I did create an inventory transactions table. I should have mentioned that upfront, but I wanted to keep the question simple (and maybe that was not a wise thing to do).

I have different types of transactions in that table:

starting inventory

orders (deductions in inventory)

purchase order receipts (increases in inventory)

manual adjustments (can be a deduction or increase)

(thus far... I have not added in returns yet)

The calculations will take longer with more data, but my thinking (which could be wrong) is that I can set regular time intervals to clear the data and start with a new starting inventory becuase the orders, purchase orders, etc. are really data pulled from other tables, so it's not like I'm erasing that data.

However, it still doesn't use any scripts to change quantities.

The way I have worked it out (and you can tell me if this is good, bad, makes sense or not) is as follows:

There is a field defined as transaction type (order, purchase order, etc)

There is a quantity field, which is essentially the quantity of items in that order

there are other fields such as date shipped, date order placed, etc.

There are two fields that calculate the impact of that order upon the inventory:

1- calculates its impact on available inventory (for an order it would be a negative impact on available inventory, for a received purchase order it would be a positive impact on available inventory)

2- calculates the order's impact on physical inventory (which is only adjusted for an order when the order ships, to calculate this, it looks to see if there is a date in the ship date field)

So while it doesn't loop, it does require lookups. But not that many, as orders are not constantly being processed throughout the day.

It imports newly added line items (update matching, add remaining records as new) from the line items table, then it sorts so that the "counting" calculations can be made, then the line items table performs the lookup to get the stock status of each line item.

Having the inventory transactions table has allowed me to easily see any errors in calculations without un-doing scripts or anything, until I know that everything is set up properly.

I don't know if I am missing anything, this is as far as I have gotten.

[LaRetta]

Then I, like John, still don't understand the question or problem. If you look at the second quote in my last post, you indicate it's exactly this piece that you are missing. Would you like to try again to rephrase your question? Because I still see no specific question in your last post either. :crazy2:

[malia]

Hi laretta

When I first started the thread, I didn't have the solution.

Later on, I found an older post on this board that provided a link to a downloadable file that does count off inventory and I was able to resolve most of the problem by incorporating that file's calculations and methodology.

My last post was simply just posting what I came up with, after the fact, just to check and see if anything was missing.

[LaRetta]

When I first started the thread, I didn't have the solution. Later on, I found an older post ...

Hmmm, well Malaria, it doesn't sound like you created the inventory portion later at all, according to the next quote which you mentioned PRIOR ...

I did create an inventory transactions table. I should have mentioned that upfront, but I wanted to keep the question simple

John responded appropriately all the way through, and just as I and many others would have given the information you provided. Your attitude was a bit sucky considering that he was giving you his time and attention for nothing; which he usually charges a lot for. I'd have dumped you a few posts prior but John kept trying to help.

Hey John! Great patience under the circumstances! Way to go!

LaRetta

[malia]

Hmmm, well Malaria, it doesn't sound like you created the inventory portion later at all, according to the next quote which you mentioned PRIOR ...

I don't know if the Malaria was an insult or a typo (and it comes across as an insult), but...

The table was there, the knowledge of how to calculate the values I needed wasn't there when I first started the thread.

Yes, John was trying to help, but he was insisting I do it in a method which was not feasible for me.

I feel that you have been helpful, with the suggestion of the inventory table. However, if this is going to degrade into what was said when and what was and was not clear, then it becomes pointless.

[Lee Smith]

I think it was because you called her Laretta, instead of LaRetta.

I also think that it is time to show proper FM Forums decorum.

Lee

[LaRetta]

Now why would I be helpful then degrade? And why would John insist you do things his way when we are here to help? Just because someone suggests something a bit differently than you might want (as John was suggesting) and just because I made it clear that I thought John was quite patient when you were coming across snitty - that doesn't mean that I then felt a need to be rude. Sure. I mistyped your on-line name just to be mean. I truly have better things to do with my energy than pick on someone on Forums today so don't flatter yourself if you think that's what I'm doing.

It seems that last few days everyone is overly sensitive, taking things out of context, and biting each others' heads off - many people have mentioned it to me (both moderators and guests). Seems people forget why we are here and think we respond to degrade; heck, maybe it's just a full moon.

I wish you well with your Inventory module. As said, it is a difficult functionality to implement. I can further suggest you get all the rules in writing (and get it signed off by management) because inventory issues can circle back around and bite you in the tush if any parts of it are unclear.

I'm off to play with my dawg - one of the few critters that take everything at face value; what a pleasant relief it is too.

[LaRetta]

I hadn't seen your reply, Lee. Nah, many people don't cap my 'R' and it doesn't bother me. Worst I might do is correct them but most times I let it go without saying anything. Heck, they call Comment Veteran! You can call me anything as long as you don't call me late for dinner. :laugh2:

[mr_vodka]

I wanted to let this thread die but you seem to keep insisting that I was determined that you should do it MY way. That was never the case.

First you didnt even state until your 4th and (much rude) post that it HAD to be a certain way. Many times people come on here and post a problem that they currently have, and the resolution is easier than they think. It is one of the strong points of these forums to get different methods on achieving the same goal. Various eyes and experience will give input and sometimes you will see that it could have been done in a different manner. People tend to over-complicate things.

So based off the information people post on here we try to help each other as much as we can. There are tons of posts on here where people realize that the current method that they are running things is not the most eloquent or optimal way of doing it. We can only post about our opinion on what we think is the best solution. If everyone can on here with the attitude that it can only work their way, there would be no room for growth.

You posted on here with limited information and I offered you my opinion with a guessing game on your structure; then with subsequent posts when I drew out more information from you, you decide to get rude with the free advice that I offered.

It is always your choice in the end to listen or not listen to any kind of advice anyone can give you, but just keep in mind that there are always better ways to do things.

[Lee Smith]

I hadn't seen your reply, Lee. Nah, many people don't cap my 'R' and it doesn't bother me. Worst I might do is correct them but most times I let it go without saying anything. Heck, they call Comment Veteran! You can call me anything as long as you don't call me late for dinner.

Well, sometimes I don't think so good.

:

[LaRetta]

Ha ha! We ALL don't always think so good. And sometimes we say things and they come out wrong. Being given benefit of doubt would sure be a good grace once in a while. BTW ...

Nah, many people don't cap my 'R' and it doesn't bother me.

Actually, that isn't true - it DOES bother me. But my friends always spell it right and I decided that's all that really matters. :yep:

[Genx]

they call Comment Veteran

Ahahahaahahaha... ahahahaha... yeaahhhh.