10 Replies Latest reply on Apr 23, 2016 5:19 PM by erolst

    Unstored Calculation will not hold result of calculation

    DominiqueHawinkels

      My Problem

      I am trying to count the number of pallets of product, used over a period of time, and am having trouble getting an unstored calculation to displaying the correct value.

      It constantly shows the count for all the pallets, not just the ones related to the product. )

      When I test the calculation in the Data Viewer, it works fine.  However, when I attach it to a field as an unstored calculation it fails to work.

      Help understanding and solving the issue would be appreciated.

       

      Supporting Material

      The following  are the fields and relationships (TOCs) that I use.

       

      Fields - Product Table

      • StockAtStart (Number)  In which I can enter a value for beginning of Period
      • StockReceived (Number) which holds the amount of new stock added during period
      • StockUsed (Unstored Calculation) Count of stock units
      • StockInStock (Unstored Calculation) Count of StockAtStart + StockRecieved - StockUsed
      • PeriodStartDate (Date) In which I can enter a start date

      PeriodEndDate (Date field) In which I can enter a finish date

       

      Fields - Pallet Table

       

      - Date (Date:- Date pallet used)

      - Product ID (Number:- ID of product stored on pallet)

       

      Calculation for StockUsed field.

       

      Case (

             Product::Period | Start = "" ;

               Case (

                      Product::Period | End = "" ;

                        "" ;                                                                              // No date range

                        Count ( ProductsUptoPeriod::__ID )                         // End date only

                                 ) ;

               Case (

                      Product::Period | End = "" ;

                        Count ( ProductsFromPeriod::__ID ) ;                       // Start date only

                        Count ( ProductsForPeriod::__ID )                          // Date range

                    )

           )

       

       

      Relationship TOCs

      Product TOCOperatorProductsUptoPeriod  TOC
      __ID=__|D   | Product
      Period | EndDate

       

      Product TOCOperatorProductsFromPeriod
      __ID= __|D   | Product
      Period | EndDate

       

      Header 1Header 2Header 3
      __ID=
      Period | StartDate
      Period | EndDate
        • 1. Re: Unstored Calculation will not hold result of calculation
          PeterWindle

          Just a quick glance, as I don't have a lot of time... but...

          Two things I would look out for here:

          1.

          you may want to change the test on related value of "" to :

          isempty(relatedfield)

          I think even

          not isvalid might be better...

           

          Remember, testing on related values is only going to work, if there is a related value to test on, so test on the validity of the value, rather than the value, does that make sense??

           

          2.

          The case statement and the order in which things in the case statement work, from top to bottom. A case within a case could get messed up. Break it into smaller case statements in separate fields and test the result. You may need to send up doing an If then two case tests?

          • 2. Re: Unstored Calculation will not hold result of calculation
            DominiqueHawinkels

            Hello Peter,

             

            Thank you for your quick response.

             

            I have made changes as you suggested.

            Unfortunately result is still the same.

             

            Updated Calculation.

             

            If (

                not ( IsValid ( Product::Period | Start ) ) ;

                  Case (

                            not ( IsValid ( Product::Period | End ) ) ;

                              "" ;                                                      // No date range

                              Count ( ProductsUptoPeriod::__ID )                        // End date only

                                      ) ;

                  Case (

                            not ( IsValid ( Product::Period | End ) ) ;

                              Count ( ProductsFromPeriod::__ID ) ;                      // Start date only

                              Count ( ProductsForPeriod::__ID )                          // Date range

                          )

                )


            What I should also note is that I have tried entering just a result like Count ( ProductsUptoPeriod ) with the same result

            Again calculations work in the Data Viewer but not when attached to a field which is an unstored Calculation.

            My feeling is that it has something to do with my relationships or the values I am trying to calculate.

            Currently, regardless of calculation I use, it always gives the result for ALL the product I have in the table, not just the ones of a particular type.

             

            Any other suggestion much appreciated.

            • 3. Re: Unstored Calculation will not hold result of calculation
              PeterWindle

              ok, so how about we look at the data via the relationship...

              throw up a portal and make sure you've got some valid results...

               

              A count is pretty straight forward and should work, if there is valid related data to count.

               

              I remember reading about an alternative way of counting related records - it was something like grabbing the first record number of the related records and the record number of the last related record and working out the difference (subtract last from first), this is apparently much faster than Count.

              • 4. Re: Unstored Calculation will not hold result of calculation
                BruceRobertson

                i see that isEmpty() was recommended to you; but you did not use it.

                Why is that?

                • 5. Re: Unstored Calculation will not hold result of calculation
                  DominiqueHawinkels

                  Hello Bruce,

                   

                  I have tried both with same result

                  • 6. Re: Unstored Calculation will not hold result of calculation
                    DominiqueHawinkels

                    Hello Peter,

                     

                    Portals show the correct number of records.

                    I also , remember the alternate way.  Just pulling up an example from one of my other programs to make sure I have logic right and will test.

                    Back to you shortly.

                    • 7. Re: Unstored Calculation will not hold result of calculation
                      PeterWindle

                      when all else fails try to replicate it in another file... it might not help resolve the problem, but at least you can start with a fresh set of eyes... if you know what I mean.

                       

                      Check also that the calc is calculating from the correct TOC (see attachment for example)

                      • 8. Re: Unstored Calculation will not hold result of calculation
                        erolst

                        Here's a suggestion on how to simplify your calculation:


                        Let ( [

                          start = Product::Period | Start ;

                          end = Product::Period | End

                          ] ;

                          Case (

                            Count ( start ; end ) = 2 ;

                              Count ( ProductsForPeriod::__ID ) ; // range

                            not IsEmpty ( start ) ;

                              Count ( ProductsFromPeriod::__ID ) ;  // start only

                            not IsEmpty ( end ) ;

                              Count ( ProductsUptoPeriod::__ID ) // end only

                          )

                        )

                         

                        where “both empty” is the implicit last case, with an implicit empty result.

                        • 9. Re: Unstored Calculation will not hold result of calculation
                          DominiqueHawinkels

                          Problem solved.

                           

                          Thanks everyone for your feedback and reminding me of all the ways that I can do the calculations.


                          Peter you were right.

                          My problem related to the StockUsed calculation calculating from the wrong TOC.

                           

                          If I am right, it is important to remember that unstored calculations are context sensitive from the point of where they are VIEWED FROM.

                           

                          Thanks again.

                          • 10. Re: Unstored Calculation will not hold result of calculation
                            erolst

                            DominiqueHawinkels wrote:

                            If I am right, it is important to remember that unstored calculations are context sensitive from the point of where they are VIEWED FROM.

                             

                            All calculations are sensitive to the context (table occurrence) from where they are evaluated – which for calc fields is specified by the popup “Evaluate in the context of …” in the Specify Calculation dialog, and for other calculations by the current layout.