Portal field sums, removing duplicate related fields

[ToyGuy]

here's the scenario. Multi-key related records in a portal field, 'related' by Last Name, Address, or Property_ID#. There are known duplicates of each of these three fields (husband/wife, parent/child, customer/supplier) with as many as four instances of the same Property_ID#. Associated with each Property ID# is a Property Value and a Property Fee (calculation based on Property Values).

Portal displays ALL related records. NAME.......ADDR........PROP_ID#......PROP_VAL.....PROP_FEE

At the bottom of the portal we have a sum of the Property_Value and Property_Fee columns, but this is overstated whenever there exists duplicates of the Property_ID#. We do not wish to keep these duplicates from displaying as it is an important part of our verification process to know all the potential Property owners for any given Property_ID, but we don't want to overstate the related Property_Values, or our potential Property_Fees.

How can we continue to display the related records, but perhaps created value lists of Property_ID#'s for which we can then associated the related Property_Values and calculate the Fee potentials?

Here's a limited duplicate screen shot to help explain our problem.

http://themoneylocat...Portal_Dups.jpg

Thanks in advance, Stephen

[comment]

It would be best to keep the Property Value and Property Fee attributes in a Properties table, where each Property ID# has a unique record. You didn't say what your other tables are, but as an example, if you have:

Claims -< Claimants >- Properties

then a calculation field in Claims =

Sum ( Properties::Value )

will return the sum of all property values associated with a claim, with each distinct property tallied once only. Or, for display only, place a summary field from the Properties table (Total of Value) on a layout of Claims.

[ToyGuy]

I don't know. I don't have a Property table, but I suppose it wouldn't be difficult to create one. They are unique ID's for the other values except the Property Owner, Address.

Current tables are simply: Contacts, Notes, Documents, Linked Claims

I'll review the suggestion. Thanks...Stephen

[ToyGuy]

@comment

OK, so I created the new, unique property ID table. I'm stuck on how to use the multi-link related table to then create a Summary of the claims displayed using the new property ID table. I tried a relationship Property_iD[multi] <=> Property_ID[unique] but the sum is only on one property ID, not the several shown in the related claims column of the portal.

Stephen

[comment]

I am not sure what you mean by "the multi-link related table". Why won't the schema suggested in post #2 above work for you?

[ToyGuy]

I don't know how to display all the related claims and then create the sum of the uniquely listed claims (property ID values) and the related fee summary values. I'm including a screen cap which shows the problem of the duplicates (which we want to show) being included in the totals (which we don't want).

I could create a small example version of the fp7 tables with some dummy data to provide a working example, if that would help.

-Stephen

[comment]

I could create a small example version of the fp7 tables with some dummy data to provide a working example, if that would help.

Yes, I believe that would be a good idea. Please discard anything that's not directly relevant to the issue.

[ToyGuy]

Here's the program, full Guest access, with two sets of examples. I think I created the necessary matching files in the PROPERTIES table (unique property information and ID's).

Use the WEB-TABBED ENTRY layout and click the related claims tab to view the related claims.

Hope this helps us....TNX, Stephen

MODIFIED FILE

UCF-Mgr-working.fp7 2.zip

[comment]

Please discard anything that's not directly relevant to the issue.

Please.

[ToyGuy]

File resubmitted and has been stripped as far as I can do it.

-Stephen

[comment]

Sorry, I still don't follow. I don't understand your relationships or the fields they are based upon. As an example, how come you have a Property_ID_Number field in the Contacts table? That would only make sense if each contact is associated with at most one property.

I believe you'll need to rework your initial data model before you can solve the summing problem.

[ToyGuy]

Thanks, but having more tables is not the solution, IMO. Contacts can have multiple Property IDs and Property IDs can have multiple contacts. The only unique things are Property ID and Property Value. Contacts can have multiple addresses and addresses can have multiple contacts.

We receive this data in a PDF (some 5,000 pages) and parse the data as best we can. Addresses are provided in one string, which may or may not be divided into fields by commas and may or may not contain multiple fields (Address, Street, City, State, Postal code). Some have none of these characteristics and some have all (or more - they may contain names or trusts or banks). Names appear in the same manner, usually Last Name first, but no divisors and no indication whether it is a personal or business name (the latter which appears usually in proper order), so there is no good way to devise a Surname. The addtional fields in the PDF include the Property ID number, Property Type, Property Value and number of shares.

To create the table you suggested (unique Property ID's and related values) we created a new table with limited fields, imported the entire contact table (similar fields) and then deleted all the duplicates (about 50,000 of them), leaving us with a table of unique property ID's and values. We then intended, somehow, to relate this table to the contact table via the Property ID.

The Related Claims tab is a multi-key portal displaying data where NAME or ADDR or PROPERTY ID match. This is done so we can know that a Property ID has multiple contacts, all of which we need to negotiate a contract and use to locate a property owner. Some Property ID's may have three, four, five or more related parties (partial owners, someone with an interest in the property, or a wife/husband/son/daughter, etc). We are constructing an 'approved' related table which will clean up some of the mismatches (address is simply Mississippi, so there may be thousands of matches) or name may be John Smith (hundreds if not thousands of matches too), but this is some time off and will not solve the Property Value or Fee Value total problems since there will still be multiple listings of the same Property ID in the portal.

I see this as a need to link the related claims (Properties) back to the Properties table and then create the Property Value sum and Potential Fee sum based on the unique related Properties.

If that doesn't help more, then thanks, but I'll have to seek help elsewhere.