9 Replies Latest reply on Jan 29, 2012 7:58 AM by philmodjunk

    If formula on child records

    eibcga

      Title

      If formula on child records

      Post

      Beginner here using Mac with FMP11.0v4.

      I've created a small database to track bids placed on gemstones.  A stone can have many bidders, because a stone can have more than one bid.  A bidder can bid on many stones, because many stones can receive bids from the same bidder.  The Relationship Graph shows the many-to-many relationship.  Here are the constraints: For example, there are 80 stones (which will vary at each auction).  There will always be two bidders, no more or no less, at any given auction.  Each bidder must bid on each stone.  The bidder who has the highest bid wins that stone.  Each auction does not need to be tracked in the database (at each auction, the data for the list of stones on auction are imported into the Stones table, and the bids data are imported into the Bids table).  The database then runs a series of reports.

      I'm stuck at the part where, on the Stones layout, I have a portal to the Bids table.  When I look at each stone record, the portal properly shows two bids, one from each bidder.  I then add a Max summary field under the portal to tell me which of the two bids has the highest bid amount.  But, how do I have FMP calculate which bidder name got the highest bid (not just the highest bid amount)?  Do I use an IF formula?  How do I use that formula on related records in this case?  I'm good so far and everything is working until this step.

      I also add other calculations, like max bid amount for each stone, multiplied by the stone weight (in carats), to arrive at the total extended bid amount.

      Here is the final report I would like to run each time I import stones and bids data into the database everytime an auction takes place (with column headings along the top, and each stone in the auction as rows).   The report should list each stone and its characteristics, which bidder won each bid for each stone, and what is the total bid amount payable by the highest bidder.

      <stone_packetid>  <stone_sort_size>  <stone_weight>  <bidder1 total value if max bid>  <bidder2 total if max bid>

      1                               10ct                             10.232                           n/a                                     102,298.22

      2                               11ct                               5.984                           n/a                                       50,234.88

      3                               +11ct                            7.110                      553,231.50                                        n/a

      .

      .

      .

      Total                                                                                              553,231.50                                  152,533.10

      ***

      There are other calculations and fields I would use as well which is currently not relevant to this post.  This all works fine in Excel, but I'm trying to automate the entire process by using FMP where I can have all the reports created, and just import the data at each auction.

      Thank you again for any help you can provide to guide me.

      Screen_Shot_2012-01-18_at_10.41.53_PM.png

        • 1. Re: If formula on child records
          philmodjunk

          If you define a one row portal to Bids and specify that it be sorted in descending order by bid_amount, you can place Bidders::Bidder_name in this portal and it will display the name of the winning bidder.

          For your report, it looks like all data except the bid amount is from the stones table. You can base your report on stones. You can include Bids::bid_max to show the maximum bid. You can use two filtered portals to put the winning bids in columns like you show for your report.

          Bids::Bid_amount = Bids::bid_Max AND
          Bids::_kf_bidderid = GetNthRecord ( bids::BidderID ; 1 )

          for column 1 and

          Bids::Bid_amount = Bids::bid_Max AND
          Bids::_kf_bidderid = GetNthRecord ( bids::BidderID ; 2 )

          For column 2.

          The same expressions in conditional format expressions can hide or reveal N/A layout text. (To make layout text disappear in a conditional format expression, set the font size to 500.) That way, when the portal filters out all records (losing bid) the N/A is visible...

          • 2. Re: If formula on child records
            eibcga

            Ok, I'm trying to follow your steps.  Thank you very much so far.

            This is what I have so far in the Stones layout based on Bids in the screen shot below.

            A few things:

            1) For some reason the Body part of the layout is not listing the stones, one stone for each row (total of 81 stones in this data).

            2) As well, Column 2 does not show any amount (even when I cycle through the stone records).

            I was expecting to see a list of all stones, and if Bidder One had the highest bid, then Column 1 would show the extended bid amount for that bidder (i.e., weight x max bid, which I have in a bid_extended calc field), and if Bidder Two had the higest bid, then Column 2 would show the extended bid amount for that bidder.

            3) How would I get a total for each of Column 1 and Column 2 if I added a Subsummary part?

            4) In the Bids table, I have a bid_max summary field that deterines the maximum bid amount per stone from the two bidders.  I also have a bid_extended calc field (weight x max bid) in the Stones table.  I was expecting to be able to do this calc: Stones::stone_weight x GetSummary(Bids::bid_max), but it didn't work.  I had to work around it by adding another calc field: Stones::stone_max = Bids::bid_max.  Then in my Stones::bid_extended field I calculdated: Stones::stone_max x Stones::stone_weight.  Why did that work but using getsummary didn't?  It seems FMP needs more of a 'direct approach' in calculated fields in the same table and cannot work with a related summary fields in formulas with the getsummary function?  Just trying to understand why FMP can't figure that out.  

            Thanks again.  Any ideas? 

            • 3. Re: If formula on child records
              philmodjunk

              1) your screen shot shows that you have selected "view as form" change the view to view as list and you will see all 81 records in your table.

              2) if by column 2 you mean bidder two total I would not expect to see a value in that column in your above screen shot as bidder two did not win the auction for the first stone--that's how I interpreted your original example post, anyway.

              3) To get a summary total for each bidder, define a summary field in bids to compute the total of the extended bid amounts (Size * bid amount). You can add a field in bids to compute this value, then add the summary field to compute the total of it. Then you can place filtered one row portals on your layout with a portal filter that only allows records for the specified bidder and place this summary field (from the new occurrence), inside that portal row.

              Make a new occurrence of Bids and link it to stones like this:

              Stones::anyfield X AllBids::AnyField

              Then this portal filter expression will limit the bid records to just the bids from Bidder 1

              AllBids::__kp_bidderid = GetNthRecord ( Bidders::__kp_Bidderid ; 1 )

              Change the 1 to 2 in the getNthRecord function to filter for Bidder 2.

              • 4. Re: If formula on child records
                eibcga

                Hi Phil,

                1)  Thanks.  I switced to View As List View and it worked.  I should have caught that.

                2)  The screen shot was not meant to show the problem about Bidder Two's amount in the 2nd column, but I do see this to be the case when I cycled through the Stone records involving Bidder Two.  All records appeared in Column 1.  Sorry I wasn't more clear.  The screen shot below will show the problem.

                3)  Ok, I gave that one a try.

                I learned something about FMP -- I didn't realize one could use 1-row filtered portals on a layout body part to produce reports in list view.  I thought only fields (not 1-row portals) could be used.  That is neat.

                Anyway, almost there.  Thank you very much indeed for your help so far.

                 

                • 5. Re: If formula on child records
                  philmodjunk

                  What are the portal filters you have set up for the Bidder one total and bidder two total portals?

                  What I described was intended to leave (for a given stone) column 1 empty if bidder two won and column 2 empty if bidder one won.

                  • 6. Re: If formula on child records
                    eibcga

                    Bidder One total in trailing grand summary part:

                    AllBids::_kf_bidderid=GetNthRecord ( Bidders::__kp_bidderid ; 1 )

                     

                    Bidder Two total in trailing grand summary part:

                    AllBids::_kf_bidderid=GetNthRecord ( Bidders::__kp_bidderid ; 2 )

                     

                    Bidder One total in body part:

                    Bids::bid_amount = Bids::bid_max and Bids::_kf_bidderid = GetNthRecord ( Bids::_kf_bidderid ; 1 )

                     

                    Bidder Two total in body part:

                    Bids::bid_amount = Bids::bid_max and Bids::_kf_bidderid = GetNthRecord ( Bids::_kf_bidderid ; 2 )

                    • 7. Re: If formula on child records
                      philmodjunk

                      Hmm, don't see any obvious errors there, but your screen shot would only be correct if Bidder 1 won every auction...

                      I'll need to experiment with a demo file and see if I get the same results you do or if it works for me. Then I can either tell you what to change or upload the demo for you to compare to your file...

                      • 8. Re: If formula on child records
                        eibcga
                         I could still be doing something wrong. If you would like the demo file I've been using, please ask. I have removed all stones except for 10 to keep things simple. Thanks again.
                        • 9. Re: If formula on child records
                          philmodjunk

                          I finally got to play with this concept a bit and discovered that using references to a related table within the filtered portal, results in something of a "feedback loop" affecting the values returned by the getnthrecord function and the sMaxBid summary field.

                          Use this expression for the column filters:

                          BidsReport::_fk_BidderID = GetNthRecord ( Bidders::__pk_BidderID ; 1 ) and
                          BidsReport::Bid = Bids::sMaxBid

                          Set up BidsReport as an added occurrence of Bids linked to stones just like the current copy of bids and base your portal (and field within the portal) on BidsReport instead of bids.