Forum Discussion

becpow's avatar
becpow
New Contributor
9 years ago

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

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. 

6 Replies

Sort By
  • dmiscannon's avatar
    dmiscannon
    Frequent Contributor
    9 years ago

    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's avatar
      Colin_McCrae
      Community Hero
      9 years ago

      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's avatar
        becpow
        New Contributor
        9 years ago

        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.