Select Unique Values From Child Table with Switch

[appsheeter]

Have a project table with task table as child

In the project table I want a column that selects all the task.animal values, dedupes them, assigns a number for each text value, then sums it.

Here is the expression I am trying...

SUM(
SELECT(
SWITCH([Related tasks][Animal],"moose", 1,"dog", 10,0),
TRUE, TRUE)
)

Here is error I am getting...

Column 'AnimalToNumber' in Table 'project_Schema' of Column Type 'Price' has an invalid expression in the Formula field.'=SELECT( SWITCH([Related tasks][Animal],"moose", 1,"dog", 10,0), TRUE, TRUE)'. SWITCH function is used incorrectly: Cannot convert input 2 of type 'Text' to 'List of Text'

[TeeSee1]

I am sure there are other ways to do this. One way I would do this is..

1. Create a table having animal (key), value(decimal)

2. Create a virtual column (could be a physical col with an App Formula) in Tasks table with the expression (call it value)

LOOKUP(
 [_THISROW].[animal],
 "table in 1",
 "animal",
 "value"
)

 3. The final column in Projects table with the expression

SUM(
 [Related tasks][value]
)

[appsheeter]

If  "dog" exists 3 times in the tasks table, then I only want 10 included in the sum, not 30.

I don't think your solution dedupes the animals first does it?

[TeeSee1]

You're right, completely forgot about that part.

You can try below. Step 2 in my previous post is not required for this to work.

SUM(
 SELECT(
  table 1[value],
  IN(
   [animal],
   SELECT(
    tasks[animal],
    IN(
     [task key],
     [Retaled tasks]
    ),
    TRUE
   )
  )
 )
)

[TeeSee1 · Accepted solution]

Simplified. This should work

SUM(
 SELECT(
  table 1[value]
  IN(
   [animal],
   UNIQUE([Related tasks][animal])
  )
 )
)

[appsheeter]

So now I have projects, tasks and animalValues tables.

I'm putting this expression in a column on projects.

SUM(
SELECT(
animalValues[value]
IN(
[animal],
UNIQUE([Related tasks][animal])
)
)
)

I'm getting error "Unable to find column 'animal', did you mean 'ID'?"

[TeeSee1]

You have to adjust the names where necessary because I do not have the definitions of your tables.

[appsheeter]

here is animal values table