How to find specific characters/values in fields to perform calculations

[stephaniellawless]

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!

[eos]

If you only need a value for specific records, try a calculation field like, say, cValueIfPool, defined as

Case ( someField = "pool" ; unitLength ) // you didn't tell us the name of the field whose value could be "pool"

To calculate a running total of UnitLength only when someField is "pool", use a summary field type “Total of” for cValueIfPool, with “Running total” checked.

 

Sort your records (e.g. by date, or sampleNumber) so the running total gives meaningful results.

 

Another option would be to first find all records where someField = "pool", and in your summary field (running total) use the unitLength field itself.

 

Is it easier to run a script?

 

It's always easier to run a (working) script; having it in the first place may be the complicated part …

 

We don't have enough information to tell whether you need a script at all, or if simply placing the summary field on a layout (and maybe keeping the found set in sorted order) is sufficient for your needs.

[comment]

I hope this is clear enough.

 

I don't think it is.

[stephaniellawless]

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! I posted a pic below

[eos]

 

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

 

Was there anything unclear about the solution I suggested?

[stephaniellawless]

I meant that my original post was clear as mud. 

 

Yes- thank you for your suggestion. However, I need to count running totals of lengths between each pool. I need to use those lengths to calculate the pool frequency by dividing the lengths by another field called "Active Channel Width". How do I write a script where I can do this? As you can see in my screenshot, there are other values in the "unit type" field that would need to added together. Those totals would equal the value I need for my pool frequency values. 

 

Thank you again for the help. I'm a novice with fm pro 13

[stephaniellawless]

To be more specific--

 

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!!

[stephaniellawless]

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. 

[comment]

I still have no idea what you mean.

[doughemi]

Try this script:

Go to Record/Request/Page[ First ]
Set Variable [ $tot.dist; Value:0 ] 
Loop 
   If [ pools::type = "pool" ]
      Set Field [ pools::pool.distance; $tot.dist ] 
      Set Variable [ $tot.dist; Value:0 ] 
      Set Field [ pools::someField; pools::pool.distance / pools::Average_Active_Channel_Width
   Else
      Set Variable [ $tot.dist; Value:$tot.dist + pools::distance ] 
   End If
   Go to Record/Request/Page [ Next; Exit after last ] 
End Loop 

The final average is a summary field, which is Average(someField).

[stephaniellawless]

Thanks for this. 

 

The field that the names are in (pool, riffle, etc.) is called "unit_type." Is that what I should paste into here:

 

If [ pools::type = "pool" ]

 

The name of the table/layout is "habitat_entry_form." 

 

I'm also not sure what pool.distance is. (Set Field [ pools::pool.distance; $tot.dist ])

 

Thanks again for helping me work on this!

 

I also rewrote my request below since i originally messed a number up:

"...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."

 

[doughemi]

Replace what I called "pools::type" with your table name::field name, i.e. habitat_entry_form::unit_type.

 

pool.distance is a field in which you store the accumulated distances between pools.  

someField is a field in which you will store the results of the calculation of accumulated distance/width.

 

These fields must be added to your table, but do not necessarily need to be on your layout. Note that these fields will be populated only in records where unit_type = "pool" after the script is run.

 

BTW, the layout name is irrelevant; only the table name is important. Many layouts can be based on each table occurrence.

[eos]

Here is a file with your sample data and field names, and a commented script.

 

I'm not sure which values you want to store in the table; you could perform the entire calculation within the script and only write out the end result – which, btw, should go where? Does a set of records like in your illustration belong to a sample record? For the purpose of this demo, the script simply writes to a global field. 

 

See if it helps you; if not, at least we may have a better basis for this discussion.

habitat_entry_form_eos.fmp12.zip

[stephaniellawless]

ah, thanks- you both are getting this.  The Average_Active_Channel_Width field is a summary field that has the same numerical answer for a set of records sorted by site name and data. So.... let's say the average Active Channel width for a certain set of records is 10. Then if I used the sample set below, my answer would be: 5.525 

 

The way I got to this was I added up the lengths between the pools (step 1), and got 28 and 82.5. I then divided each length by 10 (step 2). Finally, I averaged both the answers, which got me to 5.525. 

 

This is the script that I'd like to get to. 

 

doughemi- what fields do I set up for the following: Set Field [ pools::pool.distance; $tot.dist ]

 

 

Thanks!

Oh- and when I do add a "pool frequency" field to the layout, I do want it to be a running summary, so it changes as I bring up different sorted records.

Just for the sake of understanding what I'm collecting data on, here is a snapshot of my data entry form for each record. 

[doughemi]

what fields do I set up for the following: Set Field [ pools::pool.distance; $tot.dist ] 

 

 

As I told you  

 

pool.distance is a field in which you store the accumulated distances between pools.

 

It is a field you must create in each unit record. It will not have data in each record, only in the records where the unit_type is "pool

$tot.dist is just a variable within the script to temporarily store the accumulated distances between pools as we add them up.

 

You still haven't given us enough information.

 

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

 

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

 

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

 

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?

 

IIUC, you need two tables: a Sites table, where you enter the specifics of the site (the top portion of your data form), and a Units table, where you have a SiteID foreign key, and one record for each unit with unit_type, unit_length, and other pertinent information about that particular unit.  The Sites table record would be where you have fields for Average_Active_Channel_Width and other calculated accruals of the characteristics of the whole site.

[stephaniellawless]

ok- after thinking about this, talking with a colleague, and hearing replies, 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 answer some questions:

 

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.