I'm trying to input information in a field based on values in 2 different fields (ie. If this and that then set field to this). Each of the fields is in a related table, but not necessarily related to the current record (the information is probably in the database, but may not be in a related record).

Any ideas?

A bit more info about the field would help

You may need an if or case statement

Case ( Field1 = x ; Field2 ; Field3 )

Case ( Field1 = x ; Field2 ; Field1 = y ; Field3 ; "default" )

If ( Field1 = x ; Field2 ; Field3 )

I want the field to be populated with a value that can (hopefully) be found in a related table based on 2 other fields in the originating table (also to be found in the related table). These values can be found in a related table, but not necessarily in a related record (ie. their in there somewhere, but not sure where).

Hi Popp, welcome to the forums.

You're question isn't makinga lot of sense to me. Somewhere in the related table, but not necessarily in a related record? That statement makes no logical sense to me.

Could you please expand further and we'll try to help more.

To refresh, a related record is one that has the same information in the related fields. In this case, the 2 pieces of information are in the related table, but do not have a Primary Key value that is the same. I'm sure I'm overlookign something easy, but...

So you want FileMaker to pick two random records in the related table?... because while the table is technically related, there are no records in that table that relate.

For instance, Related::Origin = OtherFile::Origin and Realted::Destination = OtherFile:Destination and therefore set field Other to Value. Value is contained in the related table, but may not have the same Primary Key.

Your tables are related Primary Key to Foreign Key, not Primary Key to Primary Key so it's not an issue.

The "Foreign Key" is simply whatever you define it to be, in the case i suggest below you are using the two otherfile fields as a primary key together and the two related keys as the foreign key.

Why don't you just literally set the above relationship:

OtherFile::Origin = Related_Table2::Origin

OtherFile::Destination = Related_Table2::Destination

Script:

If[ not IsEmpty( Related_Table2::Value )]

Set Field[ OtherFile::Value ; Related_Table2::Value]

End If

There are two fields that need to be compared in the primary table to the foreign table. These determine the value of a third field in the primary.

... Yes, so relate both feilds?

Hi ThePopp1,

So far, you've provided ONE SENTENCE responses in abstract-speak basically repeating your request but not really providing new information. That isn't going to work. Simply, you need to provide specifics, ie, what is the context; what are the field and table names involved; what is the criteria in the other fields which needs to make the match, etc. Example:

'I'm in Contacts and I want the third Invoice from a different Contact but only an Invoice if it was taken on Thursday and it's category is BLUE. They are related on ContactID using = so that won't work. I need blue invoices on Thursday to display in this current Contact but only if it's the same sales rep.'

So drop Origin, Primary and Destination. We need to understand the VISION of your need, okay?

LaRetta

Nicely put LaRetta.

Origin and Destination ARE the names...

If someone wishes to scream at me, it will be my wife...

Don't flatter yourself - no one is screaming at you. Perhaps your wife can understand your description of the problem. I sure can't, and I am the fourth person here to tell you so.

Origin and Destination ARE the names...

If someone wishes to scream at me, it will be my wife...

Popp1,

Nobody is screaming at you; especially Laretta. Its not really her style. :-)

Anyway, as being a newbie on these forums, you have done what most newbies do when they first post on here. They give a general question without details and just want an answer. However, the reason that we ask for more information about the process, how it is expected to be executed, etc, etc, etc. is that many times, a newbie with FileMaker will approach the problem in the wrong way and ask the wrong questions the first time. Those that are more familiar with FM will be able to guide them in the right direction but can only do that when all the details are given.