Is there a way to have the sum function only calculate when the match field IDs are the same?
The four tables in question are: Personnel Records, Employment History and Entitlements and Administrative TO-DO. In my Admin to-do I have a portal which calls upon all sorts of tables effective dates and when they come into a certain range of the current date they appear in the portals as to do tasks. My trouble is this:
I want my entitlements to show up one month before they need to be updated to give my administrative team time to take all the appropriate actions that comes with entitlement increases. At this moment I cannot create a proper relationship because eligibility for entitlement increases is based on a calculation of years of service which cannot be indexed. I have a calculation field in my personnel records table that sums all the work days an employee has worked with us. This calculation calls on the employment history record linked to the employee in question and sums the days the between the start and end dates (if end date is empty it takes current date). Several of our employees are seasonal which is the reason for this calculation. My entitlement records then have a calculation field that takes the required years of service, subtracts current years of service, multiplies by 365 and then adds this to the current date to produce an "Effective Date" for the entitlement increases.
As you know, I can't use the Effective Date in a relationship because it's calling on all sorts of fields from all sorts of tables and cannot be indexed. One solution is to amalgamate Employment History and Entitlements but I don't know of a way to make the sum function "smart" and bring it into the Employment History table instead of assessing from the Personnel Records.