Please help I have had a similar question bef...

[Jason_F]

Please help I have had a similar question before but didn’t get the correct answer.

I made an amazing app but cant get this part correct due to the List Type can not be compared with Date Format Type. We have to make sure all the products are inspected in the past 15 days.

So need to list all the products that ARE NOT inspected in the past 15 days. I tried to create a virtual column in Product Table but then I can not compare it with Today(). Can anyone please give me the formula and the solution?!

Thanks Everybody

[RezaRaoofi]

Have you tried any formula that has failed? Have you tried a simple condition with an expression like this: [Inspection Date] > TODAY()-16

[Jason_F]

I used this formula also to create a virtual column on Product Table, but its very wrong. It shows everything regardless of the date.

=ISNOTBLANK(

MAXROW(

“Inspection Table”,

“Inspection Date”,

AND(

([Product] = [_THISROW].[Product]),

([Inspection Date] <=(TODAY()-15))

)

) )

[RezaRaoofi]

What happened? What did you mean by: …due to the List Type can not be compared with Date Format Type…

[Levent_KULACOGL]

@Jason_F

MAXROW() function returns a ref/list, therefore you cannot enclose it with an ISNOTBLANK() expression. Create a Virtual Column with MAXROW() and then use a de-ref to check any column’s value:

=ISNOTBLANK([VirtualColumnName].[Inspection Date])

[Jason_F]

@RezaRaoofi

using a virtual column got the last date and tried to compare it in another virtual column and got that error.

But in my latest version.

I used this formula also to create a virtual column on Product Table, but its very wrong. It shows everything regardless of the date.

=ISNOTBLANK(

MAXROW(

“Inspection Table”,

“Inspection Date”,

AND(

([Product] = [_THISROW].[Product]),

([Inspection Date] <=(TODAY()-15))

)

) )

[RezaRaoofi]

@Jason_F sorry when I commented the second time you did not have that formula yet! Now I see you changed it and included the failed formula.

Well, Levent addequatly replied!

[RezaRaoofi]

Can you explain your goal again? Where do you need to use this list of recent Products that are not inspected in last 15 days?

If the goal is to simply get a list of products that their inspection date is older than 15 days ago, then you did not need MAXROW().

Something like this seems to be enough: SELECT(

Inspection Table[Product],

AND(

([Product] = [_THISROW].[Product]),

([Inspection Date] <=(TODAY()-15))

)

[Levent_KULACOGL]

@RezaRaoofi

I believe @Jason_F is querying the list’s COUNT rather than the list itself. So:

COUNT(SELECT(…))=0

OR

COUNT(SELECT(…))>0

might be the criterion he is looking for any kinda TRUE/FALSE evaluation

[Levent_KULACOGL]

@Jason_F

Additionally, I have noticed that your [Inspection Date] column is DateTime type, so in your expression, your evaluation shall be:

DATE([Inspection Type])<=TODAY()-15