6 Replies Latest reply on May 20, 2009 10:59 PM by mike_k

    Linking data fields between a main file and subfiles

    mike_k

      Title

      Linking data fields between a main file and subfiles

      Post

      What I have is a so called Main file which is linked to 3 other sub files.
      All 4 of those files have the data fields nr,sulprice order nr and project name. Now what i am trying to do is when i create a new record in the main file and entered the information for nr,sulprice order nr and project name that it automatically creates that new record in the other 3 files as well,with the info for those fields...this would save me a lot of time.....sometimes the sulprice order nr. changes and i have to update it.....right now i have to update the info in all 4 files everytime.

      what i am hoping for is that i only have to make the changes in the main file and all this info will than automatically go into the other 3 files as well.

      there are many more fields that i am trying to link like that but as soon as someone explains me how to do it with these 3 the rest will be easy

       

      appreciate any suggestions

       

      mike

        • 1. Re: Linking data fields between a main file and subfiles
          KIDO
             <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

          Hi mike,

           

          Do you really need those fields in the 4 tables? Can't you have them in the main table and establish a relationship between your main and the other tables based on the field nr?

          You can use portals to view your data from the other 3 tables.

          If not, let us know a bit more about your database structure. The more you tell us, the better we can help you.

           

          Regards

          • 2. Re: Linking data fields between a main file and subfiles
            mike_k
              

            Hi KIDO,

             

            It would be beneficial to have them as other ppl view the data as well and they might never even go into the main file, therefore it gives u an overview to which project etc. the data is linked to.  As i said some might never even go into the main file and just scroll through the subfile, this would make everything easier as you don't need to always go into the main file and check to which project the information is linked to.

             

            Right now what i have is that all these subfiles are linked to the mainfile through 3 relationships as there are 3 subfiles.the nr field is the relationship for all of these (nr = nr). now everything works fine when i go from the mainfile to the subfile it opens exactly the same project that i was looking at in the mainfile.(this works in all 3 of the subfiles)....the same thing going from the subfile to the main file.

             

            However the problem i face is that when i create a new record in the main file(when we have a new project) it does not automatically enter the data for those fields also in the subfile.......so i have to enter the data into all 4 tables each time,therefore i'd like to simplify this by just having to enter it once into the main file and than it just adds the data as well into the fields in the subfiles.

             

            would i have to create a script so it always imports all the data from the mainfile once i open the subfile......or what would be the solution to this?

             

            Regards

             

            mike

             

             

            • 3. Re: Linking data fields between a main file and subfiles
              KIDO
                 <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

              Hi Mike

               

              Whenever possible I'll use your terminology for better understanding. Following the next steps you may achieve what you're looking for:

               

              1 – Make sure the following boxes are checked in your subfiles relationships. Double click in each of the relationships and in the pop-up window, under your subfile side, check the boxes “Allow creation of records in this table via this relationship” and “Delete related records in this table when a record is deleted in the other table”.

               

              2 – Write the Following script: - Title: - Link ProjectData

              -------

              I assume that the field nr in your Main file is a auto-enter serial number. If not, the script must be readjusted to reflect the correct definition.

              ------- Script Link ProjectData ------

              Allow User Abort [Off ]

              Set Error Capture [On]

              # Adding a new record to all files

              If [ Get (ScriptParameter ) = “add”]

              New Record/Request

              Set Field [ Subfile1::nr ; MainFile::nr ]

              Set Field [ Subfile2::nr ; MainFile::nr ]

              Set Field [ Subfile3::nr ; MainFile::nr ]

              # When amending sulprice order nr and/or project name

              Else If [ Get (ScriptParameter ) = “amend”]

              Set Field [ Subfile1::sulprice order nr ; MainFile::sulprice order nr ]

              Set Field [ Subfile2::sulprice order nr ; MainFile::sulprice order nr ]

              Set Field [ Subfile3::sulprice order nr ; MainFile::sulprice order nr ]

              If [ not IsEmpty( MainFile::project name) ]

              Set Field [ Subfile 1::project name; MainFile::project name ]

              Set Field [ Subfile 2::project name; MainFile::project name ]

              Set Field [ Subfile 3::project name; MainFile::project name ]

              End If

              End If

               

              3 – Go to your Main file layout and, in layout mode, create a button “Add New Project” and in button setup select Perform script, select your script Link ProjectData and as Optional Parameter write “add”.

               

              4 - Select the field “sulprice order nr” and MENU / FORMAT / SET SCRIPT TRIGGERS and in the pop-up window check the box OnObjectModify, select your script Link ProjectData and write “amend” as Optional script parameter.

               

              5 – Repeat step 4 for your field “project name”.

               

              Test it now.

               

              If it bothers you to have another button to add project records, or if your application is to be used by others, you can assign the script to a subset menu and have the FM “New Record...” to perform it. This way your subfiles will be updated when a new record request is presented. Let me know if you need further help to achieve this.

              Regards

              • 4. Re: Linking data fields between a main file and subfiles
                mike_k
                  

                Hi Kido,

                 

                I'm facing one problem with this script.....when i write it like this: 

                 

                Set Field [ Subfile1::nr ; MainFile::nr ]

                 

                it highlights

                 

                Set Field [ Subfile1::nr ; MainFile::nr ]

                 

                and says (an operator is expected) 

                but i don't know how to write this script so i need help on what to put instead

                 

                after that i hope it will work

                 

                 

                I haven't even gotten this far but i thought I'd ask now so i won't have to bother u again...you wrote:

                 

                 

                4 - Select the field “sulprice order nr” and MENU / FORMAT / SET SCRIPT TRIGGERS and in the pop-up window check the box OnObjectModify, select your script Link ProjectData and write “amend” as Optional script parameter.

                 

                5 – Repeat step 4 for your field “project name”.

                 

                 

                Don't i also have to repeat step 4 for the field "nr" ?

                 

                Thanks for helping out

                 

                Regards

                 

                mike

                 

                • 5. Re: Linking data fields between a main file and subfiles
                  KIDO
                     <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

                  Hi Mike,

                   

                  You don't bother me. You may ask the questions you need to ask to clear any doubts.

                   

                  As I said in my last post, I used your own terminology for better understanding. I don't know what names you have used for your tables (you call them main file and sub files), therefore, where you read MainFile you must replace it with your main table name and where you read Subfile1,Subfile2 and Subfil3, you must replace them with you related tables names (sub files).

                   

                  To minimize mistakes, use the script step functions in the left scroll window of the script maker.

                  Double click on Set Field under Fields.

                  On the bottom right there are 2 buttons labelled “Specify”. Click on Specify target field – Specifyand in the pop-up window select one of your sub files and double click on field nr. Click ok. Now click on Calculated result – Specifyand select your main file name and nr field on the top left drop-down of the new pop-up window. Click OK ad repeat the procedure with the other Set Field steps in the script.

                   

                  As for your second question, the answer is no. Step 4 does not apply to field nr. I assume that the field nr is auto generated by serial number in auto-enter. If not, we need to alter the script.

                   

                  Let me know if you need further help.

                  Regards

                   

                  • 6. Re: Linking data fields between a main file and subfiles
                    mike_k
                      

                    Hi Kido,

                     

                    I did a simple mistake when writing the script,i did something wrong with specifying the target field and specifying the calculated result.

                    Everything works perfectly now, this is great.

                     

                    Thanks a lot for the help

                     

                    Regards

                     

                    Mike