Need assistance looping properly in CAD Table

[jeffl]

Hi,

Let me preference by stating I'm not very good with lsp and this is my first attempt at trying to create a table with lsp. My issue is I'm not looping properly, but cant figure out the proper way to get it to work. If you look at the code you'll see I open 2 record sets. I open recordset1 and then open recordset2 and loop through and then back to recrordset1. When I just look at the sql side of things it is looping through the records properly. But when writing the table portion I'm not able to get it to write the data in the same sequence. Meaning it is writing recordset1 then recordset2 then recordset1 then recordset 2. What it should be doing is writing recordset1 then looping and writing recordset2 3 times then recordset 1 and then recordset2 3 times and so on. Here's some screen shots of the invalid sequence and then what I'm expecting.

Any and all advice is appreciated.

sct-20181113.lsp

[dlanorh]

You will need to post a drawing containing the two table and the record sets so someone has something to test on.

[jeffl]

Hi dlanorh,

 

I can post a drawing, but not sure if that would help much, you would basically have to rewrite everything because the DB calls are specific to the application. 

[Roy_043]

@jeffl

Why not start by helping yourself and properly indent your code? Code that is better readable is easier to analyze and understand.

I see that lstOfSublist and lstOfSublist1 each contain a single sublist when the code enters the foreach on line 69. From what you want I would expect the latter to contain 3 sublists.

[jeffl]

@Roy_043

 

Thanks, that solved my issue.

[jeffl]

So, I've still not figured this out. No matter how I open the recordsets I'm not able get the table to properly write in the same sequence as fetching the records. The sequence in which this should occur is as follows:

    (setq rsid (AfmRsOpen SQLQuery)) ; Open record set for primary owner
        (while (not (AfmRsEOF rsid)) ; start loop for primary owner data
            (setq HpAcad (AfmRsGetStr rsid 0))
            (setq buCode (AfmRsGetStr rsid 1))
            (setq BuName (AfmRsGetStr rsid 2))
            ;(alert BuName)
            (setq lstOfSublist (list (list HpAcad buCode BuName )))
                (foreach lstSublist lstOfSublist
                    (foreach strItem lstSublist
                        (vla-settext objtable intRow intColumn strItem)
                            (setq intColumn (1+ intColumn))
                    )
                )
    (setq rsid1 (AfmRsOpen SQLQuery1)) ; Open record set for counts data
                        (while (not (AfmRsEOF rsid1)) ; open loop for counts data
                            (setq RmStd (AfmRsGetStr rsid1 0))
                            (setq AvRm (AfmRsGetInt rsid1 1))
                            (setq OccRm (AfmRsGetInt rsid1 2))
                            (setq VacRm (AfmRsGetInt rsid1 3))
                            ;(alert BuName)
                            (setq lstOfSublist1 nil)
                            (setq lstOfSublist1 (list (list RmStd (itoa AvRm) (itoa OccRm) (itoa VacRm))))
                                (foreach lstSublist1 lstOfSublist1
                                    (setq intRow (1+ intRow))
                                        (foreach strItem1 lstSublist1
                                            (vla-settext objtable intRow intColumn1 strItem1)
                                                (setq intColumn1 (1+ intColumn1))
                                        )
                                        (setq intColumn1 2)
                                )
                        (AfmRsMoveNext rsid1) ; Fetch next from counts data
                        ) ; end while loop for counts data
                        (setq intRow (1+ intRow))
                        (setq intColumn 0
                            intRow (1+ intRow))
        (AfmRsClose rsid1) ; Close record set for counts data            
        (AfmRsMoveNext rsid) ; Fetch next primary owner
        ) ; end while loop for primary owner
        (AfmRsClose rsid) ; Close recordset for primary owner

 

Here's what it's producing, it gets the owner and writes it properly but always uses the last record for the count data 

 

[jeffl]

I was able to solve this, had to move the sql statement for the count data to inside the loop of the primary.