Hi everyone,
I’ve been working on a project in Glide since yesterday and could really use some help as I’m stuck in something that I think should be easy with code (filters, ifs, ands, etc) but seem to elude me in no code 
.
It’s only selecting the correct rate based on the days of the hours logged in a project.
I’ll try to explain my setup as clearly as possible.
Setup:
- Members Table:
• This table lists the members involved in the project.
• Each member has a unique ID (e.g., member001, member002).
• It includes columns like the member’s name and their ID.
- Hourly Rates Table:
• This table tracks the hourly rates for each member, as the rates can change over time (the more experience they have in the project, the higher their rate).
• Each row has a unique ID for the rate change (e.g., RVrate001, RVrate002).
• The table also includes:
• Hourly rate (the rate during that time period).
• Start date (when the rate becomes active).
• End date (when the rate is no longer valid).
• Member ID (to link each rate to a specific member).
- Hours Logged Table:
• This table logs the hours worked by each member on specific dates.
• Each log entry has a unique ID (e.g., log001, log002).
• It includes:
• Date of the log (the day the work was logged).
• Member ID (to link the hours to a specific member).
• Hours worked on that day.
Goal:
I’m trying to calculate the total contribution for each member by multiplying the hours logged on a specific day by the correct hourly rate that was active for that member on that specific date. For example, if a member worked on July 3rd, I want to use the rate that was active for them on July 3rd.
Issue:
I’m having trouble getting Glide to match the correct hourly rate based on the date of the log. I’ve tried using if-then-else statements, lookups, and relations, but I can’t seem to filter and find the correct rate from the Hourly Rates Table for the specific date when the hours were logged.
Has anyone dealt with something similar or knows how to approach this in Glide? Any advice would be greatly appreciated!
Thanks in advance for your help!