Help with SELECT() & IN() expression

Hi everyone

Got a quick question:

I’m creating a select expression for a report… and i’ve managed to make it work ALMOST to the point of perfection but i’ve stumbled on a seemingly simple step.

Here’s my expression:

SELECT(
JOB_MAIN_AREAS[MAIN_AREA_UID],
AND(
	IN([Foreman],[Related CHECKPOINT_MASTERs][Job Original Foreman]),
	[Related CHECKPOINT_MASTERs][CreatedDate] >= [FROM],
	[Related CHECKPOINT_MASTERs][CreatedDate] <= [TO],
))

My original expression was only included the IN() expression and that was great because i did get a list of areas where the selected [Foreman] = [Job Original Foreman].

And now i simply want to limit this list of areas even more but not just by matching the Forman columns but also by comparing the dates…

Any ideas?

Should the CreatedDate values be those from the matching Job Original Foreman rows?

Yes @Steve exactly right! :+1:

The [Foreman] match is a prerequisite…

1 Like

I guess i could do it in two VCs or something like that

Create a VC#1 with all the Main Areas matching the foreman… and then a VC#2 that filters out based on the date range… and use that as the basis for the report… but seems like a bit of a clunky solution. Not very elegant. Amateurish. Inefficient.

This is for the report’s <<Start>> expression?

Yes exactly… preferably i would like to build the list in the template and not use any VCs.

1 Like

Maaaaybe this???

FILTER(
  "JOB_MAIN_AREAS",
  ISNOTBLANK(
    SELECT(
      [Related CHECKPOINT_MASTERs][_ROWNUMBER],
      AND(
        [Job Original Foreman] = [Foreman]),
        [CreatedDate] >= [FROM],
        [CreatedDate] <= [TO]
      )
    )
  )
)
1 Like

@Steve

As always :bowing_man: :bowing_man: :bowing_man:

(there’s a rogue parentheses in there - removed)

I had a quick follow up question.

So this now generates a list of Ref values. Is there a way to Tap into them? What if i needed to sum up a list of numeric values each of which is stored in each of these refs?

Sorry for the basic questions…

EDIT: Actually managed to do it myself :slight_smile: thanks for the massive help anyway!

1 Like