11 Replies Latest reply on Nov 11, 2013 8:30 AM by philmodjunk

    FIFO tracking shipment

    BobWhallz

      Title

      FIFO tracking shipment

      Post

           I would like to create a small FIFO database and I have no idea on how to start. Has anyone ever build one and can help me with ideas. I would like to track items stored at the warehouse so that the first item checked in should be the first to ship. if another item is selected before the first, an error message is displayed.

           Thank you. Please understand, I am new using filemaker, two months now learning.

            

        • 1. Re: FIFO tracking shipment
          philmodjunk

               FIFO means "first in first out". But such a database project could take many different directions depending on the needs of the business.

          • 2. Re: FIFO tracking shipment
            BobWhallz

                 I only wanted to check in the items and check them out. like having a list of all checked in items with their dates and times. now when they are checked out, they can only be checked out in the order they were checked in. I have seen many project with Java, SQL but not Filemaker

            • 3. Re: FIFO tracking shipment
              philmodjunk

                   Then you would appear to need table with fields to identify the object, its description and fields to log the date at which it was received and the date shipped. A field in that table could identify its location or you might need a related table of locations in some circumstances.

                   You can then perform a find for a specific type of object and sort the records to list the first object of that type to be received first as the object to be shipped out.

              • 4. Re: FIFO tracking shipment
                BobWhallz

                     I create a table Warehouse Sorting (_WarehouseIDpk,DateCheckedIN,DateCheckedout,Location). This table is linked to the ProductsLineITem(_WarehouseIdfk) and I have a ShipmentID filed in the Invoices tables that works like a tracking Number.

                     In my Warehouse Sorting table I created a portal to display the Item we would like to check in using the ShipmentID number.and I have a Check In Item buton with a script associated to it that points to the ShipmentID field . 

                     Now I can manually input the ShipmentID number after I press the Check In Item button  however the other fields do not populate except the DateCheckedIN and CheckedINby.

                     here is a screen shot

                        

                • 5. Re: FIFO tracking shipment
                  BobWhallz
                  /files/5925b65061/untitled.JPG 1024x742
                  • 6. Re: FIFO tracking shipment
                    philmodjunk

                         So you have this relationship?

                         Warehouse Sorting::_WarehouseIDpk = ProductsLineITem::_WarehouseIdfk

                         And your portal is to ProductsLineItem and your layout refers to Warehouse Sorting?

                         

                              and I have a ShipmentID filed in the Invoices tables that works like a tracking Number

                         But what relationship links Invoices to either of these other two tables?

                         And in what table are the fields that show empty defined?

                          

                    • 7. Re: FIFO tracking shipment
                      BobWhallz

                           Yes I do have this relationship Warehouse Sorting::_WarehouseIDpk = ProductsLineITem::_WarehouseIdfk. The Invoices Table is in relationship with the ProductsLineItem through the InvoicesIDfk. Warehouse Sorting is grandson to Invoices. The fields that are showing empty are from other related tables to the ProductLineItems (Invoices,Products and Customers tables)

                      • 8. Re: FIFO tracking shipment
                        philmodjunk

                             Hmmm, Your relationship graph does not match the text descriptions in your two posts.

                             There isn't any table occurrence named "ProductsLineItems" visible. I'd guess that you really meant ProductsInvoices?

                             The relationship between Warehouse Sorting and ProductsInvoices looks wrong on your relationship graph. It shows a one to one relationship between Warehouse Sorting and ProductsInvoices. That doesn't look right But I also don't really have a clear picture of what a record in Warehouse Sorting represents here.

                             There are several such one to one relationships (single lines at both ends, no crow's feet) that look like they may be wrong and suggest that some fk fields are either getting auto-entered values that they shouldn't or are constrained to unique values when they shouldn't be.

                             Your first post mentions a shipment ID. But there's no such field shown as a match field in any of your relationships. Thus assigning a shipment ID to a record in any of these tables does not link it to any other related records...

                        • 9. Re: FIFO tracking shipment
                          BobWhallz

                               Sorry for the trouble. I meant ProductsInvoices instead of ProductsLineItems. Why are my relationship showing a one to one relationship? Sorry I just feel like I have to start all over. I fixed all the fk, they were not properly set up. Still have one to one relationship

                          • 10. Re: FIFO tracking shipment
                            BobWhallz
                            /files/0cee4ede12/sorting.JPG 922x591
                            • 11. Re: FIFO tracking shipment
                              philmodjunk

                                   Your screen shot shows correct settings for a primary key field (PK). The issue lies with the foreign key fields (FK) that match to it in a relationship.

                                   A "single line connection" will appear in the relationship graph for one of two reasons:

                                     
                              1.           The match field auto-enters a serial number.
                              2.      
                              3.           The match field has a "unique values" validation field option specified.


                                   Neither should be the case for an FK field in a one to many relationship as it should get it's value from the parent records PK field (so no serial number should be auto-entered) and the values can't be constrained to unique values or you are limited to just one related record and thus get the one to one relationship predicted by the single line "connector" at each end of the relationship line.