1 Reply Latest reply on Sep 26, 2012 12:39 PM by philmodjunk

    How to copy sub summary results to a field in another table?

    MarlynMclennan

      Title

      How to copy sub summary results to a field in another table?

      Post

            

           I have a summary report based on a LineItems table showing how many of each style have been ordered. 

           A found set is selected by invoice number.

            

           Also I have a WorkBook table with one record per style.

            

           Now I want to use these LineItems subtotals in further calculations.

           I want to copy these subtotals to a field in WorkBook. 

            

            

           Trying ReplaceFieldContents:

            

           From Workbook, sorted by StyleID, ReplaceFieldContents of field WorkBook::Needs

            

           If (StyleID = workbook_LINEITEMS::StyleID;

             GetSummary ( workbook_LINEITEMS::QTYtotal; StyleID );

            0)

            

           This doesn't work. I run a script to do the ReplaceFieldContents from WorkBook.

           The result is mostly blank but some 0 in random places, seeming unconnected to matching StyleIDs.

            

      Screen_Shot.png

        • 1. Re: How to copy sub summary results to a field in another table?
          philmodjunk

               This seems to be the day for 'selective sums'. I think this is the 5th thread or so that touches on this subject just today.

               I don't know of any way for GetSummary to return a correct value except from a calculation in the context of the table of the summary field it references. That's because it uses the "break" field parameter to determine the groups used to produce the subtotal that it returns and this in turn is based on the current found set and sort order on a specific layout.

               Instead, you'll need a relationship that matches to only those records in LineItems that will make up the sub total that you need.

               That relationship is unclear to me at this point because you have different fields that appear to have the same job, that of identifying a specific Style:

               StyleID, __kpStyles, and _kfStyles

               Is this the case? It seems like you do not need both _kfStyles and StyleID as only one would appear to be needed for what you need.

               And can you explain why you need to copy this data? Once we have a relationship working, it will be possible to use the relationship to refer to the original records in LineItems without any need to copy this data. (But that does not mean there aren't a few good reasons out fhere for doing this.)