5 Replies Latest reply on Apr 12, 2013 9:24 AM by philmodjunk

    Adding a single field across multiple records

    ClaireCollin

      Title

      Adding a single field across multiple records

      Post

           I have data for a particular part number for each week of a given year.  I need to add the data fields across records by month for each part number. Essentially 4 to 5 records make up 1 month and the data for those records needs to be added together for a total for that month for a specific part number.

           Here is my basic field and data set-up

           SKU    Week     Month    InStock   Committed

           101        1              1              14             0

           101        2              1                5             6

           101        1               2               21            5

           101        2               2                7             8

           102        1               1                4             2

           102        2               1               10            0

           102        1               2               16           11

           102        2               2               32            9

            

           For each month I need to total the In Stock and Committed by SKU

           EX) SKU   Month    InStock   Committed

                   101      1               19                6

                   101      2               28               13

                   102      1               14                2

                   102      2               48               20

           I am using this information to populate a portion of a secondary table.  I have tried summary fields but they only sort by either SKU or Month, not by both.  Is there a function or calculation that would allow this addtion to work?

        • 1. Re: Adding a single field across multiple records
          philmodjunk

               Summary fields don't sort by anything. They produce a an aggregate value determined by the context in which they are used.

               Create a layout based on the table in which the summary field is defined and the summary field totals values based on the current found set or , if used in a getsummary function or in a sub summary layout part, a subset of the total found set produced by sorting the records into those subsets.

               Refer to a summary field in a related table and you get a summary value based on the set of related records.

               And then we have a summary field inside a filtered portal which has yet a third context...

               That said, a summary report layout based on the table shown first in your layout can produce exactly the results you show at the end--one row for each part-month with a subtotal for both InStock and Comitted. You would sort your records first by SKU, then by month in the same sort records setup. A subsummary layout part or a GetSUmmary function could then be set up with Month as the "break" field to produce the sub totals shown. You'd delete the body layout part to eliminate listing the individual records and just show the sub totals.

               There are other options that can be used as well in places where a sub summary layout won't work.

          • 2. Re: Adding a single field across multiple records
            ClaireCollin

                 I think I have the report working but how do I get these values to populate into a table?                      

            • 3. Re: Adding a single field across multiple records
              philmodjunk

                   Why do you need them in the table? This can be done, but may not actually be necessary.

                   A looping script with GetSummary could be used to copy the sub totals into variables which are then used to create new records in another table.

                   but there may be other approaches that don't require this.

              • 4. Re: Adding a single field across multiple records
                ClaireCollin

                     I believe I mentioned before that these values are a portion of a larger table. They need to be added to already existing records, I was hoping for a calculation or lookup so they would populate automatically.  I am not very familiar with scripts.  How does a looping script work? Or is there another way to do this?

                     This is my current overall set up:

                     Table1 = raw data

                     Report1 = Based on Table1, Gives me Field summaries by Month and SKU

                     Table2 = Pulls specific data from Table1 by week for each SKU using Lookups, Needs to also pull data from Report by Month for each SKU
                                          Week1                                 Week2                          Month1                             Month2
                     SKU1    InStock  Committed       InStock  Committed       InStock  Committed       InStock  Committed
                     SKU2    InStock  Committed       InStock  Committed       InStock  Committed       InStock  Committed

                • 5. Re: Adding a single field across multiple records
                  philmodjunk

                       What is the purpose of Table 2? Why can you not just generate reports based on Table1 instead of having to copy data into Table 2?

                       Relationships and portals (possibly filtered) can be used to pull up the totals but arranged by SKU with the totals you describe. This is called a 'cross tab' report and you may want to search out threads that discuss that approach.