Summary Grid Creation

[Connonymous]

I am hoping to create a summary grid for my data that looks like the HTML table I created below. Is that possible within Caspio? I can't wrap my head around how that would work while having the grid be dynamic (updating to reflect changes in the criteria of individual employees, able to be searched or filtered by department, etc.). 

The only possible workaround I can think of right now is creating this setup by having each list be an individual DataPage of its own, then deploying those DataPages within the HTML of the website inside the table (i.e. imagine replacing the "List of employees..." with the iframe or script that contains each table). That is not a solution I like because it involves using so many DataPages... 

Any other ideas would be greatly appreciated!

In case additional information about the setup of the data would be helpful, I have an employee database that contains one entry per employee. Each employee has various pieces of information listed, each in its own field to include things like employee ID, department, job title, etc.

[Volomeister]

Hi @Connonymous

You can set up filters on your report DataPage to filter out records based on external parameters:
https://howto.caspio.com/parameters/tech-tip-custom-filter-elements/

So you will have Criteria 1 and Criteria 2 filters that accept Criteria_1 and Criteria_2 external parameters.
Then, using iframe deployment, you can deploy the same DataPage 9 times and each time pass different values to Criteria 1 and Criteria 2 filters via query string in the iframe source. For example, 

<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=A"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=B"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=C"></iframe>

And so on. 

If you need to add additional search filters on top of these Criteria 1 and Criteria 2 so that they accept dynamic values based on search input, additional JavaScript will be required.
The flow can be summarized as follows:

1. You create a submission form DataPage that will act as a search form. This submission form should have only virtual fields, and the destination after submission should be the same page if you want search results to be below the search form.
2. JavaScript is needed to listen to "FormSubmitted" event. JavaScript will collect values from search input and add additional query string parameters with these values to source URLs of iframes. For example, if you need to filter out records based on department after search submission, the iframes will look something like this:

<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=A&Department=ValueFromSearchInput"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=B&Department=ValueFromSearchInput"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=C&Department=ValueFromSearchInput"></iframe>

[Connonymous]

6 hours ago, Volomeister said:

Hi @Connonymous

You can set up filters on your report DataPage to filter out records based on external parameters:
https://howto.caspio.com/parameters/tech-tip-custom-filter-elements/

So you will have Criteria 1 and Criteria 2 filters that accept Criteria_1 and Criteria_2 external parameters.
Then, using iframe deployment, you can deploy the same DataPage 9 times and each time pass different values to Criteria 1 and Criteria 2 filters via query string in the iframe source. For example, 

<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=A"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=B"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=C"></iframe>

And so on. 

If you need to add additional search filters on top of these Criteria 1 and Criteria 2 so that they accept dynamic values based on search input, additional JavaScript will be required.
The flow can be summarized as follows:

1. You create a submission form DataPage that will act as a search form. This submission form should have only virtual fields, and the destination after submission should be the same page if you want search results to be below the search form.
2. JavaScript is needed to listen to "FormSubmitted" event. JavaScript will collect values from search input and add additional query string parameters with these values to source URLs of iframes. For example, if you need to filter out records based on department after search submission, the iframes will look something like this:

<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=A&Department=ValueFromSearchInput"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=B&Department=ValueFromSearchInput"></iframe>
<iframe  src="DATAPAGEURL?Criteria_1=A&Criteria_2=C&Department=ValueFromSearchInput"></iframe>

Thank you for this walkthrough, that makes sense in theory! I will spend some time messing around with this idea and see if I can implement it as described. I do have a dashboard style setup now that uses a submission page as the search page for multiple different datapages, so I am familiar with that setup in general, but I have not passed external parameters from outside of Caspio before.

Just to be sure I understand properly, if:
- My datapage URL is "Caspio.com/mydatapageurl"
- Criteria_1 and Criteria_2 are the names of the fields I would like like to filter on
- A, B, and C are the values that would be in the Criteria_1 and Criteria_2 fields
- Department and Class_Title are two fields that are included in the external search field

Then my final iframe setup would read:

<iframe  src="Caspio.com/mydatapageurl?Criteria_1=A&Criteria_2=A&Department=ValueFromSearchInput&Class_Title=ValueFromSearchInput"></iframe>
<iframe  src="Caspio.com/mydatapageurl?Criteria_1=A&Criteria_2=B&Department=ValueFromSearchInput&Class_Title=ValueFromSearchInput"></iframe>
<iframe  src="Caspio.com/mydatapageurl?Criteria_1=A&Criteria_2=C&Department=ValueFromSearchInput&Class_Title=ValueFromSearchInput"></iframe>

 

Thanks again for taking the time to assist! 

[Connonymous]

@Volomeister Thank you again, this seems to be working! There just seems to be one small issue-- my fields don't update when I click the "submit" button on my external filter, they only update if I enter the new values, then refresh the browser. I am guessing this is what you were referring to when you mentioned that JavaScript would be needed. I do not have a background in JavaScript, but I tried to pull something together and it doesn't seem to be working. I added this script to my webpage where the class that houses my search function is 'search_iframe' and the class that houses the iframes that I would like to reload would be 'grid_iframe'. Is there an issue with this script?

    <script>

      document.addEventListener('DOMContentLoaded', function() {

        var topIframe = document.querySelector('.search-iframe');

        if (topIframe) {

          topIframe.contentWindow.addEventListener('FormSubmitted', function(event) {

            var iframes = document.querySelectorAll('.grid_iframe');

            if (iframes) {

              for (var i = 0; i < iframes.length; i++) {

                iframes[i].contentWindow.location.reload();

              }

            }

          });

        }

      });

    </script>

[Volomeister]

Hi @Connonymous
 
You need to embed iframes into the footer of your search form.
Also, you need to add cbResetParam=1 to the iframe's source so that parameters do not get stuck in the cache.

I did this setup on my end, and here is what I have in the footer on the submission form DataPage:
 

<iframe src="https://c7eku786.caspio.com/dp/7f80b000be1b30686b1642d39c6c?Criterion_1=A&Criterion_2=A&cbResetParam=1" class="dpIframe"></iframe>
<iframe src="https://c7eku786.caspio.com/dp/7f80b000be1b30686b1642d39c6c?Criterion_1=A&Criterion_2=B&cbResetParam=1" class="dpIframe"></iframe>
<iframe src="https://c7eku786.caspio.com/dp/7f80b000be1b30686b1642d39c6c?Criterion_1=A&Criterion_2=C&cbResetParam=1" class="dpIframe"></iframe>

<iframe src="https://c7eku786.caspio.com/dp/7f80b000be1b30686b1642d39c6c?Criterion_1=B&Criterion_2=A&cbResetParam=1" class="dpIframe"></iframe>
<iframe src="https://c7eku786.caspio.com/dp/7f80b000be1b30686b1642d39c6c?Criterion_1=B&Criterion_2=B&cbResetParam=1" class="dpIframe"></iframe>
<iframe src="https://c7eku786.caspio.com/dp/7f80b000be1b30686b1642d39c6c?Criterion_1=B&Criterion_2=C&cbResetParam=1" class="dpIframe"></iframe>

<iframe src="https://c7eku786.caspio.com/dp/7f80b000be1b30686b1642d39c6c?Criterion_1=C&Criterion_2=A&cbResetParam=1" class="dpIframe"></iframe>
<iframe src="https://c7eku786.caspio.com/dp/7f80b000be1b30686b1642d39c6c?Criterion_1=C&Criterion_2=B&cbResetParam=1" class="dpIframe"></iframe>
<iframe src="https://c7eku786.caspio.com/dp/7f80b000be1b30686b1642d39c6c?Criterion_1=C&Criterion_2=C&cbResetParam=1" class="dpIframe"></iframe>

<script>
const DepartmentInput = document.querySelector('#cbParamVirtual2')
const ClassInput = document.querySelector('#cbParamVirtual1')


const updateIframeSrc = (e) => {
e.preventDefault()
document.querySelectorAll('.dpIframe').forEach(iframe=>{
let iframeBaseSrc = iframe.getAttribute('src').replace(/&Department=\w*&Class_title=\w*/,'')
iframe.setAttribute('src', `${iframeBaseSrc}&Department=${DepartmentInput.value}&Class_title=${ClassInput.value}`)
})
}

document.addEventListener('BeforeFormSubmit', updateIframeSrc)

 </script>

I decided to run script on 'BeforeFormSubmit' event and use preventDefault() so the page does not reload when the search query is submitted.

Here is this DP: https://c7eku786.caspio.com/dp/7f80b000b0af30fde03a48da9cc5

 

[Connonymous]

@Volomeister Thank you again for the help, I've got it working as expected! 

I see this is working with the code you provided in your app, but I was having some trouble getting this to work. I tried trouble shooting by removing pieces, and then adding them back in one by one until I found the problem. It seems like the JavaScript might not be necessary once the DataPages are in the footer section, because I have the setup that seems to be working exactly as expected without any JavaScript code whatsoever! It really seems like the only step I needed to take was including the iframes in the footer, rather than directly in my webpage.

Thanks again for the idea, as well as the help with the execution!