Re: Row filter for referenced table with exclusion...

SKETCHwade · 11-05-2022 02:53 AM

Hi Everyone,

I am working on a row filter that I have posted about previously but I am changing it so thought I should create a new topic.

It is supposed to check to see if a finish date is blank, look in the referenced table and return row ID where Job ID matches and a column is marked as complete. This works perfectly. But this only does the check on each row that matched Job ID not all of the rows that match Job ID.

To counteract this I tacked on another section that does the same checks but checks to see if the column is not complete and then exclude that rows ID. At least that is my plan. Can you please tell me where I have gone wrong. I am sure its easier than it looks.

AND(
	AND(
		AND(
  		ISBLANK([Finish Date]),
  		ISNOTBLANK(
   			FILTER(
    		"Tasks",
    			AND(
	 					[JOB id] = [_THISROW].[JOB id],
     				[Completed?]<>FALSE
    			)
   			)
  		)
 		),
		AND(
 			ISBLANK([Finish Date]),
  		ISNOTBLANK(
   			FILTER(
    		"Tasks",
    			AND(
	 					[JOB id] = [_THISROW].[JOB id],
     				[Completed?2]<>FALSE
    			)
   			)
  		)
 		)
	),
NOT(
	OR(
		ISNOTBLANK(
   		FILTER(
   		"Tasks",
   			AND(
	 				[JOB id] = [_THISROW].[JOB id],
    			[Completed?]<>TRUE
    		)
   		)
  	),
   	ISNOTBLANK(
   		FILTER(
    	"Tasks",
    		AND(
	 			[JOB id] = [_THISROW].[JOB id],
    		[Completed?2]<>TRUE
  			)
			)
		)
	)
 )
)

WillowMobileSys

@SKETCHwade wrote:

only does the check on each row that matched Job ID not all of the rows that match Job ID.

I am not sure what difference you are trying to make with this comment. Do you mean you want to return ALL Job ID rows if ANY of the Job ID rows is marked as "Complete"?

If so, it seems you already have that expression. See below. I might suggest you use [Completed?] = TRUE.

[Completed?] <> FALSE is ok ONLY if you always have FALSE or TRUE in all the columns. If any columns are blank they will be included which is not what you want. To avoid this potential issue, I always use "= TRUE" or "<> TRUE". There is no ambiguity this way.

If above assumption is correct about what you are trying to accomplish, then it seems the expression only needs to be:

AND(  	
    ISBLANK([Finish Date]),
  	ISNOTBLANK(
   	    FILTER(
    		   "Tasks",
    		   AND(
	 	       [JOB id] = [_THISROW].[JOB id],
     		       [Completed?] - TRUE
    		   )
   	    )
  	)
 )

SKETCHwade

Hi @WillowMobileSys

I'll try to explain it better.

The expression I am using will look at the tasks table and return all matching Job ID where completed is not False. This is to intentionally allow situations where the task does not use completed?2 and it can be left blank and have this function correctly.

Each job can have many rows in task. Because of this only one row of the Job ID needs to meet these requirements and it will show. But I want to only grab the Job ID if all of its rows in Tasks are marked as completed or left blank.

Does that make more sense?

The original expression was:

AND(

ISBLANK([Finish Date]),

ISNOTBLANK(

FILTER(

"Tasks",

AND(

[JOB id] = [_THISROW].[JOB id],

[Completed?]<>FALSE

)

),

AND(

ISBLANK([Finish Date]),

ISNOTBLANK(

FILTER(

"Tasks",

AND(

[JOB id] = [_THISROW].[JOB id],

[Completed?2]<>FALSE

)

WillowMobileSys

@SKETCHwade wrote:

But I want to only grab the Job ID if all of its rows in Tasks are marked as completed or left blank.

Does that make more sense?

Mostly. Just like in code, we have to be careful how we explain things in words using "AND" and "OR".

I read the current meaning to be "include Job ID if all Tasks rows have either [completed?] = TRUE or ISBLANK([completed?])"

But it could also mean, "include Job ID where all Tasks rows have [completed?] = TRUE OR all Tasks rows have ISBLANK([completed?])"

Which one do you actually mean?

A couple of other things...

1) I am getting a sense that your goal is to differentiate between Jobs that are not yet started -VS- jobs that are in progress -VS- jobs that are fully completed, If so, then I would recommend having an Enum [Status] column in BOTH the Tasks table and the Jobs table. The Job [Status] is automatically set based on the state of all the Tasks. With this in place you can easily select Jobs by their status.

2) Additionally, I am seeing that you can reduce your current expression to that below. How it is further adjusted will depend on your answer to the above question.

AND(  	
    ISBLANK([Finish Date]),
  	ISNOTBLANK(
   	    FILTER(
    		   "Tasks",
    		      AND(
	 	          [JOB id] = [_THISROW].[JOB id],
                          OR(
     		             [Completed?] <> FALSE,
     		             [Completed?2] <> FALSE
                          )
    		      )
   	    )
  	)
 )

SKETCHwade

Hi @WillowMobileSys

You are right I am struggling to explain this.

The answer to your question is my goal is:

"include Job ID if all Tasks rows have either [completed?] = TRUE or ISBLANK([completed?])"

I put your expression in place, Seems to grabbing all Job ID's where Finish date is not blank as intended but then it seems to be ignoring your Completed and Completed2 rules

WillowMobileSys

@SKETCHwade wrote:

I put your expression in place, Seems to grabbing all Job ID's where Finish date is not blank as intended but then it seems to be ignoring your Completed and Completed2 rules

Re-reviewing your original expression, the 2 "completed" columns should have been ANDed like this:

AND(  	
    ISBLANK([Finish Date]),
  	ISNOTBLANK(
   	    FILTER(
    		   "Tasks",
    		      AND(
	 	              [JOB id] = [_THISROW].[JOB id],
     		          [Completed?] <> FALSE,
     		          [Completed?2] <> FALSE
    		      )
   	    )
  	)
 )

I don't think you need both "completed" columns but again I don't know you data structure.

If this still doesn't give you the results you want, it would be helpful to post images of:

1) The actual expression you are using if you modified it in anyway.

2) The column list for BOTH tables involved. I know one table is the "Tasks" table but don't know anything about the table in which you are using this expression. It would be good to see both to understand the relationships.

3) Sample data you are trying to filter by the expression 0 both tables.

Row filter for referenced table with exclusions