Forum Discussion
Resolver ILOOKUPVALUE with multiple results
Sorry lbendlin, I have confused matters by misunderstanding the data myself. After consulting with the developers who support the source database they have confirmed that the dates are a red herring as there will only ever be one record but that it has to match not just the related work order record but also met be of the correct assignment type and also be the Primary record.
so, my final formula is:Assigned Staff = LOOKUPVALUE(AssignedStaff[Staff Name],AssignedStaff[Record_UID],[UID],AssignedStaff[StaffAssignmentType],1,AssignedStaff[IsPrimary],TRUE())Thank you for your time and efforts in helping me solve my problem. Kudos given 🙂
Resolver IThanks, that isn't actually working but here is some more info and example data that might help.
The CaseWorkOrder table has a primary key of [UID] and contains various data about the case.
The AssignedStaff table has the following fields:
[UID] the Primary key
[Record_UID] which refers to the UID of CaseWorkOrder, though no relationship exists in the database
[StaffMember_UID] which relates to the StaffMember table
[CreatedDate]
[ModifiedDate]
[StaffAssignmentType] an Enum where a value of 1 means "Staff member is assigned to a case work order record." Other values indicate that the assignment record refers a different table.
I need a column in CaseWorkOrder that will give me the name of the currently assigned staff member, i.e. the one with the most recently created/modified entry in the AssignedStaff table.
Example:
CaseWorkOrder table
| UID | Assigned Staff* |
| 97 | S7 |
| 98 | S2 |
| 99 | S4 |
*new caclulated column
AssignedStaff table
| UID | Record_UID | StaffMember_UID | CreatedDate | ModifiedDate | StaffAssignmentType |
| 01 | 99 | S1 | 20240901 | 20240902 | 2 |
02 | 98 | S2 | 20240901 | 1 | |
| 03 | 99 | S3 | 20240902 | 1 | |
| 04 | 99 | S4 | 20240901 | 20240903 | 1 |
| 05 | 99 | S5 | 20240902 | 1 | |
| 06 | 97 | S6 | 20240905 | 1 | |
| 07 | 97 | S6 | 20240907 | 3 | |
| 08 | 97 | S7 | 20240906 | 1 |
Does that clafify the problem?
Thanks for you help
Super Userthe one with the most recently created/modified entry in the AssignedStaff table.you lost me on this one. which date do you want to sort by, created or modified?
- IntaBruce
Sorry lbendlin, I have confused matters by misunderstanding the data myself. After consulting with the developers who support the source database they have confirmed that the dates are a red herring as there will only ever be one record but that it has to match not just the related work order record but also met be of the correct assignment type and also be the Primary record.
so, my final formula is:Assigned Staff = LOOKUPVALUE(AssignedStaff[Staff Name],AssignedStaff[Record_UID],[UID],AssignedStaff[StaffAssignmentType],1,AssignedStaff[IsPrimary],TRUE())Thank you for your time and efforts in helping me solve my problem. Kudos given 🙂