orderform into inventory

[rlo]

hey guys,

I have a relationship question. How do you separate quantity into records. IE: I order 10 computers and want to be able to put them into inventory each with there own serial numbers.

I use orderform to order the computers and input the serial numbers in there, which i want to be able to have added to the inventory as soon as you put the serial number in.

let me know if this isn't clear enough i'm not sure how to even describe what i want to do.

thanks a bunch!

[bcooney]

One way is to have a Receipts table, and have the receipt line items be inventory records.

[rlo]

Receipts table?i'm betting it's very helpful, my issue is i'm not sure i know how or what that is.

if you could explain that would be greatly appreciated.

thanks for the reply

[bcooney]

Well, an inventory system is quite a complex system for someone with no experience to try to build.

Have you looked at examples?

Look here:

FM Starting Point

From FMs website

[comment]

The basic idea is that when you get the computers you enter them directly into your inventory, instead of into the order. If necessary, you can relate the inventory records back to their order. In fact, you could place a portal into Inventory on a layout of Orders and enter the received merchandise from there. This way, your order is also the receipt and entering the data once solves all three issues at once.

[rlo]

a few, i haven't found anything that fits what i am trying to do. My basic problem is that i work for an audio company that primarily builds systems to rent out.

So when we purchase say microphones, we may purchase 10 microphones and 3 of them go into the PA system while 2 go into stock to sell and another 5 go into a microphone rental package.

I have things setup so once they get into the inventory you can create packages (PA system) but I need a way of separating them once we purchase them and automatically get put in the inventory table.

does this help?

[bcooney]

You might benefit from reading this article by Jonathan Stark: Recursive Data Structures

I'd also search this forum for any thread about Inventory. Soren and I have many posts regarding inventory systems.

[comment]

Can you explain what is recursive about this, or inventory in general?

[bcooney]

I don't know enough about his business to presume it warrants a recursive structure. I simply find Jonathan's article worth reading for anyone attempting an Inventory system.

[comment]

I am afraid I don't follow. Suppose I run a supermarket: in what way would a recursive data model benefit me in my inventory system?

[bcooney]

I don't see the need for a recursive structure in a supermarket. However, he hinted at components:

"So when we purchase say microphones, we may purchase 10 microphones and 3 of them go into the PA system while 2 go into stock to sell and another 5 go into a microphone rental package."

..and imho that's when it's appropriate to look at a recursive structure.

I'm up for an exchange of data modeling ideas for this. If you are, let's go.

[comment]

Oh, I see. I took it to mean they are simply assigned to a location (rental kit or stock) in the Inventory table.

In any case, the recursive part would be needed only if one can rent the SAME microphone either as a stand-alone or within a package.

[bcooney]

Got it. Like the location idea. So, how would you structure rental kits, so they show as "On Rental" in their component inventory records? Join table btw RentalKits and Inventory?

[comment]

I am not up to this. Let me just say that I still haven't seen a really good solution for the kit/component problem.

[bcooney]

OK, in our free time, lol.

[Søren Dyhr]

the recursive part would be needed only if one can rent the SAME microphone either as a stand-alone or within a package.

Well I come from a PA rental environment, and the idealistic thought of keeping a component to a fixed set is more an academic thought than one belonging to the real world, where resources usually are restraint in quantities. What I have expirienced is even though we tried to have one whole in a truck, would we anyway have that the trucks needed to meet and exchange equipement, to handle a "special" task.

If dealing with sound equipement is it easy to understand why this would happen, not all venues have the same size, and they change sizes in 3 dimentions unfortunately - when a ballroom is just a tiny tad longer and wider it suddenly can take twice the crowd!

Even pretty well equipped companies need to let some items flow from fixed package to another.

The recursive approach comes in here by letting the system calculate which system or set at all can be managed when considering what is booked already.

Where i not yet, have come to in my systems though, is to make track of what kind of tasks are turned down because previously made bookings are blocking. The problem is:

http://sethgodin.typepad.com/the_dip/2007/04/the_real_8020_r.html

...which means that a lot of smaller bookings are preventing the landing of larger and often more profitable customers.

Here would it be neat to calculate a "critical mass" for each component, instead of turning down clients based on hunches and guesses to whats profitable as well as what really is required to make the business run as smooth as possible.

--sd

[bcooney]

Soren, I designed an inventory system for a small boat manufacturing company here on Long Island. I wrestled with kits/parts for weeks. As with PA systems, it seemed logical to "commit" parts to a boat kit so that they were no longer "Available." However, observing the real world, I often saw the rigging guys take parts off of another boat in production (whoever's account is closer to paid gets the steering wheel that takes 3 wks to get in).

Even worse, the owner would often give his friend a battery off of a boat in production. Just give it to him, never telling the system anything! Then, we'd all wonder where the battery went.

Then, I attempted to convert the collection of parts into a boat. However, because of their consist cannibalizing of parts off a "boat," I realized it was unrealistic to create "finished goods" inventory.

I was so frustrated. Yes, the real world plays havoc with inventory systems. Thanks for listening to my rant, and now you know why inventory systems push a button for me, lol.

[Søren Dyhr]

the owner would often give his friend a battery off of a boat in production. Just give it to him, never telling the system anything!

Oh yes been there, seen that... etc. They are even the first to raise the fingers if employees jeopardises data consistency!

--sd

[bcooney]

You know it. He still can't understand why the inventory levels aren't accurate. I gave up after drafting a suggested process flow for tracking the flow of parts.

[Søren Dyhr]

But back to the thread, one way of having an orderform is to build it this way:

http://www.fmforums.com/forum/showpost.php?post/149069/

...in order to create individual records for the pulls in the inventory, perhaps if it could be combined with this:

http://www.dwdataconcepts.com/dl/tw/compinv2.ZIP

What could be accomplished is then to have a sheet over the inventory, where numbers then can be added to each component. The reason Don's template could find it's way into the solution as well, is that it makes room for selecting among sets as well, and sort of solve the recursive structure issues discussed earlier.

What we did in our rental system was to use traffic light behind the numbers for possible - green, close call where nothing after the booking is left in the warehouse - yellow and finally red for overbooked. The reasoning behind this was to say yes to everything, and then sub-rent from other facilitators in the marked.

But by and large is it the opposite of looking at the stored and then assinging it accordingly ... warnings are first given when a number is attempted via the coloring. However should the free stored concept be studied as well:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

--sd

Edited January 18, 2009 by Guest
wrong linkin in the first

[Dave Graham]

I'm coming a bit late to this thread, but I'd also recommend reading a Len Silverston's Data Model Resource Books, both volumes 1 & 2. Vol 2 specifically talks about product bills of materials, substitutions and inventory. It's been my experience that you would benefit from marketing BOMs (e.g., pre-built BOMs for the purposes of selling), product BOMs (e.g., customized BOMs used to assemble/manufacture the finished good), and possibly even service BOMs which can track actual components which have been replaced over a period of time.

The process of managing raw material to finished good inventory is never easy, but if the client isn't going to enforce a structure on how orders are fulfilled then inventory is going to be a crapshoot at best. In that event you'd better also have a good manual inventory override process to keep current levels approaching reality.

I use a scripted approach to adding BOMs to an order, which usually includes an option to show/hide and edit BOM items. It's not a recursive structure, just a parent/child structure at the order line items level. I use Todd Geist's transactional approach to posting any type of order which can affect inventory.

[bcooney]

Thanks, David, sounds like the book I needed 5 yrs ago! Yes, the client refused to part manage (slows down production, lol). A crapshoot just about describes it.

[Søren Dyhr]

sounds like the book I needed 5 yrs ago!

But claiming to be unaware of it, is a bit far fetched:

http://fmforums.com/forum/showpost.php?post/250485/

It's just being postponed, isn't it? While the best quote in the quoted thread is this:

or one may engage in some introspection to determine if their knowledge might indeed be deficient.

I really hope to be abel to find the money to attend one of those Bootcamps:

http://filemakerbootcamp.com/

--sd

[Søren Dyhr]

I use a scripted approach to adding BOMs to an order, which usually includes an option to show/hide and edit BOM items. It's not a recursive structure, just a parent/child structure at the order line items level.

It's what I meant by linking to Don's CompileInvoice template, although it doesn't use Todd's transaction model, here's BTW an example of a genuine recursive:

http://www.spf-15.com/fmExamples/BilloM.fp7.zip

Where it calculates the price, instead of availiability which might be the crucial issue in this thread.

--sd

[rlo]

hey guys, wow thanks for all the info it will take me a little while to work my way through it and find what will really work. Soren you are right on what i need to do. Although we have designed systems we always steel from one in order to make a job happen, we have 40 microphones in each system but one job may have only 1 speaker while another could be a whole blown band and choir so we would steel microphones from the system that only has 1 speaker in order to do perform the job of the full band.

So yes i need to know full inventory count at all times. That is why i need each item to be in the inventory but then also have it within a system.

One thing I was thinking was a script trigger that sees when 6 microphones are ordered it runs a script that creates 6 records in inventory. That will at least get the records started, then it's a matter of assigning these products to a system. But what do i do for products that are intended for resale? as we also sell video cables and other small things.

once again thanks for all the input this is really helpful!

[Søren Dyhr]

One thing I was thinking was a script trigger that sees when 6 microphones are ordered it runs a script that creates 6 records in inventory.

This is strictly speaking not really nessersary!

The template attached show only the booking, while subsummaries are required to make packing lists and invoices. The template only deals with one at present, but is easily changed to deal with ranges of dates instead! One requirement though is conditional formatting only availiable with newer version of filemaker!

Now the template uses Sum( - But it will in some circumstances give wrong results. Each booking should behind the scenes be assinged via a lookup a set of individual ItemID's so another aggregate function List( can grap the assinged ID's for the datespan. The issue is if say 8 of type comes back and the same 8 leaves in the datespan then new booking is attempted, will they count as 16 pulled, so the calculation of the level is instead established via:

FilterValues(ValueListItems( ... ) ; List(theLookupedID's))

This way will the same item only be counted as one... The lookup does as well prioritise the early booked and behalf of the late!

--sd

GearBooking.zip

Edited January 21, 2009 by Guest
Made a template

[Riley Casey]

Hello Soren

I see that you are in the thick of things again.

which means that a lot of smaller bookings are preventing the landing of larger and often more profitable customers

Also good to see that you have such a solid grasp of the real issues at hand. ;-)

I'm just now poking your sample template with a stick to see how it moves. What level of Filemaker is required to achieve the conditional formatting you refer to ?

The template only deals with one at present, but is easily changed to deal with ranges of dates instead! One requirement though is conditional formatting only availiable with newer version of filemaker!

[Søren Dyhr]

Hi Riley

The conditional formatting arrived with fm9 i believe, but they are indeed faster than the mergefield trickery we used to have underneath each line...

Sorry for getting back to this a bit late - I've however been too occupied lately and the template above is unfortunately somewhat buggy, contact me off forum with your present e-mail address and lets talk about an update I'm working on! Mine's available in my profile ... others are obviously welcome to do the same, if they should wish.

I find it a bit easier to keep track with e-mail and iChat communications in my present workload.

--sd