4 Replies Latest reply on Jun 8, 2010 12:34 PM by ninja

    New record from data in other table)



      New record from data in other table)


      Hi there,


      I've been going nutts for days trying to figure this out and hoping that someone can help me out here.


      I am trying to build a database that will help us track jobs (we supply roof trusses for home builders)

      I have two tables Table 1 "Jobs" and table 2 "Site". I have a fields in "Jobs" listed as 'Site Name', 'Site Address' and 'Site City'. 

      I have the same fields ('Site Name', 'Site Address' and 'Site City') in the "Sites" table. 


      The idea is to have a new record in the "Site" Table when a new site is added in the "Jobs" Table. 


      I have tried to made the 'Site Name' field as related and clicked on "Allow creation of records in this table via relationship" and nothing. 


      Any help would be greatly appreciated. Thank you.

        • 1. Re: New record from data in other table)

          Howdy Sharpie,


          General "are you doing OK" questions:

          #1: how is the Jobs table related to the Site table?  It should be by a unique ID#...is this the case?


          #2: Do you every have more than one job on a site?  More than one site for a job?


          To answer your question:

          #3: Instead of having the Jobs::SiteName field on your job layout, put the Site::SiteName field on the Job layout.  Then it will create a new related record when you enter the SiteName.

          • 2. Re: New record from data in other table)

            Allow creation via this relationship is intended to be used with a portal so that you can type in data into a bottom blank row of a portal and the matching data from the relationship's key fields in the parent record will be automatically copied to the corresponding fields in the new record thus created. That doesn't appear to be what you are setting up here.


            It doesn't make sense to me to use data in a new Jobs record to create a new, matching site record. You'd need a script that first checks to see if a related site record exists and then creates the record and moves data from your address fields into the new site record. That's a pretty complicated method to use and maintain.


            I would first create a new site record each time we need to add a new job record that takes place on a new site. You could place a button labeled "New Site" that creates this new record for you and then uses the new record's Site ID serial number to link it to my current Job Record.


            Freeze Window

            Go TO Layout [Site]

            New Record/Request

            Set Variable [$SiteID ; Value: Site::SiteID ]

            Go To Layout [original layout]

            Set Field [ Jobs::SiteID ; $SiteID ]

            • 3. Re: New record from data in other table)

              #1 Each job is given a job # and each site is given a SiteID. Relation i have setup (might be wrong) is like this: Jobs::SiteName = Sites::SiteName. 


              #2 There can be multiple jobs in one site (I know this method will probably make duplicate records). Not sure of making the "Sites" table a value list that can be added and looked up in the same field. 


              #3 That worked! But #2 now becomes an issue.


              Thank you

              • 4. Re: New record from data in other table)



                If there can be multiple jobs on one site, then you should probably be working primarily from a layout based on the "Sites" table with SiteName, SiteAdd, etc.


                That layout would have a portal from the "Jobs" table with fields like JobName, Cost, whatever...


                If you enter info in the Jobs portal on the Sites Layout, it will make a corresponding record in the "Jobs" table and your OP question will be solved without duplicate records.


                I would recommend that your Site::SiteID be an autoenter serial#.  You should then create the field Jobs::SiteID.  Link your tables by Site::SiteID --- Jobs::SiteID instead of using the name.  When a new record is made in the Jobs table through the portal, it will be tagged with the correct Jobs::SiteID upon creation simply because you entered the data through the portal.