Classic Novice mistake regarding DB building & reltionships (I think) & can't fix problem

[Gloria]

As a novice I spent hours building a DB for a convenience store, in my zeal to see if it worked I entered over 1200 Product ID's, it did work initially but my hopes were quickly dashed when I realized it was too cumbersome to search through over 1200 items when I was creating a purchase invoice.

The purpose of the DB is too make the year end inventory process less of a nightmare, I wanted to enter purchase invoices inorder to quickly look up wholesale costs at year end so that I could valuate the year end inventory on hand.

I often purchase the same product from several different wholesalers (depending on the cost), some of these wholesalers provide their own product SKU's but some of the cash n carry wholesalers do NOT list their SKU's on the sales slip. Here is my problem, I want to be able to enter either the wholesler SKU or my Product ID & have both these numbers relate back to the Product Description, which I want to appear in the Invoice.

I don't know if I need another Table for Wholesale Products & have it relate to Products, or have more fields in my Product table such as Primary Wholesaler, Primary Wholesale ID, Secondary Wholesaler, Secondary Wholesale ID etc. I have tried so many ways without success & I am now too confused & frustrated, the simplest tasks are becoming difficult. Can anyone shed some light on what I am doing wrong, I'd be soooo grateful. :bang:

DR_Sample.zip

[Anuviel]

There are some tables missing in your file. Also I noticed that you related Product description to Product Id in one of the tables. Try changing that to Product id = product id.

Since the product description will not match product id the relationship will not work.

[Anuviel]

Also, it might be a good idea for your ID fields to be of the same type, IE all text or numbers.

[Gloria]

Thankyou for the input, I've made the changes you suggested, in trying to duplicate the original file I had overlooked these things. Unfortuneatly I'm no closer to a solution

DR_Sample.zip

[Søren Dyhr]

Could an ItemID come from several suppliers, I would give them individual ID's and then get the supplyer info along the liness of relation. My guess is that you have overcomplicated your relational graph!

--sd

[comment]

I can't find my way around your file (could be my limitation, not necessarily anything wrong with the file itself).

To answer your question:

I want to be able to enter either the wholesler SKU or my Product ID & have both these numbers relate back to the Product Description

Define a calculation field in ... well, in the table where both the SKU and the Product ID live... as =

SKU &¶& ProductID

The result must be Text. Define your relationship from... from wherever you want to enter either SKU or ProductID... to match the new calculation field.

---

EDIT:

Beware of false matches - make sure your ProductID cannot accidentally match another product's SKU and vice versa.

Edited July 7, 2007 by Guest

[Gloria]

I believe you are correct in regards to the over complicated graph which is why I am haveing such a problem. I can't seem to uncomplicate it in my head.

[Gloria]

Define a calculation field in ... well, in the table where both the SKU and the Product ID live... as =

SKU &¶& ProductID

The result must be Text. Define your relationship from... from wherever you want to enter either SKU or ProductID... to match the new calculation field.

I truly am a NOVICE so please bear with me while I sort this out.

"Define a calculation field in..." Are you saying it shouldn't matter if the the Product ID and the wholesaler SKU are in the same table? Could I put all the Wholesale product information in the same table as my Product info. I believe this is part of my confusion, in building the relational DB I was thinking of wholesale products as a separate table.

Will the function you provided return the product description?, I don't know what this function means.

[bcooney]

Perhaps my experience creating an inventory system will help unravel some of the structure that you may need.

My customer also purchases products from several vendors (wholesalers). I have a table related to my products table, "VendorProducts," and in this table i store the ProductID, VendorID, Vendor SKU, Vendor cost (actually I have three level costs, ie Cost1/Qty 1, Cost2/Qty 2, Cost3/Qty3 to reflect volume pricing).

On a PO, the user selects a vendor (popup window in list view of vendors). Then they add a part to the line items portal (another popup window). Since I know the vendor, when they select a part and enter a qty, I relate to the VendorProducts table for the correct cost.

Receipts are entered after specifying a PO. Then you can receive only the parts that were on the PO. This helps narrow down the parts that you are choosing from to receive. Here is where you may wish to use the Vendor's SKU or your part number relationship. However, since you know what was on the PO, perhaps this doesn't become such a difficult task to identify the part you are receiving?

One thing that we've learned is that sometimes wholesalers use different manufacturers for the "same part," and so it is not the same part in our system.

How do you value your inventory? Sounds like a candidate for a weighted average cost system. That's what we do. Check with your accountant.

Another thing to be aware of is that FM will get very slow since such calcs as Qty on Order, Qty to Buy, Qty Sold are unstored calcs. You may consider writing a transaction-based system. For example, when you "approve" a PO (we have a separate button for this), the QTY on Order for each of the parts in the line items file, is increased via a script. Yes, FM can calculate that amount, but as I said it will get quite slow over time. I actually have both, the transaction-based numbers and the relational calculations. I use the relational calcs to check that the transactions are working properly.

[comment]

Are you saying it shouldn't matter if the the Product ID and the wholesaler SKU are in the same table?

It matters - I am assuming they are in the same table.

Will the function you provided return the product description?

No. The product description should come from the related record. Using the calculation field for the relationship will make the record related if either its SKU or the ProductID matches the entry on the other side.