iLogic to add multiple blank lines between Vendors on the parts list.

[Anonymous]

Hi,

Please can one of you iLogic veterans help me out an iLogic novice with a problem.

Our company requires our parts lists to have 3 blank lines (custom parts) between vendors. As you can imagine this is very time consuming depending how big the assembly is.

I can get the parts list to sort using iLogic to the order I need but have no idea how to use iLogic to add in all the lines in between.

Thanks 

[jdkriek · Accepted solution]

Something like this?

Dim oDrawDoc As DrawingDocument = ThisApplication.ActiveDocument
Dim oPartList As PartsList = oDrawDoc.ActiveSheet.PartsLists.Item(1)
Dim i As Long = 1
On Error Resume Next
	For i = 1 To 500 'arbirary number as PartsListRows.count() won't reset
		oCell  = oPartList.PartsListRows.Item(i).Item("PART NUMBER")
		'if the part number is blank then it's not a real BOM item
		If oCell.Value = "" Then
			'skip
		Else
			oPartList.PartsListRows.Add(i, False)
			oPartList.PartsListRows.Add(i, False)
			oPartList.PartsListRows.Add(i, False)
		End If
	Next
oPartList.Renumber
oPartList.SaveItemOverridesToBOM

[Anonymous]

Hi Jonathan,

Thanks so much for your help. That is close to what I am looking for.

I need the 3 lines to separate the different vendors. Please see attached picture below.

Hope you can help.

Thanks

[AlexFielder · Accepted solution]

Hi @Anonymous,

The solution provided by @jdkriek will obviously work for a generic situation.

What you need is to sort the parts list by vendor and part number then iterate through each row checking whether the current row's vendor matches the next one.

If it doesn't, add the blank rows as per the above like this:

Public Sub Main()
    Dim trans as transaction = ThisApplication.TransactionManager.StartTransaction(ThisApplication.ActiveDocument,"Add Blank Rows")
    Try
        Dim oDrawDoc As DrawingDocument = ThisApplication.ActiveDocument
        Dim oPartList As PartsList = oDrawDoc.ActiveSheet.PartsLists.Item(1)
        oPartList.Sort("VENDOR",True,"PART NUMBER",True)
        Dim i As Long = 1
        For i = 1 To 500 
            Dim thisCell As PartsListCell = oPartList.PartsListRows.Item(i).Item("VENDOR")
            If Not thisCell.Value = "" Then
                Dim nextrowint As Integer = i + 1
                If nextrowint > oPartList.PartsListRows.Count Then Exit For
                Dim nextCell As PartsListCell = oPartList.PartsListRows.Item(nextrowint).Item("VENDOR")
                If Not thisCell.Value = nextCell.Value Then
                    oPartList.PartsListRows.Add(i, False)
                    oPartList.PartsListRows.Add(i, False)
                    oPartList.PartsListRows.Add(i, False)
                End If
            End If
        Next
        oPartList.Renumber
        oPartList.SaveItemOverridesToBOM
    Catch
        trans.Abort()
    Finally
        trans.End()
    End Try
End Sub

Because I wanted the rule to run inside a transaction which makes it easy to undo, I had to remove the "On Error Resume Next" line. This meant I had to include a check for whether the nextrow was larger than the number of available rows in the parts list and exit the loop if so.

Cheers,

Alex.

[jdkriek]

I see now, thank you for the extra explanation.

---

@Anonymous wrote:

Hi Jonathan,

Thanks so much for your help. That is close to what I am looking for.

I need the 3 lines to separate the different vendors. Please see attached picture below.

Hope you can help.

Thanks

---

@AlexFielderYes, thank you I wasn't aware fully of what he was trying to do. 

[Anonymous]

Hi Alex,

Thanks for your help and advice. I am getting an error with your code.

Please see below.

Thanks

[AlexFielder]

Hi @Anonymous,

Did you use my code unchanged? If so, I think the error stems from the "VENDOR NAME" column I can see in your screenshot(s); My code uses the default "VENDOR" column name.

Failing that, are you able to record a screencast showing how the rule runs for you?

Thanks,

Alex.

[Anonymous]

Hi Alex,

You are a champion!!!! works perfectly.

Thanks again for your help. 

[AlexFielder]

You are most welcome!

Feel free to add me on LinkedIn if I have helped you out. (Link in my signature)

[Anonymous]

Hi Alex,

I was wondering if you could hep me add and extra process to the code you helped me with.

Would it be possible to add a step that runs after the sort order which takes a specific vendor and moves all parts with that vendor to the top of the parts list?

Thanks

Drew

[AlexFielder]

Hi Drew,

I can help, but I can tell you now this will double (at least) the number of lines in the rule and the complexity level will have to increase sharply.

We will need to make use of .NET's Generic list object which you can read about here.

Without delving into it too far, I suspect we would also want to make use of a new class object to store information about each row of our parts list.

this post is an ELI5 for Object Oriented programming which is where this thread is headed:

https://www.reddit.com/r/explainlikeimfive/comments/1pyhng/eli5_objected_oriented_programming/cd7k92...

I suggest you have a read of all these links and have a go at editing my original rule and if you get stuck then come back here.

IF I do the work for you, you and others will not learn anything. 🙂

Cheers,

Alex.

[Anonymous]

Hi Alex,

Thanks for the info and link. I will have a good read through and see what I can come up with.

"IF I do the work for you, you and others will not learn anything. :-)" a very true statement.

Thanks again for your time and knowledge

Drew

[blandb]

I am using the code you suggested, but once I have added the desired amount of rows, how would I go about filling out specific cells?