So I have this formula that I need a hand with:
ANY(SELECT(Checkout Log[Checked out to], AND([Barcode]=[_THISROW].[Barcode], [Checked Out?]=Yes)))
The above works as expected, however, I would like to setup further so that when โ[Checked Out?]=Noโ it returns static text โEQ ROOMโ
Tried playing with IF(), OR() and drinking too much coffee but could not figure out
Thanks in advance my wizard friends!
Neal
Solved! Go to Solution.
@Neal_MF_Harper
Try with this:
IF(
LOOKUP([_THISROW].[Barcode],"Checkout Log","Barcode","Checked Out") = "No",
"EQ ROOM",
LOOKUP([_THISROW].[Barcode],"Checkout Log","Barcode","Checked out to")
)
@Neal_MF_Harper
Try with this:
IF(
LOOKUP([_THISROW].[Barcode],"Checkout Log","Barcode","Checked Out") = "No",
"EQ ROOM",
LOOKUP([_THISROW].[Barcode],"Checkout Log","Barcode","Checked out to")
)
Thanks so much Levent, it worked!!
Have a wonderful day Sir.
smiley:
Youโre welcome
So I hit a snagโฆ
The checkout log will contain multiple entries, can I use MAX() or MAXROW() somehow to only look for the latest entry in the log?
Right now, if I have two entries in the log, the first record being โChecked Outโ NO (returned) and the second entry YES, it returns the โEQ ROOMโ text from the first entry, while I need it to be based on the most recent entry. Make sense?
Thanks in advance for your time!
Neal
Try and replace the [_thisrow].[barcode] with the maxrow formula:
maxrow(Checkout Log, DATETIME_COLUMN, [Barcode] = [_THISROW].[Barcode])
Youโll need to enter a column name where I put DATETIME_COLUMN, and maybe tweek the condition at the end - but replace the [_thisrow].[barcode] part of each lookup with the maxrow.
(I would test the maxrow formula in the editor, just add a new virtual column and see if the formula works - then delete the column like it was never there.)
@Neal_MF_Harper
You can enhance the formula with this one:
IF(
ANY(SELECT(Checkout Log[Check Out],AND([Barcode]=[_THISROW].[Barcode],[_RowNumber]=MAX(SELECT(Checkout Log[_RowNumber],[Barcode]=[_THISROW].[Barcode]))))) = "No",
"EQ ROOM",
ANY(SELECT(Checkout Log[Checked out to],AND([Barcode]=[_THISROW].[Barcode],[_RowNumber]=MAX(SELECT(Checkout Log[_RowNumber],[Barcode]=[_THISROW].[Barcode])))))
)
User | Count |
---|---|
32 | |
26 | |
26 | |
21 | |
21 |