Formula

Hi All,

 

I have a requirement and this is a very rare formula. Please help me with the same.

 

In Numbered list has Duplicate Products for different parents.

 

For Ex: Product A is a parent member it has a 3 Childs(D, E, F)

             Product B is a parent member it has the same 3 Childs(D, E, F)

              Product C is a parent member it has the same 3 childs (D, E, F).

 

We need to move all E product data to D.

Calucualtion D = D + E

PARENTCHILDData before logic Data After logic
A    
 D100 300
 E200 0
 F300 300
B    
 D100 300
 E200 0
 F300 300
C    
 D100 300
 E200 0
 F300 300

     

Can you please help me with how to write this calculation. Hope we understood.

 

Regards,

Naidu

Answers

  • HI @NAIDU,

    I have a solution for you!!

    Please see below.

    image.png

     image.png

    Here I have used system generated Id as that will be sequential and would work in this case.

    System Id       : To get the back end "#" number

    System Id +1   : To get the next item

    Find item         :  Converting the above number to list format

    Units_Staging  : To bring in the value for D and E with respect to the above stated condition

    Final Units       :  Final Output

     

    Hope his helps!!

    Regards,
    Kavin.

     

     

  • Hello @NAIDU 

     

    There are several ways you could do it. Using formulas, using process etc.

     

    But keep PLANS and DISCO in mind. The solution must be sustainable.

     

    I strongly advice not to use Hard coded formulas, today the transition is from E==>D, tomorrow it could be different and there could be multiple transitions. Consider all scenarios.

     

    Never use the # code of the numbered list. 

     

    I modeled the following for a single transition, you could extend it for multiple transitions.

     

    I created three lists, a flat list for parent, a flat list for child and the numbered list.

     

    image.png                       image.png               image.pngimage.png

     

    Created a transition model to accept input on what to transition from and to.

    image.png

     

    Created a module to do the transition calculations as follows,

    image.pngLine item formulas,

    Data 
    Take OutIF Numbered List.Child = Transition.Move From THEN -Data ELSE 0
    Add InIF Numbered List.Child = Transition.Move To THEN -1 * Take Out[LOOKUP: Numbered List.Parent Item] ELSE 0
    After MovementData + Take Out + Add In

    It works as below, hope it helps.

    image.png

     

    Thanks

    Arun

     

     

  • Both great solutions @ArunManickam and @kavinkumar

     

    But a very valid point made by Arun in that this needs to follow DISCO and PLANS and be sustainable.