Checking particular records in child table

[Sanjai]

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

[Ender]

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.

[Sanjai]

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

[Ender]

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.

[Sanjai]

Hi,

Thanks, the above solution is working fine.

Regards,

--Sanjai