Leaderboard

I was trying to figure out how to insert a large number of photos (>1000) into a table of staff. I could successfully import the files into caspio bridge, but could not find a documented way to automate the process of inserting these into the table. Support did not think it was possible, but I was able to find a solution! Posting this in case it is helpful for others. Solution: 1. Ensure each file is named in a way that allows it to be linked to the rest of the data. In my case I named each file with the linkage key 'staff_id' e.g. "1.jpg" 2. Upload the files into a folder in Caspio bridge, as documented elsewhere. In my example, this is /photos/. 3. In the table, create a formula field that generates the path of the file that corresponds to the record. E.g. in my dataset, the path for the correct file is "/photos/1.jpg"; where staff_id = '1'. The formula is: '/photos/' + CONVERT(VARCHAR, [@field:staff_id]) + '.jpg' 4. Save this field. It should now contain a generated list of paths. 5. Go back to the Table Design section and change the field type from "Formula" to "File". It will convert the type to "File" but retain the path data and now the file is correctly embedded. I have suggested that Caspio support add this to their documentation as I think it could be a very useful feature.

Why would they (Caspio) spend all that time updating to 3.0 and completely refresh the web interface with a much cleaner look, but FAIL to actually move the damn chat bubble to the other side, out of the way? We have been asking for that for nearly two years now and it has been voted up several times in the IdeaBox, but for some reason is being ignored by Caspio.... Seems like when they were re-working the entire GUI web interface they could have spent the 5 minutes to move the chat bubble too.....

Hello @sfraden, @kpcollier Thank you for providing valuable feedback. We apologize for the inconvenience caused due to the placement of the chat bubble. Our team is working on a solution to address the concern and we understand that chat function is very beneficial to users to conveniently reach out to Caspio Support team for assistance with inquiries. In Release 30, Caspio user interface has been refreshed to improve the navigation and usability. We value your feedback and are committed to continually improving our product.

Thank you @Meekeee! Because in order to provide you the answer I found the culprit: I was referring a var instead of a field, so I was clearing the var instead of the field. Let me share a trick I found very useful to debug triggers: 1) I setup log table (my is i2e_log) with the fields: timestamp; Event; Source; Description 2) Then I inseret into specific points this insert with fiedls filled with var, messages or fields I want to check. Thank you again. I would have another question but it is matter for another topic.

Hello Caspio Family, I wish to share a JS code snippet with you, in case if you wish your users to confirm form submission if a certain condition is met. For example, if some field is empty. Let's say we have a text field for email which is not required. When that field is empty, we wish to ask our visitors if they are sure they want to submit the for without the email. document.addEventListener("BeforeFormSubmit", function (event) { if (document.getElementById("InsertRecordEmail").value == "") { event.preventDefault(); if (confirm("Are you sure you want to submit the form without your email?")) { document.forms["caspioform"].submit(); } } }); Let me know who thinks it is useful.

Hello @JenKri, I think this Forum post suits better for your workflow https://forums.caspio.com/topic/19337-multiple-customers-unique-ticket-number-starting-at-000/ You may check the steps I described in that Forum post. And if to modify the Trigger and Formula for your case: 1) For example, I have the table like this: 2) We are going to insert the Chapter_Number and Year. The Project_Number will be populated with the following Trigger: 3) Formula (the ProjectNumberFinal field) is the following: CASE WHEN [@field:Project_Number] < 10 THEN CAST([@field:Chapter_Number] as nvarchar) + '-' + [@field:Year] + '-' + '00' + CAST([@field:Project_Number] as nvarchar) WHEN [@field:Project_Number] >= 10 AND [@field:Project_Number] < 100 THEN CAST([@field:Chapter_Number] as nvarchar) + '-' + [@field:Year] + '-' + '0' + CAST([@field:Project_Number] as nvarchar) WHEN [@field:Project_Number] >= 100 AND [@field:Project_Number] < 999 THEN CAST([@field:Chapter_Number] as nvarchar) + '-' + [@field:Year] + '-' + CAST([@field:Project_Number] as nvarchar) END 4) The result:

wow, thanks so much! Truly appreciate it. It's been great learning JS and building out our caspio platform - really appreciate the sample code and will give it a try!

@peewee hi, I guess this should help:

@autonumber - I deleted it and it worked perfectly. Thank you so much! Thank you for your reply too @Meekeee!

Hi! Just an update: Caspio has finally released a feature to show DISTINCT records on Reports (Tabular/List/Gallery). It is available in R30 version you may check this link: https://howto.caspio.com/release-notes/caspio-30-0/

Thank you so very much.

@jbloome for implementing it on Details, instead of 'InsertRecordFIELDNAME', you must use 'EditRecordFIELDNAME', and place the code not in the Footer of the page, but add a new HTML block element just after all other elements, and put the code there. Also, additional event listener is needed, here is the entire code: <script> document.addEventListener('DataPageReady', function (event) { const fields = ['EditRecordPhone1','EditRecordPhone2', 'EditRecordPhone3']; const inputs = []; for (let f of fields){ inputs.push(document.querySelector('#' + f)); } for(let i of inputs){ i.maxLength = 14; i.onkeyup = telephize; i.onkeydown = telephize; } function telephize(v_e) { // this.value = this.value.replace( /\D+/g, "" ).replace( /([0-9]{1,3})([0-9]{3})([0-9]{4}$)/gi, "($1) $2-$3" ); //mask numbers (xxx) xxx-xxxx v_e = v_e || window.event; if (v_e.keyCode >= 65 && v_e.keyCode <= 90) { this.value = this.value.substr(0, this.value.length - 1); return false; } else if (v_e.keyCode >= 37 && v_e.keyCode <= 40) { return true; } var v_value = (this.value.replace(/[^\d]/g, '')); if (v_value.length == 7) { this.value = (v_value.substring(0, 3) + "-" + v_value.substring(3, 7)); } else if (v_value.length == 10) { this.value = ("(" + v_value.substring(0, 3) + ") " + v_value.substring(3, 6) + "-" + v_value.substring(6, 10)); }; } document.querySelector('#caspioform').onsubmit = function(e) { e.preventDefault(); if (input.value.length >= 14){ this.submit(); } else { alert('Please input a valid phone number'); input.focus(); } } }); </script>

Hello @MeralomaFC, I have one idea and it requires using only Caspio standard features. Maybe someone can share another idea. I don`t think that we can display the Prefixed Autonumber(Ticket_No ) in the popup right after clicking the Submit button since this is an auto-generated value. It appears in the table only after submitting the new record. So, you may try these steps: 1) Add the Number_Of_Tickets field to your table (data type is Integer) 2) Create a second table with the same design, just change the DataType for the Ticket_No field to Text(255) 3) In the first table you may create a Triggered Action(if this option is included in your plan). The Trigger works on data insert and inserts the number of records that is equal to the Number_Of_Tickets into the second table. For example: And these are the records that are inserted by the Trigger into the second table: 4) To display this data to the user we need 2 DataPages: * For example, Details DataPage that is based on the second table, on the 'Search and Report Wizard - Search Type' you need to select the 'Filter data based on your pre-defined criteria' option and receive the Ticket_No value as an External parameter. Since this is Prefixed Autonumber you need to add manually the 'InsertRecord' before the field name: * Submission Form that you mentioned based on the first table. On the 'Web Form Wizard - Select Fields' you need to check this checkbox to pass the Ticket_No value as a parameter: This article regarding the Passing Parameters can be helpful https://howto.caspio.com/parameters/passing-parameters/ And on the 'Web Form Wizard - Destination and Messaging' you need to select the second DataPage as the destination As a result, once the Submission form is submitted, the user is redirected to the Details DataPage where all the records are displayed. It can be a Tabular Report DataPage instead of the Details, for example: Hope this helps.

Boyah!! I love you guys!!

Hi @CoffeeLover - you may check their online help to customize the tablet and mobile view: https://howto.caspio.com/datapages/responsive-datapages/modifying-styles-for-tablet-and-mobile/

@kpcollier - Appreciate your very detailed response and guidance, I am going to move forward with this. Thank you!

Hi @jbloome and @Meekeee We can also use arrays and loops to further reduce the code: <script> const fields = ['InsertRecordPhone1','InsertRecordPhone2', 'InsertRecordPhone3']; const inputs = []; for (let f of fields){ inputs.push(document.querySelector('#' + f)); } for(let i of inputs){ i.maxLength = 14; i.onkeyup = telephize; i.onkeydown = telephize; } function telephize(v_e) { // this.value = this.value.replace( /\D+/g, "" ).replace( /([0-9]{1,3})([0-9]{3})([0-9]{4}$)/gi, "($1) $2-$3" ); //mask numbers (xxx) xxx-xxxx v_e = v_e || window.event; if (v_e.keyCode >= 65 && v_e.keyCode <= 90) { this.value = this.value.substr(0, this.value.length - 1); return false; } else if (v_e.keyCode >= 37 && v_e.keyCode <= 40) { return true; } var v_value = (this.value.replace(/[^\d]/g, '')); if (v_value.length == 7) { this.value = (v_value.substring(0, 3) + "-" + v_value.substring(3, 7)); } else if (v_value.length == 10) { this.value = ("(" + v_value.substring(0, 3) + ") " + v_value.substring(3, 6) + "-" + v_value.substring(6, 10)); }; } document.querySelector('#caspioform').onsubmit = function(e) { e.preventDefault(); if (input.value.length >= 14){ this.submit(); } else { alert('Please input a valid phone number'); input.focus(); } } </script>

Thanks for the link--I'll check it out but I pretty much gave up. I need the basic ability of Caspio Bridge being able to preview uploaded pdf's, because they need to be private.

will someone help me into a situation I need to place a date and time stamp on oneof my pages and I dont know how ( I was thinking a Java scrip or a tiny app) thankyou

@Meekeee - Thank you SO much, I will experiment with this and advise if I have any additional questions. Thank you!

Thanks @Meekeee.

Hi @Gigigigi - Just to confirm, for every change in the 2nd radio button - you would like your 2nd or 3rd dropdown to be blank or back to its first option(which is no value), is that correct? If yes, you may try to use this custom JS: <script> document.querySelector("input[id*='SPECIFIC_ID_OF_YOUR_RADIOBUTTON']").addEventListener("change", function test(){ //checks if there are two or more options already if(document.querySelector("select[name='SPECIFIC_NAME_OF_YOUR_DROPDOWN']").options.length >= 2){ //selects 1st value in the dropdown, count starts from [0] document.querySelector("select[name='SPECIFIC_NAME_OF_YOUR_DROPDOWN']").options[0].selected=true; } }); </script> make sure to check the Form Elements of your DataPage here: If you want to have additional condition to other radio buttons, you just need to copy both scripts: <script> //Any Radio Button document.querySelector("input[id*='SPECIFIC_ID_OF_YOUR_RADIOBUTTON']").addEventListener("change", function test(){ //checks if there are two or more options already if(document.querySelector("select[name='SPECIFIC_NAME_OF_YOUR_DROPDOWN']").options.length >= 2){ //selects 1st value in the dropdown, count starts from [0] document.querySelector("select[name='SPECIFIC_NAME_OF_YOUR_DROPDOWN']").options[0].selected=true; } }); //Approved Radio Button document.querySelector("input[id*='SPECIFIC_ID_OF_YOUR_RADIOBUTTON']").addEventListener("change", function test(){ //checks if there are two or more options already if(document.querySelector("select[name='SPECIFIC_NAME_OF_YOUR_DROPDOWN']").options.length >= 2){ //selects 1st value in the dropdown, count starts from [0] document.querySelector("select[name='SPECIFIC_NAME_OF_YOUR_DROPDOWN']").options[0].selected=true; } }); </script> Hope it helps!

Hi @CoffeeLover - for additional information of Caspio's system requirements: https://howto.caspio.com/system-requirements/ Hope it helps!

Hi @kpcollier - Have you tried to create a calculated field that will have a conditional link based on certain criteria? Perhaps like this one: CASE WHEN Commercial_Service is checked THEN Commercial SRU form link ELSE Residential SRU form link Then this calculated field can be referenced to another HTML Block for <a> tag or button.

Hi @Meekeee, Thanks for your response. I'm not going to worry about the Facebook App browser as I have read most people disable it and use Safari or Chrome. I am doing some testing on a IPhone using Safari today and I'll let people know if Safari acts the same way as Chrome. Kind regards CoffeeLover

Thanks for posting this. I'm having a similar issue and this is helping work around.

I don't know if this will fit your workflow but I have this CSS code that I am using for a tabular report. I have tested this in a pivot table as well so it should work on your pivot properly: Put this code in the Header: <style> #target table:nth-of-type(1) td:not(:nth-of-type([@FieldtoDisplay])) {display: none;} #target table:nth-of-type(1) th:not(:nth-of-type([@FieldtoDisplay])) {display: none;} /* The part below is for excluding a column from being hidden by default*/ #target table:nth-of-type(1) td:nth-of-type(1) {display: table-cell;} #target table:nth-of-type(1) th:nth-of-type(1) {display: table-cell;} </style> <div id="target"> And this in the Footer: </div> Basically, what this code does is hide all the columns by default except the column specified using the NOT selector of CSS. You can specify your column by passing a parameter from your separate search form [@FieldtoDisplay]. This parameter should contain a integer value that corresponds to the position of the column that needs to be displayed. The second part of the code is optional, it can be added if you wish to diplay a certain column regardless of the selection. Let's say you have a column for ID or Name and you want them to be visible at all times, you can specify their position and change "1" to exclude them from being hidden.

Hi @Meekeee, Yes I have had no issues with passing parameters either by URL or by Caspio Datapage to Datapage. Most my parameter passing is done through Caspio but I have a few instances where I use JotForms where I pass parameters by URL back to Caspio forms. You do need a paid account to be able to embed HTML such as Caspio datapages. Cheers CoffeeLover

Yes, it seems easier to do all the converting and reformatting in excel. Many thanks!

Hello @jboder2, I can see redundant open parenthesis before [@cbParamVirtual1]. Also, please wrap text values in single quotes. You may test this: CASE WHEN [@cbParamVirtual1] = 1 THEN 'Approved' WHEN [@cbParamVirtual1] = 2 THEN 'Denied' ELSE 'Pending' END Hope this helps.

By default, Text area values in Results page are rendered as text / string so we need to render it first to HTML so the HTML tags will not be shown.

Hi @CoffeeLover, Thank you for explanation. As far as I can see, because the values of authentication fields are taken at the moment of login, they remain unchanged during the entire session. Only way to refresh data is starting a new session - log out and then again log in. I found this Forum thread about that also: I'm guessing that for required workflow, you might need to use additional table.

Hi @CoffeeLover, To update user authentication fields, you can use Single Record Update DataPage, with the Users table as the Data Source. Then you can restrict access to the page based on Authentication: https://howto.caspio.com/datapages/forms/update-forms/ I'm not sure what do you mean by "How can I have the users authfields updated if changed? (without them logging off and loggging back in)", could you specify a little bit more?

If your page 2 is a webpage where you have your datapages deployed, make sure that they are deployed there by embed code and not by iFrame since iFrame does not allow parameters to come in easily. But if that is the case, you may want to see this article:

If you have it set up as NiceDuck described, and you are sure that the parameter name in the URL is the same with the [@parameter_name], you should be getting it. A good test is to try calling it by itself first before applying to a button to see if a value is being received. You can just like disable html editor on the HTML datapage and put [@testparam] then preview it. If it show the value then you are receiving it and might just a problem in using the value for the redirection.

Just to update this post, you may consider checking this updated link for app access logs. The App access logs allow you to monitor the activities of the authenticated users that access your apps. https://howto.caspio.com/logs/app-access/

Hi @CoffeeLover, You can use the following code to check if there is an error prior to submit, and submit only if there are no errors: <script type="text/javascript"> const error = document.querySelector('div[data-cb-name="HeaderErrorMsg"]'); if(!error){ setTimeout('document.getElementById("caspioform").submit()',1); } </script>

Hi @kpcollier Great spot by you, the proposed solution does not work if we have search form, and we are experiencing some strange behavior - hiding some other fields as you described it during consecutive search. The reason for this is because it appears that when 'Search' button is clicked, the code from footer is not executed only once, but a couple of times. In order to execute the code only once, when page is first reloaded, we can add an IF statement before the actual code, which will check how many columns are still there: <script> document.addEventListener('DataPageReady', function (event) { const check = document.querySelectorAll('table[data-cb-name="cbTable"] th'); /* Just set the number in IF below to the number of columns you have BEFORE hiding anything */ if(check.length===9){ // code goes here } }); </script> Additionally, I refactored whole code to be able to hide any number of columns without adding redundant code, just add or edit arguments in function deleting : <script> document.addEventListener('DataPageReady', function (event) { const check = document.querySelectorAll('table[data-cb-name="cbTable"] th'); if(check.length===9){ const deleting = (...args) =>{ let label, values for(let a of args){ label = document.querySelector(`table[data-cb-name="cbTable"] th:nth-of-type(${a})`); values = document.querySelectorAll(`table[data-cb-name="cbTable"] td:nth-of-type(${a})`); label.parentElement.removeChild(label); values.forEach(el => { el.parentElement.removeChild(el); }); } } /* In below function call, just put any number of column you want to hide, but in reverse order - from highest number to the lowest */ deleting(8,5); } }); </script>

If you want to place Timestamp in Submission Form, you can use Virtual Field and set it in Advanced tab to Receive value or parameter like in the screenshot: The result is as follows: In the Report DataPages, you can just use Calculated field with value SysUTCDateTime() or SysDateTime()

Hi Clay, I encountered the same issue actually and followed a similar path to use CSS to hide and reveal the nav. So far it has worked pretty well and I have integrated the approach into my site using the authentication object (i.e. under "Authentica" in the caspio bridge). In my authentication object for the datapages I added a header\footer and in the footer I placed the following javascript: <script> if("[@authfield:tbl_Users_OfficeReadAll]" == "Yes"){ } else if ("[@authfield:tbl_Users_FieldTechnician]" == "Yes"){ } else{ var nav = document.getElementsByClassName("wsite-menu-default"); nav[0].style.visibility = "hidden"; nav[1].style.visibility = "hidden"; } </script> The javascript DOM call document.getElementsByClassName refers to the classname used by Weebly for the nav, and this approach allows you to hide the nav in both the mobile and desktop style browsers. The script will hide the nav unless the authenticated user is of a particular type (in my case one of two types). You can the same portion of the script to reveal the nav as well if necessary: <script> var nav = document.getElementsByClassName("wsite-menu-default"); nav[0].style.visibility = "visible"; nav[1].style.visibility = "visible"; </script> good luck! hope that helps

Upon further investigation it turned out that is it possible not to specify the field name in thge formula if the Aggregation function is the same for those fields. So, I used this formula and it works correctly for each field: However, if it is needed to use different Aggregation function (Sum, Avg, Count, etc.) and display the result in 1 row (1 Aggregation), then the solution that I described before can be helpful.

Hello @VincenzoCocciolo, I would like to update this thread since I wanted to achieve the same result as in your request. I need to create a Report like this: So, I added 3 Totals&Aggregations fields. I cannot find the option to have only 1 Totals&Aggregations field, since I used custom Formulas (I need to display the sum of values for Sales, Costs, Profit without the Central Region). My formulas have the following syntax: SELECT SUM(Sales) FROM Sales_By_Region WHERE Region_Name !='Central' SELECT SUM(Costs) FROM Sales_By_Region WHERE Region_Name !='Central' SELECT SUM(Profit) FROM Sales_By_Region WHERE Region_Name !='Central' And I tried the following steps: 1) In the Header section I added the CSS code to hide the second and third Totals&Aggregations fields. Also, there is the CSS property to align the values: <style> tr[data-cb-aggregation="Aggregate2"], tr[data-cb-aggregation="Aggregate3"] { display: none; } td.cbResultSetTotalsData{ text-align: right !important; font-weight: bold !important; } </style> 2) In the Footer section I added this JS code: <script> document.addEventListener('DataPageReady', moveTotalsToOneRow) function moveTotalsToOneRow() { const totalsSecond = document.querySelector('tr[data-cb-aggregation="Aggregate2"] td:nth-child(2)').innerHTML; //select the value from the Aggregate2 const totalsThird= document.querySelector('tr[data-cb-aggregation="Aggregate3"] td:nth-child(2)').innerHTML; //select the value from the Aggregate3 document.querySelector('tr[data-cb-aggregation="Aggregate1"] td:nth-child(3)').innerHTML = totalsSecond; //assign the value of the Aggregate2 to the Aggregate1 row document.querySelector('tr[data-cb-aggregation="Aggregate1"] td:nth-child(4)').innerHTML = totalsThird; //assign the value of the Aggregate3 to the Aggregate1 row document.removeEventListener('DataPageReady', moveTotalsToOneRow); } </script> Maybe you will need to change the element order number in selectors like td:nth-child(2), etc. 3) Here is the result: Also, I want to add that I need a Report without the Search option, so this solution works fine. However, there can be some nuances with the Search to display the correct result. Hope this helps. And maybe someone has more ideas.

Hi, I need to save history of changes of the fields in my table. For example, if a user changes his address I'd like to save his old address as well. Thanks in advance!

Just wanted to add, here are the other references regarding API from the Caspio's howto link https://howto.caspio.com/web-services-api/creating-a-web-services-api-profile/ https://howto.caspio.com/web-services-api/rest-api/authenticating-rest/ https://howto.caspio.com/web-services-api/web-services-api/ https://howto.caspio.com/web-services-api/rest-api/important-header-parameters/

@drckarcher, will the users have 1 role each or more than 1? I personally have a Role field in my User Table, as each user is assigned to 1 role (that can be changed if needed). I am not sure if your Role is related to the User's hierarchy in the company or related to the specific Job itself. If the former, I would add Role to the Staff table. If the latter, I would add Role to the Job_Schedule_Table (see below). As for the jobs and assigned dates, I believe you should create more tables. One for all of the Jobs and one for assigning staff to a job. Put all of your job information in the Job Table (Job name, address, contact, etc). Then, in the Job_Schedule_Table (or whatever you want to call it) you would have a field for User ID, Job ID, Start Time, End Time, (add Role here if it is for Job-level, discussed above) and whatever else you would like to include. This way you can assign as many users to as many jobs as you want and have the database be normalized. You will be able to pull data and create views on all of these tables to create staff profiles.

Hello @Shreecaterers, Swagger claims to be the most popular Open-source documentation template. It is a popular third-party tool for developers to effortlessly interact and test against Caspio’s Bridge REST API using a visual interface. Check this article for more information on how you can use the Swagger UI and Caspio: https://howto.caspio.com/web-services-api/rest-api/swagger-ui/ Regards

the answer is this: in rules for datapages based on virtual calculated field queries of Yes/No variables, the values are Y and N.

Hi @IamBlossom, For File DataTypes, you need to use SQL's "DATALENGTH" to measure if the field has content or not. It is a behavior that NOT NULL or IS NULL will not work for File Datatype. Use this instead: CASE WHEN DATALENGTH([@field:Images]) = 0 THEN 'No image attached' ELSE 'Image available' END Hope this helps!

Hi all, I just want to update the links that were given by MayMusic, https://howto.caspio.com/deployment/seo-deployment-directions/ http://static.caspio.com/pdfs/Caspio-SEO-Deployment-Best-Practices.pdf https://howto.caspio.com/deployment/seo-deployment-directions/seo-sitemap-generator/ Hope this helps

You do not need to create a link for each detail. You can separate your report and details page on two pages as described here http://howto.caspio.com/parameters/in-action/results-and-details-in-two-separate-web-pages.html As it describes you add an HTML block to your result page and pass a unique field value through the link to the details. Let me know if that is clear to you :wink: :wink: :wink: