Solved: Warp Filter and Select() in Valid_if

desmond_lee · 01-12-2025 07:48 PM

Hi Guys

I am trying to warp two condition in the ref database Project List but it seems to be not able to do so. How to I warp these two in one ?

SELECT(SURVEY PROJECTS[PROJECT ID],([ID]=[_THISROW].[ID]))-
SELECT(TEST RECORD[PROJECT ID],[PARTICIPANT`S SITE NAME]=[_THISROW].[PARTICIPANT`S SITE NAME])

FILTER("Project List",
[PROJECT SITE NAME]<>[_THISROW].[PARTICIPANT`S SITE NAME])

I try this and it does not work too.

IN(
FILTER("Project List",
[PROJECT SITE NAME]<>[_THISROW].[PARTICIPANT`S SITE NAME]),
SELECT(SURVEY PROJECTS[PROJECT ID],([ID]=[_THISROW].[ID]))-
SELECT(TEST RECORD[PROJECT ID],[PARTICIPANT`S SITE NAME]
=[_THISROW].[PARTICIPANT`S SITE NAME])

desmond_lee

I comes up with these. I wonder which one is correct and which is better. Both seems to be working. The filter is filtering row and the SELECT is selecting Column values. I think the later is correct for IDs - IDs. Any comment or new method ?

FILTER("Project List",[PROJECT SITE NAME]<>[_THISROW].[PARTICIPANT`S SITE NAME])
-SELECT(TEST RECORD[PROJECT ID],[PARTICIPANT`S SITE NAME]=[_THISROW].[PARTICIPANT`S SITE NAME])

SELECT(SURVEY PROJECTS[PROJECT ID],AND([ID]=[_THISROW].[ID],[PROJECT SITE NAME]<>[_THISROW].[PARTICIPANT`S SITE NAME]))
-SELECT(TEST RECORD[PROJECT ID],[PARTICIPANT`S SITE NAME]=[_THISROW].[PARTICIPANT`S SITE NAME])

View solution in original post

Aurelien

Because these two expressions are equivalent:

SELECT([Related SURVEY PROJECTSs][PROJECT ID],
TRUE
)

and

SELECT(SURVEY PROJECTS[PROJECT ID],
  AND(
    [ID]=[_THISROW].[ID],
    TRUE
  )
)

Difference being, the [Related SURVEY PROJECTSs] has already been calculated. You may want to benefit from its result in a SELECT() expression: otherwise, the query will be performed against the whole table. With a [related XXXs][id], it's calculated against a subset of the table.

@desmond_lee wrote:

This 'PROJECTSs' is a typo error?

No.

If you have a table named SURVEY PROJECTS, with a column with type Ref that refers to a table named MYPROJECT, then you will have in the table MYPROJECT a virtual column named [Related SURVEY PROJECTSs].

From what I see from your screenshot, that you don't have such table name, but it all depends on where you run your expression in the end.

EDIT:

My bad, I didn't notice the ID was the same in both table...which led to a misunderstanding.

These would be equivalent:

SELECT([Related SURVEY PROJECTSs][PROJECT ID],
TRUE
)

and

SELECT(SURVEY PROJECTS[PROJECT ID],
  AND(
    [PROJECT ID]=[_THISROW].[PROJECT ID],
    TRUE
  )
)

View solution in original post

desmond_lee

I comes up with these. I wonder which one is correct and which is better. Both seems to be working. The filter is filtering row and the SELECT is selecting Column values. I think the later is correct for IDs - IDs. Any comment or new method ?

FILTER("Project List",[PROJECT SITE NAME]<>[_THISROW].[PARTICIPANT`S SITE NAME])
-SELECT(TEST RECORD[PROJECT ID],[PARTICIPANT`S SITE NAME]=[_THISROW].[PARTICIPANT`S SITE NAME])

SELECT(SURVEY PROJECTS[PROJECT ID],AND([ID]=[_THISROW].[ID],[PROJECT SITE NAME]<>[_THISROW].[PARTICIPANT`S SITE NAME]))
-SELECT(TEST RECORD[PROJECT ID],[PARTICIPANT`S SITE NAME]=[_THISROW].[PARTICIPANT`S SITE NAME])

Aurelien

Hi @desmond_lee

I suggest to use this button for future code-pasting, and indenting the code pasted to make it easier to read:

It would come as it:

SELECT(SURVEY PROJECTS[PROJECT ID],
AND([ID]=[_THISROW].[ID],[PROJECT SITE NAME]<>[_THISROW].[PARTICIPANT`S SITE NAME])
)
-SELECT(TEST RECORD[PROJECT ID],
[PARTICIPANT`S SITE NAME]=[_THISROW].[PARTICIPANT`S SITE NAME]
)

You may want to optimize it if there is a Ref type, this way:

SELECT([Related SURVEY PROJECTSs][PROJECT ID],
[PROJECT SITE NAME]<>[_THISROW].[PARTICIPANT`S SITE NAME]
)
-SELECT(TEST RECORD[PROJECT ID],
[PARTICIPANT`S SITE NAME]=[_THISROW].[PARTICIPANT`S SITE NAME]
)

desmond_lee

The key in SURVEY PROJECT is PROJECT ID. I don't quite understand 'related' as in this SELECT([Related SURVEY PROJECTSs][PROJECT ID]. Is related also an expression? This 'PROJECTSs' is a typo error?

Aurelien