Conditional Lookup?

[ViciousTruth]

Hey All,

Anyone know how to implement a conditional lookup?

Here's the problem:

I have three tables: Customer, ContactLineItem, and Proposal.

In ContactLineItem, there are two fields: ContactType and Number (ex. data: Job Site, 555-5555)

In the Proposals layout, I want to create a lookup field that will look at the related records via customerID and copy from the ContactLineItem the telephone number if the type equals "Job Site".

How do I do this?

[Peter (duksis3)]

I cannot very clear imagine your task.

Do you want to lookup from 2 tables to table proposal?

[comment]

Are you sure you want to use a lookup here? What if the contact's job site number changes - shouldn't the number in Proposals reflect the change?

[ViciousTruth]

Are you sure you want to use a lookup here? What if the contact's job site number changes - shouldn't the number in Proposals reflect the change?

Comment: The job site phone number may change in the future, but the Proposal needs to reflect the job site number at the time it was created.

I've been thinking about this and I think the problem is that I am trying to accomplish it in the wrong way. I think what I want to do is write a script that, upon creation of a proposal, will set the fields on the proposal with the data needed based on the address and contact records available.

Thanks for the consideration.

[comment]

Well, you could do a lookup - but you would need another relationship for this, one that is filtered so that only "Job Site" types are related.

Another option is to auto-enter a calculated value - see a very similar issue here:

http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=34180#M34180

[ViciousTruth]

Well, you could do a lookup - but you would need another relationship for this, one that is filtered so that only "Job Site" types are related.

Another option is to auto-enter a calculated value - see a very similar issue here:

http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=34180#M34180

Comment,

I'm trying to understand what that custom function does and then apply it to the problem I have... could you elaborate on how you would implement this (CorrespondingValue)?

[comment]

The function is very similar to the VLOOKUP function in Excel: it finds a given value in one column and returns the value from the same row in another column.

In this case, you would look for "Job Site" in the 'column' produced by List ( Phones::Type ) and return the corresponding value from List ( Phones::Number ). Since List() always follows the sort order of the relationship, these values would come from the same record (assuming there are no empty values in preceding records).