How to collect issue tracking data in sharepoint

Question

**EDITED for clarity

I'm in the process of building an internal tool using Sharepoint, Flow and PowerApps to track issues with our various vendors/service providers for our customer.

The scenario is this: The company has various vendors, delivery agents and service providers. We also have retail locations and project coordinators who help a customer's project along. For orders were issues go wrong, we could have multiple types of errors from multiple sources.

I'm trying to find the best way to do this. So far I've come up with two.

The first option has the employee select what sort of issue each party was responsible for. This is stored on SharePoint as a text string, and a calculated column will look for sections of that string to see how often they show up. This allows me to assign a "Score" to each party. The user would only have to enter the type of party responsible and the issue, as their choices may prompt further questions. Actual companies would be added in separate columns, based on a scheduled query of order numbers. It would look something like this.

|Timeline|Pricing|CustomerService|CountofA|CountofB|CountofC|
-------------------------------------------------------------
|   ABC  |    B  |        C      |    1   |    2   |    2   |

The second would be a series of Boolean columns, one for each combination of party/issue.

      |Timeline|Pricing|CustomerService|
----------------------------------------
Role A|   AT   |  AP   |       AC      |
Role B|   BT   |  BP   |       BC      |
Role C|   CT   |  CP   |       CC      |

Each column would be a 1 or a 0, and would then be counted.

|AT|AP|AC|BT|BP|BC|CT|CP|CC|CountA|CountB|CountC|
-------------------------------------------------
|1 |0 |0 |1 |1 |0 |0 |0 |1 |  1   |  2   |  1   |

Option 1 creates a list that is a little more manageable with fewer columns but wouldn't be as easy to pull data from. Option 2 creates a mess of columns but if you know what you're looking for its easier to interpret the data.

I'm pretty sure I'm going to go with option 2 but if there's anything I'm missing, or another way to collect such information please do let me know. I grew into this role and I feel like I'm starting to get to the limits of what I know how to do.

Thanks!

Answer 1

Neither. The first will require you to write code for all interactions with each list item (or powerapps logic, or whatever). The second requires you to create a new set of columns for each new company. Instead I'd have two columns, company and issue. To generate scores, I'd use a reporting tool, such as PowerBI to create a reports that show counts by company, counts by issue, etc. Adding a new company or issue would simply involve adding a new choice, and would not require changes to the list schema or the reports.