create a flow to update status of list item based on multiple variables on another

I have 2 SharePoint lists: list one called ‘tech status’ has a list of all my technicians including a certification status column with multiple selections (good standing, expired, about to expire) to show the overall status of their certifications. List two has all of their certifications with tech name, acquisition dates, expiration dates, etc. The technicians have multiple certifications on list two which some will be in good standing and some will have expired. What I’d like to do is create a flow that if the technician on list one has any expired certificates on list two, to update list one status column to ‘expired’. Also to update the status on list one to ‘about to expire’ if any certifications will be expiring within 30 days. Also to update list one status to ‘good standing’ if there are no expired certificates for that technician. Again, this is just an overall status update. If any certificate on the list is expired that would be first, then about to expire, etc. I don’t need the actual certificate that is expired, I just need the status to change. I will have a link on list one where the user can follow to see the certificate that has expired.

This may have to be done in a certain order good standing first? Then about to expire? Then expired so that expired certs aren’t even overwritten by the other statuses.

i would greatly appreciate any assistance on this.

Quick Links

Leaderboard > Power Automate