Jump to content
Sign in to follow this  
CAS

auto-calculation related tables

Recommended Posts

I have a specific question regarding related tables and auto-calculations or summaries. I still feel I am relatively new to alternative options to figure things out in filemaker, so hoping there is an easy or easier answer to this.

In table A I have 200,000 records that have a field that defines its locality by a 10 digit number, we'll call it "LOCALE" that can be one of 395 unique locations and a descriptor field that can be 1 of 350 unique options that is in a field called "ID" . In the related table "B" I have 395 rows of data that are the 395 LOCALES and the fields are the 350 IDs.

If this were much smaller database exporting the whole bunch into Excel and making a pivot table is essentially what I want to do.

I am currently using FM7 and trying to find a way to auto-calculate a count or summarize or really anything that would allow be to identify which combinations I have of these LOCALE X ID. I dont need an actual count but just an indication that the combination exists.

I can think of numerous scripts and export options to do this but requires a lengthy script-running /writing or exporting and such to get the results. I sometimes add thousands of records or make corrections to a single "ID" field that would make a dynamic set-up much more user-friendly.

Thanks

CAS

Share this post


Link to post
Share on other sites

In Table B, each record is a Locale, and contains 350 fields? That's doesn't seem like an efficient set up, seems having another table for the IDs would be a step to normalization. You don't say what the purpose of your database is, or how you would use the combination information, which can be helpful.

Anyway, make a calc field cLocaleID = Locale & " " & ID. Create a Layout, "LocaleID". Create a sub-summary part that breaks on cLocaleID. Put your cLocaleID in that part, delete the Body part.

Sort your 200K records on cLocaleID in that layout and you'll have a list of all the combos that exist. You can also create a summary field to Count cLocaleID, put that in the sub-summary part and have a count of each.

Share this post


Link to post
Share on other sites

Yes, I believe explaining the purpose of the end data format might be important. The end result is something I will be exporting into GIS software to created distribution maps (the LOCALE is a watershed and the ID is a species), hence the unwieldy format for table B. So, I need something that just "tags" the combinations so that I can dump the whole table and know at which locale a species occurs and which it does not occur at. Then I can join the LOCALE of the exported table to the dbf file to make my maps.

Maybe someone has a better way of doing this. I am open to options.

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.