How do I navigate to a particular record in a table variable?

[becpow]

I have a couple of tables that are related to one another and I need to select the particular records depending on the PK that is selected from the parent table.

For example:

Patient table has MRN, NameFirst, NameLast ...

Address table has MRN, AddressID, Address1 ...

There is a data-driven loop for the Patient table

There is a while loop for the Address table where Address.MRN = Patient.MRN

When I choose to start at a particular record in the Patient table, say record 3 which happens to be MRN = 3.

The code for the While loop of the Address table is not recognized because the Address.MRN = 1, and not MRN = 3 that it needs to be.

How do I force the Address table to start at Address.MRN = 3 record?

I tried Call Object Method and looked at the Addres.Iteration, Value [Set], Address.MRN = Variable.MRN (which has the value of the current MRN working on).

This didn't work. 

[dmiscannon]

See if the following will help. Basically, I set the RecordIdx of the second (address) table to the MRN (or record) of the first (name) table. The log messages just read the data from my two tables.  While I am selecting to read all records from the first table, you can change it to start at a different record by modifying the While command of the SelectTableRecord table to start at a different record.

Sub Test1()
  Dim RecordIdx, AddressRecord
  AddressRecord = 0
  ' First Data Driven Loop (contains name data)
  Call Project.Variables.SelectTableRecord.Reset
  While Not Project.Variables.SelectTableRecord.IsEOF
    Call Log.AppendFolder(Project.Variables.SelectTableRecord.Value("MRN"), "")
    ' Set AddressRecord = to the record's MRN
    AddressRecord = Project.Variables.SelectTableRecord.Value("MRN")
    Call Log.Message(Project.Variables.SelectTableRecord.Value("MRN"), "")
    Call Log.Message(Project.Variables.SelectTableRecord.Value("NameFirst"), "")
    Call Log.Message(Project.Variables.SelectTableRecord.Value("NameLast"), "")
    ' Second Data Driven Loop (contains address data)
    Call Project.Variables.SelectAddressRecord.Reset
    ' Set the RecordIdx to the AddressRecord (which is equal to the MRN)
    RecordIdx = AddressRecord
    While RecordIdx = AddressRecord
        Call Log.Message(Project.Variables.SelectAddressRecord.Value("MRN"), "")
        Call Log.Message(Project.Variables.SelectAddressRecord.Value("Address"), "")
        Call Log.Message(Project.Variables.SelectAddressRecord.Value("City"), "")
        Call Log.Message(Project.Variables.SelectAddressRecord.Value("State"), "")
        Call Log.Message(Project.Variables.SelectAddressRecord.Value("Zip"), "")
        ' Increase the RecordIdx (or you end up in an endless loop)
        RecordIdx = RecordIdx + 1
    WEnd
    Call Log.PopLogFolder
    Call Project.Variables.SelectTableRecord.Next
  WEnd
End Sub

[Colin_McCrae]

I'm slightly confused what you're trying to do here?

You have two tables - one names, one address. The two have "MRN" as the common value linking the two?

So when you select a name in table 1, it's MRN will match an address in table 2? You want to find the row in table two that matches the MRN in table 1?

But you table lookups (which lookup and write to the log only right?) only appear to retrieve a value and write it to the log? They don't actually change the position of any cursor/pointer in the table? (I don't think, I'm not sure how or why you're using project variables for this?)

And your second loop will only ever run once?

You set a value (based on the first loop) and your loop says "do this loop while the values are equal".

But you set the values to equal before you run the second loop:

RecordIdx = AddressRecord
    While RecordIdx = AddressRecord

And then increment RecordIdx in the loop. So as soon as you increment it (which will be the first time in the loop) the condition is broken and the loop will exit. So you'll only ever check one row in the second table?

The lookups all appear to be hardcoded (in quotes) in the both loops as well? Should they not be using variables and comparisons?

What kind of table is it? If you're asking what method of the table (or underlying data set - depends on the table type) you use to move the cursor/pointer within the table, we need to know what kind of table it is ....

I'm confused ....

[becpow]

The table type is a TestComplete Table Variable.  One table, the parent, has patient information in it and the second table, the child, has address information that is associated to the patient table.  There can be 1 to many address information so I need to be able to iterate through these values or the particular MRN of the Patient.  The MRN field is the PK, FK relationship between them.  I have this set up in a KeyWord test.  The Patient table has a data-driven loop around it.  I'm doing a while loop around the Address table.  I have a variable set up that has the current MRN number of the Patient table. The while loop is set up to go through the Address table while it's MRN is equal to the MRN variable that is set to the one currently in the Patient table. There is no problem with incrementing through the loops. Where I have the issue is that if I start the Patient table out where the MRN = 3 (which you can do with the data-driven loop, is start at any record number you want), the while loop for the Address table doesn't do anything because the current MRN is 1, not 3.  I want to start the Address table at the same MRN as the Patient table.  Basically like in a sql query, Select * from Address where MRN = 3 and then do a while loop around it so that it will go through till the MRN doesn't equal 3.  Does that make sense? Since I'm dealing with the Table Variable I'm not sure how to get the cursor to start at a particular record in it. 

[Colin_McCrae]

Ah right. I thought this was a Table within an application.

I've never used the table variable in TestComplete. Which would explain why I didn't understand how you were addressing it.

So maybe have a look here: http://support.smartbear.com/viewarticle/74145/

About 2/3s of the way down that page, it mentions:

On this page, you can specify the records from the needed data storage which will be processed by the Data-Driven Loop operation. By default, the operation processes all records of the specified storage. If you want to specify the start and end records for processing, enter their identifiers into the From Record and To Record edit boxes respectively.

But I don't know how you vary those at run time?

[dmiscannon]

A couple of things. If there is a 1 (patient) to many (address) relationship, then the record number where you start won't be the same for the 2 tables. Also, I would recommend using Excel or csv files instead of Table variables. I have had issues when attempting to edit integer values within a Table variable. (After editing an integer within a Table variable, TC no longer recognizes the value as an integer - I have submitted a support case for this. This occurs in both TC 10 and 11.) Also, you can more easily sort and edit Excel files.

Instead of using a While loop, you can accomplish what you want using nested DDT commands. The first DDT command will read the records of the Patient table while the second (nested) DDT reads the Address table. You can use an If Then command within the second (nested address) DDT to only read the address records matching the MRN of the Patient table. See the attached screen print for a possible KWT solution. This option doesn't run as fast as I would like, but it does work. I think if you work with a scripted version of the above solution, it would run quite a bit faster. Good luck.

[becpow]

Yes, the relationship is 1 (patient) to many (address). Which is why I need them to match up.  It has been hard trying to figure out how to do this with Table Variable and inside Keyword tests.  I'm not very familiar with TestComplete.  I have found the same issue with Table variables as you and have to do a conversion to int in order to overcome it. 

I like the idea of doing two DDT instead of a DDT for the Patient and While loop for the Address.  I will definetly try that out to see if it works.  For now I'm not able to change the Table variable in this test suite because there is too much in changing things around.  For future tests I'm not going to use the Table variable because of the limitations I've been having with it.  It just doesn't work for what I need to do. 

Thank you very much everyone for answering my question and helping me get this figured out.