4 Replies Latest reply on Jul 12, 2012 9:55 AM by roughler

    How do you create multiple discrete records based on a quantity field

    roughler

      I thought this would be easy but am having no success...

       

      I have a simple flat inventory database which includes a quantity field, for instance I have 9 widgets in stock.

      I now want to give each widget a unique indentification.

      How can I create a script that will look at the amount in the quantity field and create an equivalent number of discrete records - resetting the quantity in the original record to 1.

      Then loop this script through a around 3000 records (only about 400 have a quantity greater than 1)

       

      I always appreciate help - I need lots.

        • 1. Re: How do you create multiple discrete records based on a quantity field
          MattLeach

          You would have to set a variable with the amount of records you would like to create along variable that keeps track of the count. You would then setup a loop to create your new records, incrementing the count by 1 and exit when the counter equals the quantity. Then set the original quantity to 1

           

          Set Variable [$quantity; Value:Table::quantity]

          Set Variable [$counter; Value:'0']

          Loop

               Exit Loop If [ $counter = $quantity ]

               (perform your actions here)

               Set Variable [$counter; Value:$counter + 1]

          End Loop

          Set Field [Table::quantity; "1"]

          Commit

          1 of 1 people found this helpful
          • 2. Re: How do you create multiple discrete records based on a quantity field
            erolst

            I would suggest (unless this was your intention anyway) to set up a related table for the individual Widgets, because you still need to track information about each widget category (like name and quantity …), and you don't want to store that with each individual widget record (normalized data structure and all that).

             

            Assuming the original table is called WidgetCategories, and your new table is Widgets, with foreign key field _widgetCategoryID_kf , the following script should work, using one loop to go through your WidgetCategories records, a second one to create the necessary number of Widget records. (Don't forget to create an auto-entry serial number (primary key) in Widgets, like _WidgetID_kp.)

             

            Enter Browse Mode

            Go to Layout [ WidgetCategories ]

            Show all Records

            Go to Record… [ First ]

            Loop

               Set Variable [ $categoryID ; WidgetCategories::_widgetCategoryID_kp ]

               Set Variable [ $quantity ; WidgetCategories::quantity ]

               Go to Layout [ Widgets ]

               Set Variable [ $counter ; 1 ]

               Loop

                  New Record…

                  Set Field ( Widgets::_widgetCategoryID_kf ; $categoryID ]

                  // other stuff you may need to do

                  Commit Record

                  Exit Loop If [ $counter = $quantity ]

                  Set Variable [ $counter ; $counter + 1 ]

              End Loop

              Go to Layout [ original layout ]

              Go to Record… (Next ; Exit after Last )

            End Loop

             

            Now you can set up a relationship from WidgetCategories to Widgets and redefine the quantity field in WidgetCategories as calculation Count ( Widgets::_WidgetID_kp ).

            1 of 1 people found this helpful
            • 3. Re: How do you create multiple discrete records based on a quantity field
              roughler

              Sorry, It's taken so long to respond.

               

              This worked for me.

              • 4. Re: How do you create multiple discrete records based on a quantity field
                roughler

                thanks for you support.

                 

                Steve Connolly