Help with LOOKUP calculation

[Baylah]

Greetings,

I have Lookup Calculation where the 1st condition of the If statement works perfectly but the fail) condition/result is ignored. I have tried this as a case statement where there is not/= operator in the second statement but it does not work either.

If someone has a moment to take a look will you let me know where my logic/syntax is wrong?

If(CurrencyExchangeRate::Abbreviation=CurrencyExchangeRate::Abbreviation; 



"The" & " " & GetAsText ( CurrencyExchangeRate::Region ) & " " & "currency rate was last updated" & " " &   Lookup(CurrencyExchangeRate::Updated);



"Please navigate to the Currency Exchange table and enter the Proper Values for this currency"

An example of a return when the condition is met looks like this:

[color:blue]The <> currency rate was last updated <<1/4/2007>>.

Data in << >> indicates information pulled from related fields.

If the statement fails because there is no match on "Abbreviation" my result looks like this:

The currency rate was last updated ?

Any help would be greatly appreciated.

Thanks,

Steve

[comment]

Your condition is a tautology: a thing is always equal to itself - even when the thing is not.

Try testing for the existence of matchfield value in the CurrencyExchangeRate table.

[Phillip Holmes]

Hi Steve,

Are all fields RELATED?

If they are you might not need to use the "lookup" function to get the data.

Something like this

If

(

// Test

CurrencyExchangeRate::Abbreviation=CurrencyExchangeRate::Abbreviation ; 



// Result if true

"The "& GetAsText ( CurrencyExchangeRate::Region ) & " currency rate was last updated " & CurrencyExchangeRate::Updated ;



// Result if False

"Please navigate to the Currency Exchange table and enter the Proper Values for this currency"

)

Cheers

Phillip

[Baylah]

Your condition is a tautology: a thing is always equal to itself - even when the thing is not.

Hi Comment and Phillip,

Thanks for the reply....

Tautology...now there is a word I havene't heard in a while!

I suspected this was the case but I did not know how to phrase my request properly.

Philip,

I cut and pasted your code into my solution but it did not work. The matches worked (just like in my code) but the fails still produced an inaccurate return.

The currency rate was last updated

(no question mark now at the end as mine returned But the extra space is still between THE and CURRENCY"

Any othe suggestions? The tables are related...If they weren't the matches wouldn't give a return either would they?

I'm going to play around a bit to see if I can nail this. I was not aware of TEST and need to look in to this.

Thanks for your help guys,

Steve

Edited August 4, 2008 by Guest

[Baylah]

I got it! :

I needed to test against the two tables, not the tautology I had created per Comments, comments (lol). As always, the fix was easy once I knew where to look...thanks guys!

If

(



CurrencyExchangeRate::Abbreviation=WorkingTemplate::Currency; 





"The "& GetAsText ( CurrencyExchangeRate::Region ) & " currency rate was last updated " & CurrencyExchangeRate::Updated ;





"Please navigate to the Currency Exchange table and enter the Proper Values for this currency"

)

Steve

[comment]

I needed to test against the two tables

I don't think so, but it's hard to tell the context - i.e. which table are you in, and what is its relationship to CurrencyExchangeRate?

[Baylah]

I think I just stated what I did incorrectly....

I'm not sure how to say it better, but...I needed to compare the match field of one table against the match field in the related table instead of comparing the field against itself as in my original attempt.

Thanks Comment...your instructions were perfect.

Steve

I changed the name of the matching field in the WorkingTemplate related table to also be abbreivation...it mght make more sense now:

If

(



CurrencyExchangeRate::Abbreviation=WorkingTemplate::Abbreviation; 





"The "& GetAsText ( CurrencyExchangeRate::Region ) & " currency rate was last updated " & CurrencyExchangeRate::Updated ;





"Please navigate to the Currency Exchange table, create a new record and enter the proper values for this currency."

)

Edited August 4, 2008 by Guest