Forum Discussion
Find Value in Same Table
Hello KayceVC
Give this a try. I had a check for the time on the 42 being after the time on the row but it doesn't read like you need that but I left it in the code and just commented it out.
Result = VAR TheDate = DATEVALUE ( auditdata[Date_Updated] ) VAR TheDateTime = auditdata[Date_Updated] VAR TheUser = auditdata[User] VAR NumRows = 0 RETURN IF ( auditdata[Key_ID] = 22, CALCULATE ( MAX ( auditdata[Date_Updated] ), FILTER ( auditdata, auditdata[User] = TheUser && auditdata[Key_ID] = 41 && DATEVALUE ( auditdata[Date_Updated] ) = TheDate --&& auditdata[Date_Updated] > TheDateTime ) ) )
Hello KayceVC ,
Can you add the result column to your screen shot for what you would like to see?
| ParentID | ChildID | Key_ID | User | Date_Updated | TableIndex | RecordIndex | Result |
| 4061 | 2232 | 21 | Aguy | 6/27/2019 | 15 | 1 | |
| 4061 | 2233 | 22 | Aguy | 6/27/2019 | 16 | 2 | |
| 4061 | 2234 | 2 | Aguy | 6/27/2019 | 17 | 3 | |
| 4061 | 2235 | 22 | Aguy | 6/27/2019 | 18 | 4 | |
| 4061 | 2236 | 3 | Aguy | 6/27/2019 | 19 | 5 | |
| 4061 | 2237 | 41 | Aguy | 6/27/2019 | 20 | 6 | |
| 4061 | 2238 | 1 | Agirl | 6/27/2019 | 21 | 7 | |
| 4061 | 2239 | 3 | Agirl | 6/27/2019 | 22 | 8 | |
| 4061 | 2240 | 1 | Agirl | 6/27/2019 | 23 | 9 |
Hey jdbuchanan71
This would be what I would expect. I would like to return the date where the 41 on the same day occurred on the row item for the associated 22. This will allow me to calculate the 2 follow up values I need a boolean for was the 41 record (a time entry note) updated within the allowed time (before the end of the day) and then a calculation for how long it took for each 22 record to receive the time entry update.
ParentID ChildID Key_ID User Date_Updated TableIndex RecordIndex Result 4061 2232 21 Aguy 6/27/19 1:00 15 1 4061 2233 22 Aguy 6/27/19 2:00 16 2 6/27/19 2:45 4061 2234 2 Aguy 6/27/19 2:15 17 3 4061 2235 22 Aguy 6/27/19 2:30 18 4 6/27/19 2:45 4061 2236 3 Aguy 6/27/19 2:30 19 5 4061 2237 41 Aguy 6/27/19 2:45 20 6 4061 2238 1 Agirl 6/27/19 3:05 21 7 4061 2239 3 Agirl 6/27/19 3:15 22 8 4061 2240 1 Agirl 6/27/19 4:00 23 9 PattemManohar Unfortunately this is as much sample data as I can provide. It is formatted exactly how my real data is, but due to the senstive nature of my data, I am not permitted to post actual system data. The above sample is very close to my ideal input/output though.
Hello KayceVC
Give this a try. I had a check for the time on the 42 being after the time on the row but it doesn't read like you need that but I left it in the code and just commented it out.
Result = VAR TheDate = DATEVALUE ( auditdata[Date_Updated] ) VAR TheDateTime = auditdata[Date_Updated] VAR TheUser = auditdata[User] VAR NumRows = 0 RETURN IF ( auditdata[Key_ID] = 22, CALCULATE ( MAX ( auditdata[Date_Updated] ), FILTER ( auditdata, auditdata[User] = TheUser && auditdata[Key_ID] = 41 && DATEVALUE ( auditdata[Date_Updated] ) = TheDate --&& auditdata[Date_Updated] > TheDateTime ) ) )Sorry for getting back to this so late, my project priority got reassigned.
Your solution worked brilliantly and very quickly. I was afraid with all the steps it might be a long running column, but it updated in less than 5s. This was exactly what I needed, thank you so much!
