1

I'm using SharePoint Online via Office 365. I have 3 lists:

  • Employees
  • Training Certifications
  • Training Cert Types

The 3 lists are linked together via lookups. The "Training Certifications" list has a field that's an employee lookup tied to the "Employees" list and another field that's a lookup to the "Training Cert Types" list. The usage would be a user adds a new entry into the "Training Certifications" list and in the process selects an employee and a cert type from their respective lookups. I want to be able to generate some kind of report, a view on the "Training Certifications" list would be ideal, that can show me all the cert types an employee has (this is easy) as well as cert types they do NOT have (this is the question). Keep in mind, it's possible for an employee to have no certs and therefore not even exist in the "Training Certifications" list. This report would then show them multiple times, one for each cert they are missing.

If this were in SQL I could do it in about 60 seconds, but I'm not finding a way to effectively "join" these lists together to get the information I need. Is this possible?

For SQL people here's some pseudo code of what I'm looking for:

select * from
[Employees] e full outer join [Training Cert Types] ct on 1=1
left join [Training Certifications] tc on tc.ID = ct.ID and tc.Employee = e.ID

Thanks

Improve this question

1 Answer 1

Reset to default
0

There is no quick easy way to achieve the report you're after, but there are a lot of not so quick, less easy ways.

I would lean toward making a SharePoint Framework web part that queries the 3 lists and then does the "joins" in code, then you have maximum flexibility over displaying the report and could make it far easier to read as you would be able to combine the multiple entries per employee.

If you aren't comfortable with a coded solution and you are able to spend some money on a 3rd party solution, Axioworks has a product called SQList that lets you query lists like SQL tables. I haven't used this product myself, but have heard good things from a colleague.

Improve this answer
2
  • I'll probably look into your first option, if you know of any resources for that feature let me know. The SQList product looks like it's just replicating data to a local SQL server. I am currently doing that with in-house code for other projects but for this I'd like to keep it all on SharePoint. Thanks for the insight. Commented Apr 12, 2018 at 11:51
  • The best place to start for SharePoint Framework is the official Microsoft tutorial. docs.microsoft.com/en-us/sharepoint/dev/spfx/web-parts/… Commented Apr 12, 2018 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.