10 Replies Latest reply on Jan 30, 2015 9:36 AM by StephanieLawless

    how to make a running calculation with a specific character found in a field

    StephanieLawless

      Title

      how to make a running calculation with a specific character found in a field

      Post

      Hi all,

       

      I'm hoping you can help here. I am trying to make a calculation on a layout. I need to calculate measurements of a specific drop down value that I write in a specific field. The value is called "pool." There are other values called "rapids" and "riffles" etc., but I need to write a calculation that finds running totals of the lengths between the "pools." Is it easier to run a script? If so, how to do I do this? FYI- I have another field that gives unit lengths. Again, I want to calculate the unit lengths between the value "pool". 

       

      I hope this is clear enough. Ideas? Thanks!

        • 1. Re: how to make a running calculation with a specific character found in a field
          SteveMartino

          Clear as mud :)  Can you provide a screen shot, or a mock up?  And can you use terms other then  "...a specific drop down value...in a specific field.  Where do the lengths of the 'pool's come from.   A little more info will get ya a lot more help.

          • 2. Re: how to make a running calculation with a specific character found in a field
            StephanieLawless

            Yes, clear as mud indeed as I re-read.....

            I would like to find out the frequency of pools (in the "unit type" field). In order to do this, one of the calculations I need to make is to find the lengths (in "unit length") between each pool. I have multiple records with different unit types, and I figured out how to write a script to just find the pools in the "Unit type" field, but cannot figure out how to find the lengths between the pools...

            Thanks again for the help!

            • 3. Re: how to make a running calculation with a specific character found in a field
              StephanieLawless

              Oh, and to be more specific, I need to find a script or calculation to find those lengths in order to use the said script/calc. for another calculation. Thanks.

               

              • 4. Re: how to make a running calculation with a specific character found in a field
                SteveMartino

                Thanks for starting to clarify :)  More questions:

                Can you give an example of your final result?  I see what appears to be multiple records with multiple Unit Types called Riffle with corresponding numbers.  What should happen next?  Do you need a calc for each record of "Riffle" to get the corresponding number and do something?  Or do you need all the records with "Riffle" to do something (add, summarize), then do a calc?

                Trying to combine your first and last post to get an idea.  I just don't get the "...how to find the lengths between the pools..." part.  For example, lets look at the first 3 records of the screen shot, and let's say they are the only 3 records.  If that were the case, what would you need?  A way to show records 1 & 3 with which number(s)-- 1 +25.5, or 25.5-1? (if I'm on the right track)

                • 5. Re: how to make a running calculation with a specific character found in a field
                  StephanieLawless

                  Thanks for helping me clarify! 

                  Here is the calculation: 

                  I need to add together the lengths of each unit between the "pool" units. The answer I need for each "pool" unit is this total length. For the screen shot below, I wouldn't have an answer for the first pool because it is the beginning of the survey. The answer for the 2nd pool unit would be 9.5. The answer for the third pool unit would be the sum of 17.5+33+23+9. The next calculation I need to make is to divide each of these lengths (the 9.5 and the second total) by another field called Average_Active_Channel_Width. The end result, which is pool frequency, will be the average of the pool distance lengths/Averaage Active Channel Width. 

                  How do I set up a script (?) to find these lengths automatically, and then use them in another calculation (the one I described above)?

                  Thanks again!!

                  • 6. Re: how to make a running calculation with a specific character found in a field
                    StephanieLawless

                    and.....I read my own table wrong. The length for the first pool is 0 because it is the start of the survey. The length for the second pool is 28, not 9.5. Apologies for the discrepancy. 

                    • 7. Re: how to make a running calculation with a specific character found in a field
                      SteveMartino

                      OK were getting closer.  Could you edit this part of your next to last post to reflect the proper numbers.  Thanks

                      "...The answer for the 2nd pool unit would be 9.5. The answer for the third pool unit would be the sum of 17.5+33+23+9. The next calculation I need to make is to divide each of these lengths (the 9.5 and the second total) by another field called Average_Active_Channel_Width. The end result, which is pool frequency, will be the average of the pool distance lengths/Averaage Active Channel Width..."

                      • 8. Re: how to make a running calculation with a specific character found in a field
                        StephanieLawless

                        Yes-

                        "...The answer for the 2nd pool unit would be 28. The answer for the third pool unit would be the sum of 17.5+33+23+9 (82.5). The next calculation I need to make is to divide each of these lengths by a SUMMARY field called Average_Active_Channel_Width. So, the second calculations would be 28/Average_Active_Channel_Width, 82.5/Average_Active_Channel_Width, etc. The end result will be the average of the answers above...

                        Hope this helps... Also, the field column with the names (pool, riffle, etc.) is called "unit_type" and the field column with the numbers is called "unit_length." The layout is called "habitat_entry_form."

                         

                         

                        • 9. Re: how to make a running calculation with a specific character found in a field
                          SteveMartino

                          Stephanie, I'm going to keep asking questions until either I (we) figure it out, or someone has enough info to jump in and figure it out.

                          Looking at your last screen shot, are all the numbers in the right spot?  Based on your edited comments in red, they don't seem to be.

                          Is the calculation for total based on the numbers between the word pool?

                          Let's say the numbers are right.  Based on your last screenshot, the total calculation part should be as follows (if I follow):

                          Pool 1 should be 28 //sum of one Riffle (below Pool record)-the 17.5 in Pool record is ignored.

                          Pool 2 should be 82.5 //sum of Glide, Riffle, Glide, Riffle (below Pool record)-the 9.5 in Pool record is ignored.

                          Pool 3 should be 10 //sum of one Riffle (below Pool record)-the 6.5 in Pool record is ignored.

                          Am I on the right track (please say yes)

                          • 10. Re: how to make a running calculation with a specific character found in a field
                            StephanieLawless

                            Yes! You are on the right track!

                            ok- after thinking about this, talking with a colleague, and reading your responses, this is what I want to figure out:

                             

                            I want to figure out how to find cumulative lengths between the values that say "pool" in a field called "unit-type." The unit lengths of the pools themselves are irrelevant. My colleague created a function that combined all the other values in that field (riffle, rapid, etc.) so that when they show up, they would be listed just as one value, called "fast water."  Now that he's created that, we want to find the cumulative lengths of "fast water" units. The lengths are found in a field called, "unit _length." Is there a calculation that could be written to find this instead of a script? If so, it would be very easy to finish the rest of the calculation. 

                             

                            To address some stuff that someone else asked:

                             

                            What is the scope of the series of pools, riffles and glides? One Site ID or more than one?

                            They are all values in one field called "unit_type"

                             

                            How do you calculate the average width?  just the  pools, or all the pools, riffles, and glides? Where is this calculation stored?

                            The average width is a summary field that is calculated and displayed as a running average as more records are added. 

                             

                            Is your database one flat table, or does it consist of related tables

                            It consists of some related tables, but for this purpose, the table I'm working with will only need to be related to a future summary table. 

                             

                            Does each pool-to-pool calculation need to be used, displayed, or reported elsewhere, or are they just a means to calculate the Average_Active_Channel_Width?

                            The pool-to-pool calculation does not need to be displayed or used elsewhere. They only are a means to calculate the ending result, pool frequency, which is the calculations/Average_active_channnel_width. 

                             

                            I am a novice at all of this. Thanks for trying to help me clarify more. I'll post in table format the lists of records in the unit_type and unit_lengths fields again.