Jump to content

Totalling fields from a related table?


FMRadio

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

Recommended Posts

I am attempting to help my neighborhood organization with some land issues. I am using FM 11 on a Mac running OS Sierra.

My primary database is Neighborhood and the related database is Parcel. Neighborhood and Parcel are joined by the field Parcel #. County parcel numbers are the ultimate identity of property, so each property parcel is an individual record in the Neighborhood table.

Using the unique parcel number I am able to align the information from the two tables. Neighborhood is of my own creation and Parcel is provided by the county and since this is a major metropolitan area, it is quite large. Parcel has only one field I am interested in, ParcelArea., that contains the area of each piece of individual property, Parcel does not contain the individual property's address, while Neighborhood does.

In Neighborhood, using this relationship, I am able to show the address, owner and other details of each piece of property, plus the area from the Parcel table.

Using a list layout, I placed a Summary field in the footer that would tally what was showing in the list of individual property records, based on various search criteria. When I had the property field with the lot's area stored in Neighborhood, the Summary field, ParcelAreaTotal, worked fine.

But when I switched designs, from internal data storage to an external, related table; the Summary field, ParcelAreaTotal, stopped working correctly. Instead of showing the total property area from a selected group of records, ParcelAreaTotal shows me the area of what is the currently selected record.

I tried a number of options, failures all, my last attempt was using the function  Sum ( EXTR_Parcel Converted::ParcelArea ) but this approach yielded the same outcome as before, whatever is the currently selected record.

Judging by my repeated and varied efforts to get this to work and a bit of reading, I am clearly missing something obvious about total calculations and related tables.

Thanks for any guidance.

 

 

 

Link to comment
Share on other sites

As you discovered, Summary fields only work on the current context's table. You best bet might be to 'pipeline' the Area data from the Parcel table into a calculated field in the Neighborhood table. I.e., make a calculated field in Neighborhood like so: related::parcel. You can then summarize this field.

Link to comment
Share on other sites

2 hours ago, FMRadio said:

Neighborhood and Parcel are joined by the field Parcel #.

That would make sense if one parcel has many neighborhoods and each neighborhood belongs to one parcel. Is that the case?

 

2 hours ago, FMRadio said:

But when I switched designs, from internal data storage to an external, related table; the Summary field, ParcelAreaTotal, stopped working correctly. Instead of showing the total property area from a selected group of records, ParcelAreaTotal shows me the area of what is the currently selected record.

This is difficult to understand, because you did not tell us in which table the summary field is defined, and how exactly you are trying to use it. In any case, I don't see why separating the tables into individual files should make any difference - at least in this aspect. You say that ParcelArea is a field in the Parcel table - so if ParcelAreaTotal is a summary field, it too must be defined in the Parcel table. But here's where it all stops making sense, because if one parcel has many neighborhoods, then what exactly are you trying to total here?

BTW, what advantage would separating the tables bring?

 

1 hour ago, Fitch said:

Summary fields only work on the current context's table.

I am not sure what exactly you mean by that. Related summary fields work just fine.

 

Link to comment
Share on other sites

Sorry I didn't word that right. I meant that a summary field can only summarize data from its own table.

Link to comment
Share on other sites

Quote

Parcel table into a calculated field in the Neighborhood table. I.e., make a calculated field in Neighborhood like so: related::parcel. You can then summarize this field.

Thanks for your response. I thought a picture might be worth a thousand words, or so they say.

I uploaded a screenshot of the table Neighborhood. Basically, the table is an elaborate calculator, the idea being to find sets of properties based on some criteria and get total values at the bottom of the window. Those totals could be total square feet or a total of land values. The find criteria might be the block property is located on or one of the alternative rezoning proposals.

In the screenshot, all of the fields shown are from the Neighborhood table, the primary table. The only exception is the field labeled Lot Area (Parcel :: ParcelArea) which comes from the related table Parcel. The Parcel# field, at the far left, is the field that links the two tables with property parcel number.

At the bottom of screenshot in the footer is the field ParcelAreaTotal, in line with the field Lot Area (Parcel :: ParcelArea), where the totaled value is to be shown. As you have pointed out and as I have read, you cannot use a summary field for this operation.

But, so far, my attempts at using a calculation field have failed. You can see that value in ParcelAreaTotal is not a total of the found list but is the value of the currently active record 9547200070.

This is my central question, how do you use a calculation field in this situation to total a value located in another table?

Thank you.

Privacy: The information in the screenshot is not private data. Public data from the online county records and the properties shown are from a commercial developer.

597900ab722cf_NeighborhoodTable.thumb.jpg.516ab9fb716a16f11e20ebfbde17d46d.jpg

 

 

Edited by FMRadio
Incorrect Image
Link to comment
Share on other sites

  • 3 weeks later...

Fitch, sorry for the delay in responding. Crazy trail over here, I actually did not understand your suggestion, I did not have enough knowledge.

So, as I so often do, I messed about and experimented. Thru a kind of accident I realized that I could (rather than above attempt) bring a remote, related table field into a calculation field and get the result I was looking for, a total of all the values of the records showing -all or a found set. So, in other words, exactly what you suggested in 'pipelining.'

The accident was that I tried to sort a field that would not cooperate because the original database set it as text, I needed a number. So I setup the calculation field to 'massage' that data into a number so that it sort correctly. That's when I realized that I could now carry out a Summary action because the data was now local.

You told me to go out in back of the house to find what I wanted, but apparently I had to go out behind the barn and keep digging until I reached the house.Some of us are kind of slow.  :-)

Bringing the related data to a local field for analysis works beautifully, with a few extra features thrown in.

Thanks again for your help.

Edited by FMRadio
Link to comment
Share on other sites

This topic is 2415 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
×
×
  • Create New...

Important Information

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