I'm trying to link two DB files for Inventory

[Rob_Jones]

Okay, I will try to explain what I need to do. I know it can be done, but I can't figure out how to make this work.

I manage a company that sells children's books to schools (not textbooks). We have a fairly large warehouse and keeping track of all that inventory is a nightmare. I created a FileMaker database that holds all the inventory with descriptions and such. My main concern is tracking quantities so that we don't run out of anything. That part is working fine.

This is where it gets confusing: Our best selling products are sets of books. Basically, we take a dozen or so individual books with similar themes and group them into a set. We don't actually stock pre-assembled sets, but rather pull books from the shelves and "build" the sets as they are packed for shipping. What I need to know from the database (assuming an accurate inventory count), is if I have enough enough individual books to build the sets required to fill all my open orders.

In other words, if I create "Set-A" in the database, I need to be able to specify what books are in that set. Then, on the same screen, be able to see at a glance how many of each book I have in stock. That way, if I have a large order to fill, I can see quickly if I need to replenish anything.

I think I've explained all this fairly clearly, but if not, don't hesitate to ask for more clarification.

[Ugo DI LUCA]

Hi Rob_Jones,

More than an inventory count problem here.

My first thought would be to hold 2 "Product db" :

1 that would be your Catalog (Catalog_Id is the key field)

1 that would be your Operational Product File (Product_Id is the key field and a Multi-key field for relationship to Catalog_Id)

This latter file would hold either :

- all individual products

- all your "packages"

All entries in the Line Item (and lookups) should be based on the Product_Ids, but the Inventory should be calculated on the Catalog_Ids.

This could be more complicated if you had to deal with quantities in your packages definition (2 booksA, 1 bookC, 4 bookD,...).

[Fitch]

It seems to me that you can only build as many sets as the lowest inventory level of any given book in the set. I.e., if you have 3 copies of "Fellowship of the Ring" and 10 copies of "The Two Towers, " the number of "Lord of the Rings" sets you can build is: 3.

So, if you have a file called Sets, with a many-to-many relation to Books, you'd need a "join" file we'll call SetBooks. This file would contain at least the Set ID, the Book ID, and the Book Inventory Qty. Probably the latter field should be a calculation that pulls its info from the related Book record so it will be up-to-date.

Now the real answer to your question. Given the structure I've described, create a calc field in the Sets file:

Min (SetBooks:: Book Inventory Qty)

That's the minimum number available, based on a relationship from Sets to SetBooks.

I think I've explained all this fairly clearly, but if not, don't hesitate to ask for more clarification.

[Ugo DI LUCA]

Hi Tom and others,

I'll jump on that one.

I answered to Rob Jones cause I could have similar needs in the future, but I hadn't thought a lot of the subject before.

Sorry Rob if I "stole" your post....

For the moment, my inventory count is stored in the Product db using calcs in the Line Items.

I have a product db, and in some occasions (essentially Sale period), I sell "lots/package" of products that haven't been sold or that are known to go out of production.

As I said, as a first thought, I would create a ProductToSet join file, then buid a Set db (with a Set_Id) and use this new Id as the key in the Line Item for all operations :

A set could consist of :

- a unique individual product (to convert any individual Product in a new Set_Id)

- several individual products with equivalent quantities (1 of each, 2 of each,...)

- a combination of different poducts (1 of A, 3 of B, 5 of C)

- a combination of the same product (this is very specific to my business, so let it apart at a start).

So far so good, I can use it in my orders and invoices with no problem. But how can I keep track of the inventory using this method ?

BTW Tom, I see you with very accurate and efficient answers. How can you have only one rate ?!!!

[danjacoby]

Fitch -- what happened to "The Return of the King"? You can't build sets of a trilogy without all three books.

I just love finding irrelevant details.

[Ugo DI LUCA]

Never mind, I had a sudden illumination...

For those interrested :

One Parent Product db

One join file for Product to Sets

One Set/Package db

The Classics Line Items for PO/Invoice...

Entries for packages are made from a portal to the Join file in Parent Product db (using the "Product_ID" and "quantity")

The Set/Package db holds 1 quantity field and 2 calcs :

- "Quantity" (in set ---> entered through the portal)

- the "Parent quantity" = ::Product : "Count Individual sales"

- "Quantity Set sold" = "Quantity" * "Parent quantity"

Then in the Product db :

Count Individual sales = Sum(LineItem::"Quantity sold")

Count sales on lots/sets = Sum(Set/Package::"Quantity Set sold")

But I agree with Dan, Fitch...

[Fitch]

Ugo, you could probably "pipeline" the inventory info from your Product db, this actually came up earlier today in this thread.

As to why I have "only one rate," I'm confused -- what I see to the left of my posts is:

Fitch

Enthusiast (shouldn't that be "Addict?")

***** (5 stars)

Are you saying you only see 1 star? I don't get it. It's no big deal -- sure, I'm a top-20 poster, but I get no respect, I tell ya! However, I'm curious why we'd see different things, and also, it seems to me I used to be able to see how many people have rated me, but for the life of me I can't find that feature anymore.

Dan: Three books you say? Fascinating!

[Fitch]

Looks like our messages crossed...

[Ugo DI LUCA]

Fitch,

I had seen this thread.

About your unique vote, just put your mouse over the stars for 1 or 2 seconds and you will see how many votes you have.

I must confess you impressed me on this thread.

thread

But YOU DESERVE MORE !

My kids really love your smooth pink fly, when they look to FM Forums

[Rob_Jones]

Thanks for all replies! I'm still struggling with this thing. I'm very new to FimeMaker, so much of the terminology is greek to me.

Your answers make sense, but to implement them, I still need to learn more aboout the program [FileMaker]. It's like I want to change the station on my new stereo. I know what station I want it on, I have the instructions that explain the process of changing the station (turn the knob), but they don't mention where the knob is. As soon as I can find the knob, I should be able to do what I want.

I'll keep checking in here and trying things until I get it working.

By the way, is there a way to have the forum send me an email when there is a new reply to this thread? I hadn't received any notices and assumed there weren't any replies. I just happened to check it and saw that there in fact were.

Thanks!!

[Fitch]

Thanks for the compliment, Ugo. Hmmm... I'm using Safari on a Mac, and nothing happens when I mouse over the stars. Ah... using Internet Explorer I see the tooltips. OK. But how many stars do you see? I see 5 for me. How many votes, I can't see. Hey, if you think I deserve more, feel free to rate me!

But 1 vote's enough, if it's 5 stars -- which is why I think it's kind of a dumb system. Better than nothing, I guess. Look at danjacoby -- over 1100 posts and how many votes?

As for my "smooth pink fly," LOL, that's a flying PIG -- I thought I had snagged it from <www.randi.org>, but I don't see it there now, so I could be wrong.

[Fitch]

Rob, thanks for getting us back on topic... if you could be more specific about what you don't understand, we can go from there.

[Ugo DI LUCA]

Tom,

Hey, if you think I deserve more, feel free to rate me!

Don't you undertand I cannot rate you anymore, my love.

Ask others now, or see thread as I'm sure Stephen has already an answer.

[Ugo DI LUCA]

Rob,

I'll try to upload a little attachment on what I am (will) doing. May be it could help you start for what you want to do.

[Rob_Jones]

Guys,

Thanks for the informative answers. I know I can get this working! The only problem is that a lot of your advice is going right over my head.

I understand the concept of relationships and how they work, I'm just having trouble making the tools work they way I expect them to.

I'll try to give more details:

I have a DB with two layouts. One is for what I'm calling "Single Items". This is a DB of each individual item in our catalog (go to www.innovative-educators.com to see the kind of stuff I'm talking about). By the way, the web site is "driven" by an MS Access database that was developed by somebody else (a contractor). I've been able to convert that DB into FM to build my "Single Items" DB.

The second layout is called "Set Listing". This is where I'd like to be able to display the catalog number of a particular set and have a field for each of the individual titles in the set, a field for the quantity we have on hand, the price, how many are on order and how many we need to order. All of this data is available in the "Single Items" DB.

How do I get it to show up in the "Set Listing"?

[Ugo DI LUCA]

Hi Rob,

Not really easy to make an answer here without confusing you some more...

Hmm. I'll have some time on week-end for the attachment I promised.

In the meanwhile :

- Both set and book_Id will share a catalog_Id in the Catalog file.

- Records from set would be shown in a portal, not in separate/repetition fields.

- You cannot decide on a layout before you decide upon relationships.

If you were scrowling Layout 1, you will surely end wiyh a empty layout (or empty field in layout) if the record you are on is a set. IMHO, this is not a good design.

All the fields you are talking about would be calcs field. Not sure (refresh/update ?) but I will check if these calcs could be stored in the portal .

Ciao.

[Rob_Jones]

Okay, this is kicking my butt.

I've got the relationship working, but there is a new problem.

Let's say that a set has eight books. If I enter the catalog number for the first book, it fills all the fields with its data. I can't enter another title.

I made a copy of the DB files and if anybody would like to take a shot at fixing it, let me know and I'll send them to you.

[Rob_Jones]

I may be on to something...

My head hurts from looking at this all day, BUT I may have hit upon a solution!

This is what I did:

I defined a field "Item 1" for the first item in a set, "Item 2" for the next and so on. Then I created a relationship between "Item X" and the Catalog number field. Then I placed a field next to the "Item X" field that is related to the "Quantity" field. That works.

I made a set with 8 items with a line for each item in the set. So far, it seems to be working...

Thanks for all of your help!!!

[Ugo DI LUCA]

Hi Rob,

Nope.

Looking to your site was hepful to understand what you are looking for.

Here are some steps, and I'm free to look at your files.

List all your books in the Catalog File with your current fields

Category (Hardcover, Big Books, Infant, Board Books)

Your Book_ID (HB 820, HB 821, HB 864, B499,...)

Your Book Theme (Colors, Alphabet, Shapes,...)

Your Book Price ( 6,99 $, 15,95 $, ...)

Your Book designation (Blue, Green, Circles, Ovals, ABC Kids,...)

Then create a CatalogToSet File with these fields :

Book_ID

Parent Book_ID ((That will be your new Catalog_ID for the Set in the CATALOG File)

Parent Book Designation

Quantity to Set

Book_Designation

In the Catalog File, create a relationship Catalog:Catalog_ID::CatalogToSet:ParentBook_ID.

Set this relationship with allow creation of related records

Draw a portal, and place in it these fields from CatalogToSet :

- Book_ID

- Quantity

- Book_Designation

Format the field Book_ID to use all values "Catalog_Id" from Catalog file.

Back to CatalogToSet, create 2 relationships to the Catalog File :

- Parent = CatalogToSet:BookParent_ID::Catalog:Catalog_ID

- Catalog = CatalogToSet:Book_ID::Catalog:Catalog_ID

Then redefine these fields to be related calcs :

- Parent Book Designation = ::Parent:Designation

- Book_Designation = ::Catalog:Designation

Go back to the Catalog file and start entering your sets :

HS 158A+ Books, Colors and then create 6 related records using the portal. You will pick up from the list in the Book_ID the Id's fro Blue, Green, Orange, Purple,...

Once you are started with this, it will be easier to understand the process and for us to help in the Counts of Inventory sold...

Don't hesitate to ask for help on Private if you wish.

[Fitch]

Rob - each Set can have many books. That's called a one-to-many relationship. One set::many books. Even when you have many sets, it's still a one-to-many, as long as each book can belong to only one set. A common example is company::contacts (one company, many contacts).

But what happens when a book can belong to more than one set? Then you have a many-to-many relationship. It's generally impossible to implement this type of system with just two files. What's required is a 3rd file, often referred to as the "join" file, that stores the I.D./Serial no./whatever of both the parent record (from Sets) and the child record (from Books). The classic example of this is an Invoices file relating to a Products file. The join file is LineItems, which looks up its price, qty etc. from the products file. This allows you to have many invoices with many products. HTH.

[Ugo DI LUCA]

Hi Fitch,

I partly agree with you on this one. I also started with these 3 files, but implementing the solution given, in that odd particular case, the join file would be used to enter records from the Catalog file to the Catalog file.

Then you will need only 2 files.

I strongly agree on the Invoice/Line Item system though.

[Ugo DI LUCA]

The test file is in the Sample section here :

Sample Forum

[Ugo DI LUCA]

Following the discussion that had temprarilly moved to the Sample forum...

sample section

Basically, this is how it works right now:

MYOB is used for all bookkeeping, invoicing and purchasing. When a new customer order is placed in MYOB, a packing slip goes out to the warehouse. from there the order is pulled and the packing slip goes back to bookkeeping once the order ships. Then it's changed to an invoice and sent to the customer. If a large order comes in, we have to go out to the warehouse and actually count everything to see if we can fill the order. If we need anything (we usually do), a replenishment order is placed with our vendors using MYOB.

As you can see, there is room for improvement. As it is right now, Purchasing, Invoicing and Inventory are not linked. It can be a real headache if several large customer orders come in at once. It gets worse if we know certain items are on order and have to be accounted for when counting inventory.

This is the point of the database. Somebody will still have to keep all the counts in the DB current, but it will be much easier to manage than our current "system".

I bought some handheld scanners for the warehouse staff to use when packing or unpacking inventory. But until the DB is in use, they aren't much use. The data can be downloaded from the scanners into the DB at the end of each day. This will give me a fairly accurate picture of what's going on in the warehouse. Of course, as replenishment orders arrive, they will be scanned as well.

There is still room fro improvement

One big one would be to jump to relational design using FM as a central solution.

For the tricky part of counting inventory, there is IMHO no inventory absolutely accurante to the 100%. Depending on the business structure, you would better use a "flag" to anticipate any order (but this is more business relevant that FM).

That is why in the sample, you'll find a related calculation for the minimum quantity necessary. You could modify this calc using a given "multiplier" for a new calculation (do you follow me ?).

Else, in the example given, the inventory counts on purchase order. This was for simplicity.

In my system (that isn't actually exactly the same), inventory is tracked both on purchase order and on "shipments (goods received). One is the forecast with a date forecast, the other is the real inventory.

I'm quite sure FM could help you in that there would be a "instant" inventory count and "alerts" independantly of the number of orders. But I'm not sure on how I would personaly implement the handheld scanners you are using. Instead of synchronizing the datas, I'll prefer to have a computer in the Warehouse and a "Warehouse entries file".

As the orders come, the staff would compare the quantities ordered (Order N

[Ugo DI LUCA]

Hi,

An additional thought.

I bought a Palm and FileMaker Mobile one year ago without really knowing how I could really use it...in a relational design

What could be related to the current post is that I have in my "Project Basket" a file called "Arrivals checking". I've tested it once and seemed to work fine. I've just looked at it again, and I think it could be of interest for you to know what I planned this file for.

Surely my business is smaller than yours, but I also have some improvals on how the entries (shipments from suppliers) are checked and how the db is updated.

A little detail is necessary...

My business is a Wholesale/retail.

We buy directly from Foreign Manufacturers (Italy, Spain, Portugal, Germany,...) and from some other wholesalers in France and Belgium.

The goods aren't directly shipped to us, we work either with Int'l Transporters, local transport services that we personnaly hire, or our own trucks. So we also have to deal with Transport Orders.

When a Purchase Order is created and sent, we get a confirmation from our suppliers, of which items are availables and when.

We send back to them a "Preparation Order" and specify the date when we will get there to carry the goods.

All Preparation Orders are related to the Transport Order database, and dispatched to the different Transporters, based on the dates of "shipment" required.

I'll ask Transporter A to go in Italy to the warehouse of Manufacturer 1, 3, 5, 7 and ship me the Prep Order 501A, 402B, 306F,...Transporter B will go to Spain to Manufacturer 7 and 4 and carry the Prep Order 402C, 501C,...

When the Transport Order is created and sent to the Transporter, the whole datas holded on the Line Item for that Transport Order is imported/exported to the File "Arrivals checking".

This file is a FLAT File with no relationship (maybe just a constant 1 to 1 to the Operational database. It only holds some fields common to the Line Item.

Obviously, these records are stored in the Palm device with a daily HotSynch,

When goods arrives to our warehouse, the field "quantity received", next to the field "quantity ordered" in the Palm "Arrivals checking" File is filled. A Hotsynch to the Warehouse Mac is performed to the "Arrivals checking" file, flagging all records controlled. And a button set an "alert" to the Main db that some records need to be updated.

What could be then performed, with no problem IMHO if the db is used or not, is a script "Import from Arrivals" that would :

- update the Main db based on the records from the "Arrival Checking"

- reset the whole Flat file with only those records not flagged.

[Rob_Jones]

Guys (especially dilucaugo68), I have not forgotten about this project. I've been distracted the last few days by other unrelated problems.

Give me a little while to catch up on the posts and I'll update you on my progress.

[Rob_Jones]

I did some poking around in MYOB and I found a neat feature. It can export nearly any record as a delimited text file. SO, I can export each day's sales and bring them into FM relatively easily. This method is backwards from my original plan (import data from FM into MYOB), but it is a means to the same end - Accurate and up-to-date information on inventory levels that's easy to maintain.

I'm still working with the Book Set relationships, but it feels good to have at least one problem solved!