Pulling Single Value out of a Related Table

[benchwrm11]

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.

[beverly]

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

[dansmith65]

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.

[eos]

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

[benchwrm11]

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

FM 11 Pro Advanced

[Lee Smith]

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

[benchwrm11]

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!

[eos]

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

[benchwrm11]

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