9 Replies Latest reply on Dec 3, 2014 9:37 AM by SteveKeiser

    Either or lookup

    SteveKeiser

      Title

      Either or lookup

      Post

      I have a table (Inventory) that looks up information from another table (Equipment) when an equipment ID number (what they are really picking is a foreign key) is selected in the Inventory table. At times the equipment ID is unknown, yet a record needs to be created. We have been dealing with this by entering fake numbers like "9999999". This is not a very good solution however. I have been trying to think about how to do a lookup that copies information over from the equipment table when the equipment ID is known, and copies from the Equipment table based upon the serial number when the equipment ID is unknown. I cannot think of how to go about this. Any ideas would be appreciated. (By the way, I use lookups to copy the information and not hotlinks because sometimes things get relabeled and I want the older information in the Inventory table to remain as originally copied, when things change in the Equipment table.)

        • 1. Re: Either or lookup
          philmodjunk

          I am afraid that I don't get this. How can you copy information over from the equipment table if you don't have a specific piece of equipment identified? From what record in the equipment table would you copy that data?

          • 2. Re: Either or lookup
            SteveKeiser

            The info is copied over via lookup when the equipment id is selected in the inventory table. equipmentID_fk in the inventory table matches to equipmentID_pk in the equipment table.

            • 3. Re: Either or lookup
              philmodjunk

              Yes, but you have stated that you want to copy over data when you don't yet have such an ID to enter. Hence my confusion.

              I don't see any major issues with entering a placeholder ID that you later update with an actual ID. You can even define that record in your equipment table with a description field that reads something like "Not yet Identified" or some such text to make it clear that this entry does not represent an actual piece of equipment.

              • 4. Re: Either or lookup
                SteveKeiser

                That is my dilemma. Users are frequently asking me how do they enter an item into the inventory record when they don't know the equipment ID. I set everything up to center around that number. But I can see that sometimes they need to enter something into the system to establish the record until they can obtain the equipment ID (the next time they are at the customer account). What they want to be able to do is enter an inventory record by using the serial number. So, I have been scratching my head trying to figure out how to do that. I can set up a table occurrence of the equipment table to match with the inventory table based upon a serial number match, however I cannot figure out how to do an either or lookup based upon the equipment ID when that is know, or the serial number when the equipment ID is not known. I am probably not explaining this very well since I am not a trained developer.

                • 5. Re: Either or lookup
                  philmodjunk

                  Ok, I was reading "serial number" and "Equipment ID" as one and the same value.

                  Next question:

                  Is the serial number unique to each item of equipment but your inventory is by type of equipment? In other words, you have 4 identical widgets with serial ID's of 234, 897, and 123 but all with the same Equipment ID of 876543?

                  And a follow up question assuming that your answer to the first is "yes":

                  Do you have those serial numbers logged in your database anywhere? Could you do that, perhaps by scanning bar codes?

                  • 6. Re: Either or lookup
                    SteveKeiser

                    The serial number may not be unique. They probably are unique to a given manufacturer, but it is possible for two or more manufacturers to use the same serial number sequence. In other words, we might own equipment from manufacturer A with serial number 123, and from manufacturer B with serial number 123. (It probably is not frequent for serial numbers to be duplicated, but possible.)

                    Combining manufacturer and serial number might yield a match which might not have more than one equipment record. However, a given manufacturer might use the same sequence for one line of products and use the same serial number sequence with another line of products.

                    For this reason, each new record made in the equipment table creates a unique serial number which becomes the equipmentID_pk. Upon entering a new record, a bar code number associated with a label on the equipment is entered and the serial number, along with a number of other identifying things about the equipment. The reason why in the inventory table the equipmentID_fk field is hidden is because what the user sees as their selection is the bar code number.

                     

                     

                     

                     

                    • 7. Re: Either or lookup
                      SteveKeiser

                      By the way, the inventory table should really be called the billing table. There is a one to many relationship between equipment table and inventory table.

                      • 8. Re: Either or lookup
                        philmodjunk

                        What you describe are manageable problems. I am not working towards a solution that would use the MFG serial numbers as primary keys but rather as search criteria and the search tools can readily identify and provide the user with a way to resolve any such cases where more than one piece of equipment is found.

                        The first step, though, would be to log the serial numbers into your database so that they can be used in data searches to find equipment records and look up their equipment ID (Primary Key) vaules.

                        Something like this:

                        Equipment-----<SerialNumbers

                        Equipment::__pkEquipmentID = _fkEquipmentID

                        where you'd record the mfg serial number in a field in the SerialNumbers table.

                        Then take a look at "Adventures in FileMaking #2 - Enhanced Value Selection"--particularly the "Auto-Complete Value Lists II" section. This section uses a name to look up an ID which could be adapted to use an MFG serial number to look up an Equipment ID. The system demo'd there is designed to handle duplicate names that should also work in the unlikely event that you get duplicate serial numbers. And if you can input MFG Serial numbers by scanning a bar code, you have an even better way to manage this process by adapting this method to start from a bar code scan.

                        • 9. Re: Either or lookup
                          SteveKeiser

                          Adventures in FileMaking #2 provided an answer to my dilemma. Thanks for providing this treasure trove of great ideas!