10 Replies Latest reply on Feb 23, 2010 4:41 PM by philmodjunk

    Add new records across relationships

    ekloeselvp88

      Title

      Add new records across relationships

      Post

      I m currently building a solution to help me.  I am a tour manager for a band and am building a solution to keep track of information and to help speed me up.  I have several tables, I have a table named "Show Advance", and one named "Merchandise."

       

      I am having a couple of major problems so far.

       

      I do all of my work in my "Show Advance"  I create daily updates in this table  Say on Feb 5th we play at Ven0001 and Feb6th we play at Ven0002 each of these its own record that stores information about that particular show for the day.  I also have a table "Merchandise" which is another file, that stores all of the merchandise Sales for each Venue (Ven---).  They are linked together by the Venue Id field.  

       

      The problem is this.  I want all new dates that I add in the "Show Advance" table to be added to the "merchandise" table.  Currently what I did was write a script that opens the "merchandise" file and then within the "merchandise file I have a script trigger set to run when opened that imports data in...and it is set to Match up the "Venue id" and to update data based on the venue id's being equal and to add new data as new records.  

       

      My problem is that it adds records that have already been in the table.  It doesn't do it all the time either so I can't figure out why it adds records I already have only some times.

       

      Is there a way that when I add a new record In "Show Advance" that it will automatically add a new record in "Merchandise" with that Venue Id, Name, etc.

       

      Hopefully I was clear on that.

        • 1. Re: Add new records across relationships
          philmodjunk
            

          Yes that can be done. The details depend on how you've structured your tables, relationships and layouts.

           

          "My problem is that it adds records that have already been in the table.  It doesn't do it all the time either so I can't figure out why it adds records I already have only some times."

          We won't be able to figure that out either without being able to see your script. You can upload a file to a share site for others to look at it or you can copy paste your script to a reply to this thread, or upload a screen shot of your script to a share site. To paste your script, print it to PDF, select the text in PDF and copy it to the clipboard or, if you have filemaker advanced, make a database design report and copy your script from there.

          • 2. Re: Add new records across relationships
            ekloeselvp88
              

            Show All Records Copy [ RHYDER TOUR::DATE ]
            [ Select ] Close File [ “Merchandise” ] Show All Records
            Import Records [ Source: “file:Rhyder.fp7”; Target: “MERCHANDISE”; Method: Update matching; Add remaining; Character Set: “Mac Roman”; Field Mapping: Source field 1 import to MERCHANDISE::VENUE

            Source field 2 import to MERCHANDISE::VENUE ADDRESS

            Source field 3 import to MERCHANDISE::VENUE CITY

            Source field 4 import to MERCHANDISE::VENUE STATE

            Source field 5 import to MERCHANDISE::VENUE ZIP

            Source field 11 import to MERCHANDISE::VENUE PHONE NUMBER

            Source field 12 import to MERCHANDISE::VENUE FAX

            Source field 13 import to MERCHANDISE::VENUE CAPACITY

            Source field 94 import to MERCHANDISE::DATE

            Source field 392 match with MERCHANDISE::SHOW ID ]

            [ No dialog ]

            Open File [ “Merchandise” ]

             

            That is what I currently have.  Does that help?  If I need to upload the fiels to a share site so we can take a look at them I can do that too I just need to knwo what a good share site is? It is linked up to a button to when I click on the button that is what it does.

            • 3. Re: Add new records across relationships
              philmodjunk
                

              Well when you try to kill a bug by dropping a mountain on it, sometimes you kill more than just the bug. :smileywink:

               

              There's a much simpler method to do what you want here.

               

              I take it you have two files with one table each: Merchandise and Show Advance.

               

              It seems you have linked them with this type of relationship

               

              Show Advance::VenueID = Merchandise::VenueID

               

              In your Show Advance file, find this relationship in Manage | Database | Relationships and double click the line linking these two table occurrences. Select "Allow creation of records via this relationship" for Merchandise.

               

              Now a single line script will do the job:

               

              Set Field [ merchandise::venueID ; Show Advance::VenueID ]

               

              But what about all those other fields for venue name and address? Those fields are not needed at all in your merchandise table as far as I can tell from here. Instead, replace each such field in your layouts with the matching field from Show Advance and make sure that you have the same relationship shown above defined in your Merchandise file. Filemaker will use that relationship to display the Venue name and address fields from Show Advance without any need to copy the data from one file to the other.

               

              • 4. Re: Add new records across relationships
                ekloeselvp88
                  

                Set Field [ merchandise::venueID ; Show Advance::VenueID ]

                 

                When trying to enter the above into a script it will not allow me to select two fields like above in the set field area, so i went to the calculation side and tried to key this in and it is giving me an error also.  Guessing I am trying to enter this into the wrong place?

                 

                Do I need to go into the manage database area instead of the scripting area?

                • 5. Re: Add new records across relationships
                  philmodjunk
                    

                  Use manage database to set up the relationship, but enter the set field script step in the script editor.

                   

                  Many new users get confused the first time they try to add Set Field to a script. See the following link for step by step instructions: (Scroll down towards the end of the thread.)

                  Selecting an "All" option with checkboxes

                  • 6. Re: Add new records across relationships
                    ekloeselvp88
                      

                    so basically will I have two buttons with scripts here Phil? 

                     

                    If I am in my show advance field.

                     

                    The first one to do this:

                     

                    Click the first Specify... button ( Specify Target Field ) to select the field you want set field to modify. In my example, you would select the check box formatted field you have already created so that it appears in place of "YourTable::YourCheckBoxField".   Which will be (specify target field) and i would select  "show advance: Venue id"

                     

                    The second button? 

                     

                    Click the second Specify... button ( Calculated result ) to enter the expression to the right of the semicolon ";" do not enter this semi-colon as Filemaker will add it for you.

                     

                    little confused here

                     

                    I am sorry Phil, I don't mean to bother too much with redundant info.  Just a little confused here.

                    • 7. Re: Add new records across relationships
                      ekloeselvp88
                         The more i think about it ...also with this script will it automatically put all new records in or will i need to run this script after each new record is added?
                      • 8. Re: Add new records across relationships
                        philmodjunk
                          

                        Set Field has two parameters: In our example: merchandise::venueID and Show Advance::VenueID

                         

                        Click the first specify button and select merchandise::venueID from the list of fields (select merchandise from the drop down then click the venueID field. )

                        Now click the second specify button and type in Show Advance::VenueID (If Show Advance is the name of your table and VenueID is the name of your field.)

                        Click OK.

                         

                        Now you should see: Set Field [ merchandise::venueID ; Show Advance::VenueID ]

                         

                        in the script editor. That's all there is to it.

                         

                        "The more i think about it ...also with this script will it automatically put all new records in or will i need to run this script after each new record is added? "

                        You could simply click a button that runs this script each time you need it. You can also set up the OnRecordCommit layout script trigger to run this script every time it is updated. If a related record exists, no change occurs. If no record exists, one will be created.

                         

                        You might also consider creating a portal to your Merchandise table on your Show Advance layout. That's a term you can look up in filemaker help to get a good idea how it works.

                        • 9. Re: Add new records across relationships
                          ekloeselvp88
                             Could I add another Line to the script that would add to another file...say a "marketing file"?
                          • 10. Re: Add new records across relationships
                            philmodjunk
                              

                            Sure, as long as you have a relationship defined with the "allow create..." option, it should work the same way.