Search the Community

I'd like to add some HTML to my Datapage to conditionally change the color of a report's background and text color based on the value of a calculated field. This field finds the number of days left until a project is due. I'd like to change the text color of this field to red and the background to yellow if it is past due (if the days left are negative). I know there are a couple of other pre-existing threads on this topic but for some reason I am having no luck. I pasted these in the source of my header but they had no effect. Let me know if you see anything wrong with my formulas: //for the background <a id="visi[@calcfield:1#]"> <script> var isi = document.getElementById("visi[@calcfield:1#]"); if(isi < 0){ isi.parentNode.parentNode.style.backgroundColor = 'red'; } else{ isi.parentNode.parentNode.style.backgroundColor = '#NoColor'; } </script></a> //for the text <div id="mydiv[@calcfield:1#]"> </div> <SCRIPT LANGUAGE="JavaScript"> if ("[@calcfield:1#]" < "0"){ document.getElementById("mydiv[@calcfield:1#]").innerHTML ="<span style='color:red;'> [@calcfield:1#] </span>"; } </SCRIPT>

How can I hide some columns in tabular report without having extra spaces? I have some calculated field which I need to hide.

I am trying to get the freeze headers in tablular report script to work and am unable to get it working. I have followed the instructions at the link below but it is still not working. Has anyone had success in getting this script to work? It is for a tabular report in an iframe inside an Caspio HTML datapage. http://forums.caspio.com/index.php/topic/4678-j-freeze-header-in-a-tabular-report/

Hi there, For one of my projects, I needed to display 0-100 values in one of the fileds on tabular report in a form of a progress bar. Here is an example of how it looks: https://c7eku786.caspio.com/dp/7f80b0006598726cb69c4239b1dc In this forum post I would like to share 2 solutions to achieve this. One presuposses using only HTML/CSS and the second one requires JavaScript. 1. HTML/CSS solution. 1.1 Add next CSS to the header of your DataPage" <style> .progressbar-container { background-color: #f1f1f1; } .progressbar { background-color: #9e9e9e; height: 24px; padding: 5px; color: white; }</style> 1.2 Add HTML block to your tabular report (results page) that will represent a progress bar 1.3 Add next HTML code to the HTML block: <div class="progressbar-container"> <div class="progressbar" style="width:[@field:ProgressBar]%;">[@field:ProgressBar]%</div> </div> 1.4 Substitute [@field:ProgressBar] with the field name where you store values from 0 to 100 2. JavaScript solution 2.1 Add the same CSS code as in 1.1 2.2 Add next JavaScript snippet to the header of your DataPage (below or above style) <script> if (typeof addProgressBar == 'undefined') { const progressBarPosition = 3 const addProgressBar = ()=> { document.querySelectorAll(`.cbResultSetTableCellNumberDate:nth-child(${progressBarPosition})`).forEach(progressBarCell => { let value = progressBarCell.innerText progressBarCell.innerHTML = `<div class="progressbar-container"> <div class="progressbar" style="width:${value}%;">${value}%</div> </div>`}) document.removeEventListener('DataPageReady', addProgressBar) } document.addEventListener('DataPageReady', addProgressBar) } </script> 2.3 Substitute value of progressBarPosition variable so it matches the position of your field that holds 0-100 value in the report DataPage Difference between this approaches is that HTML version of the progress bar will not allow sorting once you click on the field label where JavaScript approach allows sorting once field label is clicked. Hope this helps to someone

Hi, I'm trying to hide two columns in a tabular report if one of the fields has null values or is empty. I've tried the following Java Script several different ways, but can't seem to get it to work correctly. A submission form feeds this tabular report, and if the user doesn't enter values in the 'Qty' field, it should be hidden on the tabular report. <script> var stl='none'; var tbl = document.getElementsByTagName('table')[0]; var rows = tbl.getElementsByTagName('tr'); var EmpQty = ("[@field:Employee_Productivity_Qty]").length; if (EmpQty<0) { for (var row=1; row<rows.length;row++) { var cels = rows[row].getElementsByTagName('td'); cels[7].style.display=stl; cels[8].style.display=stl; } var heads= tbl.getElementsByTagName('th'); heads[7].style.display=stl; heads[8].style.display=stl; } </script> Thank you!

Hello, I would like to create a calculated field in tabular report datapage that displays a column in related table as a concatenated string. For Example, many of my apps use the tblPlants as a data source. This is a table of plant species that has a one-to-many relationship with many other tables. I would like to present a field in the report that lists the the botanical gardens for each plant is found in from the tblBG as a string. SELECT STRING_AGG(tblBG.Garden,",") FROM tblBG WHERE tblBG.PlantID = [@field:PlantID]; I get an error that say there is a syntax error near "(", which I take to meant hat the STRING_AGG function does not exist. Is there another function I could use to accomplish this?

Hi, is there any way to download the data from a Tabular Report Datapage via the API? Or even export it?

Hi guys, Is it possible to print the tabular report excluding some data? I have a tabular report, where we keep some data and one of the column is important for analytics, but should not be included in the printed report. It is a report with totals and aggregations, so I want to see in the printed report the totals without the aggregated fields, only the total without the data it combines. I know it might be possible to create two different datapages, but it is not good practice to use a datapage just for printing a single report sometime. Does anybody have any JS or CSS suggestions for my case?

Hi there, I have a tabular report datapage, that I am using to display calculations from my table. I want to show week number, and what has happened during that week. So far I have 2 columns other than week number, number of cards updated, and total number of updates. The calculations I am using are working fine, but it's showing each number as a separate row. So if the calculation for week 6 is 7 updates, it shows 7 rows. See attached screenshot. Below is my code. Anyone know how I only show each line once (so there's only 1 row for each week number) Number of cards updated SELECT COUNT (DISTINCT [@field:Card_ID]) FROM Card_comments_and_updates WHERE Week=target.[@field:Week] Total number of updates SELECT COUNT([@field:Card_ID]) FROM Card_comments_and_updates WHERE Week=target.[@field:Week]

Newbie to Caspio. I have a tabular report with a details page and I want to create a text field so Users can add comments. Then another user for example can open the details page, see the 1st user's comment and add their own comment. So the final result should look like a textbox to add comments And any comment that were previously submitted from other users. Similar to the image below

I'd like for file paths in my tabular report to be translated as links so that I can click them and see the related file. Is there a way to do this?

I have a Tabular report in which Bulk Edit is enabled. I would like to have a Javascript that will allow to automatically put a check in the Select All check box and automatically click the BULK edit link. Is this possible?

I am trying to create a pop-up child form in the form of a Tabular Report datapage, as I want the user to be able to edit information relating to existing contacts or add a new contact for the organization; the organization is unique and represented by Org_ID on the original Submission Form. For additional records, I hope to set the inline insert value for the organization to its Org_ID, which is the parameter I want to filter the report on. If at all possible I would like to hide this Org_ID, and show the organization name either in the tabular report or ever better in the report Header. The link to open a pop-up would be next to the select a contact dropdown on the Submission form.

I want to created a multi record audit function, ie the client clicks on a check box to approve a record in a list. There could be many hundreds of lines in the list. The approval is for a parent table from the criterior specified in the child table. I created a view for this and make the parent table the editable table in the view and I have a field for the checkbox in the view. However if I have multiple records in the child then in grid edit in a tab report I would need to click on all rows of the child table. I want to only click on one check box from the parent table. Is there another way around this? Create a tab report for the parent and then an iframe with the report of the child records?

Hello, I saw this How-to article on changing the background of a row based on the value of a field: http://howto.caspio.com/tech-tips-and-articles/advanced-customizations/how-to-dynamically-change-the-background-of-a-results-page/ However, I need to delete the row instead of changing its background color. Also, I need to delete the row when a calculation returns a blank value. Is this possible? I don't know how to write JavaScript code.

I have two tables - accounts and invoices. I want to create a tabular report where each record contains the sum of AccountBalance by AccountType and uses calculated fields to return data from the invoice table. How do I create a tabular report that is first grouped by AccountType so that I can eliminate multiple rows and provide a higher level report? Can this be accomplished with a view of Accounts? ACCOUNTS Account# AccountType AccountBalance 1 1 50 2 1 50 3 2 75 INVOICES Invoice# Account# InvoiceTotal 1 1 10 2 2 20 3 3 10 Desired Report ------------ AccountType AccountBalance Invoice RemainingBalance 1 100 30 70 2 75 10 65 Instead of --------- AccountType AccountBalance Invoice RemainingBalance 1 50 30 20 1 50 30 20 2 75 10 65

Sharing this in case you need to show the text instead of ID value in report datapage. Steps: 1) Make sure you have a table that has your lookup values 2) Add a Calculated Field in your Tabular Report DataPage and insert the code below SELECT LookUpTableDisplayText FROM LookUpTable where LookUpTableIDvalue='[@field:IDFieldInDataPage]' 3) If you want to hide the ID column, you can use the steps below.

I need to ensure that values in a column in a tabular report are between 0 and 100 (They are meant to be a percentage impact figure to be entered by the user). I studied the JS example of creating column totals in a tabular report (see http://forums.caspio.com/index.php/topic/3171-js-calculate-column-sum-in-the-result-page/ ) and modified the code as follows, but I cannot get the script to fire at all. I added a number of debugging statements to no avail. I have tried an "onsubmit" type of call as well, apart from the below example. Ideally there should be a way to fire this at the end of adding / editing each row, but I could live with at the end of the whole page as well. At the end of the Header: <div id="cb_checkImpact"> At the beginning of the Footer: </div> <script> function f_check() { alert('Inside function'); var v_impact = 0; var cas_form = document.getElementById("cb_checkImpact"); if (cas_form.getElementsByTagName("table").length > 0) { alert('Found records'); var cas_rows = cas_form.getElementsByTagName("table")[1].getElementsByTagName("tr"); for(var rowIndex=1; rowIndex < cas_rows.length; rowIndex++) { alert('Inside row loop'); var cells = cas_rows[rowIndex].getElementsByTagName("td"); /* 1 – change value inside brackets to choose column to perform validation */ v_impact = cells[2].innerHTML; alert('Found value:'); alert(v_impact); if ( parseFloat(v_impact) < 0 || parseFloat(v_impact) > 100){ alert('Value in %Impact field must be between 0 and 100'); return(false); } alert('No issues found'); } } alert('About to call function'); f_check(); alert('Finished function'); </script>

I need to combine two (at times more, but once I can resolve for 2 I can easily extend) fields into a third field and commit the value back into the database, all the while working with fields in the results page of a tabular report. The example on the forum, ( http://forums.caspio.com/index.php/topic/3200-js-concatenatecombine-two-fields-into-one-field/ ) , is good but only for submission forms, and the comments on extending it also apply only to update forms or detailed pages of tabular reports. So basically it can't work within a given row in a table as it stands now. I have also checked out the example in ( ) but this one is about totalling a column across multiple rows, and it anyway doesn't commit the results to the database, whereas I need to combine multiple columns across the same row, AND commit to the database. Combining the learnings from the above two examples, I have tried as follows but it is not resulting in any value being inserted in the target field, and my suspicion is I need to do something different in the very last statement of the function. Please advise. 1. Added an HTML block at the top of the list of fields (so effectively the first column in the tabular report) containing: <div id="row_at_a_time"> 2. Added an HTML block at the end of the list of fields (so effectively the last column) containing: </div> <SCRIPT LANGUAGE="JavaScript"> function concatenate() { var ProcessName = document.getElementById("row_at_a_time").getElementByTagName("Table")[1].getElementByTagName("tr")[1].getElementByTagName("td")[1].innerHTML; var ProjectID = document.getElementById("row_at_a_time").getElementByTagName("Table")[1].getElementByTagName("tr")[1].getElementByTagName("td")[6].innerHTML; var ChkUnique = ProjectName + ProjectID; document.getElementById("row_at_a_time").getElementByTagName("Table")[1].getElementByTagName("tr")[1].getElementByTagName("td")[6].innerHTML = ChkUnique; } document.getElementById("caspioform").onsubmit=concatenate; </script> Note that: - The first [1] in each of the statements is something I don't understand but have simply followed the example - what does it refer to? - The second [1] in in the place where the example uses "row_index" so it should help point to "current row" since we have declared each row of the tabular report as a single-row-table (I believe row indexes start at 1, and not 0, unlike column numbers?) - The last number in each of the statements ([1], [6] and [7]) points to the column numbers and means I am setting column [7] to be the concatenation of [1] and [6]. This will have to be customised for each use of this method in various tabular reports. However I think I am missing the equivalent of "insert record" aspect of the following statement and just setting the variable doesn't complete the job: document.getElementById("InsertRecordChkUnique").value = ChkUnique; And therefore this method is not working. Any thoughts? Incidentally I am using this method to enforce uniqueness across multiple columns, so for example column A and column B are individual not unique but column B needs to have unique entries for all records for a given value of column A. I am concatenating the two columns and forcing the value into a third column (hidden from users) which is set to "unique" so that the user is stopped from committing such records. If you know of a different way to achieve this which does not need the above solution, that too will solve my original problem anyway. And the key is to make this work in tabular reports, not just submission or update forms. And it needs to fire on insert as well as update. Many thanks!

I have a number of instances of the user moving from one tabular report on a parent entity, by clicking on one of the records, to go another tabular report on the child entity. I am passing parameters through the query string. I have noted that if I happen to delete a record in the parent tabular report, and THEN click on some other record to drill into the child tabular report, then only the parameters of type [@field:XXX] get passed correctly, and other parameters of type [@XYZ] get dropped. This dropping does not happen if I have not deleted any record in the parent tabular report. This functionality is deep inside the app and you can't replicate the issue by simply going directly to that page so I am not including any URL at this stage. I have raised a trouble ticket as well in parallel. This has become urgent, appreciate the help.

I am trying to implement logic on search fields such that the same column needs to be compared to two different values with an OR in between - like USERNAME = null OR USERNAME = [@authfield:USERNAME]. It is not accepting such entries; the auto editor keeps changing both the OR branches to equate to the same thing. Is there a way to implement such logic?