4 Replies Latest reply on Feb 28, 2014 7:19 AM by KristinThomson

    FM13: grabbing one bit of data from a portal

    KristinThomson

      Title

      FM13: grabbing one bit of data from a portal

      Post

           I don't know why I cannot figure this out on my own, so here goes.

           We have a FM13 database that has a 6-row portal. In each row, users first select from a value list (a type of concert ticket), then type in the number of tickets available and the price of that ticket in the remaining boxes on the row.

           One of the potential values that users can pick from the value list is Comps. So for a particular concert the user could allocate 50 tickets to comps.

           How do I reference that typed in value of 50 in a separate field that's not in the portal? I essentially want to reference it, or copy it, to a different field. I tried LOOKUP, GETFIELD, but I can't copy this field that's inside a portal to another field.

           This is probably laughably easy and I'm overlooking the obvious, but I'm at wit's end.

           Kristin

        • 1. Re: FM13: grabbing one bit of data from a portal
          philmodjunk

               Can you explain a little more about exactly how you want this to work?

               Do you want the field to always show the Qty for comps tickets, the current portal row or by some other criteria?

          • 2. Re: FM13: grabbing one bit of data from a portal
            KristinThomson

                 Thank you Phil.

                 Attached screenshot shows the portal with three rows of data and two portal summaries at the bottom: Tickets Available, and Gross Potential.

                 To calculate the Average Ticket Price (the green outlined box), it should be a simple equation: Gross Potential/Tickets Available. 

                 However, we cannot include the Ticket Type = Comps in this calculation, because the average ticket price is only based on tickets for sale to the public.

                 For the moment, my formula for Average Ticket Price is:

            If (Ticket type="General Admission"; Ticket price; Gross potential / Tickets Available)

                 ...but there are many other records in this database for which this formula is not sufficient.

                 What I was trying to do was reference the number of comps that are typed in to any portal, so they could be subtracted from the Tickets Available. Possibly in a formula like this.

                 Average Ticket Price =

            Gross potential / (Tickets Available - Comps Amount)
            


                 My thought was to create a new field, independent of the portal, called "Comps Amount" to power this formula, but what I cannot figure out is how to reference, or copy, the number of comps typed into the portal row and auto-fill the "Comps Amount" field.

                 There's a good chance I'm over-thinking things.

                 Thanks for your help.

                  

            • 3. Re: FM13: grabbing one bit of data from a portal
              philmodjunk

                   You have two options:

                   1) set up a special relationship to a new occurrence of the portal's table. Use the same match fields that you do for the portal's relationship but then add one more pair of match fields   constComp = Type, where constComp is defined to return the text "Comps" in all cases. Then you can refer to fields in this new table occurrence to just get numbers for Comps tickets and you can use that in your calculation. No new fields need be defined for this method. (But you may want to use Sum() if there is the slightest possibility that two comps entries might be made for the same event.)

                   2) Use ExecuteSQL to access this data. A WHERE clause can limit the query to just comps records.

              • 4. Re: FM13: grabbing one bit of data from a portal
                KristinThomson

                     Thank you for this thorough advice, Phil. I will try #1 and see if I can make it happen.