3 Replies Latest reply on Nov 30, 2011 4:07 PM by philmodjunk

    Blank related fields

    GreciaGarcia

      Title

      Blank related fields

      Post

      Hello! I am a first time use of FM, I am currently trying to make a database for our inventory and sales information. I have 90 tables in the database (each representing a store), I have already related them using the product's SKU number as the common denominator. The problem I have is that in some products some of the fields with the store and invetory data dissappear. I tried tweaking the relationships but so far it is not working. What should I do?

       

      Thank you!

        • 1. Re: Blank related fields
          philmodjunk

          I have 90 tables in the database (each representing a store)

          Yikes, not what I'd recommend here.

          Combined tables of data with a storeId to identify the store would be much more flexible. (IF they open a new store, you have to define a new table with relationships instead of just adding new records to existing tables.)

          What kind of data have you entered in these 90 tables? Items available for sale at each store or something else?

          given the reference to inventory data, I think you would be better off with this structure:

          Stores::StoreID----<StoreInventory::StoreID
                                       StoreInventory::ProductID>--------Products::ProductID

          Either a portal to StoreInventory on a Stores layout or a list/table layout based on StoreInventory can be used to list all Products offered for sale at a given store.

          Let me know if this works for you. If so, I can share a demo file that may give you some ideas on how to work with such a many to many relationship between stores and products.

          • 2. Re: Blank related fields
            GreciaGarcia

            Thank you so much! I'll make sure to try this solution.

            Each table has the following columns (in CAPS) the SKU number, the MODEL, INVENTORY, STATUS and sales for JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER and DECEMBER. I get a regular report of the store month by month that goes into an excel file, that excel file is the one that turns into a table in FM through a recurring import. I am aware this is complicated but like I said I am experimenting (I do not have a computer degree so this is pretty hard for me).

            Ok, so from what I understand is that I should merge the tables? I'm guessing this way could be simpler, it would mean that there would be a lot of repeated SKU's would it not? I do have a Store ID for each, so maybe this way could work better. I'll let you know when its done.

            Thanks again!

            • 3. Re: Blank related fields
              philmodjunk

              it would mean that there would be a lot of repeated SKU's would it not?

              no repeated SKUs, they would be stored in the products table. The StoreInventory table would link a given store to a given product in the products table. And the SKU, while unique may not be the best choice for linking Product records to other tables. SKUs are often externally generated values that contain embedded info that a knowledgeable person can decode in order to learn more about the product. While this has its uses, this leaves the format and content of a SKU subject to possible revision and you want to use identifiers (called primary keys) that aren't subject to change. I suggest using an internally generated serial number in the products table for your relationship links to other tables. By all means define a field for the SKU in products so you can use it to find and sort products records, just don't use it in relationships.

              You might also want to consider using one record for each month's sales totals for each product for that store. It can be more work to set up a layout with your columns that way, but can make other reports much easier to set up.

              Here's the demo I mentioned: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html