Multi Step Case Statement

[Joseph31]

I know how to make a case () statement, but I need some help doing a more complicated one.

I can easy do one with the case statement looking up one (1) value and returning another.

But how do you do one where you have to look up two (2) values.

Example:

Company_Name = Widget & Son

Contact_Name = Joe

Return value = 1156 Wood Street

Vs

Company_Name = Widget & Son

Contact_Name = Tom

Return value = 1200 Water Ave

Both have the same company name but different contract name therefor different address.

Thank you,

Joseph

[Fitch]

You gave an example of the input, but what is the output that you want?

[Joseph31]

Example:

Company_Name = Widget & Son

Contact_Name = Joe

Return value = 1156 Wood Street

or

Company_Name = Widget & Son

Contact_Name = Tom

Return value = 1200 Water Ave

or

Company_Name = XYZ & Gears

Contact_Name = Sue

Return value = 3000 Ave

Both have the same company name but different contract name therefor different address.

Need a case () Statement that can work off this example:

Ie: Case ("Widget & Son" if "Tom" return "1200 Water Ave" if "Joe" return "1156 Wood Street";

Case ("XYZ & Gears" return "3000 Ave")

Not sure how to write a 2 step case statement.

Joseph

[Anuviel]

Case ( test1 ; result1 {; test2 ; result2 ; ... ; defaultResult} )

Case (

Company_Name = Widget & Son and Contact_Name = Joe ;

Return value = 1156 Wood Street;

Company_Name = Widget & Son and Contact_Name = Tom;

Return value = 1200 Water Ave; "")

I believe something along these lines. It probably can be written better but the above should work.

This case will return "" if the above conditions for company name and contact name are not correct.

Edit: You will have to hard code as many entries as you have different companies and contacts for the case statement to work. If I am not mistaken there should be a better approach to what you are trying to accomplish.

Edited October 8, 2008 by Guest

[comment]

What has a Case() statement to do with a lookup?

[David Jondreau]

If you're doing a Lookup (copying data from a related table), it's not a Case() statement you need, but a different relationship, one that takes into account the name of the contact.

[Joseph31]

Well it's not really a look up that I need because -

What I have is company names with there contacts. Contacts for each company can be the same across different companies ie: XYZ Company can have a "Bob" Working for them and Wigets Company can also have a "Bob" also working for them.

What I need is to know how to do a multi source case function. The case functions works well for me on one level. Meaning if I only have the company name to look up it returns the correct value, it returns a address. But when I introduce the second variable (the name of the employee) it all does not work. The reason I need or think I need a case function is that some companies have just an address they want me to use each time and others would like the address to be associated with the employee. Is there a way to get this done?

Thank you,

Joseph

[Anuviel]

I would say data separation might work. One table for company, contacts and so forth.

Each company should have one record. Each contact should have one record. One company can have multiple contacts but contact cannot have multiple companies Unless you have Bob working for two companies at the same time...

From that setup you then filter the needed data via relationships.

That is as far as my knowledge of FM takes me, hope it is correct, sorry I cannot be more helpful.