# Calculation of related records

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

## Recommended Posts

Here's my situation:

I have 3 tables: Locations, People & History.

Each record in People will be unique, meaning they are different people.

History records all the locations a certain person visits.

Locations store the possible locations a person can visit.

A person can visit a location many times, creating many related history records.

I need a calculation in my Location table that tells me how many people have visited this location...not how many times it has been visited. In other words, one location may have been visited by 3 out of 4 of my people, but each one of those 3 visited 2 times. I need my calculation to tell me "3" people have visited.

The reason...so that I can tell which locations have been seen by all, so I can mark that location as being complete for the group (of people).

It seems like this should be easy, but I can't quite get there. Can someone help?

##### Share on other sites

The place to perform the calculation you are puzzling over is within the Locations file, and a method you might like to consider would be:

1. Create a relationship from the Locations file to the History file based on the location field in both files.

2. Create a value list called 'Visitors' within the Locations file, which uses values from the People field in the History file, and select the "Only related values" option, based on the relationship defined at step 1.

3. Create an unstored calculation field (of result type number) in the Locations file, with a formula along the lines of:

PatternCount(ValueListItems("Locations.fp5", "Visitors"), "

##### Share on other sites

SWEET!

I had found another way, but this is much, much better. I had no idea about the valuelistitems trick. What does the (+1) do?

Thanks again.

##### Share on other sites

Values in a value list are separated by carriage returns, which means that there is a carriage return after every entry except the last. So the number of "

##### Share on other sites

This topic is 7812 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