How to filter related records for a calculated field?

[El_Pablo]

Hi,

For a specific function, I need to fetch a specific field from a related table.

An assignment can have multiple external file numbers.

assignments (_pk_assignment_id, ...)

externalFileNumbers (_pk_fileNumber_id, _fk_assignment_id, type, fileNumber, ...)

I need to get the value of the fileNumber where the type = "NAS".

The SQL equivalent would be.

SELECT externalFileNumbers.fileNumber

FROM externalFileNumbers

INNER JOIN assignments ON externalFileNumbers._fk_assignment_id = assignments._pk_assignment_id

WHERE externalFileNumbers.type = "NAS";

This is not for display, so no portal filtering...

Thanks for any help

[dansmith65]

If you need it in a calculation, then you will probably need to create a relationship matching by:

externalFileNumbers._fk_assignment_id = assignments._pk_assignment_i

and

externalFileNumbers.type = assignments.constant_NAS

Where assignments.constant_NAS is a global field that is either calculated to equal "NAS", or is set by a script step to equal "NAS" before evaluating your calculation. If you need to access types other than "NAS", then you'd want the 2nd option, so you can set it's value to match a different type when needed.

[El_Pablo]

I thought about creating a special field for the relationship, but I don't like this method.

I found a way using the CF ListIndex from Brian Dunning site.

http://www.briandunning.com/cf/269

Thanks for helping!

[bruceR]

A better custom function (no substitution required) is Kevin Frank's ValuePosition:

http://www.filemakerhacks.com/?p=2082

Or Comment's excellent CorrespondingValue function:

http://www.briandunning.com/cf/908

[comment]

I need to get the value of the fileNumber where the type = "NAS".

What if there are more than one such fileNumbers?

[El_Pablo]

It is not possible to enter 2 fileNumbers of the same type. This condition is validated when the data is entered.

If this would be the case, then the first number is picked.

[comment]

Then indeed CorrespondingValue() should work well for you.

[El_Pablo]

Hmmm... interesting!