1 Reply Latest reply on Apr 16, 2013 1:13 PM by philmodjunk

    How to create records in one table using another?



      How to create records in one table using another?


           I am writing an application for a Job Bank.
           I have the following tables:
           Jobseeker        PKContactID
           Jobseeker Skills    FKContactID and FKSkillID
           Open Jobs        PKJobID
           Open Jobs Skills Needed    PKJobID and FKSkillID
           Referrals (A list of Jobseekers with matching skills

           for a Job)

           I have the relations set up so that when I display a
           specific Open Job, there are two portals, once which
           shows the related Open Jobs Skills Needed, and the
           other Jobseekers with skills that match.

           I can then flag specific Jobseekers with the job

           Problem: I then want to create Referral records for

           each Jobseeker/Job combination.
           I am assuming a button on the Job layout which will
           run a script to create these records: this would be
           used immediately after selecting specific Jobseekers:

           there would be records created only for the Jobseekers
           that are selected for one single job.

           I do not see how I will access the Jobseeker records
           that have been flagged and create a Referral record
           for each one.

           If I Find matching records in a Jobseeker layout, the
           add a record to referrals, then the focus is now on a
           Referral record and I have lost the ability to cycle
           thru the Jobseeker records.

           I hope this is clear...

           Thanks for any thoughts.


        • 1. Re: How to create records in one table using another?

               Think of variables as "buckets". Your script can load one or more "buckets" with data from your current record on your current layout and then can change to a different layout, create a new records and dump the data from the buckets into corresponding fields in the newly created record.

               The basic code looks something like this:

               Set Variable [$ID ; YourTable::PrimaryKey ]
               go to Layout [RelatedTableLayout]
               New Record/Request
               Set Field [YourRelatedTable::ForeignKey ; $ID]
               Go to Layout [original layout]

               You can use as many pairs of set variable/set field as you need for moving data from the first table to a new record in the second, but once you have created a new record and set the foreign key field, you may also have established a relationship link back to the current record of the original table, so you often do not need to use more variables.