Announcement

Collapse
No announcement yet.

Scripting relation results per page

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Scripting relation results per page

    Hi there,

    I created a script with a scripting relation that allows me to compare values from two different data relations. My problem is that my results are over 1000 and if I show the results in one page, it crashes the server because of the timeout.

    My workaround to this was to limit the results to 20 per page and use a data-pager to go through them.

    Now the first page shows me the first 20 results, but once I try to go to the second, third, fourth, etc, it shows me no results.

    Would you have any idea of what it might be?

    If you need to see the code, I can paste it here.

    Thanks in advance.

  • #2
    Hi Miguel,

    this is just a guess but when you say that you have two different relations to input to your scripting relation. Have these two relations been set to display more than 20 records per page? I could imagine that if you use the scripting relation on a PURL and want to page through the pages in steps of 20, that if the input relations are set to 20 (for example) that the scripting relation also cannot calculate further than 20.

    Hope it can be a point to check.

    Kind regards,
    Marcel

    Comment


    • #3
      Hi Marcel,

      Thank you for your answer.

      Yes, the relations have been set to have more records. Let me try to explain:

      Databases to be imported:

      StoresDB // has stores information
      EventStoreDB // has the store ID and event ID

      I will have 1 record on the eventsDB referring to the event I am viewing on the frontend and I am trying to check if there is any store in the the StoresDB that is related to that event. Each of these databases are set to show 100 records per page. My scripting relation is set to show 20 per page.


      Here is the code:

      BEGIN METADATA

      BEGIN COLUMNS
      COLUMN "ID" "int"
      COLUMN "storeName" "string"
      COLUMN "storeCounty" "string"
      COLUMN "storePostcode" "string"
      COLUMN "storeCode" "string"
      COLUMN "areaName" "string"
      COLUMN "regionName" "string"
      COLUMN "nEvents" "int"
      COLUMN "selected" "string"
      END COLUMNS

      END METADATA

      if(Mode == "GetCount" || Mode == "LoadData")
      {

      var storesTable = Data.GetTable("stores", "storesTable");
      var eventsStoreTable = Data.GetTable("storeEventView", "storeEventViewTable");

      if(Mode == "GetCount")
      {

      MyTable.ResultCount = storesTable.GetRecordCount();
      }
      else if(Mode == "LoadData")
      {
      storesTable.Load(MyQuery.CurrentPage, MyQuery.ResultsPerPage); // storeDB table
      eventsStoreTable.Load(MyQuery.CurrentPage, MyQuery.ResultsPerPage); //eventStore relation table

      var selected = [];
      var j = 0;
      var n = 0;

      //Check if store is related to event
      for(j = 0; j < storesTable.RowCount; ++j){
      var currentStoreRow = storesTable.GetRow(j);
      for(n = 0; n < eventsStoreTable.RowCount; ++n){
      var currentEventRow = eventsStoreTable.GetRow(n);

      if(currentStoreRow.GetValue("storeID") == currentEventRow.GetValue("storeID"))
      {
      selected[j] = "Yes";
      n = eventsStoreTable.RowCount;
      }
      else
      {
      selected[j] = "No";
      }
      }

      }


      // ADD TABLE ROWS
      for(var i = 0; i < storesTable.RowCount; ++i)
      {
      var newRow = MyTable.AddRow();
      var currentRow = storesTable.GetRow(i);

      newRow.SetValue("ID", currentRow.GetValue("storeID"));
      newRow.SetValue("storeName", currentRow.GetValue("storeName"));
      newRow.SetValue("storeCounty", currentRow.GetValue("storeCounty"));
      newRow.SetValue("storePostcode", currentRow.GetValue("storePostcode"));
      newRow.SetValue("storeCode", currentRow.GetValue("storeCode"));
      newRow.SetValue("areaName", currentRow.GetValue("areaName"));
      newRow.SetValue("regionName", currentRow.GetValue("regionName"));
      newRow.SetValue("nEvents", currentRow.GetValue("storeNumberEvents"));
      //Adjacent Rows
      newRow.SetValue("selected", selected[i]);

      }
      }
      }

      // end of code

      I'm not sure what I am doing wrong or if it is even possible to do this, I tried to mess about the MyQuery.ResultsPerPage on the databases but even if I put 1000 it doesn't seem to work.

      If you have any other suggestions, I would appreciate it.

      Kind Regards,

      Miguel

      Comment


      • #4
        Hi Miguel,

        I think in your line
        var eventsStoreTable = Data.GetTable("storeEventView", "storeEventViewTable");
        it needs to be
        var eventsStoreTable = Data.GetTable("storeEventView", "eventsStoreTable");

        But I might be mistaken as you said it is loading data, just not enough. Rest of the code seems fine to me. Is there a chance you could attach the campaignexport with the script inside etc.? I understand if not though as this is a public forum.

        Also in:
        storesTable.Load(MyQuery.CurrentPage, MyQuery.ResultsPerPage); // storeDB table
        Have you tried setting some manual values here? Something like:
        storesTable.Load(1, 99999); // storeDB table

        Kind regards,
        Marcel

        Comment


        • #5
          Hi Marcel,

          Thank you for your response,

          I tried both of your advices:

          "
          I think in your line
          var eventsStoreTable = Data.GetTable("storeEventView", "storeEventViewTable");
          it needs to be
          var eventsStoreTable = Data.GetTable("storeEventView", "eventsStoreTable");
          "
          I think the change on this will only change the name of the storeEventView in the scripting relation (SR). Shouldn't affect the scrolling through the pages of the scripting relation (by the way, the name of the actual SR is "storesNotSelected_Select");

          "
          Also in:
          storesTable.Load(MyQuery.CurrentPage, MyQuery.ResultsPerPage); // storeDB table
          Have you tried setting some manual values here? Something like:
          storesTable.Load(1, 99999); // storeDB table
          "

          This part sort of worked and by this I mean, it reversed the problem. Now the first page throws me an error "
          Can't load relational data [storesNotSelected_Select]: [Line 47 | Column 43 - 63]: Stack empty. []"
          which I believe is related to the max timeout of 2000ms we have on our server, but it would show me the rest of the data once I go through the data pager.

          I also tried to do:

          storesTable.Load(1, 40);

          Since I specified 20 records per page in the scripting relation settings it would show me the first and the second pages, after that I would get blank pages.

          One of my thoughts is to try and get the value of how many records the stores table has, but at the moment I have 1300+ records which would also take too much time to load.

          The best solution I can think of would be to load the specific page once I click a button on a data pager. Is this something that is possible to do? I can get the button on the data pager to submit the page and to change pages, but I don't know how to tell the scripting relation which page I am on. Is this something you've done before or that you happen to know how to do?

          I really appreciate your help, unfortunately I cannot share the campaign, but I have attached some screenshots for you to see.

          Kind Regards,

          Miguel
          Click image for larger version

Name:	screen1.png
Views:	7
Size:	17.0 KB
ID:	1018Click image for larger version

Name:	screen2.jpg
Views:	3
Size:	95.8 KB
ID:	1020Click image for larger version

Name:	screen3.jpg
Views:	4
Size:	103.8 KB
ID:	1021
          Attached Files

          Comment

          Working...
          X