Run javascript before caspio pagination logic

[NickO]

I have an odd problem trying to work around the lack of "Distinct" query functionality within Caspio. My data has many to many joins and therefore the view lists a specific field multiple times. When I query to reach a list of these fields, there are obviously multiple rows. I am trying to eliminate those multiple rows and list a single row for each unique value in that field only.

The problem is that the datapage is still formatted as if all the results are present. So I get multiple pages with only a single entry on each. While I can change the view to include more records per page, that does not actually solve the problem. I would like to be able to prevent the pagination or actually remove the extra rows from the result set instead of just hiding them. 

Any ideas?

[BaySunshine]

Hi @NickO,

 

Any time you have a many-to-many relation between two tables, always create a third table which has ids from both tables. That way you avoid having this kind of an issue.

Example of a many-man-many relation:

In this example, Orders and Products table are related many-to-many.  So, a third table is added, Order_Products which will join these two tables individually by one-to-many relationship. 

I hope that helps.

 

Regards,

[Ed727]

NickO -- I am curious if your issue is the same one I am describing in this post -- that if you search a joining table used to create many-to-many relationships, you get duplicate results.   I am still searching for a solution to this issue but no one seems to have one.

 

 

[NickO]

@Ed727 I actually did find a few potential solutions. Please note that none of this is my original work per se. I have stitched together a bunch of different answers and tips I have found through the forums. I would love to give credit where it's due specifically, but I don't recall exactly where all of it came from and I have tweaked some of it to fit my needs more specifically.

The first, and easiest, is to use a Pivot Table type data page and make the data the links or the lookups you want. There are several posts here about how to add those elements to Pivot Tables. The downside is that this is not efficient for large views. I found serious performance issues once I got over about 100k records.

The second is a little more complicated, but is working great for me in a variety of data pages. I'll try to explain as best I can. In a nutshell,  you are searching against a view and then using Javascript to hide any rows that don't match that search. Where this method really gets useful, at least for me, is when I am doing complex joins across multiple tables. I can create my view using Outer joins then whittle the results down based on whatever combination or criteria I choose to use (limited only by your creativity with SQL).

• Start with what I call the "Target" table. For example, if you have Orders and Products linked with a normalizing table as in the above example and you are looking for all the orders that contain a certain product, then your Orders table would be your target table.
• Create a view that links all of your relevant tables together. We will call it OrderProductView for this example.
• Create your datapage with the Target table as the source (Orders for us).
• In your search criteria you will add a Virtual Field that references the names and values for Product_id (again using the tables above as a point of reference)
• In your results, you will add whatever information you want from the Orders table and a Calculated Field
• The Calculated field will be used to create a query that counts the number of "hits" to your criteria. Something along the lines of the statement below. The last criteria is important because it makes sure you are counting hits for the specific row of the datapage.

• SELECT COUNT(DISTINCT(**FIELDNAME**)) from _v_**VIEWNAME**
  WHERE 
  --For criteria fields that are in the target table OR if all inner joins in view being searched
  **CRITERIA FIELDNAME1**=COALESCE(NULLIF('[**CRITERIA PARAMETER1]', ''), **CRITERIA FIELDNAME1**)
  .
  .
  .
  AND
  --For target criteria. I.E. when searching the "Description" table, DescriptionID would be the CRITERIA TARGET FIELD
  **CRITERIA TARGET FIELD**=target.[@field:**TARGET FIELD NAME**]

 

• Specifically for our example, it would be something like this (assuming we use the naming convention in the view of Table_FieldName and the name of the virtual field in the search is configured to pass @ProductID on exit from the advanced tab).

  SELECT COUNT(DISTINCT(Order_Order_id)) from _v_ProductOrdersView
  WHERE
  --For criteria fields that are in the target view
  ((Product_Product_id is NULL AND '[@ProductID]'='') OR Product_Product_id=COALESCE(NULLIF('[@ProductID]', ''), Product_Product_id))
  AND
  --For target criteria. I.E. when searching the "Product" table, Product_id would be the CRITERIA TARGET FIELD
  Order_Order_id=target.[@field:Order_id]

   

• Now that we have a mechanism to count the unique hits, we have to hide any rows that have 0 hits. Add the below code to a HTML block in your datapage and modify the fields as indicated in the notes on top.

• // CHANGE @FIELD:FIELDID FOR ROW KEY ID
  // CHANGE @CALCFIELD TO FIELD WITH COUNT OF QUERY HITS (OR CHANGE CRITERIA COMPLETELY TO MEET NEED)
  
  
  <a id="visi[@field:FieldID]">
  
  <script>
  var isi = document.getElementById("visi[@field:FieldID]");
  if([@calcfield:1#] == 0){
  isi.parentNode.parentNode.style.display = 'none';
  }
  </script></a>

 

IF we've done it all correctly, the datapage should show us only those rows that meet the criteria chosen and the "hit count" column should never actually display a zero.