Inventory Scripting

[yafreax]

Hey all...

In our invoincing system i would like to create a inventory type system that will auto update when an item is entered into a PO. Here is the main components:

Pricing DB: holds all items, will be the base of the Inv. solution.

PO DB: holds all the basic PO info.

PO Backend DB: A portal that stores the line items for each PO. Conditional Value Lists relate to the pricing module.

INV Add: keeps track of the additions

INV Remove: keeps trak of the removals

Items within POs will sometimes need to be removed from the inventory (customer order) or added to the inventory (stock refurbished). I didnt originally have a unique id for each PO line item, only for each Pricing Item. But now i found the need. So i created one using the 20 character Status(date), Status(time), status(recID) format. My scripts/field calcs are as follows:

Field-> "CurrentQnty": Pricing DB: Calc: BaseQnty + ((Sum(INVAdd::QntyAdded) - Sum(INVRemove::QntyRemoved)))

Field-> "INVStatus": PO BackEnd, used in scripts to determine if inv. should be added or removed.

Script-> "AddINV": PO DB: called from within PO BE Portal line:

--

If("PO_BE::INVStatus = Add")

Halt Script

EndIf

Copy (PO_BE::LineItemID)

If ("PO_BE::INVStatus= Remove")

Perform External Script: "DeleteLineItem" Script in "INVRemoveDB"

---INVRemove - Delete Line Item Script is as follows:

-Show All Records

-Enter Find Mode

-Paste(LineItemID)

-Perform Find

-Delete Found Records

---End Of SubScript

EndIf

Set Field (PO_BE::INVStatus = "Add")

Perform External Script:"AddINV" Script in "INVAdd" DB

---INVAdd - AddINV is as follows

-New Record

-Paste "LineItemID"

---End Subscript **See Note

End Script

** In both the INVAdd and INVRemove DB's, a relationship with the PO_BE DB is established using the LineItemID... this calls the ItemRefID and the Qnty Add/Removed that is required in the PO per line item.

I also have a respective "Remove Inventory" script that works in the same manner.

first of all, have i gone about this all wrong and made it too difficult? i tend to do that...

Secondly, the problem seems to occur when i have more than one Line Item per PO, which will be probably 60% of the time... What happens is that if i call the Add script, then realize it should've been Removed, then i call the Removed script. This usually works the first time, but if i then call the Add script again, things start falling apart... or if i do it in the opposite order as well... it will then tell me "No Records Match" when performing the find to delete the line item. not only that, but after it stops working correctly it will continue to Add or Remove subsequent qnty's as i call the scripts.

Am I making any sense? Generally the person that enters these in will call the right script (activated from buttons within the portal row) and so this is a non-issue. But, if i ignore the issue, then sure enough it will be a big problem. Not only that, but i dont understand why it only happens with multiple line items... i can sit there all day and press "Add" then "Remove" back and forth on the first item and never have any problems. That leads me to believe that i have misunderstood scripting in portals.

Thanks for the help... if it comes

Jeremy

[LiveOak]

I really have no idea why you would have both an add and remove file and delete and add records to each!

I wouldn't use two files for add and remove. I would use a single file for Inventory Transactions, which would include BOTH credit and debit type transactions. Also, NEVER delete a record in the transaction file! If you inadvertently post an "add", just post an ofsetting "delete".

I would also have different transaction types to differentiate stocking items to inventory, drawing items from inventory and making adjustments to inventory. Usually, adding and removing items from inventory isn't a function of Invoicing, but of the actual picking and shipping operation (or receiving and stocking).

-bd

-bd

[yafreax]

LiveOak said:

I really have no idea why you would have both an add and remove file and delete and add records to each!

Cause i was ill-advisedly taught that way?? still, my fault... as i mentioned i tend to do things the long way mainly because this isn't the focus of my job. It fills a purpose. I understand a lot of what FM can do, but i dont understand the best way to go about doing those things sometimes. This PO/INV system works great for it's purpose and has very few flaws, but i wouldnt be suprised if it is a mess compared to what some of you do. In fact this program has technically been a work-in-progress for the past 3.5 years. Revamped completely once, and tweaked here and there... when i am making structural changes, i often wonder myself if i shouldnt send it off to one of you guys and seek advise for simplifying the system. I dont think very conceptually or critically when it comes to this sort of thing. Anyway, i digress... not to support myself but to vent.

I wouldn't use two files for add and remove. I would use a single file for Inventory Transactions, which would include BOTH credit and debit type transactions.

I'll work on the one file thing.

Also, NEVER delete a record in the transaction file! If you inadvertently post an "add", just post an ofsetting "delete"....

I would also have different transaction types to differentiate stocking items to inventory, drawing items from inventory and making adjustments to inventory. Usually, adding and removing items from inventory isn't a function of Invoicing, but of the actual picking and shipping operation (or receiving and stocking).

I agree with all of your points here, and did so before i posted. However, all i can do is make suggestions to the one that makes the decisions... i.e. - the one that uses the system 90% of the time... i.e. - the owner / CEO / sales / management of the three person company we are... He originally wanted to be able to simply fix his mistake if he made it... After talking to him again, we came to the agreement that there should be no deleting, but he still wants to set the add/remove in the PO/Invoicing portion. Whether that's best or not is irrelevant at this point...

however, since we have come to the agreement that there should be no deleting, and it is being transfered to one DB, the main issue in my first point should also be irrelevant. Thanks for the suggestions thus far.

jeremy

[LiveOak]

Inventory is definitely a complicated thing (for all of us). The reason for no deletes comes from good accounting practice. I'm sure a lot of small business owners (timid lot they are) want the ability to change what they want, when they want to. They, however, don't want others to have this ability (all animals are equal, some are just more equal than others!).

Sometimes we have to keep counseling the appropriate changes and hope they well be accepted, someday...

-bd