Calculating numer of sorted record

[DavidLomax]

I'm a complete newbie to FM and I apologize in advance if this is a stupid question, but I hope somebody can help me...

I have separate tables for Companies (8 companies in total), Retail Locations (each company has 2-10 retail locations) and Employees (each retail location has 2-5 employees. All the tables are related.

The number of Companies, Retail Locations and Employees changes frequently.

In a new table I'm trying to define two fields that would automatically calculate unstored count of

1) all retail locations of all companies and

2) all retail locations of a company based on a pop out menu with retail locations names (so if the user selects as a retail location "Company1Location2", it will automatically calculate the number of all retail locations of Company1).

I experimented with get (FoundCount) and get (TotalRecordsCount) and either I'm not getting the syntax right or they're not the right functions.

Can somebody please tell me what calculation/function/script I should use for these fields to show the correct number? Please be as plain as possible and if you could give a specific example of the syntax of the calculation, it would be really great.

I appreciate any help, thank you.

[comment]

An unstored calculation field returning Get ( TotalRecordCount ) should work for the overall count - provided there is some relationship between the two tables. However, it would probably be best to use a summary field placed inside a filtered portal - see the attached example.

Note that another table is not actually necessary - you can use another occurrence of an existing table.

I didn't get your second question: if a user selects a location, shouldn't the number of locations equal 1? If they want to see the number of locations a company has, why not select the company?

FilteredPortalCount.zip

[DavidLomax]

Thank you, Michael, for your help, your attached example helped a lot and I finally managed to get the Get (TotalRecordCount) properly working. So, the first problem solved. :

But I'm still stuck with properly defining the 2nd field. To explain further, what that field needs to calculate is the total number of Retail Locations of the particular Company.

So to explain my example better: let's say Company1 has 7 Retail Locations. If one of their locations is selected from a pop up menu (let's say Company1Location2) that what this field needs to show is not the count of this particular location (which is obviously always "1") but the total count of all locations owned by Company1, which is "7".

I can't get that calculation right. Using Get (FoundCount) It either keeps saying "0" or "1" or the name of the location "Company1Location2". I can't get it to say "7" no matter what I do... :

[comment]

Get (FoundCount) returns the count of the current found set. You would need to be on a layout of Retail Locations and perform a find for a specific company to get the number you expect.

Relationships, OTOH, work independently of any found set. I still don't see why they would select a location instead of company, but it is possible:

SiblingCount.zip