Format Rule Expression help

[SKETCHwade]

Hello,

This app has a table called pre start in which machines are inspected.
Another table called Risk assessment in which machines are inspected.
Obviously these tables cover different aspects to inspect.

I am trying to format the risk assessment UX.
I have this:

Tick means it passed the risk assessment
Cross means it failed
Exclamation mark means the machine has moved location since last assessed and requires a new assessment.

Both tables referred to above use Timestamp as the key column, Equipment to identify the equipment and Site to refer to the location.

My issue arises with the exclamation mark expression.
It should be asking:
Risk assessment [Site] = Pre Start[Site]

The expression I have so far:

[Site] =
LOOKUP(MAX(SELECT(
Pre Start[Timestamp],
([_THISROW].[Equipment] = [Equipment]))),
“Pre Start”,
“Timestamp”,
“Site”)

This results in the image above.
This is incorrect as it should look like this:
!W1
W26
!W28
W29
W43

[LeventK]

@SKETCHwade
Try with this one:

[Site] =
LOOKUP(
	INDEX(
		SORT(
			FILTER(
				"Pre Start"
				([_THISROW].[Equipment] = [Equipment])
			),
			TRUE
		),
		1
	),
	"Pre Start",
	"Timestamp",
	"Site"
)	

[SKETCHwade]

Interesting. I got this:

And expected this:
!W1
W26
!W28
W29
W43

[SKETCHwade]

On a side note if I am searching for the latest row in a table does searching the table in reverse order make the application more efficient? As in a noticeable amount?

[Bahbus]

What about

IN(
     [Site], 
     SELECT(
		Pre Start[Site],
		([_THISROW].[Equipment] = [Equipment])
     )
)

[Bahbus]

Oh wait, is it only if it’s the most recent in Pre Start?

[SKETCHwade]

Yes I am checking if the most recent pre start[Site] is equal to the risk assessment[Site]

[Bahbus]

And what does [Equipment] have to do with it?

[SKETCHwade]

[Equipment] is the column I am using to find the correct row. think of it as the ID number.

Does Risk Assessment[Site] = Pre Start[Site] in the latest row where,
Risk Assessment[Equipment] = Pre Start[Equipment]

[SKETCHwade]

[Site] =
IN(
[Site],
SELECT(
Pre Start[Site],
([_THISROW].[Equipment] = [Equipment])
)
)

Returns:

And expected this:
!W1
W26
!W28
W29
W43

[SKETCHwade]

After doing some tests I have found my initial expression works just fine as a virtual column!

[Site]=
LOOKUP(
MAX(
SELECT(
Pre Start[Timestamp],
([_THISROW].[Equipment] = [Equipment]))),
“Pre Start”,
“Timestamp”,
“Site”)

Now my question is why would an expression work just fine but then when used as a format rule act completely differently?

[Bahbus]

That’s a thing that happens sometimes. I think it has to do with the amount of time the expression takes, but not entirely sure. BUT since you said the expression yields the correct results in a VC, I do remember seeing someone around here suggesting that sometimes it might be much better to have a VC do the heavy lifting and have the format rule just look at the VC.

So if the VC yields the correct TRUE/FALSE results just set the Format Rule to [the virtual column].

[SKETCHwade]

Thank you!
I didn’t even think of using the VC as the rule haha

It would be very interesting to hear why this happens anyway.

[SKETCHwade]

Now I have noticed more odd behaviour.
Sometimes selecting a row will take you to the detail view of the last row in the table.

I think this is due to the Slice I used:
[Timestamp]=MAXROW(“Risk Assessment”,“TimeStamp”,[Equipment]=[_THISROW].[Equipment])

(it is not taking you to the last row of the table if the VC condition fails, I looked)

I do not understand how this works as I have used essentially the same slice condition on another view and it works as expected.

[SKETCHwade]

Here is a GIF to explain what I am seeing.
On the left you can see the columns for this view. The Site Match column (VC) uses the same expression as the right. The right side being the Format rule.

In the GIF you can see that they have the same expression. In the Emulator you can see the format rule taking effect on the left and next to it the output of the VC. They do not line up, neither of them are even correct.

[SKETCHwade]

Any ideas whats going on here?

I have a [Site] column on the right (Site that last risk assessment was completed)

a [Site match] VC column in the middle (Looks in another table for the same equipment and returns its most current location)

This format rule compares the site match and site columns, if they are different then put a red exclamation mark on the VC column.

As you can see in the image the format rule is saying none of the columns match, even though W26 and W43 should match.

[SKETCHwade · Accepted solution]

I have found the cause of the issue!

For anyone interested in this in the future.
My test data had three timestamps with the same values.
Timestamp was the key in this data.

This resulted in odd behaviour with selecting a row in a table, deck or form view. Selecting some rows would produce a different row in detail view.
This also caused format rules to act completely unexpectedly.

For the Appsheet team, for some reason appsheet did not inform me of the duplicate key values until I changed the view type to form.

[Bahbus]

Not only did I somehow miss your previous bumps, I would not have thought something wrong with the keys.