Lookup by Calculation?

[Courtney]

I thought this would be a simple thing to do, but it's not working as expected, so I'm looking for a little feedback.

I've got four "levels" of staff for billing purposes, stored in a staff table with other HR type information. Each client has a designated rate for each of those levels, which is stored in a client table. My "timesheet" table looks up the billing level for each record based on the employee code. I'd like it to lookup a billing rate for each record from the client table based on the billing level. (I have a relationship from the timesheet table to the client table, based on a unique identifier for each client/project.)

I have tried a calculation field, and also a number field with an auto-entered calculation. I started with a nested if, but when that didn't work, I left out all the nesting to try to trouble-shoot, and just went with a straight, single if like so:

If ( Billing Rate Level = "Executive" ; Client::Executive Rate ; 0)

With this formula, I get 0 when I should get 0, but I get nothing when the result is true with "Executive" being the billing level.

Ultimately, I want to end up with something like:

If ( Billing Rate Level = "Executive" ; Client::Executive Rate ; If ( Billing Rate Level = "Technical (Ph.D.)"; Client::Default Rate; If (Billing Rate Level = "Technical (Non Ph.D.)" ; Client::Technical Rate Non PhD ; If ( Billing Rate Level = "Administrative"; Client::Administrative Rate; 0) ) ) )

Any thoughts or guidance on this?

~Courtney

[-Queue-]

Unless you have multiple client records for a single client, it seems like your calc should work. I would ditch the nested Ifs for a Case statement though.

Case( Billing Rate Level = "Executive"; Client::Executive Rate; Billing Rate Level = "Technical (Ph.D.)"; Client::Default Rate; Billing Rate Level = "Technical (Non Ph.D.)"; Client::Technical Rate Non PhD; Billing Rate Level = "Administrative"; Client::Administrative Rate; 0 )

[Slobey]

I am a little confused about the first part of your question. The billing rate level is in the Time sheet table?. How are the 3 tables related? If you could post a file it would be easier.

But it looks like the "nested" if statement should be a case statement

Case(

billing rate level = "Executive"; Client::Executive Rate;

billing rate level = "Technical(Ph.D.)"; Client::Default Rate;

billing rate level = "Technical(Non Ph.D.)"; Client::Technical Rate Non PhD;

billing rate level = "Administrative"; Client::Administrative Rate;

0)

[Courtney]

Well, I made a clone to upload, and then added a few sample pieces of data to it... and it works in the clone, but not in my original file.

So I'm perplexed and will keep digging, but it's obviously not a problem with my formula or with the way the field is defined.

(I did change it to a Case statement, rather than a nested If. Still having the same issue in the original file though.)

~Courtney