5 Replies Latest reply on Mar 13, 2012 5:20 PM by ohlittlejeannie

    newbie! creating multiple records from single record

    ohlittlejeannie

      I have a Table named Royalty Percentages with fields: Item Number, Royalty to, Royalty Percentage. Each item number has 1-4 records, depending on number of individuals who are paid royalties for the item.

       

      I have a second Table named Net Sales with fields: Item Number, MonthYear, Net Sales. There will be one record for each item number per month.

       

      I want to create a table of Royalty amounts to be paid each month. I am, apparently, a terrible script writer. I am also about to lose my mind (what's left of it). Anyone want to come to my rescue? Thanks!

        • 1. Re: newbie! creating multiple records from single record
          comment

          Roughly:

           

          Set Variable [ $amount ; Net Sales::Amount ]

          Set Variable [ $month ; Net Sales::MonthYear ]

          Set Variable [ $contacts ; List ( Royalty Percentages::Royalty to ) ]

          Set Variable [ $percentages ; List ( Royalty Percentages::Royalty Percentage ) ]

          Go to Layout [ Royalty Amounts ]

          Loop

          Set Variable [ $i ; $i + 1 ]

          Exit Loop If [ $i > ValueCount ( $contacts ) ]

          New Record

          Set Field [ Royalty Amounts::Royalty to ; GetValue ( $contacts ; $i ) ]

          Set Field [ Royalty Amounts::MonthYear ; $month ]

          Set Field [ Royalty Amounts::Amount ; $amount * GetValue ( $percentages ; $i ) ]

          End Loop

          Go to Layout [ original layout ]

           

          Assumptions:

          • The script starts at the Net Sales layout.
          • You have a relationship in place between Net Sales and Royalty Percentages, matching on Item Number.
          • There are no empty Royalty to or Royalty Percentage fields in the Royalty Percentages table.

           

          You also need to add a test to prevent the script running again for the same record in Net Sales.

          • 2. Re: newbie! creating multiple records from single record
            ohlittlejeannie

            Michael,

             

            Thank you. Thank you. Thank you. Never in a million years would I have been able to write that script. I have thought about this so much that I can't think anymore.

             

            How do I get the Item Number to show in the new records in Royalty Amounts table?

             

            How should I add the test you suggest for preventing the script running on the same record in Net sales?

            • 3. Re: newbie! creating multiple records from single record
              ohlittlejeannie

              I just noticed that something is amiss.

               

              For the following Data--

              Table: Royalty Percentages

              Record 1-- Item Number:000100, Royalty to: JA, Royalty Percentage: .15

              Record 2-- Item Number:000100, Royalty to: JD, Royalty Percentage: .15

              Record 3-- Item Number:000100, Royalty to: DC, Royalty Percentage: .20

               

              Table: Net Sales

              Record 1--Item Number:000100, MonthYear: 03/2012, Net Sales: 1000

               

              I get these results--

              Table: Royalty Amounts

              Record 1-- Item Number:   , MonthYear: 03/2012, Royalty to: JA, Royalty Amount: 150

              Record 2-- Item Number:   , MonthYear: 03/2012,Royalty to: JD, Royalty Amount: 0

              Record 3-- Item Number:   , MonthYear: 03/2012,Royalty to: DC, Royalty Amount: 0

              • 4. Re: newbie! creating multiple records from single record
                comment

                ohlittlejeannie wrote:

                 

                How do I get the Item Number to show in the new records in Royalty Amounts table?

                 

                If you want the Royalty Amounts to have a Item Number field, you need to load the Net Sales::Item Number into a $variable at the beginning, then populate the new record with it (same as MonthYear).

                 

                 

                ohlittlejeannie wrote:

                 

                How should I add the test you suggest for preventing the script running on the same record in Net sales?

                 

                I am hesitating here, because I don't see the entire picture - namely how the Net Sales and the Royalty Amounts table will be related. In any case, the test is for the existence of related records in Royalty Amounts; if there are any, you should either delete them first or stop altogether.

                • 5. Re: newbie! creating multiple records from single record
                  ohlittlejeannie

                  Sure. Both points make perfect sense. Done and done. I'll keep plugging away at the other issues with the script (posted with original question). Much appreciation if you have any thoughts. Thanks again for sharing time and knowlege.

                   

                  jt