3 Replies Latest reply on Feb 4, 2015 1:18 PM by philmodjunk

    Select a part using "history"

    HarryFisher

      Title

      Select a part using "history"

      Post

      I am working on a DB that records the parts ordered for my  farm machines.

      I would like to find a way to select a part for a machine by viewing past order history and selecting from the pop up list or selecting a new part previously entered on the parts table.

      Any help would be appreciated.

      H

      Screenshot_%2862%29.png

        • 1. Re: Select a part using "history"
          philmodjunk

          Is that a portal that I see on your layout? What is the relationship on which it is based?

          What you request has a number of different parts to the whole. Which parts of your attempts to do this for yourself don't work?

          • 2. Re: Select a part using "history"
            HarryFisher

            Hello Phil

            yes it is a portal - see screenshot for relationships.

            It is early days but as I am trying to bring in data from a MS Access DB with lots of history and one thing I have learnt is that it is best to develop the design before putting lots of data in. There are lots of parts for many machines - I would like to be able to use a value list that suggests parts based on previous order history 

            ultimate aim is to make an order up and when you select the machine, tab on to  part required field a list of historical parts for that machine appears and when one is selected a photo (stored in container field of parts form) is displayed for confirmation.

            how do I get the value list to display only parts previously ordered for that machine ?

            Regards

            H

             

            • 3. Re: Select a part using "history"
              philmodjunk

              What you describe is called a conditional value list. I'll put a link at the end of this comment to a demo file on conditional value lists. Most conditional value lists rely on a relationship to limit the listed values to a specific category such as the the specific machine or type of machine for which you are ordering parts. So we need to look at your data model and your screen shot is exactly what I need to see in order to do that with you.

              What I see looks good, but if you want a value list that only lists parts for a particular machine, you need a way to identify which records in your parts table are associated with a particular machine. The extra challenge here is that I would expect at least some parts to be parts for more than one record in your machine table--either because you have records for different machines that are the same exact type of machine or simply because many parts are interchangeable. That will require a many to many relationship between parts and machines in order to show that a given part can be installed on many machines and a given machine consists of many installed parts.

              Start with these relationships:

              Machine_tbl-----<Machine_Part>-----Parts_tbl

              Machine_tbl::Machine ID = Machine_Part::Machine ID
              Parts_tbl::Part ID = Machine_Part::Part ID

              You can place a portal to Machine_Part on the Machine_tbl layout to list and select  Parts_tbl records for each given Machine_tbl record. Fields from Parts_tbl can be included in the Portal to show additional info about each selected Parts_tbl record and the _Part ID field can be set up with a value list for selecting Parts_tbl records by their ID field.

              Once you have that working, an occurrence of Machine_Part can serve as the source of values for your value list and be limited by the current machine for which you are ordering parts.

              For detailed examples of multiple versions of a conditional value list (10 different approaches), see:

              "Adventures In FileMaking #1 - Conditional Value Lists"