Filtering Many Rows of Data to Select Only 1 Row

[Newlogiq]

Hello...I need some guidance, please.  I have a view of 2 tables.  The first table contains customer information and an order number.  The second table contains the order number plus a variety of notes associated with that order that have been date and time stamped.  There are multiple notes entries for each order number.  I'm trying to create a report that only shows the order number 1 time when a date from the second table matches some filtering criteria.  Specifically, I want to only show those orders (again, just show order number 1 time) that do NOT have a note within XX days of today.  How would you do this?  I'm fairly new to Caspio...thank you.

[CoopperBackpack]

Hello @Newlogiq,

Firstly, you can add the criteria directly to the View. It should work if you need to apply a condition like an exact number of days.
For example:

 

However, there can be more than 1 record for each order that matches the condition. What is the expected output in this case? Do you need to display those dates as comma-separated values? Or do you need to display the most recent date? 

Do you only need to display the order number and the date? Or do you need to display more fields? 

[Newlogiq]

Hi CopperBackpack...thank you very much for your suggestions.  In my case, I need to produce a report from that data set that shows only the most recent entry in the table for an order where an associated note is more than 5 days old.  If there are 10 notes all older than 5 days associated with the order, I only want to show the order 1 time with the most recent entry.  Please see the attached screen shot of the notes data table. Thoughts?

[CoopperBackpack]

Hello @Newlogiq,

1) The first option is to use a separate Table that stores the records that match the criteria (so, stores 1 record per order number). 

This can be done by Tasks/Triggers. However, it could be complex to keep the table relevant. Triggers/Task should track the changes in the Note field, also the records can be deleted, inserted, etc.

2) The second option is to use the Calculated field and JavaScript. It is not ideal because the idea is to hide the redundant records by JavaScript and the DataPage is displayed slower than without applying JavaScript.

Let`s say I have the following table:

If I understood the requirements correctly, for example, today (02/02/2024) the report should display:

If this is the desired output, here are the steps:

1. Add the Calculated Field on the Results page and the formula with the following logic:

CASE  WHEN

(SELECT MAX(Date)
FROM Customer_Order
WHERE Note = '' AND Order_ID = target.[@field:Order_ID] AND DateDiff(day, Date, SysUTCDateTime()) >=5
GROUP BY Order_ID ) = target.[@field:Date]

THEN 1

END

You need to replace the field names and the table name. Please note that if you use a View and not a Table, you need to reference a View with the prefix _v_ before the View name.

In this formula I select the maximum Date for each Order_ID where the Note field is blank, the Date is 5 days old or more. Then this date is compared with the Date in each row for each Order_ID. If this is the Date I am looking for the formula returns 1.

 

2. The next step is to hide the Calculated field.

For that, add the Header section, disable the HTML editor on the Advanced tab, and paste the CSS code.
In code, "5"  is the position of the Calculated field on the Results page. It is the 5th field in my example:

<style>
table[data-cb-name="cbTable"] th:nth-of-type(5),
table[data-cb-name="cbTable"] td:nth-of-type(5) {
          display: none;
   }
</style>

Also, you can hide the record count since after the records are hidden the number is confusing.

 

3) Add the JavaScript code to the Footer. Disable the HTML editor before pasting the code.

Replace the position number of the Calculated field.

<script>
    document.addEventListener('DataPageReady', hideRecordsHandler);

    function hideRecordsHandler() {

        const calcField = document.querySelectorAll('td:nth-child(5)'); // 5 is the posision number of the Calculated field
     
        calcField .forEach(element => {

             if (element.innerHTML==  '&nbsp;' ) {
                  element.parentNode.style.display= 'none'; 
            }       
        });

  document.removeEventListener('DataPageReady', hideRecordsHandler);
};
</script>

Feel free to update the thread if you have further questions.