7 Replies Latest reply on Jul 19, 2017 3:49 PM by sccardais

    Set Variable to calculation based on Get Layout Object Attributes

    sccardais

      I have a cross tab layout that uses filtered portals to show record counts of Demos and Orders that fall between two dates.

       

      The portals are based on a Self Join that matches on a Product ID (stored in a Global field in the parent) and the values in two date fields, Demo Date and Order Date. The counts shown below are correct but I also want to show a conversion rate for each column.

       

      For example, the conversion rate for May-17 would be 33.87% (21/62)

       

      I've named all of the data objects on the layout and confirmed in the Data Viewer that the objects can be used in calculations but I don't know how to store the results in variables to show the results for each column.

       

      is there a way to do this or is there a better way to do this that doesn't involve naming objects?

       

        • 2. Re: Set Variable to calculation based on Get Layout Object Attributes
          philmodjunk

          Do you know how to put merge variables on a layout?

           

          You can set up a different variable for each column and assign the results of this calculation for each column to them.

          • 3. Re: Set Variable to calculation based on Get Layout Object Attributes
            sccardais

            Yes. I know how to create variables in a script and how to add them to a layout but I don’t know how to create  variables based on Get Layout Object Attributes.

             

            The layout is based on ADMIN. The portals are based on self join that used a Product ID (global). Each portal is filtered. The portals in the Demo # row are filtered using a calculated date range in the field, Demo_Date. The portals in the Orders # row are filtered using a calculated range in the Order_Date field. The values shown are correct so the date calculations are working - although slowly.

             

            I want to show the conversion rate for each column. I thought I might be able to do this by naming each field in each portal and using the named objects in a simple calculation. For example, Demos in the June-17 column is named Demo_1. Orders for June is named Orders_1. Using the Data viewer, Orders_1 / Demos_1 gives the correct conversion calculation.

             

            But, I don’t know how to use named objects to create variables that can be placed on a layout.

             

            Thanks for your help.

            • 4. Re: Set Variable to calculation based on Get Layout Object Attributes
              sccardais

              Thanks.

               

              I’ll read them but I think the root of my challenge is how to create a variable based on a calculation that uses named objects on a cross tab layout. Or … it’s possible that there’s a better way to create this calculation that doesn’t involved named objects.

               

              I appreciate your response. The explanation below is primarily for my benefit (sometimes I solve my own problems by describing it !) but any feedback is welcome if you feel the urge.

               

              Thanks again.

               

               

              What I want to do sounds simple.

              Calculate the number of Demos that were done between two dates.

              Calculate the number of Orders that were received during the same two dates.1

              Divide the number of Orders by the number of Demos and display as a percentage.

              Repeat for 12 different date ranges (monthly periods) starting with the current month.

               

              Each record in my Parent table (Admin) contains info about one Organization. Separate fields in ADMIN store different dates such as the Date of Demo (if any), Date of the Order (if any), Date of Cancelation (if canceled) and several other dates we consider important enough to track.

               

              ADMIN also contains a product field that identifies the product the Org is testing or using.

               

              The portals on my layout are based on a self join relationship. ADMIN::x_ProducName = admin_JOIN_productname

               

              x_ProductName is a global field in ADMIN. It references a value list in Admin that contains the names of our Products,. x_ProductName is displayed as a checklist to allow the selection of more than one product. This allows the report to show records from any combination of selected Products.

               

              Each of the portals is filtered using a date calculation that creates a date range based on the Current Date. The value shown in each of the portals is a count of the records that match the ProductName and have a Date of Demo or Date of Order in the date range specified in the portal filter. This structure is slow but it works.

               

              The problem is using the calculated values in each of the portals to calculate and display the conversion rate. Frustrating because the source data is right in front of me and because I can get the proper answer in Data Viewer using GetLayoutObjectAttributes but I don’t know how to get the answer into a variable that I can show on the layout.

               

              I may be approaching this problem in the wrong way. I know there a many ways to do things in FMPA and my approach could just be wrong or too complicated.

              • 5. Re: Set Variable to calculation based on Get Layout Object Attributes
                philmodjunk

                I'm afraid that I don't understand the problem. A simple approach would be:

                 

                Set Variable [ $$Column1Value ; GetLayoutObjectAttributes....]

                Set Variable [ $$Column2Value ; GetLayoutObjectAttributes...]

                and so forth.

                 

                It may take longer than acceptable to calculate the values and update the window, but it should work.

                 

                By using Evaluate and Let, you could even do this in a loop.

                1 of 1 people found this helpful
                • 6. Re: Set Variable to calculation based on Get Layout Object Attributes
                  sccardais

                  Thanks, Phil.

                   

                  I’ll give this a try. Don’t know why I didn’t think of it. On reflection, I DO know why I didn’t think of it.

                   

                  Thanks again.

                  • 7. Re: Set Variable to calculation based on Get Layout Object Attributes
                    sccardais

                    Thanks, Phil.

                     

                    This worked perfectly.