1 2 Previous Next 15 Replies Latest reply on Apr 8, 2014 11:45 AM by philmodjunk

    Need help with a calculation! Totally Lost

    shae1725

      Title

      Need help with a calculation! Totally Lost

      Post

           Hoping someone can help and that the image of the calculation makes sense

           Highlighted is what I need to be able to calculate.

      AAAAA.PNG

        • 1. Re: Need help with a calculation! Totally Lost
          philmodjunk

               Please explain how you plan to use this calculation. Is this an auto-entered calculation? a Calculation field? A conditional format expression? An expression in a script step? (I don't think so for this last one, but let's be sure.)

               What result should it produce for you?

               And what exactly identifies the "previous record"? The preceding record in the found set? The last record to be edited by the user? The previous record for a specific sub set of records in your table such as the previous record linked to the same client record in a related table?

          • 2. Re: Need help with a calculation! Totally Lost
            shae1725

                 Hi Phil

                 I have four fields A B C D  Another we will call Field_X is not used in record 1

                 On the first Record if A B C D are empty and I enter a value into any one of them they will all populate with the same value!

                 If any have any values in the fields then I can enter different values into each one.

                  

                 For the second record and all records after.

                 When add a value to Field X  I want to return previous records values minus Field X

                 ie Record 1    A  100     B 100  C 200   D 200      X no data in Record 1

                     Record  2         90          90      190       190       10

                     Record  3         75          75       175       175        15

                  

                  

            • 3. Re: Need help with a calculation! Totally Lost
              philmodjunk

                   Sorry but this only answers a few of my questions and causes me to ask a new one:

                   

                        On the first Record if A B C D are empty and I enter a value into any one of them they will all populate with the same value!

                   How exactly are you doing that? (I can think of several different ways, best guess is that you have defined the same auto-enter calculation for all four fields.)

                   Unanswered questions:

                   

                        Is this an auto-entered calculation? a Calculation field?

                   

              And what exactly identifies the "previous record"? The preceding record in the found set? The last record to be edited by the user? The previous record for a specific sub set of records in your table such as the previous record linked to the same client record in a related table?

                   The best approach to get what you need depends on how you answer that last question.

              • 4. Re: Need help with a calculation! Totally Lost
                shae1725

                     In Answer to your first question - if you look at the first 3 lines of the let statement (you helped me with that last year) that part of the calculation works just fine for the first record

                     And Yes its an auto enter calculation and its the "preceding record" in the found set

                     Just in case this helps  - in record 1   I  enter a value in Fields A B C D

                                                                 record  2 and above    I only used Field X  for entering my value

                      

                • 5. Re: Need help with a calculation! Totally Lost
                  philmodjunk

                       After posting nearly 60,000 different comments since joining this forum, It is highly unlikely that I will remember what I did to help you last year. wink

                       And to repeat, do you use this calculation as an auto-enter calculation? Is this same calculation defined for fields A, B, C and D?

                       Yes, I can see the list function and get what it does, but no, this does not answer the above question.

                  • 6. Re: Need help with a calculation! Totally Lost
                    shae1725

                         No probs I will work it out eventually. After looking at my post I am sure I couldn't describe what I need any better than my lack of experience will allow.

                    • 7. Re: Need help with a calculation! Totally Lost
                      shae1725

                           Got it sorted using a Let Calculation at database level for the first record and a script trigger for record 2 onwards.

                           Would really liked it all in one calculation but it works!

                      • 8. Re: Need help with a calculation! Totally Lost
                        shae1725
                        /files/1b492be98b/xxxxxxx.PNG 405x339
                        • 9. Re: Need help with a calculation! Totally Lost
                          philmodjunk

                               I have a solution in mind that is pretty simple and it does not need scripting, but I need an answer to the questions that I asked to know if it will work for you.

                               It's pretty easy to tell if you've set up your fields to use this as an auto-entered calculation. Open Manage | Database | Fields and check the field definitions. If they are auto-entered, you'll see that these fields have data types such as text or number. If they are calculation fields, then "calculation" will be shown as the calculation field type.

                               The standard function for accessing data from another record in the found set is to use the getNthRecord function. But there are differences in the results that you get depending on whether or not it is used in an auto-enter calculation or a field of type calculation.

                               In an auto-entered calculation, this function will copy from the preceding record, but subsequent changes to the values in the preceding record or changes to your found set won't update the auto-entered value automatically.

                               If this is a field of type calculation, not only will changes to the values in the preceding record produce an automatic update to the calculated value, just sorting your records into a different order will cause them to re-evaluate. And there is also an upper limit to how many records you have in your found set and still get a value when the field using this function is of type calculation.

                          • 10. Re: Need help with a calculation! Totally Lost
                            shae1725

                                 They are auto-entered fields.

                                 I am assuming I got to have calculation fields for auto updating to take place then

                                 I hadn't them set for calculation because I need to enter values in the first sorted record fields.

                                 The records will always be kept in order and there will be no more than 8 sorted records in the found set.

                                 Does this help any?

                            • 11. Re: Need help with a calculation! Totally Lost
                              shae1725

                                   Hi Phil

                                   After researching what you said I came up with this as the part of the calculation that will help me achieve my goal

                                   as you stated I have to turn the fields into calculation fields for it to work properly so I have sort out the rest of the calculation to get record 1 to work as needed

                                   Am I on the correct path? is the calculation line compiled properly compiled properly

                                    

                                   many thanks

                              • 12. Re: Need help with a calculation! Totally Lost
                                shae1725

                                     I still haven't worked out what the let function actually does so its not part of my final calculation,

                                     If I need it could someone advise.  

                                      

                                • 13. Re: Need help with a calculation! Totally Lost
                                  philmodjunk

                                       Actually, I was thinking that auto-entered calculations would make for a better option than calculation fields. If you read my last post, I pointed out several draw backs to using it in a calculation field--the most key issue is that as your found set increases, you can reach point where you either encounter major delays waiting for the layout to update or even get a series of ? due to an internal limit in FileMaker being reached when you get to long of a "chain" of these calculations stacked up. (Each record cannot evaluate until the preceding record in the found set can evaluate.)

                                       Thus, if you aren't making changes to the data that require this value to recalculate, its better to use the auto-enter option. IF you have records in your database already, you can use Replace Field Contents with this calculation to do a one time update of the records and then each new record will automatically look up (copy) the needed data from the preceding record at the time that it is created.

                                  • 14. Re: Need help with a calculation! Totally Lost
                                    shae1725

                                         I have tried both - the auto-entered calculations aren't updating the record fields below if any changes are being made in the records above

                                         If I have read right I am assuming (don't fully understand database design yet) that these calculations if there is enough of them will slow fm down?

                                         The data in the fields are used write coordinates to a .dxf file. any found set  searches I will be doing wont include any of the calculation fields. nor are any of the calculation fields used in any joins etc. would this scenario make any difference?

                                          

                                    1 2 Previous Next