Model driven app view that shows the last record for each Employee

[pepeday]

Hey there.

What I'm trying to do is monitor certain training certificates we issue for each Employee. There are two tables for this. One is [Employee] and the other is [Certificate]. Each employee has one certificate added for them in the [Certificate] table, each year (with the Employee being a lookup field in the [Certificate] table).

The important thing is to make sure the each Employees' certificate does not expire so I'd like to have a View that shows the latest certificate date for each employee.

One option would be to update a field in [Employee] when saving a new [Certificate] record, but this would only give me access to the date but not the content of the Certificate record.

Is there a way to create such a complex view?

Thanks,

Pepe

[Prakash4691]

Hi @pepeday ,

Do you want to create a view in employee table with certificate table columns?

I don't think so it is possible but we can have other way around. In certificate table we can have a view with employee table columns in it. 

If it answers your question, kindly give kudo and accept it as solution.

Regards,

Prakash

[pepeday]

Let me explain it better. One employee may have a number of certificates created in the [Certificates] table.

I want to create a view that will only display the last certificate for each employee.

[Prakash4691 · Accepted solution]

@pepeday ,

Create a new field called 'Latest Certificate' as yes/no option in certificate entity and every time while creating new record update that field.

Update through power automate flow have trigger as create of certificate record. Also add an action to get all the certificate records of an employee where 'Latest Certificate' equals 'yes'. Employee value will be retrieved from trigger step. If there are any record found, update the 'Latest Certificate' to no.

Create a new view in certificate table where 'Latest Certificate' eq yes. That will only display one record for each employee.

If it answers your question, kindly give kudo and accept it as solution.

Regards,

Prakash

[pepeday]

It's definitely one solution. Do you think there's a way to do this through a calculated field?

[Prakash4691 · Accepted solution]

@pepeday ,

Calculated field on employee table? No, it is not possible because of 1:N relationship with certificate table.