Jump to content
Sign in to follow this  
benchwrm11

Pulling Single Value out of a Related Table

Recommended Posts

I'm working on developing a database for habitat surverys. We collect multiple measures for each category (i.e. Substrate: silt, gravel, pebble, rock, leaves), but in the end are just interested in the average percent of the site that each type covers. To minimize error, the hope is for raw, unaveraged data to be entered into the table, and have FM do the averaging by subtype and spit that out in a master table (Habitat).

Right now, the data is being entered into a portal, and the weighted average is a calculation field in the same table (sum of substrate type/number of samples taken) determined via a self-relate. So if there are 5 silt entries for one site, this average is reported in each of them.

Is it possible for FM to select the average for each category and spit it out into a specific field in the overall Habitat table?

I'll try to upload the database for clarification, but I'm having problems with that right now.

Share this post


Link to post
Share on other sites

What version of FM? this sounds like a possibility with Filtered portals and ExecuteSQL (FM12).

Share this post


Link to post
Share on other sites

You should be able to extract the data with a script by looping through the records in the portal. Then for each unique substrate type found you would save the average.

You could also view the information using sub-summary part(s) in preview mode.

Share this post


Link to post
Share on other sites

See if this helps: a script generates two list, loops through them and generates one finished list with names and averages. The relationship (not the portal!) needs to be sorted by substrate name. With FM 10 and newer, you could use Set Field by Name to write the values programmatically into dedicated fields.

While this approach may work, I suggest setting up tables like Categories and SubTypes and recording all your measurements in one Measurement table; this gives you a much more flexible tool to generate reports by habitat, date ranges, sub-types and/or categories, and you only have to write reporting scripts and set up layouts once.

FLW_HelpTest_LoopingScript.fp7.zip

  • Like 1

Share this post


Link to post
Share on other sites

What version of FM? this sounds like a possibility with Filtered portals and ExecuteSQL (FM12).

FM 11 Pro Advanced

Share this post


Link to post
Share on other sites

Please update your profile properly by using this quick link. Profile

Share this post


Link to post
Share on other sites

EOS, thanks for you help and for the script! One follow-up question -- the substrate types are not neccessarily entered in order (i.e. sand, sand, rock, silt, sand). I have been trying to include a sort line, but to no avail. Do you have any suggestions?

Thanks again!

Share this post


Link to post
Share on other sites

Not sure what you mean. The substrate types are sorted in the portal; where else do you want them to be sorted?

Share this post


Link to post
Share on other sites

Oops, I missed that key, very obvious part of your post about the relationship being sorted. Thank you!!

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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