Hi,
I am trying to create an expression that I can use in a columns initial value.
I want it to return the latest row that matches column Equipment.
I have this:
ANY(
SELECT(Pre Start[Equipment],
([_ROWNUMBER] = MAX(SELECT(Pre Start[_ROWNUMBER],
([_THISROW].[Equipment] = [Equipment]))))))
Which is interpreted by appsheet as:
One randomly chosen value from this list (
โฆThe list of values of column โEquipmentโ
โฆfrom rows of table โPRE STARTโ
โฆwhere this condition is true: ((The value of column โ_RowNumberโ) is equal to (MAX(
โฆThe list of values of column โ_RowNumberโ
โฆfrom rows of table โPRE STARTโ
โฆwhere this condition is true: ((The value of โEquipmentโ from the row referenced by โTimestampโ) is equal to (The value of column โEquipmentโ))))))
I tried to do this:
ANY(
SELECT(Pre Start[Equipment],
AND(([_ROWNUMBER] = MAX(SELECT(Pre Start[_ROWNUMBER]))),
([_THISROW].[Equipment] = [Equipment]))))
But the select function becomes invalid
Any ideas on how I could select the latest row where equipment matches equipment?
Solved! Go to Solution.
I think you want this:
What does this mean?
When I put in the expression I was referring to Appsheet says:
SELECT has invalid inputs
Please provide a screenshot of Expression Assistant showing both the expression and the error.
Extra parentheses:
And the AND() isnโt needed.
Try this:
ANY(
SELECT(
Pre Start[Equipment],
(
[_ROWNUMBER] = MAX(
SELECT(
Pre Start[_ROWNUMBER],
([_THISROW].[Equipment] = [Equipment])
)
)
)
)
)
Which is the first expression in your original post.
When I use the formula you recommended it doesnt seem to work.
Although It looks like it did attempt to input data.
I believe the formula is looking for the highest row number in the table without taking into account whether that cell contains data. I think it is returning blank due to this. I just dont know how to fix it. This is why I was trying to get the formula to look for the highest row number AND contains the same equipment value.
I think you want this:
Thank you Steve, this worked great.
I will have to bookmark this tip
Hi steve
How can i create a virtual column in which can select the last row (last updated) of a related list.
Now what i got is to show all the colomn from the related list
([Related checklists][status]) like this
But i dont know how to make it show only the last row (last update status)
Thank you
Thanks Steve. This solved my query. I have 2 tables with a parent-child relationship. One record from the parent can have many records in the child. I wanted the status update from the child to update the parent, but only the last update from the child, so ignoring previous updates. I had to tweak your expression above in one way: The first "thing" is from my parent table and the second "thing" is from my child table. Works like a charm
I donโt want it to return me a โrandomlyโ chosen value. I want it to return me the last Status of a Tracking Number from the Log Sheet. Updates of the status of each tracking number will be added, thus the same tracking number will be present in the Log Sheet, but with (the same) or different status.
It also says โNote, this expression could significantly impact sync time.โ How significantly will it impact the sync time?
Should I just use the following?
MAXROW(
โMy Tableโ,
โ_ROWNUMBERโ,
([_THISROW].[Thing] = [Thing])
)
User | Count |
---|---|
41 | |
31 | |
29 | |
16 | |
14 |