Jump to content
Sign in to follow this  
stephaniellawless

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

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

post-112266-0-93901000-1422474447_thumb.

Share this post


Link to post
Share on other sites

 

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

 

Was there anything unclear about the solution I suggested?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

post-112266-0-37107800-1422483435_thumb.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. 

post-112266-0-84910700-1422563292_thumb.

post-112266-0-26374900-1422563632_thumb.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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. 

 

 

post-112266-0-79882600-1422639026_thumb.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By dmcs
      Hi. I had a really weird problem last night that was pervasive through all files I was working on, served, local, and even a new file I created to try to test the issue. It was totally crippling and driving me crazy. I had gone into a script and selected the Go to Layout command, but when I tried to select a layout, none appeared, even though there were plenty of layouts in the table. I dismissed it as a temporary glitch and tried a Set Field command, needing to set a field to a calculation, and again, found the system locked up in such a way as I had never seen before. It would not let me enter a calculation! I closed and re-opened the file. No dice. I restarted Filemaker. Nope. I rebooted my entire computer. Nope. I tried working on a local file. Still - no layouts could be chosen in ANY file! I tried making a *new* file. Nope! I was desperate, and just about posted asking for help when I thought to disable all plug-ins, just to make sure, and voila - that did it. As soon as I disabled the 360Works FTPeek and 360Works WebAssistant plugins, full functionality in the script workspace was restored! I thought maybe it was just a coincidence. I re-enabled the plug-ins and checked again. It still worked, with the plug-ins re-enabled, so - maybe I was just having a bad day, but then when I went back in this morning, again - with the plug-ins re-enabled, again - I couldn't select any layouts or make any calculations in Script Workspace, so right away I disabled the plug-ins, again, and again, that fixed it, so I'm pretty sure there's something going on with the plug-ins. I also had 360Works Email installed, but had disabled it the day before any of this started to test functionality on the server, so that one was never enabled, and that's probably the first time I've ever disabled that one while still having the other's enabled. Other than that, I haven't changed anything about my system for quite awhile. I'm actually running 16, so maybe this won't be a problem when we upgrade to 17, but for anyone else who hits this roadblock - disable your plug-ins.


    • By Monarch
      Hi everyone!
      I have a text field, with each sentence separated by ¶ .
      I would like to create buttons for each line in this field, so that I can copy each line individually (and paste manually in different application for example)
      I assume that it's easy to do in calculation with GetValue, and make a script using Get (ScriptParameter) = line number in that field, for each button. ( I ll need only 7-10 lines max).
      1. I can't figure out what script function I need to use to make a selective copy...
      2. If one of the option is Set Selection function - how would it work in my case?
      Thank you!
    • By TaiChi56
      I have always developed with FileMaker for windows. I have bought myself a MacBook Air and love it. So now I am using FileMaker 16 Pro advance for MAC. I know that MAC does not label their directories like windows does. So I am having problems figuring out to get the right coding to access my photos. I have a folder called "Photos" that reside on the C: drive in windows. Then I reference that folder so pictures will populate automatically when I put in a record. Here is what I have on the windows fileMaker:
      ImagePath:
      Let ( [ // Transform the contents of a container field named Images to text ImageRef = GetAsText ( ImagePath); Photos = Position ( ImageRef; "/"; 1; PatternCount ( ImageRef; "/")) ]; Middle ( ImageRef; Photos + 1; Length ( ImageRef ) - Photos ) ) images:
      "imagewin:/C:/Photos/" & Film::title & ".jpg" Then I go into Import folder and point to the folder all the photos are in. The problem I have now is how to point to that folder in "imagewin", is their an alternative called imagemac, then I could put in Macintosh HD/Users/patricks/Documents.  The folder is in the Document folder, called Photos. Thank you for any help.
    • By Richard Carlton
      FileMaker Coaches' Corner - Tip 11 - Improve Performance - FileMaker - FileMaker Experts
      https://youtu.be/5D2qoA_S7u8
      Get up to speed with the FileMaker Pro 17 Video Training Course! 
      Top Rated Course by FileMaker Expert, Richard Carlton.  
      http://learningfilemaker.com/fmpro16.php
      Experience Richard's dynamic and exciting teaching format, while learning both basic, intermediate, and advanced FileMaker development skills. With 27 years of FileMaker experience and a long time speaker at FileMaker's Developer Conference ,Richard will teach you all the ins and outs of building FileMaker Solutions.  The course is 50 hours of video content!
      Richard has been involved with the FileMaker platform since 1990 and has grown RCC into one of the largest top tier FileMaker consultancies worldwide. 
      Richard works closely with RCC's staff: a team of 28 FileMaker 
      developers and supporting web designers. He has offices in California, Nevada, and Texas.

      Richard has been a frequent speaker at the FileMaker Developers Conference on a variety of topics involving 
      FileMaker for Startups and Entrepreneurs, and client server integration.

      Richard is the Product Manager for FM Starting Point, the popular and most downloaded free FileMaker CRM Starter Solution.

      Richard won 2015 Excellence Award from FileMaker Inc (Apple Inc) for outstanding video and product creation, leading to business development.

      RCC and LearningFileMaker.com are headquartered in Santa Clara, CA.

      http://www.rcconsulting.com/
      Please feel free to contact us at support@rcconsulting.com
      If you want to explore building I.O.S apps for I Phone or I Pad and deploying those out to the Apple App Store.
       
      Here is a video introduction to our iOS App Training https://www.youtube.com/watch?v=cVxQe_yAshw
      Looking for FM Starting Point free software download: http://www.fmstartingpoint.com
      For More Free FileMaker Videos Check out Http://www.filemakerfree.com
      Visit http://www.learningfilemaker.com for all facets of FileMaker Award Winning Video Training.
      Please Visit Our Channel: https://www.youtube.com/user/FileMakerVideos Please Subscribe While There.

      Please Comment, Like & Share All of Our Videos.

      Feel Free to Embed any of Our Videos on Your Blog or Website.
      Follow Us on Your Favorite Social Media
      https://www.facebook.com/FileMakerVideos
      https://twitter.com/filemakervideos
      https://plus.google.com/+FileMakerVideos/videos

      Filemaker Pro 17 Training Videos
      FileMaker 17 Videos
      Filemaker Pro 17 Video Course
      #FileMakerVideos
      #FileMakerTrainingVideos
      #WhatisFilemaker17
      #FilemakerPro17Training
      #Filemaker17VideoTutorial
      #FilemakerPro17Videos
      #FileMakerCoaches'CornerTip11
    • By pandsmarine
      Hi guys,
      I’m brand new working with FM and would like your help with a calculation if I may. I want a calculation to display an estimated number of paint tins needed to paint a boat based on it’s length and width. With this information I would also like the cost of the estimated paint. I'm assuming IF can not be used as there are too many variables, can CASE?
      With the boats width, I've got anything over 7 classed as wide, so I've been using Jobs::BoatWidth > 7 to define narrow and wide (only two classes of widths).  I'm using the table below to give our customers a rough idea at the moment but would like information to be displayed in FM specific to boats length and width for each customer after entering that data: Jobs::BoatWidth and Jobs::BoatLength. Cost of each tin is £26.32. Hope I haven't left anything out and thanks in advance.

  • Who Viewed the Topic

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.