Benefits of Lookup Function

[Rich K.]

Hi --

Lets say I have 2 tables an Employee Table and a Department Table. The Employee has the following fields (pkEmpID, fkDeptID, Name, Title) and the Department table has the following fields (pkDeptID, DeptName, DefaultTitle).

Assuming I have Table Occurences the same name as the table and have the 2 TOs joined by fkDeptID.

What are the benefits of having the following:

Employee::Title ... Auto-Enter calculated value Lookup(Department::DefaultTitle)

over

Employee::Title ... Auto-Enter calculated value Department::DefaultTitle

:)

[comment]

What would you like to happen if an employee is re-assigned to another department?

[Rich K.]

I'd like for the Title to change to the DefaultTitle of the newly assigned dept.

[comment]

Why not use a straightforward lookup then? In terms of functionality, all three will behave the same in this situation (provided the auto-enters are set to replace existing value). When using the Lookup() function you get the option to specify a failExpression - though you could do the same using Case(), for example.

[Rich K.]

Thank you for your insight ... besides the fail expression functionality, I really don't see any benefit from using Lookup().

It would be really great if the Replace Script Step would honor this calculation; now, that would be a real game changer!

[comment]

It would be really great if the Replace Script Step would honor this calculation

I'm afraid I have no idea what you mean by that.

[Ashton]

It might be just me, but it does seem that auto-enter lookup is slightly faster than auto-enter calculation—even when using the Lookup function itself. The overall difference in data entry speed and responsiveness is significant enough to me, that I always try to use the former in place of the latter whenever possible. Anyone else have this experience?

[comment]

It's best to test such things thoroughly rather than rely on subjective perceptions. A quick (and rather informal) test I performed importing ~100k records shows lookup to be about 14% faster than auto-enter.