6 Replies Latest reply on Aug 22, 2012 10:57 AM by philmodjunk

    Add record through relationship



      Add record through relationship


      I have tables Model and FC

      In table Model, there are fields Factory, Model. Each model number has one record, and there are more than one model under each factory.

      In table FC, there are fields Factory, Contact. Every factory has one record.

      Relationship is:   Model::Facotry=FC::Factory

      I use this relationship to draw contact information from FC to Model table, so that every factory in Model table is associated with one Contact information to table FC.

      Now the problem is, If I want to add new  model or new factory to Model table, I want FC table to be updated simultaneously. 

      I tried with the idea adding Factory Code column to both tables. First I had number generated at table FC, and use calculation Model::FCcode= FC::FCcode to create codes in Model table. While because code number in Model table is calculated and not stored, I can not use the code as relatioinship. And if I change value type of Model::FCcode, the whole field will go empty because not stored value, which blocked me from establishing relatonship through Code.

      Now I have script:


      New Records/Request

      Go to layout[Model::Factory]

      Set Variable[$newfc;Value Modle::FC]

      Go to layout["FC"(FC)]

      New Record/Request

      Set field [ FC::Factory;$newfc]

      Go to layout[original layout]

      Commit Record/Request


      This script allows me adding a new record to FC table, but not filling the new factory field record with the one added to Model table. I was thinking maybe because the record adding to table FC is already finished before I actually typed in anything. 

      How can I achieve adding to two tables simultaneously? How can I establish valid relationship allow me to do that?


      Thank you so much!!!!

        • 1. Re: Add record through relationship

          The second line of this script should be 

          Go to field[Model::Facotry]

          • 2. Re: Add record through relationship

            If I want to add new  model or new factory to Model table, I want FC table to be updated simultaneously.

            Shouldn't this say: "If I want to add new model from a new factory to Model table, I want FC table to be updated simultaneously." ?

            If so and assuming that "Factory" is an auto-entered serial number, then your script would be:

            #Script is performed from layout based on Model
            Go to Layout [ Fc (FC) ]
            New Record/Request
            Set Variable [$Factory ; FC::Factory]
            #You may want to pause or halt the script here so that you can enter more info about the new factory Or see note below this script
            Go to Layout [Original Layout]
            New Record/Request
            Set Field [Model::Factory ; $Factory]

            Note that if you add fields from FC to your Model layout, you can enter information for your new Factory record directly on the Model layout after performing this script.

            • 3. Re: Add record through relationship

              I may add a whole new facotry with a new model to Model table

              or just add a new model to an existing facotry.

              Either situation can happen.

              • 4. Re: Add record through relationship

                Yes, but you only need a new record in Factory when the factory is new.

                When you need to add a model for an existing factory, you only create a new model in the model table and link it to Factory by entering or selecting a value in the Model::Factory field. This may not need any script at all.

                And since mind reading computers have not been invented yet, Wink you'll need to use different interface features, such as two different buttons, for each situation.

                • 5. Re: Add record through relationship

                  You are right Phil, having two buttons will make things clear and easier.

                  I still did not get Set Variable sentence you reversed above. What I had in mind was reading the new facotry added to Model table, and attach value to $Factory, then auto-enter Factory field in Facotry table.

                  Why you wrote as : Set Variable [$Factory ; FC::Factory]? 

                  And if I halt the script, will it read till I actually type in in Model table?


                  Thank you!!

                  • 6. Re: Add record through relationship

                    I'm assuming that Factory is an auto-entered serial number. (If not it should be).

                    Thus creating a new record in FC automatically enters a value into FC::Factory.

                    This value must then be copied to the new Model::Factory field in order to link the two new records to each other.

                    If you are linking by factory name, this script won't work as written, but you'd still need to enter a factory name in the new Factory table before you can copy it to the match field in Model.

                    Matching by factory name creates issues you can avoid by using the serial number. Factory names may not be unique, they can change and users can enter the names incorrectly. Any of those situations can result in having to change the value of FC::Factory, but doing so breaks the link to any related records that link by the original value. This complication and potential for serious problems with your data is avoided when you use a serial number field instead of a name in your relationships.