Hi,

I am working on a portal tool. It shows the data from a child table. Assuming that the child table has following four records related to the master table.

Cancelled

Scheduled

Closed

Cancelled

I want to calculate how many "Cancelled" records are present in the child table.

I understand that I can create a calculation field in the child table which would return 1 (one) if the date is "Cancelled" and then using a summary field, I can find the sum of calculation field. Can we find a solution without creating additional fields in the child table?

Thanks,

--Sanjai

Sure, create a global field in the parent file. This field will be used to create a relationship between the parent and the child files:

Parent::gStatusCancelled = Child::Status

Put the text "Cancelled" in the global field, then use a Count calculation function in the parent file to count the related "Cancelled" records.

For this to work, the Status field in the child file must be indexed.

Hi,

Thanks for your help.

The calculation shows all the records which have "Cancelled " status. I am looking for only those "Cancelled" records in child table which are related to one record in master table.

For Eg:

File A contains: ID

File B contains: ID, Status

File A is related to File B using the field name "ID".

Assume File A is a master file having one record where ID is 001 and File B is a child table having four records where ID is 001.

On my layout, I am using a portal tool on a layout in File A to show four records from File B. Suppose the status field in File B contains'

"Cancelled"

"Scheduled"

"Closed"

"Cancelled"

Now, I want to calculate the number of records where status field contains "Cancelled" and id is "001". I have a solution for it but I am trying not to create new fields.

--Sanjai

Oops, sorry. The parent key and the match key should include both the ID and the Status. In FM5/6 this can be done with a calc on each side. Unfortunately this does mean you'd need to add a field on the child side.

The calcs would be:

On the parent side:

ID_StatusCancelled (calculation, text result) = ID & " Cancelled"

On the child side:

ID_Status (calculation, text result) = ID & " " & Status

In this case, the global is not needed.

I suppose you could instead do some sort of scripted solution, if you really wanted to avoid adding a field. Have a count field in the parent file, and use a loop to update the count.

Hi,

Thanks, the above solution is working fine.

Regards,

--Sanjai