How to Sort this Data

[Blorf]

Hi.

I have data from our enterprise software, Mas 200, Purchase Order details. I need to be able to sort the data the same way Mas 200 does, externally. I am confident that if I sit down and write vb code, I can do this, but I am hoping a strong SQL person will see a simple solution.

The first record, in order, has 0 in the linktopreviousline field. In that records link to next line, contains the lineindex of the second. That records link to next line, again contains the line index of the third, until you reach a record with a link to next line, containing 0.

Any SQL suggestions would be greatly appreciated.

Thanks,
ChaZ

PO	LinkToPreviousLine	LinkToNextLine	LineIndex	Item
0053370	0	19013	18719	202251         
0053370	18719	18720	19013	204615         
0053370	19013	18721	18720	203334         
0053370	18720	18723	18721	203730         
0053370	18721	18757	18723	208142         
0053370	18723	18726	18757	203466         
0053370	18757	19557	18726	204781         
0053370	18726	19658	19557	208144         
0053370	19557	18907	19658	209113         
0053370	19658	18917	18907	206837         
0053370	18907	18916	18917	207660         
0053370	18917	0	18916	206453

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[traingamer]

Don't want to oversimplify, but can't you just sort by PO, by LinkToPreviousLine and get the same results?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Blorf]

Sadly no. The order is not PO, then Previous line.

It is PO, first link to previous with Zero, and the next in sequence is the line index that maches the first records link to next line, and so on.

Thanks Though,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[traingamer]

I can't think of any iterative way to do this with SQL, given the variable nature of the data. I think a VBA function is your only solution. (But I've been wrong before).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Blorf]

Hi.

I wrote the following function to create a sort table. I link my PO2 table to the sort table I create and it works fine. Takes about 8 seconds to run, with roughly 50,000 records that need sorting. The fact that it works does nothing for my mental state, I still think there has to be a cleaner way to do this. Ahh well.

Thanks for the help,
ChaZ

Function SortOrder()
    Dim MySql As String
    Dim X As Integer
    Dim Y As Integer
    
    DoCmd.SetWarnings False
    
    Y = 1
    X = 1
    
    DoCmd.RunSQL "SELECT PO2.PurchaseOrderNumber, PO2.LinkToPreviousLine, PO2.LinkToNextLine, PO2.LineIndex, 1 AS [Order] INTO POSorter FROM PO2 WHERE (((PO2.LinkToPreviousLine)=0));"
    
    
    While Y = 1
        X = X + 1
    
        MySql = ""
        MySql = MySql & "INSERT INTO PoSorter ( PurchaseOrderNumber, LinkToPreviousLine, LinkToNextLine, LineIndex, [Order] ) "
        MySql = MySql & "SELECT PO2.PurchaseOrderNumber, PO2.LinkToPreviousLine, PO2.LinkToNextLine, PO2.LineIndex, " & X & " AS [Order] "
        MySql = MySql & "FROM POSorter INNER JOIN PO2 ON POSorter.LinkToNextLine = PO2.LineIndex "
        MySql = MySql & "WHERE (((POSorter.Order)=" & X - 1 & "));"

        DoCmd.RunSQL (MySql)
        
        If IsNull((DSum("[order]", "POSorter", "[Order]=" & X))) Then Y = 0
    Wend
    DoCmd.SetWarnings True
End Function

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[waterbury]

I can direct you to a way to do this in Crytal Reports if you're interested.

 

[Blorf]

Yes, certainly. My method works I think, but it is a cheesy solution.

Thanks!

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[waterbury]

These instructions come from a user that goes by the handle of Gator - he deserves the credit. I've tested it so I know it works. Just substitute PO for SO as the PO1 and SO1, PO2 and SO2 are similiar in their structure.

Have fun,
Waterbury

Here goes - not for the light of (Crystal) heart:

*********************************************
Here is a little test I did with SO1 and SO2.

Add SO1 and SO2 to a report and link by the Sales Order Number.
Group by the Sales Order Number of SO1.
Create a formula called "index" with the following:

shared numberVar firstIndex;
shared stringVar nextIndex;

if firstIndex=0 then ToText({SO1_SOEntryHeader.LinkToFirstDetailRecord},"00000",0,"","") else nextIndex

Create another formula called "resetVars" with the following:

shared numberVar firstIndex := 0

Add "index" to the detail section.
Add "resetVars" to the group footer.

Now create a subreport with only SO2.
Select what ever data you want from the line.
Create a formula called "nextIndex" with the following:

shared stringVar nextIndex := ToText({SO2_SOEntryDetailLine.LinkToNextLine},"00000",0,"","")

Create another formula called "setFirstIndex" with the following:

shared numberVar firstIndex := 1

Add both formulas to the detail section.

The result is that the main report will have them in the wrong order, but it gives you the correct number of lines per order. The subreport will give you the correct information in the proper order. I did this test with SO1 and SO2 as I am most familiar with them so I'm not sure how it would work in the BM situation, but this may give you a start.

/EDIT: I forgot to add that the main report is linked to the subreport by the "index" formula to the "LineIndex" of SO2.

Gator