Calculate a score based on multiple Enum's

[flynnd]

Hi All,

I am having difficulty figuring this one out, but am also very new to Appsheet.

I have a table that contains 6 columns of type Enum (Area 1 - Area 6)
Each Area field has the same  5 options: "None, Poor, Mild, Moderate, High"

This Table also contains a Score Column. I want to assign a number to each of the Enum options and add all 6 scores together into the scores columns, but I cant figure out how to do this.

So the end result would be a score somewhere between 0 and 24

This is what I have.

IFS(
[Area 1] = "None", [Score] = ([Score]+0),
[Area 1] = "Poor", [Score] = ([Score]+1),
[Area 1] = "Mild", [Score] = ([Score]+2),
[Area 1] = "Moderate", [Score] = ([Score]+3),
[Area 1] = "High", [Score] = ([Score]+4)
)
IFS(
[Area 2] = "None", [Score] = ([Score]+0),
[Area 2] = "Poor", [Score] = ([Score]+1),
[Area 2] = "Mild", [Score] = ([Score]+2),
[Area 2] = "Moderate", [Score] = ([Score]+3),
[Area 2] = "High", [Score] = ([Score]+4)
)
IFS(
[Area 3] = "None", [Score] = ([Score]+0),
[Area 3] = "Poor", [Score] = ([Score]+1),
[Area 3] = "Mild", [Score] = ([Score]+2),
[Area 3] = "Moderate", [Score] = ([Score]+3),
[Area 3] = "High", [Score] = ([Score]+4)
)
IFS(
[Area 4] = "None", [Score] = ([Score]+0),
[Area 4] = "Poor", [Score] = ([Score]+1),
[Area 4] = "Mild", [Score] = ([Score]+2),
[Area 4] = "Moderate", [Score] = ([Score]+3),
[Area 4] = "High", [Score] = ([Score]+4)
)
IFS(
[Area 5] = "None", [Score] = ([Score]+0),
[Area 5] = "Poor", [Score] = ([Score]+1),
[Area 5] = "Mild", [Score] = ([Score]+2),
[Area 5] = "Moderate", [Score] = ([Score]+3),
[Area 5] = "High", [Score] = ([Score]+4)
)
IFS(
[Area 6] = "None", [Score] = ([Score]+0),
[Area 6] = "Poor", [Score] = ([Score]+1),
[Area 6] = "Mild", [Score] = ([Score]+2),
[Area 6] = "Moderate", [Score] = ([Score]+3),
[Area 6] = "High", [Score] = ([Score]+4)
)

Any help greatly appreciated

[Joseph_Seddik]

Bad solution that works: 

IFS(
  [Area 1] = "None", 0,
  [Area 1] = "Poor", 1,
  [Area 1] = "Mild", 2,
  [Area 1] = "Moderate", 3,
  [Area 1] = "High", 4
) + IFS(...) + IFS(...) + IFS(...) + IFS(...) + IFS(...) 

Somewhat Better:

SWITCH([Area 1], "None", 0, "Poor", 1, "Mild", 2, "Moderate", 3, 4) 
+ SWITCH (...) + SWITCH(...) + SWITCH(...) + SWITCH(...) + SWITCH(...)

What you should do:

1. Have a separate Ratings table, with two columns: 
   • Number: Key column, type Number, with five rows each contains one value of your. rating list: 0, 1, 2, 3, 4
   • Label: Label column, type Text, with the corresponding text value in each row: None, Poor, Mild, Moderate, High. 
     
     You can add as many rows as you like, and change the names of the labels freely, and your app will continue to function. 
     
     
2. In your areas table, change the six Enum columns to Ref, pointing to the new Ratings table. Add this condition to its Valid if field: Ratings[Number]. These columns will capture numbers, but users will see the corresponding labels. 
   
   
3. Now to calculate the score, you can just use: 
   
   [Area 1] + [Area 2] + [Area 3] + [Area 4] + [Area 5] + [Area 6]

[graham_howe]

Create another table called rating in that table have an [ID] key column, a [description] which is set as label, and a [score].

ID	Description	Score
1	None	0
2	Poor	1
3	Mild	2
4	Moderate	3
5	High	4

Then for each of your area fields set them up as a reference to the rating table. Probably using an Enum type with base type ref. Make sure you put in an App Formula to populate the reference column.

Once you have the references in place you will be able to calculate the score across all the fields by dereferencing the columns in your virtual column expression. Something like this

SUM(
  SELECT(rating[Score], [ID] = [_THISROW].[Area 1]),
  SELECT(rating[Score], [ID] = [_THISROW].[Area 2]),
  SELECT(rating[Score], [ID] = [_THISROW].[Area 3]),
  SELECT(rating[Score], [ID] = [_THISROW].[Area 4]),
  SELECT(rating[Score], [ID] = [_THISROW].[Area 5]),
  SELECT(rating[Score], [ID] = [_THISROW].[Area 6])
)

[flynnd]

Thanks Everyone,
I had just figured it out using the add multiple Switch statements method but good to know the other way also.
Thanks again.