6 Replies Latest reply on Feb 1, 2010 12:28 PM by ninja

    Record Serial ID to link Related Records

    BatchMaker

      Title

      Record Serial ID to link Related Records

      Post

      (Running FMP10 on OS X.6)

       

       What I have is three tables (Raw Materials, Master Formulas, & Master Inventory). Inventory Records are related to one Formula, Formulas are related to up to 40 Materials. Right now, the related field is the Part # (for both relationships). The problem with this set up is that if a part # changes, the related record in the other table is lost. 

       

      What I am thinking of changing to is having a RecordID (serial on creation) for Materials & Formulas. Then, the relationship will have a corresponding field in the next table to be linked to. I can then pull the Part # field from the RecordID field. This is all working fine and I am comfortable up to this point.

       

      From a user interface standpoint, the RecordID should not be seen, or even known. Just the Part # (mimicking the original relationship). Where I am getting stuck is setting up the RecordID to change accordingly when the Part # is entered or changed. I was thinking about a RecordCommit Script Trigger, but was wondering if there were a easier, more efficient way to do this.

       

      Say if the Material Part # changes, that change should be reflected on all the Formulas it is tied to, so the Part # should be a auto-enter calculation? But it needs to be modifiable in the Formula.

       

       Any ideas or thoughts would be greatly appreciated.

        • 1. Re: Record Serial ID to link Related Records
          philmodjunk
            

          Here's an approach to play with and see if you like the results:

           

          Define a value list of 2 columns of information. You'd specify your ID field as the source for column 1 and your part number field as the source for column 2. Use the value list options to hide the first column. Now format your ID number field in field/control | setup... to use this value list in a pop up menu (not a drop down list). Pop-up menus will hide the ID number and show the matching part number from the value list instead.

           

          Note: in many databases, you'd want to connect to a different record when the part number is changed. Apparently, this is not the case in your situation.

          • 2. Re: Record Serial ID to link Related Records
            ninja
              

            Howdy batchmaker,

             


            BatchMaker wrote:

             The problem with this set up is that if a part # changes, the related record in the other table is lost. 

             

            Where I am getting stuck is setting up the RecordID to change accordingly when the Part # is entered or changed.


            Isn't changing the RecordID precisely what you are trying to avoid?  Leave the record ID alone to maintain your linkages, change the part# as you need.

             

            You have a functionality in mind that makes you want to change the RecordID.  What is that functionality, and what makes you want to change the RecordID to accomplish it?

             
            • 3. Re: Record Serial ID to link Related Records
              BatchMaker
                

              I see where I am unclear.

               

              A change of the Material Part# in the Material table, should not lose the relationship to the Formula table (a slightly different part # or product replacement).

               

              A change of the Material Part# in the Formula table, should change the relationship to the new desired record (a change in the formula).

              • 4. Re: Record Serial ID to link Related Records
                BatchMaker
                   That's a good work-around, but we are using drop down menus so you can type and narrow the selection (hundreds of records). I would hate to lose that feature for a cumbersome pop-up menu.
                • 5. Re: Record Serial ID to link Related Records
                  philmodjunk
                     You may want to add a search field with a script (put a button next to the field or give it a script trigger) The script would search your table for a matching part number and then use set field to enter the ID number into the parent record's ID field.
                  • 6. Re: Record Serial ID to link Related Records
                    ninja
                      

                    Howdy,

                     

                    I don't know the level of discretion or access folks have for changing formulae.  In my shop, a formula change is about as hard as a vendor change for a government program ;)

                     

                    Since it doesn't happen often, I have a button that pops up a new window showing a layout that has the part name and "RecordID" on it along with a "Find" button.

                     

                    You find the "official name" of the new ingredient in the new window, then scroll quickly through the dropdown and select it.  All in all it takes less than a minute.  If you're changing multiple formulae daily (insert cringe here) I can see where even this method would be cumbersome.