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

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.

  • 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. – Todd1561 Apr 12 '18 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/… – CodeCrafterTroy Apr 12 '18 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.