FIFO means "first in first out". But such a database project could take many different directions depending on the needs of the business.
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
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.
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
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?
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)
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...
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
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:
- The match field auto-enters a serial number.
- 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.