Lantica User Forum - xpost question

(UTC)

Lantica User Forum › Lantica Discussions › Lantica Discussion › xpost question

(Moderator: Forum Admin)

‹ Previous Topic | Next Topic ›

Page Index Toggle

Pages: [1] 2

Send Topic

Print

Hot Topic (More than 10 Replies)

xpost question (Read 4000 times)

FlipGilbert Full Member Offline Running Ver 2.6.4 Posts: 236 Location: Sandy Eggo Joined: Mar 8^th, 2005	xpost question Apr 4^th, 2005 at 1:50am	Print Post
	I have an invoice form that has Part number, Quantity, Description and Cost elements I would like to xpost to the inventory form the quantities removed from the location specified on the invoice form. The inventory form has an element field that has a part number as the key field. It has several other element fields that show the quantity of this part and its different locations. Example one part has 3 locations and total inventory is Location A has QTY + Location B has QTY and Location C QTY To make matters worse I would like to do the reverse for the Ordering form. My question is, Can this be done or is this the same thing like my userselect question? Trying to automate an inventory.

	It's not what a man says that matters or how he says it, but what he does and how he does it.
	IP Logged

Bharat_Naik Senior Member Members Offline Ever ready to learn and share Posts: 1202 Location: Chicago, Illinois Joined: Dec 16^th, 2003	Re: xpost question Reply #1 - Apr 4^th, 2005 at 3:24am	Print Post
	Quote: My question is, Can this be done or is this the same thing like my userselect question? It seems like a typical business application with Inventory, Order and Invoice form. I am sure Sesame can handle this very effectively. What you have a little different is - having 3 locations but that should not be a big problem. Is Invoice form has line_Item subform? How easily and effectively it will be done will depend on your design.


	IP Logged

Bob_Hansen Senior Member Members Offline WOW, They have the Internet on computers now! Posts: 1861 Location: Salem, NH Joined: Nov 24^th, 2002	Re: xpost question Reply #2 - Apr 4^th, 2005 at 3:32am	Print Post
	It may be too late or too much work, but I would recommend a sub table of locations for each part.. Each record would have the PartNumber, Location ID, Qty, Status (Available, Allocated,Scrap, MRB, etc.), Last Date Counted, Cycle Count Frequency, etc. You will also need to set up to handle transactions to move parts between locations. Location ID can have sections and truncated. Location could have multiple parts, a primary locations group for computer transactions, but lower level locations that do not need to be tracked on system. Example, Location could be Warehouse, Aisle, Rack, Shelf, Bin. You may only need to track to the Rack level to minimize inventory transactions, and only need entries if changeing Racks or higher. That gives you some flexibility on the shelves as qtys go up and down. Location format could have multiple sections: Facility-WH-Aisle_Rack-(Shelf-Bin) Each of those sections can have their own coding databases/LU values and concatenated from pick lists. This may sound too complex, but the key is still to have separate records for each Part/Location. Take advantage of the hierarchical structure of Sesame. The design you have described now sounds like a carryover of flat file structures like we had with Q&A. Don't fall into that trap, use the more powerful tools.

	Bob Hansen Sesame Database Manager Professional Sensible Solutions Inc. Salem, NH 603-898-8223 Skype ID = sensiblesolutions
	WWW Skype/VoIP IP Logged

FlipGilbert Full Member Offline Running Ver 2.6.4 Posts: 236 Location: Sandy Eggo Joined: Mar 8^th, 2005	Re: xpost question Reply #3 - Apr 4^th, 2005 at 4:06am	Print Post
	Yes, this is more than a fifteen year old Q&A database. I am more then willing to do what it takes to modify the database. It would take less time than the data entry I have done with the old database. I like the sub table idea, can the parent form have a running total of the subforms? This is helpful! thank you

	It's not what a man says that matters or how he says it, but what he does and how he does it.
	IP Logged

Bharat_Naik Senior Member Members Offline Ever ready to learn and share Posts: 1202 Location: Chicago, Illinois Joined: Dec 16^th, 2003	Re: xpost question Reply #4 - Apr 4^th, 2005 at 4:14am	Print Post
	Hi Flip, Bob has some interesting input about design. Yes, as I mentioned earlier, design will be the key. It need not be to complicated at the same time it should accomodate your needs as the business and needs grows. You should have a broad picture, but you will be working with one form, keeping the other forms in mind. I am not sure if you have to have various subcateries into inventory systems as Facility-WH-Aisle_Rack-(Shelf-Bin), or just 3 locations are sufficient to begin with. In Invoicing, you must have table-subform to take advantage of Sesame has to offer. In Line item table form at minimum you have to have - PartNumber: Desc: Quantity: Loc: Unit_Price Price: Since you know, you have only 3 locations, I think, before you choose location, it should display you inventory levels of that Item at all 3 locations before you choose the location. Popupment can be made to display A 250 B 75 C 125 When you pick up one it will only pickup location. On exit the record, it can subtract quantity from the selected location and Total Inventory of the Item. That brings us to the form and Database of Inventory. The minimum requirement that I see: PartNumber: Desc: Quantity_A: Quantity_B: Quantity_C: TotalQuantity: Ofcourse, you can be more descriptive with name if you need to be. Order Form: will have all the above plus whatever you need to have in terms of information about vendors and order takers and what not. This is my preliminary thinking.


	IP Logged

FlipGilbert Full Member Offline Running Ver 2.6.4 Posts: 236 Location: Sandy Eggo Joined: Mar 8^th, 2005	Re: xpost question Reply #5 - Apr 4^th, 2005 at 4:24am	Print Post
	I like both your thinking, I am limited to the old Q&A way of thinking. Im going to work on this, after all it cant be as bad as that userslect question.

	It's not what a man says that matters or how he says it, but what he does and how he does it.
	IP Logged

Bob_Hansen Senior Member Members Offline WOW, They have the Internet on computers now! Posts: 1861 Location: Salem, NH Joined: Nov 24^th, 2002	Re: xpost question Reply #6 - Apr 4^th, 2005 at 4:40am	Print Post
	I have also introduced the concept of Status to help provide more accurate knowledge. Thought I would add some info here about the value of that single field in each part-location record. Suppose you have physical count of 1000 pieces, how many are availble for Sale? How about the 50 that are damaged and in Material Review Board(MRB) awaiting disposition? And the 75 in QC and have been rejected or not yet approved? I understand that 10 have been dispositioned to be scraped, but have not been written off yet. Have you already allocated 300 pieces for existing orders that have not shipped yet? How about the 30 pieces at Dealers Showroom or on consignmeny. Knowing the status in different locations can help determine, do we need to order replenishment stock now? If a customer orders 600, are they avaialble? So by having separate locations and a separate status for each location it is easier to make the calculations or create the tables showing how many exist for each status, and what location those parts are in. So now you not only know how many you have, but you know how many you have in each status and where you can find them. Even having only 3 locations, is not a reason to stay with existing design. Now you are locked into 3 locations without doing a redesign of the database. Having subforms allows unlimited numbers of locations and unlimited status codes. Just add when inventory transactions are made.... The quick answer re posting is Yes, it can be done, but I would encourage the design structure be done first so you don't need to redo the posting processes when you later decide to redsign from a flat file concept. Do it once, but do it correctly.

	Bob Hansen Sesame Database Manager Professional Sensible Solutions Inc. Salem, NH 603-898-8223 Skype ID = sensiblesolutions
	WWW Skype/VoIP IP Logged

Bharat_Naik Senior Member Members Offline Ever ready to learn and share Posts: 1202 Location: Chicago, Illinois Joined: Dec 16^th, 2003	Re: xpost question Reply #7 - Apr 4^th, 2005 at 4:54am	Print Post
	Flip, I whole-heartedly agree with Bob, as to take into consideration that the design should accomodate your growth and one will have to come out of Flat-File database mentality. I am also into Q&A mentality. I guess, it will take time to come out of it. Thanks Bob.


	IP Logged

Bob_Hansen Senior Member Members Offline WOW, They have the Internet on computers now! Posts: 1861 Location: Salem, NH Joined: Nov 24^th, 2002	Re: xpost question Reply #8 - Apr 4^th, 2005 at 5:02am	Print Post
	Bharat_Naik, don't you ever sleep? I thought I was the only one who was up 20 hours a day, but you are here early in the morning too....... thanks for your help...... now Hit The Rack!

	Bob Hansen Sesame Database Manager Professional Sensible Solutions Inc. Salem, NH 603-898-8223 Skype ID = sensiblesolutions
	WWW Skype/VoIP IP Logged

Bharat_Naik Senior Member Members Offline Ever ready to learn and share Posts: 1202 Location: Chicago, Illinois Joined: Dec 16^th, 2003	Re: xpost question Reply #9 - Apr 4^th, 2005 at 5:07am	Print Post
	Well, I am in Chicago. So, I have an hour more than you esterners.


	IP Logged

FlipGilbert Full Member Offline Running Ver 2.6.4 Posts: 236 Location: Sandy Eggo Joined: Mar 8^th, 2005	Re: xpost question Reply #10 - Apr 5^th, 2005 at 8:01pm	Print Post
	Im trying to grasp the idea on this inventory thing, and I have to say I like the status idea Bob, you mentioned. I have made a subform with the elements I would like and have even thrown in a key value field just for fun. My thinking is to have a popup menu on element exit in the part number field to select where to remove the part from on the invoice form. Is there a better idea? How would I write this?

	It's not what a man says that matters or how he says it, but what he does and how he does it.
	IP Logged

Bob_Hansen Senior Member Members Offline WOW, They have the Internet on computers now! Posts: 1861 Location: Salem, NH Joined: Nov 24^th, 2002	Re: xpost question Reply #11 - Apr 5^th, 2005 at 9:22pm	Print Post
	On the subform, consider a Yes/No box for Primary location. Need some programming to make sure only one location record for each part number can be Primary. So if you enter a check into one of them, all the others will be cleared. Now with your transactions you can have the Primary location show up as a default, and then have a dropdown menu for choices only when default from Primary location is not wanted. You can also predifine your transaction types to control which locations are available on a pick list. Don't want shipping to use any other FROM location other than Shipping. Many transactions can be predefined so no lists are needed, or they are limited based on inventory status and type of transaction. Will need one ADJust transaction type that allows/shows all locations to make corrections as needed. I don't know how simple or complex your needs are, but Invoicing is usually near the end of an inventory shipping process, and would think that those transactions would be done earlier. Here is a simplified flow process: 1. Get an order: Inventory Picklist is generated. Alllocation qty goes up. Allocated, can be just a Status or a phantom location, just for planning. Or it could be a "kitting/staging" area where parts are pulled from stock for assemble/shipment. 2. Move to Shippping/Assembly: Allocation goes down, Stockroom goes down, Shipping/WIP goes up. 3. Move to Inspection. WIP goes down, QC goes up. 3. Ship to Customer. QC/Shipping goes down Packing Slip and Invoice created. I would be inclined to put the Location field near the Qty field in case you need to pull from multiple locations. You may only be entering the Part Number once, but you may have need for multiple locations, and it is the qty in each location that you wanting to track.

	Bob Hansen Sesame Database Manager Professional Sensible Solutions Inc. Salem, NH 603-898-8223 Skype ID = sensiblesolutions
	WWW Skype/VoIP IP Logged

FlipGilbert Full Member Offline Running Ver 2.6.4 Posts: 236 Location: Sandy Eggo Joined: Mar 8^th, 2005	Re: xpost question Reply #12 - Apr 5^th, 2005 at 10:07pm	Print Post
	wow, that makes mine look easy. I buy parts put them on different trucks and the different trucks sell them. No Im not an ice cream man but similar in idea. I was thinking, after I enter the part # (brp_1) it would pull up a userslect menu to chose from which location to adjust from. The userselection would retrieve the (key field) and place it in an invisible LE field (loc1). From the invisible LE I can do an xpost. Kind of like this If brp_1 <> "" then Loc1 = @Popupmenu (@xlookupall("Data\newdispatch.db", brp_1, "location!brp_number", "key field"), "loc1") But this does not work. am I still thinking Q&A flat file?

	It's not what a man says that matters or how he says it, but what he does and how he does it.
	IP Logged

FlipGilbert Full Member Offline Running Ver 2.6.4 Posts: 236 Location: Sandy Eggo Joined: Mar 8^th, 2005	Re: xpost question Reply #13 - Apr 6^th, 2005 at 3:25am	Print Post
	ok, I see where I was way off on that last code. but i still find myself looking for two key fields to do the lookup. Definitely still in Q&A mode.

	It's not what a man says that matters or how he says it, but what he does and how he does it.
	IP Logged

FlipGilbert Full Member Offline Running Ver 2.6.4 Posts: 236 Location: Sandy Eggo Joined: Mar 8^th, 2005	Re: xpost question Reply #14 - Apr 15^th, 2005 at 7:51pm	Print Post
	Ok, I have hit the wall. I cannot figure out how to do this inventory control with same part number different locations. Does anyone have the time to explain this to me?

	It's not what a man says that matters or how he says it, but what he does and how he does it.
	IP Logged

Page Index Toggle

Pages: [1] 2

Send Topic

Print

‹ Previous Topic | Next Topic ›

« Board Index ‹ Board Top

Top

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2025. All Rights Reserved.

Valid XHTML

Valid CSS

Powered by Perl

Source Forge