Jump to content

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

This topic is 2016 days old. Please don't post here. Open a new topic instead.

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!

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.

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


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

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


Link to post
Share on other sites

Try this script:

Go to Record/Request/Page[ First ]
Set Variable [ $tot.dist; Value:0 ] 
   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
      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).

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


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.

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.


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 ]




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. 



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.

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. 




Link to post
Share on other sites

This topic is 2016 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By HOnza
      The "swiss army knife" plug-in is now compatible with iOS App SDK 19 and can execute macOS and Windows system scripts asynchronously
      Prague, CZ -- June 23, 2020 -- 24U Software announces the version 3.1 of 24U Toolbox Plug-In for FileMaker, toolbox of the most useful calculation functions for daily use. This update adds compatibility with iOS App SDK 19 and makes it possible to execute macOS and Windows system scripts asynchronously (without waiting for result).

      Version 3.1 brings:
      Function Toolbox_DoSystemScript can be called asynchronously Support for iOSAppSDK 19 added Fixed issue when 3 or more serial numbers are registered which caused FileMaker to freeze Compatibility and performance fixes  
      About 24U Toolbox Plug-In
      24U Toolbox Plug-In is a robust FileMaker Pro plug-in which allows FileMaker Pro databases to:
      Execute FileMaker or System shell scripts to automate tasks inside/outside of FileMaker Pro Execute SQL commands to develop faster and keep your solution clean Match & replace regular expressions to find & replace text fast using patterns Merge data values into text while maintaining styling to quickly and easily apply templates Lookup DNS name for IP & vice versa to find out who is connecting from wher Get current public IP to know more about clients and discover potential security attacks Define global keyboard shortcuts to avoid unnecessarily excessive use of script triggers Obtain precise microsecond timestamps to measure nearly unmeasurable chunks of time Get the type of a FileMaker variable to let your scripts make the right decisions Share variables between databases to avoid creating too many external data sources Get rich text as HTML to generate crystal clean HTML and CSS Support for iOS App SDK and FileMaker Cloud makes the plug-in work on the whole FileMaker Platform  
      System Requirements and Compatibility:
      24U Toolbox Plug-In 3.1 requires any Intel Mac since 2008, 2048 MB RAM, Mac OS X 10.10 Yosemite, FileMaker Pro 15. Recommended is any Intel Mac, 4 GB RAM, Mac OS X 10.10 Yosemite, FileMaker Pro 16 Advanced or newer (including FileMaker 19).
      1 GHz processor, 2048 MB RAM, Windows 8.1 and FileMaker Pro 15 are minimum requirements for using the plug-in on the Windows platform. 1.6 GHz processor, 4 GB RAM, Windows 8.1 or newer, FileMaker Pro 16 Advanced or newer (including FileMaker 19) are recommended.
      For using 24U Toolbox Plug-In 3.1 with iOS are required macOS 10.12, FileMaker Pro or Pro Advanced (for creating FileMaker solutions), Xcode8 (Swift 3.0), iOS 10.0, developer.apple.com account (for testing and deployment on device) and FileMaker iOSAppSDK 18
      Availability and Pricing
      24U Toolbox Plug-In 3.1 is immediately available for download free of charge as a fully functional 14-days trial version, which can be activated after purchasing a license. Licenses for 24U Toolbox Plug-In start at US$49 per user, $499 per server. Volume discounts are available for 5 or more users. iOS app licence for Toolbox Plug-In starts at US $499 for up to 20 users.
      More information: https://www.24usoftware.com/Toolbox/
      Download: https://www.24uSoftware.com/Toolbox#download
      Buy: https://www.24uSoftware.com/Toolbox#buy
      About 24U Software
      With customers in 75 countries and 29 years of experience with the Claris FileMaker platform, 24U excels in developing new or taking care of existing custom apps, optimizing their performance, identifying and resolving potential issues and liabilities, improving reliability, stability and scalability, integrating them with other systems including various hardware devices, and extending them beyond expectation.
      As a Claris Platinum Partner, 24U helps global businesses around the world to maintain sustainable growth by working with their in-house developers or completely taking care of the maintenance and development of their business solutions.
      (c)2020 24U s.r.o. All rights reserved. 24U and 24U Software are trademarks of 24U s.r.o. Claris and FileMaker area trademarks of Claris International, Inc., registered in the U.S. and other countries. All other trademarks are the property of their respective owners. Pricing and availability are subject to change without notice.
    • 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:
      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
      Get up to speed with the FileMaker Pro 17 Video Training Course! 
      Top Rated Course by FileMaker Expert, Richard Carlton.  
      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.

      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

      Filemaker Pro 17 Training Videos
      FileMaker 17 Videos
      Filemaker Pro 17 Video Course

  • Create New...

Important Information

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