Conditional lookup based on a relationship

[terron]

I have a table that i have displayed as a portal for users to enter default rates for specific job descriptions. For each of these jobs i have a field for hour_rate, day_rate and week_rate.

Now i would like the user to be able to set these rates once and have fields in another table lookup these values.

Here is where i run into trouble. If i just do a lookup based on the relation of job description, i can only pull one of the rates because i have to choose a field to look up. I would like the user to be able to set the job and the rate type (hour, day, week) and have the rate calculated based on a relation. I don't know how to set up this needed calculation and/or relationship.

Can anybody help me?

Edited October 5, 2006 by Guest

[Fitch]

Use an auto-enter calculation rather than lookup, and UNcheck the "do not replace" checkbox; use this case statement for the auto-enter calculation:

Case(

type = "DAY" ; related::day ;

type = "WEEK" ; related::week ;

related::hour )

[terron]

Thanks for your help. I tried this but kept running into an error. It does however get me very close.

What i find is that i only get the rates that are associated with the first record of the related table rather than the rates from each different type of work.

For example: If i enter "compositing" in vfx description i get the associated rates for "compositing" based on the rate type (i.e. $75, $200, $300). Then if i enter "animation" on the next line of the repeating field, i still get the rates associated with "compositing" based upon the rate type.

I know i can change the repeating field to a single field and use a portal here as well but i like the way the repeating field handles the summary / total for the field and across the DB.

[bruceR]

It isn't working because you are using repeats. You need to get rid of the repeating fields.