13 Replies Latest reply on May 11, 2012 11:31 AM by Tucker

    Calculate Filtered Portal Results

    Tucker

      Title

      Calculate Filtered Portal Results

      Post

       I have two single row portals that provide a sales $ figure based on filtering dates of sales.

      Next step is to perform a calculation based on the value each portal is providing (% this year when compared to last year).

      I attempted to store the figures that the portals coughed up but no luck. Is there a method to store the results of a single line and field portal so more calculation can be performed using those results or is there an obvious solution that my sleep deprivation can't see through?

        • 1. Re: Calculate Filtered Portal Results
          philmodjunk

          Filtered portals are good for displaying data but not for producing values that need to be used in a calculation.

          What filter expression did you use?

          I think the best option will be to eliminate the filter and reproduce the same filtering behavior as part of the relationship for the portal. Then calculations can be defined to will produce results consistant with what you see in the portal.

          • 2. Re: Calculate Filtered Portal Results
            Tucker

             Filtered portal to show sales figures YTD = Year(Orders::OrderMonth) = Year(Get ( CurrentDate ))

            Filtered portal to show figures sales last year = Year(Orders::OrderMonth) = Year(Get ( CurrentDate ))-1

            Now I wish to show % how YTD is comparing to last years sales: (YTD * 100) / (last years sales)

             

            • 3. Re: Calculate Filtered Portal Results
              philmodjunk

              The good news is that your filter expressions don't use features difficult or impossible to replicate in a relationship.

              Define a calculation field, cOrderYear as Year ( OrderMonth ) in the Orders table.

              Define a calculation field, cThisYear, as Year ( get ( CurrentDate ) ) in your parent table. Make this an unstored calculation so it will update on January 1st of next year.

              In Manage | Database | relationships, make a new table occurrence of Orders by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as OrdersThisYear.

              We have not duplicated a table. Instead, this new "box" is a new reference to the same Orders table already present in your database.

              Add it to your relationships like this:

              ParentTable::cThisYear = OrdersThisYear::cOrderYear

              Now sum ( OrdersThisYear::SalesAmount ) will produce a total for that year. You can also refer to OrdersThisYear::SummaryField to access the same total in a calculation.

              Use the same method for last year's figures, but add a calcualtion field to your parent table, cLastYear as Year ( Get ( CurrentDate ) ) - 1

              • 4. Re: Calculate Filtered Portal Results
                Tucker

                 Sounds brilliant. I'll give it a try. Thank you.

                • 5. Re: Calculate Filtered Portal Results
                  Tucker

                   

                  I'm good up to here:

                  Now sum ( OrdersThisYear::SalesAmount ) will produce a total for that year. You can also refer to OrdersThisYear::SummaryField to access the same total in a calculation.

                  Should I create a new calculated field in the OrdersThisYear table that performs the calculation ( OrdersThisYear::SalesAmount )?

                  • 6. Re: Calculate Filtered Portal Results
                    philmodjunk

                    I've listed two options that both work: 1) The sum calculation defined in a new calculation field in your parent table or 2) a summary field defined in the orders table. Both produce the same total.

                    1) is a better option if you will be editing data in the portal that changes the totals computed. The value will update automatically where it can take a script to refresh the window to get the summary field to update.
                    2) is an option that may reduce the number of fields defined in your tables as you may already have a summary field in your options table that you can use for this purpose.

                    • 7. Re: Calculate Filtered Portal Results
                      Tucker

                      I have done what I believe to be exaclty what you suggested (also created calculated field in the parent table per your last post) and I'm ending up with total of all sales for all years instead of only this year for a given company.

                      The calculated fields  - cThisYear (in the parent table) and the value in the Orders table for field cOrderYear, result in a number (not a date). cThisYear is not stored. cOrderYear (in Orders table) is a stored result (has to be or "<Index Missing>" is displayed in it's edit box. The field OrderMonth (in the Orders table and TO OrdersThisYear table) is stored as a date. Both of these calculated fields display 2012 which is correct.

                      I have a one to many relationship between the parent table (Company) and the TO OrdersThisYear. The join is Company::cThisYear = OrderThisYear::cOrderYear.

                      The layout Orders Edit is based on the Company table and the portal where monthly order totals are typed in is based on the Orders table. The field SumOfOrders field (from Company table) is a calculated field sum(Orders::OrderMonth) sits on the OrdersEdit layout as a edit box.

                       

                      • 8. Re: Calculate Filtered Portal Results
                        philmodjunk

                        If OrderMonth is stored as a date, why would you compute the sum of this field for all related records? (Since a date records the number of days since 12/31/0000, this will be a very large number.)

                        I would assume that you have a number field in orders that represents the order total for that record. You would thus compute the sum of this field, not order month.

                        • 9. Re: Calculate Filtered Portal Results
                          Tucker

                           Sorry, it was a typo. It is calculated based on the monthly order values and should read:

                          The layout Orders Edit is based on the Company table and the portal where monthly order totals are typed in is based on the Orders table. The field SumOfOrders field (from Company table) is a calculated field sum(Orders::OrderMonthlyValue) sits on the OrdersEdit layout as a edit box.

                          Bottom line is that result shows all orders, not just orders for 2012. Strange since the year captured in cThisYear and cOrderYear do showe 2012.

                          • 10. Re: Calculate Filtered Portal Results
                            philmodjunk

                            What do you meant by "calculated field"? Is this a field of type calculation or a number field with an auto-entered calculation? It should be a field of type calculation.

                            • 11. Re: Calculate Filtered Portal Results
                              Tucker

                               Correct - field of type calculation.

                              • 12. Re: Calculate Filtered Portal Results
                                Tucker

                                 Looking in the Orders table I can see various years listed in the cOrderYear field (as expected). Looking in the Company table I can see 2012 listed in every record in the field cThisYear. Those parts are working.

                                The only aspect that's not working is filtering out the current year from others when summing the order amounts for a given company. Instead, the total amount of all orders for all years is showing.

                                • 13. Re: Calculate Filtered Portal Results
                                  Tucker

                                  Got it working now. I did not have the TO of Orders (OrdersThisYear) in the calculation that sums the sales amounts. Once that was corrected then I would see the total of all sales for a current year on a particular Company. I further defined the relationship between the tables to Company::__pkCompID = OrdersThisYear::_fkCompID, so I could limit the sum of sales to the Company who was being displayed on the Orders Edit layout. Works perfect now. Pilot error once again! Thank you.