Counting records based on the multiple field values

[Loops_1]

Hi All

I'm new to FM and this forum and I thank you for taking the time to read my post.

I have two tables.

Table 1 contains data for individual jobs on record. These records are categorised as either Completed or Remaining in a 'Status' field. Records a grouped together using a 'Project Ref' field.

e.g.

Record 1

Project Ref: 00001

Status: Completed

Record 2

Project Ref: 00001

Status: Remaining

Record 3

Project Ref: 00002

Status: Completed

etc...

Table 2 contains Project information such as location and contact details but I would also like to calculate the number of Completed and Remaining jobs for each Project from Table 1.

e.g.

Project:00001

Completed:59

Remaining:5

Project:00002

Completed:12

Remaining:22

In Excel I can use either the SUMPRODUCT function or VLOOKUP/pivot tables to do this - but I don't know if there are similar functions in FM.

Can anyone help me? I can elaborate if necessary.

Thanks again

Paul

[sbg2]

Create a global field in Table 2 that holds the text "Completed". Then establish a new relationship (named CompletedProjects for example) between Table 2 & 1 where ID=ID AND your new global field = Status. Then create a new calculation field in table 2.... Count(CompletedProjects::ID).

For remaining projects you either need to create another global with the text "Remaining" ....

or if ALL records have either Completed or Remaining in the status field then create a new relationship where ID = ID AND MyNewGlobal ("Completed") DOES NOT equal Status. Another calc Count field will be needed in either case.

[Loops_1]

Thanks for your suggestion. I did not quite follow it but I have worked out an alternative solution.

In Table 1 -

create a field called "Completed Project Jobs" with the calculation IF(Status="Completed",Project Ref).

Set up a Relationship between "Completed Project Jobs" in Table 1 and "Project Ref" in Table 2.

In Table 2 -

create a field called "Completed Total" with the calculation COUNT(Table 1::Completed Project Jobs)

This seems to work well.