13 Replies Latest reply on Oct 30, 2013 2:11 PM by philmodjunk

    Help with a Calculation (Getting Desperate)

    shae1725

      Title

      Help with a Calculation (Getting Desperate)

      Post

           I am trying to add another Case to the field calculation! Below is the calculation I have already!

           I want to change this to include      If  Get(RecordNumber)=1

            


           Let (
                  L = List ( A ; B ; C ;D) ;
                 Case (
                 IsEmpty ( Self ) ; Get ( ActiveFieldContents ) ;
                 Self
                 )
           )

           And Need to add            If    Get(RecordNumber) >= 2

                                                         new calculation

            

           I have tried to combine both calculations but have no idea how to get this to work!!

        • 1. Re: Help with a Calculation (Getting Desperate)
          AndrewCohn

               What is the purpose of this calculation, exactly? Is it a field definition or part of a script? To me it's saying "If the field is empty then copy the data from the field currently in focus (whatever that is) to this field, if not leave the field with it's current value" I think a calculation of this nature, since it involves the Get function would be better done with a script (I'm guessing that's what your'e doing). You can set up a script trigger to run a script on entry/commit of data into the field in question. Think about this - Won't the value of Get(RecordNumber) change dramatically depending on your current found set, as well as the value of the ActiveFieldContents? Calculation fields are not continuous evaluated by filemaker, they are only evaluated when the record is committed. Until you "touch" this field in a script or modify the data manually, the value of the field will not change.

               I'm not sure how the Let() function ties in with what you're trying to do here, either.  Perhaps you can explain?

               The script could simply be

               [start at the first record in your found set]

               Loop(

               If Is Empty(Field X)

               Set Field [Field X](Get(ActiveFieldContents)

               End If

               If Get(RecordNumber) => 2

               [Do Whatever)

               End Loop (Go To Next, Exit After Last)

                

          • 2. Re: Help with a Calculation (Getting Desperate)
            AndrewCohn

                 Also 

                 Case(

                 IsEmpty(Self); Get(ActiveFieldContents);

                 Get(CurrentRecordNumber) => 2; (do whatever);

                 Self)

                 will probably work for you too

            • 3. Re: Help with a Calculation (Getting Desperate)
              philmodjunk

                   Case ( Get ( RecordNumber ) = 1 ;
                               Let (
                                       L = List ( A ; B ; C ;D) ;
                                       Case (
                                        IsEmpty ( Self ) ; Get ( ActiveFieldContents ) ;
                                        Self )
                                       ) ; // let
                                Get ( RecordNumber ) = 2 ;
                                       // calculation for when record number is 2 goes here
                              ) // Case

                   Hmmm, but the variable L seems unnecessary here and this looks like an auto-entered calculation, in which case, I don't think that this expression:

                             Case (
                                        IsEmpty ( Self ) ; Get ( ActiveFieldContents ) ;
                                        Self )

                   Is likely to update the field correctly.

              • 4. Re: Help with a Calculation (Getting Desperate)
                shae1725

                     Hi thanks for helping

                     No its not part of a script. I am trying to avoid if possible.

                     The calculation shown is only for the first record in the table. I only discovered that that I need extra calculations

                     The calculation is used in 4 fields A B C D

                     if all are empty and I enter a value in one of them then all 4 fields get populated with the value.

                     all other records .> 2 A B C D will be based on the record 1 value - Sum(Field::X)   field x is in each record but no has a 0 value in record 1

                     All the Fields are in the same table and values are entered directly to the fields.

                      

                • 5. Re: Help with a Calculation (Getting Desperate)
                  philmodjunk
                       

                            No its not part of a script.

                       I didn't say that it was. It looks like an auto-enter calculation due to the use of "self" in the calculation.

                       

                            The calculation is used in 4 fields A B C D

                       

                            if all are empty and I enter a value in one of them then all 4 fields get populated with the value.

                       That explains how the If function that I questioned works.

                       You may need to put the Let Function as the enclosing function:

                       Let (
                               L = List ( A ; B ; C ;D) ;
                               Case ( Get ( RecordNumber ) = 1 ;
                                              Case ( IsEmpty ( Self ) ; Get ( ActiveFieldContents ) ; Self )
                                          Get ( RecordNumber ) = 2 ;
                                              // calculation for when record number is 2 goes here
                                        ) // Case
                              ) ; // let

                  • 6. Re: Help with a Calculation (Getting Desperate)
                    shae1725

                         Let (
                                  L = List ( Left ; Right ; Top ;Bottom) ;
                                  Case ( Get ( RecordNumber ) = 1 ;
                                                 Case ( IsEmpty ( Self ) ; Get ( ActiveFieldContents ) ; Self );
                                             Get ( RecordNumber )  ≥  2 ;
                                                 // Left - Sum ( X)
                                           ) // Case
                                 ) ; // Let

                          

                         Hi Phil,  what am I doing wrong? Cant get FM to accept the calculation
                    • 7. Re: Help with a Calculation (Getting Desperate)
                      philmodjunk

                           In moving terms around, I left in a semi colon that shouldn't be there. The last row of text should not have a semi colon.

                      • 8. Re: Help with a Calculation (Getting Desperate)
                        shae1725

                             Still cant get FM to accept

                              

                              

                             Let (
                                      L = List ( Left ; Right ; Top ;Bottom) ;
                                      Case ( Get ( RecordNumber ) = 1 ;
                                                     Case ( IsEmpty ( Self ) ; Get ( ActiveFieldContents ) ; Self );
                                                 Get ( RecordNumber )  ≥  2 ;
                                                     // Left - Sum ( X);
                                               ) // Case
                                     )  // Let

                              

                        • 9. Re: Help with a Calculation (Getting Desperate)
                          philmodjunk

                               And what error message is displayed? What part of the expression is highlighted? That would save me a lot of time reading and analyzing the expression for what is likely a missing parenthesis.

                               In this case, I would guess that the calculation will be accepted if you remove the // to the left of Left - sum ( x ) or if you add the same comment characters to the left of the preceding line.

                               but while this will cause the expression to be excepted, sum (x) won't work like you expect. The value of sum ( x ) is x just like the sum of 5 is 5.

                               Sum needs to refer to multiple values before you can get something other than the value of x for the current record. That could be a list of fields in the current record or the value of a field from a set of related records.

                          • 10. Re: Help with a Calculation (Getting Desperate)
                            shae1725

                                 adding // to the preceding line works

                                 sum(x)  as you say wont work!  this part of the  calculation in plan English is to do as follows.

                                      Field::Left  -    sum all table   (X field) from the current record  from record number 1 to the current.

                                  

                                 ie Field::Left record 2  - Field::X + Field::X record 1

                                                                  3 - Field::X + Field::X record 2 + Field::X record 1

                                  

                                 I cant thank you enough for your help on this issue!

                            • 11. Re: Help with a Calculation (Getting Desperate)
                              philmodjunk

                                   Sum all table or sum found set?

                                   If you replace x with a summary field that produces the total of x, you'll get a total based on all the records in the current found set.

                                   If you set up a relationship based on X to a new table occurrence of the same table, Sum ( related Table::x ) will return a total for the entire table.

                              • 12. Re: Help with a Calculation (Getting Desperate)
                                shae1725

                                     I created a new field based on a running summary of Field X as suggested and this does exactly what I need :)  but with some odd behaviour!

                                     I put a temp portal on my layout to see what was happening in the table. If I  delete a record I have to give my mouse a few clicks before these summary field updates?

                                     I looked at records  in table view but the FieldX IS updating and working 100%

                                     I use a script to delete portal row  based on the Table. when the script runs the record deletes and the summary based on Field:X on all the other records are still visible for a second then go blank when the script finishes its other business

                                     Could a script be clashing in some way?

                                • 13. Re: Help with a Calculation (Getting Desperate)
                                  philmodjunk

                                       Try adding a commit records step to your script. Clicking the layout commits the record so you can use a script trigger performed script to make this happen automatically.