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

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:


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!

@Keith_Guerrette

Maybe this will help.

1 Like

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

@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