Summation in repeat regions via conditional statements

[RBobroski]

I have a bunch of piping that I am having difficulty arranging in a repeat region. The piping is listed as 16WT300_lengthinfeet. So if I have 4 pieces of 16WT300_2 and 3 pieces of 16WT300_4 my total length of 16WT300 is 20. Since the suffix of the names is different it won't combine the piping when I check to remove duplicates. Now when I make my BOM table I have 4 columns; index, part number, description, and QTY. Under QTY I have rpt_qty displayed for all elbows, reducing couplings, female adapters, etc but for general tubing I want this column to display the total length of piping for a set Diameter. If I do something like below:


IF extract(asm_mbr_part_number,3,2) =="WT"
x = (asm_mbr_length_ft * rpt_qty)
ELSE
x= rpt_qty
ENDIF

and set my QTY column to rpt.rel.x then it displays the correct QTY for all adapters and the lengths for the individual groups of piping members (x = 10 for the 2 16WT300_5, x=4 for 1 16WT300_4, x = 6 for the 3 16WT300_2, etc) but what I want is it to just say x= 20 for 16WT300. Since I want it to display in the same column as the rpt_qty for the adapters I can't use a summation command (or can I?) so I tried something like this:


IF extract(asm_mbr_part_number,3,2) =="WT"
x = (asm_mbr_length_ft * rpt_qty)
TOTAL_QTY=x+TOTAL_QTY
ELSE
x= rpt_qty
TOTAL_QTY=0
ENDIF

And tried displaying rpt_rel_TOTAL_QTY and nothing populates. Anyone have a suggestion to my problem? I haven't crossed this bridge yet but I figured if I can get at least the last 16WT300_x column to have x = total_QTY then I can use a filter rule to remove all 16WT300_x that have x<total_qty to display only one line on the table, 16WT300 at x=total_qty, but I can't even get the total quantity to calculate correctly. I've had a headache all day because of this problem, so if anyone could help me out I would greatly appreciate it. Also I understand I may have worded this in a confusing manner, so If anyone needs clarification don't hesitate to ask.

 

[HamsterNL]

Nice challenge

I have looked at your post, maybe you could try this:

1) Add an extra column to your BOM table, call it "WT Length"
2) Using your own relations, slightly edited, and show "x" in the "WT Length" column:

IF extract(asm_mbr_part_number,3,2) =="WT"
x = (asm_mbr_length_ft * rpt_qty)
ELSE
x = ""
ENDIF

3) Create an extra row in the BOM table, below the repeat region.
4) Add a Summation on this row, which will add all the x's.

This should work, but you do have an extra column and an extra row. Hopefully, this will give you new ideas...