5 Replies Latest reply on Jun 22, 2011 1:40 PM by mgores

    Layout With Relationships (Lookup?)

    JosahMower

      Title

      Layout With Relationships (Lookup?)

      Post

      I have my tables setup but having problems with the layout connecting to the tables. What I would like to happen is to create a layout with Items and Location. In both tables I have a Tag field. I want to enter a Tag number and add the rest of the information into the fields, but only enter tag number once. When the data is saved it puts the tag number in the field in both tables.

      I have been told that I need to create a button for location because you can not have more then one table in the layout. So if that is correct I would need to click the location button but would like the tag number then to be placed in the tag field in the Location layout table. How can I accomplish this. I have been trying for about 2 weeks but have had no success.

      ClearAsMud.png

        • 1. Re: Layout With Relationships (Lookup?)
          mgores

          You should be able to have field from both your items table and location table on one layout.  If you go to layout setup and select the show records from to items, and have fields for the item table to enter all that information and have the fields from the location table to enter that data (note: check the relationship between items and location and check the allow creations of records on the location side), then when you enter something in any of the fields from the location table a new record will be created in that table with the "Tag" number that you entered in the Items table.

          It would be better if Tag was an auto entered serial number, or if you used another field that was an autoentered serial number as the link between the tables.  Otherwise if someone entered information in one of the location fields before entering a Tag number, the Tag number would be blank in the location table and no longer related to the item once the tag number was entered.

          • 2. Re: Layout With Relationships (Lookup?)
            philmodjunk

            I'm not sure I can follow the logic behind the relationships you've sketched here.

            I would think __pkItemNumber would be the name of the primary key for the tblItems table rather than the primary key for tblLocation.

            It would seem that TblRequisitions is a table where each record is a potential purchase order where a portal to tblItems would list the items requisitioned. If so, you'd have this relationship rather than the one you show here:

            tblRequisitions::__pkRequisition = tblItems::_fkRequisition

            What does a record in the location table represent for each item on your purchase order? What is a tag number?

            • 3. Re: Layout With Relationships (Lookup?)
              JosahMower

              Requisitions are orders for purchase orders (PO). Requisition hold everything that is in tblRequisition except for PO, that is given after we are given the ok for the order, can be months down the road.

              PO's are given an exact dollar amount to purchase the goods in need and given to the vendor. The goods when received are tagged with the TAGs (field in tblItems) that are then linked to the PO. TAG's are the ASSET number for the companys equipment in a senceTAG's link to a PO so that we know what was purchaced with that PO's funds.

              The tags that we are given are not always in order. The Treasure department gives them out as goods come in for every department. I wish we could get a large group number of tags but its out of my hands.

              Since we tag the equipment with that tags already I was using that as the tracking number. I want to know the location and if it moves and everywhere is has been has well as the serial, part number, model details and if it is warrantied out what the serial now is and link it to the tag. Reason being the tag number will always exist as it is attached to the PO. And also when device is removed from inventory. I can then make reports and they can update their system. This will help out for the yearly inventory audits. We already have to get this information and send it to the treasure department but we can also have a copy and if we happen to not have sent it we can just run a report.

              So it goes Req to PO and tags are linked to a PO.

              I will add a  __pkItemNumber Primary Key for tblItems and change tblLocations Primary Key to __pkLocationLineNumber. But I dont know how to like them together as the keys for me to use. If I go to one of the building and say computer tag number is 12345 moved from room 1 to room 2, I would open the DB and search for 12345 and do a change of location, which creates a new line in tblLocation and enter the information. When I were to look up 12345 again when I look at current location it would have room 2 now there. It would find it from date.

              The location field will probably be enter last. We get the equipment in and then tag it, image, setup what every is needed first and then deliver it to the building and department. I was just trying to make a layout that I would just enter the information quickly. Also when I do the location change and say I am inside the Tag device's information, I would like a new window to popup and have the Tag number already in the Tag data field, can I do that and how would I if you can?

               

              • 4. Re: Layout With Relationships (Lookup?)
                philmodjunk

                Much of that is why I stated that TblRequisitions stores Potential Purchase Orders. Wink

                I'm still assuming that the records in TblItems are created when the requisition is created not when the items are received. If so, I'd still link them to TblRequisitions by the Requisition number instead of the PurchaseOrder which seems to come from an outside source.

                Nothing wrong with using the asset (tag) number for tracking, but since it is externally generated (not an auto-entered serial number in FileMaker), I'd try to avoid using it as the key in a relationship.

                It would seem that you have one record in TblLocation for each location where an item might be located. If so, define a number field in TblItems::_fkLocationNumber. Then relate your items and location tables like this:

                TblLocation::__PKLocationNumber = TblItems::_FKLocationNumber

                To assign an item to a location, format TblItems::_FKLocationNumber with a value list of location numbers and names from TblLocations.

                If you need to keep a history of each place where an item was located, use a join table linking Locations to Items that includes a date or timestamp field to record when the item was placed in that location. The record for a given item in this table with the most recent date or timestamp would record the item's current location.

                • 5. Re: Layout With Relationships (Lookup?)
                  mgores

                  If you need to keep a log of the locations for an item I would set up a portal to locations from an Item layout.  That way you can see all the locations an item has ever been at and add another if needed.  If you don't need to keep that log you can just put the location field on the Item layout and modify it as needed.

                  If you have Item and location linked by an autoentered serial number from the Item table.  You can set the Location::Tagnumber to be a looked up value based on the serial number.  That way whenever you create a record in locations from an Item, the Tag number for that item will be automatically entered.