Forum Discussion

nastester's avatar
nastester
Regular Contributor
2 months ago
Solved

Issue using Excel and variables in name mapping

I am using variables in name mapping objects to pull data from an Excel sheet and then verify with Property Checkpoints.

It is working fine for the first 3 rows in my sheet. But on the fourth row I am getting an error for the address. The previous 3 addresses worked fine. The local variable was updated and checkpoint passed. Also the first four checkpoints for this fourth row of data worked fine, it just fails on the address. 

I cannot figure out why this fourth one is failing.  Any ideas? Lots of info/screenshots below that will hopefully help. 

 

 

Name Mapping object using variable:

Project level variable: 

 

Excel sheet with address data:

function ReadManifestData()
{
  // Get the sheet of the Excel file
  var excelFile = Excel.Open(Project.ConfigPath + "Datasheet.xlsx");
  var excelSheet = excelFile.SheetByTitle("ViewData");

  // Read data from the Excel file
  var RowIndex = KeywordTests.CheckManifestScreen.Variables.RowIndex_KW;
  var Order_Num = excelSheet.Cell("A", RowIndex).Value;
  var HIN_Num = excelSheet.Cell("B", RowIndex).Value;
  var TrackNum  = excelSheet.Cell("C", RowIndex).Value;
  var Forecasted  = excelSheet.Cell("D", RowIndex).Value;
  var Street_Addr   = excelSheet.Cell("E", RowIndex).Value;
  var ServiceLevel = excelSheet.Cell("F", RowIndex).Value;
  var Commit_Time = excelSheet.Cell("G", RowIndex).Value;
  var Cons_Name = excelSheet.Cell("H", RowIndex).Value;
  var Floor_Num = excelSheet.Cell("I", RowIndex).Value;
  var Postal_Code = excelSheet.Cell("J", RowIndex).Value;
  var City_Name = excelSheet.Cell("K", RowIndex).Value;
  var State_Name = excelSheet.Cell("L", RowIndex).Value;
  var Country_Code = excelSheet.Cell("M", RowIndex).Value;
  var Urbanization = excelSheet.Cell("N", RowIndex).Value;
 
 Project.Variables.Order_Num_KW = Order_Num;
 Project.Variables.HIN_Num_KW = HIN_Num;
 Project.Variables.TrackNum_KW = TrackNum;
 Project.Variables.Forecasted_KW = Forecasted;
 Project.Variables.StreetAddr_KW = Street_Addr;
 Project.Variables.ServiceLevel_KW = ServiceLevel;
 Project.Variables.Commit_Time_KW = Commit_Time;
 Project.Variables.ConsName_KW = Cons_Name;
 Project.Variables.Floor_Num_KW = Floor_Num;
 Project.Variables.Postal_Code_KW = Postal_Code;
 Project.Variables.City_Name_KW = City_Name;
 Project.Variables.State_Name_KW = State_Name;
 Project.Variables.Country_Code_KW = Country_Code;
 Project.Variables.Urbanization_KW = Urbanization;

}

 

Keyword test:

 

 

Log:

 

27 Replies

  • This looks like a problem with the browser page object name mapping.  I would begin by ensuing the target object is mapped correctly and uniquely. 

    I also noticed you are using a value '0' (which should work) instead of the cmpEqual constant in the failing checkpoint. Probably not an issue.  

    • nastester's avatar
      nastester
      Regular Contributor

      What is that 0 even for? 
      I see what you're talking about:
      Aliases.browser.pageIcoms.panelStAdd, "contentText", 0, Project.Variables.StreetAddr_KW, true


      But I also see that for all the other checkpoints when I click into the row.

      I think you're right that something is not right with the object mapping but not sure how to troubleshoot further. Especially since it works fine for my first three rows and it "looks" correct compared to all my other mapped objects

      • scot1967's avatar
        scot1967
        Icon for Champion Level 1 rankChampion Level 1

        You can use a constant like 'cmpEqual' or the value that constant represents, 0 in this case.

        ConstantValueDescription
        cmpEqual0Check whether the Property value equals Value.
        cmpNotEqual1Check whether the Property value is not equal to Value.
        cmpGreater2Check whether the Property value is greater than Value.
        cmpLess3Check whether the Property value is less than Value.
        cmpGreaterOrEqual4Check whether the Property value is greater or equal to Value.
        cmpLessOrEqual5Check whether the Property value is less or equal to Value.
        cmpContains6Check whether the Property value contains Value.
        cmpNotContains7Check if the Property value does not contain Value.
        cmpStartsWith8Check whether the Property value starts with Value.
        cmpNotStartsWith9Check if the Property value does not start with Value.
        cmpEndsWith10Check whether the Property value ends with Value.
        cmpNotEndsWith11Check if the Property value does not end with Value.
        cmpMatches12Check whether the Property value matches the regular expression specified by Value.
        cmpNotMatches13Check if the Property value does not match the regular expression specified by Value.
        cmpIn14Check if Value contains the Property value. (Similar to cmpContains, but the other way round.)
        cmpNotIn15Check if Value does not contain the Property value. (Similar to cmpNotContains, but the other way round.)
    • nastester's avatar
      nastester
      Regular Contributor

      I do see this when trying to edit the object in namemapping.  Don't get that for any of the other objects I am using.

       

       

       

       

      Other objects:

       

  • I think you're right that something is not right with the object mapping but not sure how to troubleshoot further. Especially it works fine for my first three rows and it "looks" correct compared to all my other mapped objects

    I sometimes use this trick.  You may not be into scripting but you can start a script recording click on your target object and then stop the recording and TestComplete will show you a script with a complete path to the object.  It may also add an alias to your namemap depending on how your project is setup. 

    • nastester's avatar
      nastester
      Regular Contributor

      Will try that that but also uncovered this when adding in more logging.
      So it's not getting the value for that fourth row at all for some reason.  

       

      • scot1967's avatar
        scot1967
        Icon for Champion Level 1 rankChampion Level 1

        This image indicates an issue with the namemap accessing panelStAdd.  In your loop there is a click and a text entry action that seems to indicate adding text to a filter in the page.    

  • The first thing that I would usually check, is to ensure that the cell is of the correct type i.e. the format of the cell (General, Text, etc).

    Use Log.Message to output the values of the variables. This should give you an indication that the cells are correctly being read.

    Has the page changed, if testing web app? Does panelStAdd actually exists? Or does it become enabled after entering something else?

  • I too have seen Excel formatting issues like this.  This usually results in a checkpoint failure not a namemap error. 

    It looks like button51c is clicked prior to looping through the app objects to compare them to each Excel row cell. There is also a 'Click' and SetText for each row within the loop.

     What is going on there?

  • Your first three values are identical, the forth is different. 
    You are using a parametrized NameMapping and the value changed; therefore, it does not find the target object as the log message states.

    Each time you set value to the NameMapping variable you need to refresh the mapping info for it to take effect. Otherwise, the cache value takes over.

    https://support.smartbear.com/testcomplete/docs/testing-with/object-identification/name-mapping/how-to/refresh-cache.html 

  • All, thank you for your responses. I finally uncovered the issue. The issue was some of the data the mock API is returning had spaces in the address field (which was truncated by table so not noticeable when looking at the UI). The data from the UI was copied into the spreadsheet so it didn't have the space.  I still don't know why TC didn't log the ER/AR and show that they didn't match but when I updated the mock data to not have spaces, everything worked. 

      • nastester's avatar
        nastester
        Regular Contributor

        Expected result/actual result.

        And no, the test is verifying that the data table on the UI is displaying the expected data from the API in the right location. It isn't inputting any data. 

    • Hassan_Ballan's avatar
      Hassan_Ballan
      Icon for Champion Level 1 rankChampion Level 1

      Since your NameMapping is parameterized and your object XPath statement is using equal for comparison, the values do not match due to the spaces you described. As result, the object in question will not be found due to the incorrect value. For the EV/AV, look at the log warnings before the error, self-healing decided to use another object with blank value, which again fails as values are not equal.

      I am surprised it works for you as is. You still need to add to your code RefreshMappingInfo. In the following example if I comment out the RefreshMappingInfo statements after each "Set Variable value", it will fail.

       

  • It seems you are using the actual street address (which is dynamic) as matching data in your name map based on a keyword test parameter or project variable. "//div[.='%StreetAddr_KW%']"  This could make your test brittle as it is based on the format of the address.  A better alternative would be to map the web control using another static property and compare the value of that control if possible.  This should prevent name mapping errors unless the web page itself is altered by dev.

    • scot1967's avatar
      scot1967
      Icon for Champion Level 1 rankChampion Level 1

      The use of dynamic data in a name map also makes is necessary to refresh the namemap and object instance (effectively remapping the object) prior to each call to the mapped object to make the comparison.  This will slow your test down considerably.  

    • nastester's avatar
      nastester
      Regular Contributor

      I don't think I follow you. Could you elaborate a bit because I am genuinely curious if there is a better way to do what I am trying to accomplish.

      This is what I am doing:

      • The cells in the first row of the data table are mapped and the xpath is updated to read from a Project Level variable
      • A script pulls data from Excel which is storing the data in the table and it assign the columns to the corresponding Project Level variable 
      • A Keyword test performs the necessary UI actions including searching the table to filter it to one row of data
      • The Keyword test then compares the current value of the UI object to the Project level variable (which is coming from Excel)

       

      I'm sure there is a better way to do this. This is my first time using TC in this way. But it does work for multiple rows of data in my sheet. 

       

      • scot1967's avatar
        scot1967
        Icon for Champion Level 1 rankChampion Level 1

        To ensure the target object is found the namemap will need to be 'refreshed' in each loop because the identification data based on the web content has changed.  This can be hit/miss and slow down your test.

        As Anonymous said, "Ideally, you should get the UI control object, followed by the contentText property value, and then compare this, with the value from the spreadsheet."

        Map elements based on static identification properties id like <td id="Address_Cell"> or other unique properties that exist.  There are many types of things you can use depending on how the web page is designed.  Check out this page on 'Selectors'.  You can use multiple selectors and add your own to uniquely identify an object and avoid using variable data.   

        https://support.smartbear.com/testcomplete/docs/testing-with/object-identification/name-mapping/selectors.html?sbsearch=Name%20Mapping%20selectors

        You may also be able to avoid using the Excel sheet by querying the API directly from TC.  This will depend on you goal.

        • Does the web page data match what the API returns?
        • Does the web page data match a static list of known values?

         

        These are subtle but important distinctions.

        Dig in here...

        https://support.smartbear.com/testcomplete/docs/reference/program-objects/aqhttp/index.html

        Another alternative to Excel is to add the JSON likely returned from the API to a project variable and verify by that.