How to include unique count option in table

[Ina1]

I have a table that has multiple product variation e.g. Black Red Blue and for each variation row there’s a corresponding problem column. If a variant has multiple problem it is listed in another column under problem 1 problem 2 and so on. I want to count all unique problems for the product
e.g:
broken lid - (totalnumberofbrokenlid)/(totalcountofproblems)

I also want a separate data counting all the total problems for each variations. Right now im just manually entering and counting all the data and I want to automate this process can someone please help me.

[ThinhDinh]

First, I would suggest a structure like this. If you have more problems for a variation than your current columns count would support, you have to add more columns?

Table: Product

ProductID	ProductName
PR001	Widget

Table: ProductVariation

VariationID	ProductID	VariationName
PV001	PR001	Black
PV002	PR001	Red
PV003	PR001	Blue

Table: ProductProblems

ProblemID	VariationID	ProblemDescription
PP001	PV001	Broken lid
PP002	PV001	Scratched
PP003	PV002	Broken lid
PP004	PV002	Dented
PP005	PV003	Scratched
PP006	PV003	Dented
PP007	PV001	Scratched

Then:

• In the Product Variation table, create a multiple relation to the Product Problems table using the Product Variation ID.
• Create a rollup, you will get the total problems for each variation.
• Create a joined list column to get a list of problems for each variation.
• In the Products table, create a multiple relation to the Product Variations table using the Product ID.
• Create a joined list column to get a list of problems for all variations. You’ll get something like this: “Broken lid, Scratched, Broken lid, Dented, Scratched, Dented, Scratched”.
• Add a JavaScript column like this.

function countProblems(problems) {
    // Split the comma-delimited string into an array
    const problemArray = problems.split(', ');

    // Create an object to store the count of each problem
    const problemCount = {};

    // Iterate over the array and count each problem
    problemArray.forEach(problem => {
        problemCount[problem] = (problemCount[problem] || 0) + 1;
    });

    // Calculate the total number of problems
    const totalProblems = problemArray.length;

    // Create an array to store the result strings
    const result = [];

    // Iterate over the problemCount object to create the result strings
    for (const problem in problemCount) {
        result.push(`${problem} - ${problemCount[problem]}/${totalProblems}`);
    }

    // Sort the result array alphabetically
    result.sort();

    // Return the result as a single string
    return result.join('\n');
}

return countProblems(p1)

With p1 being the joined list in the Products table.