Source and target line items have different formats and lists when trying to use lookup.

CommunityMember117741
CommunityMember117741
in Integrations and extensibility

Hi, 

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.  

CommunityMember117741_3-1579528054887.png

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. 

CommunityMember117741_2-1579528015486.png

 

 

 

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. 

 

Kind regards, 

Tagged:

Best Answer

  • Misbah
    Misbah
    Answer ✓

    @CommunityMember117741 

     

    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

    Misbah_0-1579538864877.png

     

    Misbah_1-1579538897558.png

     

     

    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.

    Misbah_2-1579539006317.png

    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.

     

    Thanks,

    Misbah

     

     

Answers

  • JaredDolich
    JaredDolich

    @CommunityMember117741 

    If you're open to using the DISCO method (best practice) then I would suggest you create a system module for each of your lists.

    In the system module you'll create all the format types you need.

    You can also add Booleans that will help determine eligibility of certain conditions.

    Then use the system modules to do your LOOKUP instead of having line items in your target module that meet your specific formats.

     

    Lastly, if you have to use an IF statement, remember to always put the most likely condition first so the IF statement exits early.

     

     

  • CommunityMember117741
    CommunityMember117741

    Hi,

    This is not my build originally, so unfortunately I cannot add modules due to space requirements and overall structure. 

     

    Kind regards  

  • Misbah
    Misbah

    @CommunityMember117741 

     

    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

    & 

    NOT

    If 'List A'.Blades then 5f Official Customer ACV.Target value[lookup:5f Official Customer ACV.Customer] else blank.

     

    Let me know if that wasn't the case.

    Thanks,

    Misbah 

  • CommunityMember117741
    CommunityMember117741

    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.  

     

    Kind regards

  • CommunityMember117741
    CommunityMember117741

    Thank you! 

    I changed the formula a little bit and it is now pulling the information correctly. 

     

    Kind regards

Categories