lookup calc (?)

[osiris612mb]

In our database we are going to have these two tables Codelist (with code and status, ....) and TimeEntry (with code) We want the user to enter in the code number within the TimeEntry table which will do a lookup to the Codelist table and bring over info about that code. BUT IF status is "I" for inactive, we want an error message to appear stating that the code is invalid, and we want none of the related data to be looked up.

Does this make sense? Ifso how would we go about doing this?

[Ugo DI LUCA]

Hi,

This code is valid for a relationship. But if you switched to a calculated code as the lookup key, then it won't according to your If statement.

[osiris612mb]

ok this is what I was able to come up with

[color:"blue"]

If ( FMPcodeslist::Tabs status "not"= "I" ; "Client_Code" ; "This Client Code is invalid. Please enter in correct Client code, or contact the Business Department" )

[color:"black"]

Will this keep the client_code in the field if the tabs status is empty.and give an error message if the tabs status is "I"???

or should I use a case function with isEmpty?

And if the code I have above is correct where should I put it, in autoenter or validation? because I have tried it in both locations and neither have worked.

Sorry~~~I am a newbie and some simple functions just go over my head.

Thanks

[osiris612mb]

Ok i thought I figured it out, but it still doesn't seem to work.

I have also tried IsEmpty() and that doesn't work either, should I attach my files so someone can look at it?

Thanks

[Ugo DI LUCA]

May be...although my answer was focused on how to prevent a lookup and didn't answered your need for a pop-up message, which probably should be event triggered involving a plug-in.

[osiris612mb]

Attached is the file, with sample data. Please let me know if any further explanation of our needs is necessary.

I am now using this code for Client_Code and it is under the AutoEnter Calculation.

Let ( [

strip = Filter ( FMPcodeslist::Tabs status ; "I" ) ;

invalidresponse = "Invalid Code: " & TextStyleAdd(Client_Code; Bold);

validresponse = Client_Code;

response = Case( IsEmpty(strip); validresponse; invalidresponse) ] ;

response )

This enters Invalid Code: and what ever code the entered so they know that the code is wrong, obviously. But it still brings over the other looked up fields.

UGO had said it would prevent a lookup but it doesn't seem to work.

Am I missing something small? Or is what we want to happen, not feasible??

lookup.fp7.zip