Forum Discussion
Eric_J_Hansen
8 years agoQrew Cadet
Ok, then here is what you could do: in the relationship between personnel and personnel assignments, create a summary field that will summarize the minimum of the record id# with a filter that tells you that it is an active job (from the personnel assignments table fields). This summary field will give you the record id# of the first active job assigned to that person, so if there are 2, and the earlier one is correct, we can use this record id# to display that job information on the personnel record. (You could do the inverse of this and do maximum of the record id# and always return the newest active job).
Now that we have that record id#, we can build a reverse relationship between the personnel and personnel assignments table, this time where a personnel assignment can have many personnel. (This is an action not performed very often and can seem complicated and counter intuitive.) When building this relationship, click the dropdown that would current be populated with something like 'related personnel assignment' and replace it with your minimum or maximum record id# field. Once you've created this relationship, you can then create all the lookup fields for that personnel record with all the job data found on that personnel record.
Let me know if you need any help with this. My contact info is in my profile.
Good luck!
Now that we have that record id#, we can build a reverse relationship between the personnel and personnel assignments table, this time where a personnel assignment can have many personnel. (This is an action not performed very often and can seem complicated and counter intuitive.) When building this relationship, click the dropdown that would current be populated with something like 'related personnel assignment' and replace it with your minimum or maximum record id# field. Once you've created this relationship, you can then create all the lookup fields for that personnel record with all the job data found on that personnel record.
Let me know if you need any help with this. My contact info is in my profile.
Good luck!