Filter a lookup table based on a date in the current record?
Hi:
I'm working with staff / managers and activity tables. Each staff --> multiple activities. Let's say I want to enter an activity for last week, but this week one of my staff was promoted to a manager. The new manager shows up in my activity form under "managers" not under "staff" because the lookup field provides the staff table filtered on current status rather than on last week's status. And, let's say I go to edit one of the prior activity records, my new manager starts causing problems by not really being a proper staff person anymore, even though he was a staff person at the time of the record entry.
I've tried multiple things but can't seem to hit on the right solution. For example, I've made an "assignments" table with start and end dates. Staff with multiple assignments and activities with multiple assignments. Then tried a conditional drop-down based on the date field, but it doesn't give me a 'between' two dates, it only gives me a choice to match dates exactly... Surely I'm not the first person to try to do this. I imagine this can come up regularly in a business situation, whether people move departments or get promoted, or move locations. You don't want their new data showing up in older records and making changes there. Any ideas?
Dave
------------------------------
Dave Halter
------------------------------
I'm working with staff / managers and activity tables. Each staff --> multiple activities. Let's say I want to enter an activity for last week, but this week one of my staff was promoted to a manager. The new manager shows up in my activity form under "managers" not under "staff" because the lookup field provides the staff table filtered on current status rather than on last week's status. And, let's say I go to edit one of the prior activity records, my new manager starts causing problems by not really being a proper staff person anymore, even though he was a staff person at the time of the record entry.
I've tried multiple things but can't seem to hit on the right solution. For example, I've made an "assignments" table with start and end dates. Staff with multiple assignments and activities with multiple assignments. Then tried a conditional drop-down based on the date field, but it doesn't give me a 'between' two dates, it only gives me a choice to match dates exactly... Surely I'm not the first person to try to do this. I imagine this can come up regularly in a business situation, whether people move departments or get promoted, or move locations. You don't want their new data showing up in older records and making changes there. Any ideas?
Dave
------------------------------
Dave Halter
------------------------------
