iLogic updating query

[spackle42]

I have six user multivalue parameters (top_reel_1 - top_reel_6).  I've written a loop that works its way through these parameters moving the contents upward so that there are never any empty ("N/A") parameters above a filled one.  The loop works as expected, with one exception.  When a parameter is changed in the drop down box, the rule moves it to the correct position but then doesn't change the original cell to empty.

Dim i As Integer
For i = 1 To 6
    If top_reel_6 = "N/A" Then
    Else
        If top_reel_5 = "N/A" Then
            top_reel_5 = top_reel_6
            top_reel_6 = "N/A"
        End If
    End If
    
    If top_reel_5 = "N/A" Then
    Else
        If top_reel_4 = "N/A" Then
            top_reel_4 = top_reel_5
            top_reel_5 = "N/A"
        End If
    End If
    
    If top_reel_4 = "N/A" Then
    Else
        If top_reel_3 = "N/A" Then
            top_reel_3 = top_reel_4
            top_reel_4 = "N/A"
        End If
    End If
    
    If top_reel_3 = "N/A" Then
    Else
        If top_reel_2 = "N/A" Then
            top_reel_2 = top_reel_3
            top_reel_3 = "N/A"
        End If
    End If
    
    If top_reel_2 = "N/A" Then
    Else
        If top_reel_1 = "N/A" Then
            top_reel_1 = top_reel_2
            top_reel_2 = "N/A"
        End If
    End If
Next i

[adam.nagy · Accepted solution]

Hi Justin,

I could not reproduce the behaviour with the part document I created:

http://www.screencast.com/t/zH8A6ctfz

Please have a look at it to see if it works for you too.

Cheers,

[spackle42]

Thanks for taking the time to look into this Adam.  Sorry I haven't replied sooner but I got dragged off to other projects.  I tried the file that you posted but it seems to do the same thing.  I start with all fields filled as below.

I change top_reel_4 to 'N/A' and the table updates to:

And then updates again after a mouse click to:

What I'm attempting to achieve is that when I start with (1,2,3,4,5,6) and change 4 to N/A for example, the result should be (1,2,3,5,6,N/A), not (1,2,3,6,N/A,N/A) as above.

[spackle42]

EDIT: I've just noticed that you have been using a form instead of changing the parameter directly in the parameter window.  I will try setting up a form and see how that works.

I've tidied up the code to make it a little easier to follow and makes the For loop redundant:

If Not(top_reel_2 = "N/A") And top_reel_1 = "N/A" Then
    top_reel_1 = top_reel_2
    top_reel_2 = "N/A"
End If

If Not(top_reel_3 = "N/A") And top_reel_2 = "N/A" Then
    top_reel_2 = top_reel_3
    top_reel_3 = "N/A"
End If

If Not(top_reel_4 = "N/A") And top_reel_3 = "N/A" Then
    top_reel_3 = top_reel_4
    top_reel_4 = "N/A"
End If

If Not(top_reel_5 = "N/A") And top_reel_4 = "N/A" Then
    top_reel_4 = top_reel_5
    top_reel_5 = "N/A"
End If

If Not(top_reel_6 = "N/A") And top_reel_5 = "N/A" Then
    top_reel_5 = top_reel_6
    top_reel_6 = "N/A"
End If

The issue seems to be that the code is not initially able to update the field that has been modified by the user, so it copies the 'N/A' value to the field below (and cycles that value on down the list) but then the original field is still left as 'N/A' as well.  It then runs the rule a second time for some reason and moves the erroneous value in the original field to the bottom of the list as well, which results in the two 'empty' fields instead of just one.  

[spackle42]

It works fine if I use a form to enter the information