Forum Discussion
Create relationship: 1 date from 1 table being between 2 dates in 2 columns in another table
I found this thread. Pretty helpful in walking you through the processs in how to expand a table based upon two different date fields. Only thing to add is to change your field names you're referencing in the formula!
Pretty easy.
Thank you for the quick response.
I'm not sure I understand exactly what you mean though.
You're asking me to create a new row for each date between the Eff Date and Term Date for each existing row?
If that is the case...for example this one Business has roughly 100 members. One member has an eligibility range 3 years. So that one expansion would grow to 365 x 3, or 1095 rows...for just that one member.
Did I interpret that correctly?
I should also note that one member can show up in the eligibility table more than once...meaning they could have had coverage for a year...quit, and then came back two years later to have coverage for another year and were fired. So they would show up twice in the table as having two serarate rows initially.
Community ChampionGenerally when you are working with date-ranges the easier way to work is by expanding each range to include a row for each day in the interval. This ususally works really well when you have a database of project or similar with start and end-dates.
The issue with this is that you will increase the amount of data dramatically, so it really depends on how much data you're starting out with and how long the intervals are.
There is another way to do this which would mean that you create an inactive relationship to Term date and then use USERELATIONSHIP() to get the Term date which you can then use as a filter in calculations. But this can get messy quite fast and since you can have more than one instance for each person in the eligibly table I'm seeing quite alot of issues already! 🙂
Either way it doesnt hurt to try anound and see what works!
Br,
J