4 Replies Latest reply on May 6, 2011 10:18 AM by philmodjunk

    "calculate value" only works once!!



      "calculate value" only works once!!



      Here is my problem:

      My database purpose is to display box contents. Let’s say I have a box with 10x10 wells. I created a file (let’s call it “Chocolate database”) where I introduce all the information regarding each item present including the “Box Name” , “location of the box” and the “position  #” (from 1 to 100) for each item (eg: the item “kit kat” is in box “candy 4” in the “position 1” and “Mars” is in box “candy 4” and “position 2”).  
      All the positions are taken in the database, even if in reality there is no chocolate at that well on the database is written “empty well” so it counts as a record as well.Then I created another separate file where I made a layout with the “layout/grid/top view” of the box and created a field for every square, so I have A1,A2,A3,….. and also the box “name” and “location” (let’s call it “Individual Boxes”). I established a relationship between this database and the one with information for the individual items, via the box name. The purpose was to introduce, by choosing in the displayed list, the box name and it would automatically fill all the remaining fields, eg: if you choose box “candy 4” it would automatically fill A1 field with “Kit Kat”, and field A2 with “Mars”, and so on. To do this I configurated each of the A1,A2,A3,…. fields to “calculate value” and introduced the following in the A1 field:

       If ( Chocolate database::Position # = 1 ; Chocolate database::Cell line name ; "error" )

      If I do this it works, but then I go to the A2 field, do exactly the same thing but chance the position number from 1 to 2 and I gives the “error” message!!

      If ( Chocolate database::Position # = 2 ; Chocolate database::Cell line name ; "error" )

      I tried doing it for field B10 and the correspondence position 11 but it also gives the “error” message!!

      Does anyone have an idea why this happens? To me it makes absolutely no sense!!

      Hope you can help.

      Thank you for your time.


        • 1. Re: "calculate value" only works once!!

          Describing the relationships in more detail and how your layout is set up can help a lot with questions like these.

          I think you have this relationship: (names may be different than your file)

          boxesTable::BoxID = Chocolate database::BoxID

          If your calculations are defined in boxesTable, the are all referring to the same related record in Chocolate database and this is why only one does not return the error. You'd need either a different relationship for every cell or a different layout approach for displaying the contents of each 'well' in the candy box.

          If you have FileMaker 11, you can place a grid of one row portals to chocolate database on your layout, one for each 'well' in your chocolate box.

          Use Chocolate database::Position # = 1 as the portal filter expression for the first well, Chocolate database::Position # = 2 as the expression for the 2nd and so forth to display each chocolate in it's respective well.

          • 2. Re: "calculate value" only works once!!

            Yehp that's the relation between my databases.

            "If your calculations are defined in boxesTable, the are all referring to the same related record in Chocolate database" . Although I don't understand why this would happen I guess that is the case.

            "You'd need either a different relationship for every cell"  How would I do this?

            Fortunately I have FileMaker 11 and the row portals thing with the filter seem to work just fine!! :)

            I did not know about this feature so took a look in the help tab.

            Thanks a lot PhilModJunk. You're a life saver.

            • 3. Re: "calculate value" only works once!!

              I don't know what is wrong but there definetly seems to be a bug on this portal feature.

              I did the first two positions and they worked fine. But then if I move, resize the portal box sometimes they either change to display the value on the first portal I did (although they displayed the right value before!!) or they just dissapear (although I can see it when I edit the layout). It really doesn't make any sense.

              This is really anoying and basically makes this feature useless.

              I just though I would write it down here since filemaker people won't let us contact them free of cost and to know if this happened to someone else out there.

              • 4. Re: "calculate value" only works once!!

                Moving and resizing  a portal should not change it's function. However, a portal will have one or more field displayed in it and when you move or resize the portal, the field will not move/resize with it and this change may leave the portal's field outside the boundaries of the portal and this can leave it unable to display the expected value here.