Auto-Enter Calc to enter data from other file

[cbum]

I'm trying to conditionally enter the content of a field ("Sample") from another file ("Specimen"), based on the presence of values in other fields ("Row", "Column") in the other ("Specimen") file.

This is my current attempt in the auto-enter calc dialog box:

IF(Specimen::Row = "A" and Specimen::Column = "1", GetField(Specimen::Sample), 0)

but it's not finding the entry in the Sample field...

Any help would be appreciated.

[Ugo DI LUCA]

Hi,

Welcome to this Forum.

There's not much I can say given that we don't know the relationship you're using.

You could possibly have it done by a lookup too, or by a simple calculation. We could even use the Is Empty ( ).

All these methods surely depends about your exact need, but we need to know the exact relationship structure (left and right keys).

[cbum]

Ugo,

I'm not sure what you mean with left and right keys, but the relationship I created between the 2 files is based on the value in a field common to both files ("Box")....

I don't see how to do this with a lookup, since that copies the "Sample" field regardless of the content in the "Row" and "Column" fields, which I'm using as conditional check.

Hope that makes some sense.

[Ugo DI LUCA]

Hi,

The Left key is the key from the left side of your relationship (Main file I assume), while the Right is obviously....the right one (Related).

An auto-entered calculation would be filled at creation of the record, but then the "Box" field will always be empty I think, thus leading to a not valid relationship.

You can lock or limit lookup using a calculation that will display or not a matching key.

Case(IsEmpty(thisfield), this key, no key)...

Now this doesn't solve your querry as long as you're using it as an auto-entered calculation, except if, tediously, the "box" field is also auto-entered, which I assume isn't the case.

[cbum]

I had entered the "box" field manually, but if the calc only occurs at record creation, it's not what I need.

I had originally wanted to do it with a lookup, but I don't understand how to make the lookup conditional, i.e. if it finds a match in the "Box" field, it copies the "sample" field over, regardless. How do I make this conditional on a test, like presence of a value in the "Row" and "Column" fields?

Can you elaborate on "You can lock or limit lookup using a calculation that will display or not a matching key.

Case(IsEmpty(thisfield), this key, no key)..."

Where in the setup of the lookup do you put the Case .... condition?

[Ugo DI LUCA]

Ok so now it's clear that the subject title of this thread was inapropriate.

"Auto-Enter Calc to enter data from other file" vs "Auto entering data from another file based on some conditions"

How do I make this conditional on a test, like presence of a value in the "Row" and "Column" fields?

The data currently should look like :

Sample File

Main Match_Field

Field To Set

Specimen File

Related Match_Field

Field To Get

Column_Field

Row_Field

Current Relationship : Main Match_Field::Related Match_Field

A lookup will occur and set 'Field To Set' if there's a valid relationship Main Match_Field::Related Match_Field.

Therefore, if you want to lock the lookup, you'd need to alter the Match Field so that there would be no valid relationship.

In order to do this, you'd create another lookup key next to the classic 'Related Match_Field'. It would be a calculated key which result would change according to your conditions.

New Relationship : Main Match_Field::c_ConditionalKey

where the c_ConditionalKey could be defined as :

1. If you don't need this conditional anywhere else in your solution (script, other calcs )

Case(not IsEmpty (Column_Field) and not IsEmpty(Row_Field), Related Match_Field, " ") - indexed.

or better :

Case(not IsEmpty (Column_Field) and not IsEmpty(Row_Field), Related Match_Field, Related Match_Field& " 0") - indexed. - note the space " 0" !

2. If you need to check for this condition for some other settings, then I'd suggest another small change.

Related Match_Field

c_boolean = not IsEmpty (Column_Field) and not IsEmpty(Row_Field) - indexed.

c_Conditional_Key = Related Match_Field&"_"& c_boolean - indexed.

To be complete....

You could as well have altered the Main Key rather than the Related Key. You'd therefore change the relationship to a new :

c_LookupKey::Related Match_Field

where c_LookupKey = Case(not IsEmpty(Current Relationship::Column_Field) and not IsEmpty(Current Relationship::Row_Field), Main Match_Field, Main Match_Field & " 0")

This later solution would be used if the result of your calculation in the related file couldn't be indexed, because referencing another relationship, a global, another unstored calc,...

HTH

[cbum]

Thanks for your help.

Sorry for the delay, it got pretty busy around here.