Source and target line items have different formats and lists when trying to use lookup.
I am in need of some ideas, on how to solve a problem I am facing.
I have these 3 modules (picture below). The module OC Expected Renewals contains numbers. OC Expected Renewals already pulls in data from from the module 4f ACV Calculation, but, now I also need it to pull in target values from 5f Official Customer ACV into a new line item in the module (number formatted), call it target value.
Here in lies my current issue. OC Expected Renewals module has a customer list in rows and hand and another list in pages (see arrow), which we can call list A. List A contains 3 items; Blades, Products and Support. These three items are available in three different line items in the 5f Officials Customer ACV module. What I need to do is to pull the target figures from 5f Officials Customer ACV module into the OC Expected Renewals module with regards to each customer and which item is selected in pages from list A.
The OC Expected Renewals module applies to a different list than the 5f Officials Customer ACV module, but, the the 5f Officials Customer ACV module has a line item called customer which has the same list format as the module I will pull the data into. What I would want to do is a formula similar to eg. If 'List A'.Blades then 5f Official Customer ACV.Target value[lookup:5f Official Customer ACV.Customer] else blank.
The problem is that the line item target value adheres to the 5f module's list which is different from the line item customer (which has the same list as the target module, OC Expected Renewals.
In conclusion, I am trying to pull in a value from three different line items in a source module, and, which line item the formula pulls information from, to display, is dependent on which customer and which items in list A. But, the source and target line items have different formats and lists.
Apologies in advance if I misunderstood your question.
If you are trying to pull in the data from 5f Module into OC module & if Dimension of the OC module is same as the List formatted property of the 5f module the you should use SUM instead of Lookup & your formula should be
If 'List A'.Blades THEN 5f Official Customer ACV.Target value[SUM:5f Official Customer ACV.Customer] ELSE BLANK
If 'List A'.Blades then 5f Official Customer ACV.Target value[lookup:5f Official Customer ACV.Customer] else blank.
Re: Source and target line items have different formats and lists when trying to use lookup.
You might be right. The formula in question is just an example of what logic i would like to apply, but, due to the line items having different format I cannot apply the logic, as in the format of my example formula. That's why I asked, to see if someone had encountered a similar problem and found a solution.
Not sure if I get the statement when you say "due to the line items having different format I cannot apply the logic".
I have created a small mock up for you. Let me know if this helps.
5f Official Customer ACV Module - Dimensions are Accounts(Let's assume), three data related Line items and one line item Customer formatted as Customers List
OC Expected Renewals Module - Dimensions are List A, Customers, Hand. Now if I have to pull the data based on the Customers I will have a formula written as below. This assumes that the Customer List which is the dimension of this module is exactly the same which is present in the List Formatted Line item of Previous module.Nested IFs can be avoided in these scenarios as this is just for demo.
Now if you are saying that the Customer lists are different then you will have to create a mapping table between these two Customers List and Use SUM&LOOKUP to pull in the values.