Forum Discussion
Lookupvalue Problem
Hello Everyone,
I'm having a problem returning a result from a table using multiple criteria.
The tables I'm using are Cases and Workfront (which stores current project statuses).
I created a concatenated field for each to make a lookup easier...but I get an error.
(The green box just shows that a single account can have multiple products...but each individual product/account pair only occurs once/is unique. Cases are not unique...a single account is likely to have multiple cases for each product)
I really just want the Product_Stage__c value return for each unique cases in the top grid (Cases).
Thanks in advance for any guidance you can provide!
~Joseph
I think I understand what you are trying to do but it seems like your LOOKUPVALUE criteria is not specific enough to return a single value but rather multiple values. I suspect that you must have multiple lines in that other table, perhaps per status with a date assigned. If that is the case, you might try something like this instead:
Column = VAR __table = FILTER(ALL('Table2'),[Column] = [ID]) VAR __max = MAXX(__table,[Date]) VAR __status = MAXX(FILTER(__table,[Date]=__max),[LookupColumn]) RETURN __statusSo, take the [ID] from your original table and FILTER ALL of the table to a matching column, now you have all rows in the 2nd table for that [ID]. Then, find the MAX date in that table of filtered rows. Finally, filter down to that exact date in the filtered row table and return the status that you are trying to grab ([LookupColumn]).
Something along those lines.
5 Replies
You might try wrapping that in a CONCATENATEX to concatenate multiple values together.
Greg, thanks for responding!
I think that is where I am getting confused. I know my thinking is wrong, but I'm currently thinking that for each row in my cases table I should be able to pull the unique Product stage value for that company/product.
My up to would be to highlight cases that the product was in a Training stage for example.
Each Product state would show up in the row next to all the other case details and I would do some conditional formatting to highlight those cases.
Something like this...
Not sure if any of that made sense. But I appreciate the help.
I think I understand what you are trying to do but it seems like your LOOKUPVALUE criteria is not specific enough to return a single value but rather multiple values. I suspect that you must have multiple lines in that other table, perhaps per status with a date assigned. If that is the case, you might try something like this instead:
Column = VAR __table = FILTER(ALL('Table2'),[Column] = [ID]) VAR __max = MAXX(__table,[Date]) VAR __status = MAXX(FILTER(__table,[Date]=__max),[LookupColumn]) RETURN __statusSo, take the [ID] from your original table and FILTER ALL of the table to a matching column, now you have all rows in the 2nd table for that [ID]. Then, find the MAX date in that table of filtered rows. Finally, filter down to that exact date in the filtered row table and return the status that you are trying to grab ([LookupColumn]).
Something along those lines.