Search for a specific value in Child Records

[gaby78]

Hello:

I have standard Invoice – InvoiceLineItems - Product setup. I am trying to achieve the following:

From the perspective of the parent record if any of the child records contain ProductID P19 return 1.

The context is: P19 is a Service Fee that is charged only in specific cases. I need a (control) field in parent table to return 1 if among the charges (ie the line items) there is a Service Fee for that invoice.

[gaby78]

no suggestion or comment ?

[Søren Dyhr]

Are you still on fm7?? GTRR(FS) works wonders here! Before fm8 could it only be performed in the vicinity of this:

http://www.kevinfrank.com/download/multi-gtrr.zip

--sd

[comment]

I don't think GTRR returns a result in a calculation field. This can be done with:

not IsEmpty ( FilterValues ( List ( Child::ProductID ) ; "P19" ) )

In version 7, a value list of Child::ProductID (related only) must be defined, so that ValueListItems may be used instead of the List() function.

However, this is a case of violating the 'one fact per field' rule. Strictly speaking, the product type should be in a separate field that would allow independent counting.

[gaby78]

Soren:

I am running FM7 at home and FM8.5 at work.

Will be upgrading to 8.5 Adv soon.

Comment: thanks for the solution, it surely is superior to what I currently have.

[Søren Dyhr]

I don't think GTRR returns a result in a calculation field. This can be done with:

I would make a search in the lineItems table, and then make a GTRR(FS) on the found set - getting to the Invoice table.

--sd

[comment]

You are assuming the only purpose of this field is to be used in a find. If this assumption is correct, then I agree that the field is not required.