Looking up Rate for Timesheet Entries based upon Project & Employee Rates

1

I’m struggling with a concept that I suspect is quite straight forward and hope you guys can help:

I’m attempting to recreate our invoicing system from google sheets for my team of artists - we charge our clients based upon the title of the artist, at the various negotiated rates for the project. This means a two axis “lookup”.

Here’s my mock-recreated data set:

image
image.png1914×1561 180 KB

The Name and Project columns in the Test-Timesheet table are look up columns from the other tables.

My Current Understanding of Lookups
If I’m understanding this correctly, the look up is returning the data of the entire row, meaning that referencing Project.Junior would return the Junior rate. I’ve done this successfully for the Title column by using Name.Title.

My Problem
My issue is that I don’t know how to reference a variable column - in the timesheet above, I need to look up the column within the Project lookup based upon the Title of the artist who performed the work. Can someone guide me on how to query this data correctly?

Thanks!

2

@Keith_Guerrette

Maybe this will help.

lookup_rate_timesheet_2019-01-06_1457
lookup_rate_timesheet_2019-01-06_1457.png874×778 26.8 KB

1 Like
3

Thanks! I suppose constructing a long switch statement would work, but doesn’t scale well when I have n types of titles/positions.

You’ve given me a way to move forward for now, so I’m very grateful - it just seems like an in-elegant solution for how beautiful the rest of Coda is. Is there no way to look up a column whose name is defined as a cell entry? I.e. use the output from a string entry as a variable name to look up?

1 Like
4

@Keith_Guerrette

I and quite a few others I’ve seen have run into this. Coda is so awesome that one is reasonable in expecting it to do all things awesomely!! :rofl:

Anyway, I had this down on my list of submissions for the team, but I’ll drop it here since you insist :innocent:. This would be sweet :point_down:

@alden @mallika

2 Likes
5

It’s been half a year, and I know there have been a huge variety of updates (i’m still parcing through some of them), so I thought I’d ask the community - is there an easier way to do this yet?

6

@Keith_Guerrette,

I think if you change your table structure a bit, it would feel elegant :slight_smile: - here’s an attempt

1 Like
7

That definitely solves my scalability problem - thank you!!!