vidierre

I am afraid that here we shift in conceptual point of views.
The detail you added shows a table (the one I used and that is a tipical concept of DBMS) that should bheave like a sheet (that is a tipical concepts of spreadsheets software).
While spreadsheets easly refer data as-you-see and have primitives able to handle the previous-next concept or allow you to refer data by-cell, DBMS can have a likewise functionality implementing indexes. So the prev-next record is non necessary the prev-next record you see displayed but the one according the index you are referring to....
Speaking SQL, we can obtain the result you want in this way:
 
SELECT  index_key,         order_nu,         order_date, order_item, dollar_value, sum ( dollar_value ) over ( order by index_key rows unbounded preceding ) sum_of_dollar_value FROM Test_ordes; but I am afraid this is not possible to use in CASPIO.
The workaround you could use is to add one more colum to the table that make the index of your table

The change the task in this way:

When you run it you get:

ATTENTION: I think this is not a solution. It can be used as workaround if you consider:
Values are not updated in realtime when you add a record, but only after the task runs We do not know what is the limits or constrains that could be arise when the table has a lot of records (perfomance? timeout?) The first to SET statements is the only way I found to initialize the variable. If you omit the first one the variable is instantiated as an integer and all decimals are lost The For statement cannot be done on the table, but on the SELECT result because you need to consider the ORDER BY The WHERE specification is needed otherwise you get the result of the last sum on all records..... I say again low-code bring an encapsulation of the below infrastructure and some capabilities you have using it directly are hided.

Mark this as solved if you like.
 

@CoopperBackpack I tested it and I want thank you again it is an easy and elegan solution. More than what I used to use!

@CoopperBackpack is very interesting the iFrame into the footer. I have to test it. Thank you.

Hello @vidierre, let me share the steps to create a report as in the screenshot I shared above.
1) These are examples of the Tables:

 
2) The first step is to create a Report based on the 'Documents' table.
It should filter the records by the received parameter (person ID).
So, the Report should use the 'Filter data based on your pre-defined criteria' option on the Search page and receive the parameter.

 
In this example, the 'value required' is selected so this report is not displaying the records until the button on the report above is clicked. 
3) The second step is to create a Report based on the 'Persons' table. 
Add an HTML block to add the button. 
The code example:  <button class="documents" onclick="refreshIframe('Assigned_to=[@field:ID]')" type="button">Documents</button>


  In the Header, the button can be styled. Disable the HTML editor before pasting the code. 
For example:
  <style> .documents { font: bold 16px Arial; background-color: #86c4d2; color: #333333; padding: 15px; border: none; } </style>
 
In the Footer, disable the HTML editor and add the iFrame deploy code of the Documents Report.
Customize the iFrame, add its width, remove the border, and add the ID that will be used in the JavaScript code to reference this iFrame.

An example:
<iframe name="Documents Search and Report" title="Documents Search and Report" src="https://accountID.caspio.com/dp/AppKey" width="100%" frameborder="0" id="iFrameDocuments">Sorry, but your browser does not support frames.</iframe> In the example, this part is added manually to the deploy code: width="100%"  frameborder="0" id="iFrameDocuments"

Add the JS code below, and use the Documents DataPage URL in the code:
<script> document.addEventListener('DataPageReady', refreshIframe); function refreshIframe(params){ const iFrame = 'https://accountID.caspio.com/dp/AppKey?'+ params; // replace https://accountID.caspio.com/dp/AppKey with the Documents DataPage deploy URL document.getElementById('iFrameDocuments').src = iFrame; } </script>


P.S. to adjust the iFrame height this article can be helpful https://howto.caspio.com/tech-tips-and-articles/embedding-datapages-with-automatic-height-adjustment/

Hello everyone!
Just wanted to share a workaround if you wanted to show a blank value on your Dropdown's display value. 
You may use &nbsp on the Display field and it will show up as blank on your DataPage! 

@vidierre That was on the free plan. This use case is very simplistic and does not require any custom variables

@andRe it is a concrete example, very good! I will start to play with it....

I've noticed that the topic of using AI in Caspio apps hasn't gotten much attention on this forum. This is surprising, given the big advancements in AI over the past few years. 
Recently we discussed this topic during the Partners’ Office Hours with @nikcaspio and decided to test the water sharing some ideas on the forum and hearing what the community thinks. I'd gladly give more examples in future posts if you find these thoughts helpful. The topic is almost endless, with so many ways to use AI. 
But let's start simple:

WHAT IS DOCUMENT UNDERSTANDING
We all know how Caspio can help create outgoing PDF documents based on database records. But what about incoming documents like invoices, receipts, delivery notes, or bills of lading? These often come as files or even paper documents. Here, we have to do the opposite of document creation. We need to take the document and turn it into data. Usually, someone has to enter this data manually into Caspio, right?
Well, that was the old way  Things have changed.
To show what's possible now, I made a short video using our Caspio app, where I upload a JPEG file with an invoice, and the system extracts the necessary data from it and put it into Caspio:
https://www.loom.com/share/d21eb53d9b5a4540a6282a56d8d47f0e
Is it magic? No, it's simply artificial intelligence. And a pretty inexpensive one. I paid only a few cents to our "digital elves" for assisting me in recognising these documents in the video. The cost of human labor would most likely be much higher.
TECH STACK
Of course, you first need a Caspio plan with REST API and Zapier Integration. The Professional plan offers it, but you may buy it as an add-on to smaller plans.
In this example, we used Google Document AI's Invoice Processor to do the work. It has some limits but is a good place to start and see quick results. And you can overcome these limits with more advanced methods. However, for this demo, it's more than enough.
At first, I considered using Zapier for this demo because it was easy. But I know many people here find Zapier too expensive. So, we chose a more budget-friendly option. We used the newly introduced Caspio Webhooks and Make (Integromat) as middleware. This is just an example; you can use any technology you like.

MAIN COMPONENTS:
To make Document Understanding work, you need three main parts:
OCR (Optical Character Recognition): This tech changes a binary file (like a photo taken with your phone) into text. Text Extractor: This tool looks for specific info (like invoice numbers or dates) and gives it back in an organized way. Middleware: This is the connector between Caspio and AI services. It takes a file from Caspio, sends it for processing, returns the data, and puts it into a Caspio table.
GOOGLE DOCUMENT AI
Google Document AI can do tasks #1 and #2.
You can learn more about this service here  and here's how to set it up.
Of course, Google Document AI is not the only option, but it's an excellent place to start. Once you know its limits, you'll know how to work around them. There are many ways to do this. For example, in most of our projects, we use Google Document AI mainly for OCR. Then, we process the text with a tool from OpenAI, the creators of ChatGPT. But that's more advanced, and I don't want to make things too complicated in this article.

MIDDLEWARE
Google Document AI comes with a robust and well-explained API. But it needs incoming calls to have a particular format and gives back answers in its own format.
If you're already using Zapier with Caspio, you might find it the easiest option, and you probably know how to use it. If so, skip to the Make Scenario section below for extra ideas. But if you're not using Zapier, stay with me. This post will focus on Caspio Webhooks as a more budget-friendly choice.
Webhooks are good, but they have a limit: they're set in a standard way and don't allow much change. So, you'll need another tool to take the webhook call from Caspio and change it into a format that Document AI understands.
There are many choices for this, and it's a big topic that could have its own set of detailed articles. For this demo, we'll use Make (Integromat). We find it even better than Zapier, but cheaper. They also have an excellent free plan that might be enough for smaller projects. Finally, Caspio has built a connector for Make, making it easy to set up, even if you're not an API expert.

CASPIO CONFIGURATION
To keep it easy, we turned on the FileStor option. This lets us get the file from Caspio using a direct link. I know this isn't the most secure way to handle files, but that's not the main point of this article, so I hope you understand.
We also made a table and a few data pages in our demo app. These let you create a record and upload a file. Next, we set up a Caspio webhook that starts when a new record is added (Insert event). This webhook sends the call to Make's incoming webhook. Finally, we made a Web service profile so Make can talk to Caspio using REST API and return the results to the table.
All these steps are well-covered in the Caspio manual, and there are also good video tutorials. So, I won't go into the details here. (If you need those, just ask in the comments, and I can post the links.)

SCENARIO IN MAKE

The process has just six steps in order:
An incoming webhook that waits for calls from Caspio. A 10-second pause to give Caspio time to send the file to FileStor. A simple GET request to get the file from FileStor. Some minor changes to the data to get it ready for Document AI (details below). A call to Document AI to send the prepared data. A standard Caspio connector that puts the data back into the Caspio table. If you're new to Make or APIs and find all of this confusing, let me know if you'd like more details. I intended this post to be an outline rather than a step-by-step guide. However, I can explain things further if needed. It's not rocket science.
But If you're familiar with Make or at least REST API, most of these steps should be pretty straightforward. The only part that might need some extra attention is step #4, where we set up headers and converted the binary file to base64. Here's how we set it up:

If you've done everything right, you should be good to go!
Not too hard, right?

MORE ON THE TOPICS
In this post, we've just touched the tip of the iceberg on Document Understanding. It's a big topic that could fill many more articles and take hundreds of hours to make a really deep dive. This is especially true for understanding complex documents like contracts or unstructured text documents. And the real magic happens when you move past ready-made solutions and train AI models with your own data. Learning all this can be a fun adventure, and the possibilities are endless.
However, even with simple setups, AI can be a game-changer for many businesses. It can save time on dull tasks and let your team focus on more important work instead of just copying data from paper to an app.
But AI isn't just about Document Understanding; it has lots more to offer. Here are a few more examples:
Semantic Search: This isn't just looking up keywords; it understands the meaning of your question, irrespective of the words you choose. This can be useful for Knowledge Management and Customer Support apps.
Voice-to-Text: Turn your video calls into text, summarize what was said, and add it to your Caspio app as follow-up notes. This is good for CRM, Project Management, Knowledge Management, and Recruitment apps.
Classification of Incoming Requests: Automatically sort new requests and applications based on its content. This can help with CRM, Customer Support, and Recruiting apps.
And there's much more.

FINAL REMARKS
I must say, this article ended up being longer than I first thought it would be. So, big thanks to you for sticking with me till the end! 
If anything was unclear or you ran into issues, please feel free to leave a comment or write me a DM if you prefer. I'll do my best to help you out.
Now, I'd really like to hear your thoughts. Did I explain it well, or did it just confuse you? Does it make sense for you?  Are you interested in this topic at all? Do you want to see more articles like this one? If so, what would you like me to focus on? More technical details? Real-world examples? A different writing style?
I welcome your feedback and constructive criticism. 

Thank you!

Just wanted to share this usecase to allow user to input characters with diacritical marks in them(è, î, ñ, â).
This small modification will help users input characters with diacritical marks using a dropdown list and a button. For this scenario, I will use a submission form but this can be modified and can be used to Single Record Update and Details Page.
First, you would need 2 new fields for each text field or text area you wish to implement.
- 1 virtual field set to a dropdown list that will contain the characters.
- 1 HTML block that will serve as a button and container of the javascript.
Here is the script that needs to be inside the HTML BLOCK:
<style> #letter{     background: #3B6AD2;     border: none;     color: #FFFFFF;     display: inline-block;     font-family: Arial, Helvetica, sans-serif;     font-size: 12px;     font-weight: 400;     text-align: center;     text-transform: uppercase; } </style> <button type="button" id="letter">pick</button> <script> document.getElementById('letter').addEventListener('click',AddLetter); function AddLetter(){ document.getElementById("<textfield_ID>").value = document.getElementById("textfield_ID").value + document.getElementById("<virtualfield_ID>").value; } </script> Sample Output:

Hi @vidierre,

You may use Parameter to add the custom CSS Style then call them on the calculated field and use the calculated field to call the style:

For reference:
 
 

Thank you for pointing that one out.

Will there be only 4 records in table A? one for each of these? JAN-MAR, JAN-JUN, JAN-SEP, JAN-DEC

If there will be more of them, we will still need to aggregate those values first, but we can just save them on a table variable instead of an actual table.


How about this?


 

Hi, @vidierre. Since you don't have any condition on your "FOR EACH" block, it will continuously loop depending on the number of records. Currently, you have 133 records, in every loop it will affect 133 records and it will also loop 133 times affecting 17k plus records. Based on your current task setup, it is best to remove the "for each" block.
 
-Potato

@Flowers4Algernon thank you. 
At a first look I was going to answer that this is only an avaliability monitor, but when I read that it was a pingdom service I went deeper.
On the initial panel if you click on your server some performace data can be retrived. As example for my server I can read this data for April:

and an avarage response time of 681ms. Remember that I am on a Grow plan, it means shared resources. Everyone should remember the difference in terms of performance.
Of course anyone who has worked on the performance of real data centers knows that these value are not the real user-perceived respinse time. But are a very solid base to make considerations.
This strengthens my thinking about that CASPIO is an excellent choice for app adressing mid-size environments. Or at least the non-Company plans are excellent for an audience up to 50-100 users. I believe that the rule that "for greater users base you need more dedicated resources" is true for all platform.
One can argue that Caspio Corporate plans are expensive or that the price progression has an excessive surge between the two worlds. But nothing about the offer completeness.

@biota, it is hard to answer whitout knowing the data nature. Each table has the same headings? What kind of data there is into the intersections? What represent the subheadings?
Matrix or tables aren't a simple positioning of data on the floor. Let me make an example: suppose this is your matrix:

fig.1
Your column labels are a sort of types of vegan meals and row labels are the source where they are produced. The yellow fields represent how much each is consumed.
It means tah we have three coherent sets of data and they could be repressented in tabular format in this way:
    Here you have the columns and rows labels spread as data into table's column. 
fig.2
But considering the unique nature of row labels (in this example are continents), we could represent them also in tabular format this way
   Where a name that characterizes row labels (continents) make one column. 
fig.3
And what about the nature of yellow fields? because they couldn't be a single data but the results of some calculations. Just to give a taste of what I mean, it could be obtained from this table:
   Where the SUM of differents amount became the yellow fields.
fig.4
As you can see the same matrix could be the results of differents tables representing data.
My thought is that you could fit, for each table in your word, in the fig.4 case. So you need to use a table to representing your surveys with a uniquie ID for each survey and data survey-relatde (when it was done, who did it, etc.). Than a separate table for each of your matrix/tables insinde the surveys with an extra column related to the survey_id.
 

Even if Caspio provide some logging feature sometimes I nedd to have a more granular app log/trace. I want to share what I use in my apps.
I built a table to mantain all log records with this design:

Where.
ID - is a unique identifier tnt_id - I use it to handle multi-tenant app (it could be omitted) Timestamp - self explanatory Appl - Is the name of the app that logged an entry Source - Is the kind of element that logged the entry (i.e. Trigger/Task ...) Event - Is the eventname I logged (Update, Record scan, Insert, ecc.) Description - contains valuable data related to the event. It may be some field values, authentication fields or a concatenation of more field. Then I put some pattern like this into keypoints of my triggers or tasks filling Description with useful data.

If you consider this useful click the like at bottom-right.
If you know a better idea, please let me know.

Hello @EmmePGN
You can implement such a logic using both Rules and JS.
Use Rule to make appropriate fields as required.

Here is the example of the code you may add to the footer of the DataPage:
<script> function hideFieldsHandler(event) { let targetInput = document.getElementById("EditRecordDropdown_Name"); let firstField = document.getElementById("EditRecordField_Name").parentNode.parentNode; let secondField = document.getElementById("EditRecordField_Name").parentNode.parentNode; if (event.target.value == '1') { firstField.style.display = "none"; secondField.style.display = ""; } else if (event.target.value == '2') { secondField.style.display = "none"; firstField.style.display = ""; } } function changeHandler() { document.getElementById('EditRecordDropdown_Name').addEventListener('change', hideFieldsHandler); let firstField = document.getElementById("EditRecordField_Name").parentNode.parentNode; let secondField = document.getElementById("EditRecordField_Name").parentNode.parentNode; firstField.style.display = "none"; secondField.style.display = "none"; document.removeEventListener('DataPageReady', changeHandler); } document.addEventListener('DataPageReady', changeHandler); </script> Parts needs to be changed:
1. Dropdown_Name and Field_Name parts according to the names of the fields you use in your DataPage.
2. "1" and "2" values in the IF statement according to values of your Dropdown field.
Also, you need to disable the HTML editor of the footer before you insert the code.
You can find the Example here.

Hello again,

I just want to share this workflow. 

In my tabular report, I want my records to be grouped by week but when I select the week rollup, it displays the weeknumber, (ex. week 1, week 2) instead of an actual date. Since I wanted to make it display the date of the first date of that week (ex, date of every sunday), I have made this workflow:

First, I created an extra date field, then, I created a triggered action/task to process the data. This is the pattern I used:





Then I used the extra field weekdate to group my records instead of using the "week rollup" function.

Hello @vidierre,
I suggest to instead of clicking the delete button on your DataPage, you can just have a checkbox(Yes/No) field that the user will check if they want to delete the record. So the modified records will still saved and send to the backup table before the record be deleted. 

I hope this helps  

Thanks.  I am a programmer with alot of desktop experience but transitioned into project management about the time the browser programming became so prevalent that desktop programs became few and far between.  I have just now come to a time when I want to jump back into more coding so am learning new languages and methods.  I certainly can inject console.log and alert() and other commands into the code to get what I want it is just alot more tedious than being able to run the code line by line.  I think maybe I'll build a quick form outside of caspio which mimics the controls that I think caspio is using and then run my code in there where I know I can use the terrific  debugging tools like breakpoints.  Then I should be able to copy the debugged code back into the caspio form and have it work.  I do have a ticket open with caspio to see if they can figure out why I can't see my code.   Here is  a link to setting breakpoints which, if I can get my code to show up in sources, will allow me to do what I do with other javascript.  I promise to come back here and let you know if I get a solution to the code invisible problem.
Pause your code with breakpoints - Chrome Developers
 

Because I never would have thought that, I was looping through javascript thoughts. Instead this is the simpliest way!
Thank you!

Thought this might help anyone struggling as I did to create a View of only IDs (1) not duplicated between two tables or (2) duplicated between two tables but a second field is a certain value.
I have two tables:
1. Table A - has a list of every available ID
2. Table B - contains IDs of inserted records (can have same ID on multiple records)
I did a full outer join on the two tables in a View based on the ID

First criteria only show IDs that are in Table A but not in Table B

 
I then only want to include IDs if the record is in Table A and Table B, but a second field has a value of "Yes". However, Table B can have multiple records with the same ID with the second field with values of "Yes" or "No". The Not Equal filter wouldn't work because you can't NOT at the AND level.
To solve this I added a sub-AND filter with two lines stipulating what I wanted the field to be and what I wanted it not to be.

@KG360 I think your issue is similar to the one I faced building a demo environment where all data are cleaned and reloaded every 24h. 
First of all I believe that there isn't one solution for all scenarious. This because CASPIO cascading trigger limit. Your scenario may be influenced by table number and thei triggers.
What I did was:
1) create Task table - I have these fields because this ssolution can be used by more app in more tenants, but 
ID, Tenant, APP, Task, Status, Param_1 , Param_2, Param_3, Param_4, Param_5
But you cal limit to status and parameters fields
2) In your case you must put something, into some triggers of tables involved into signup process, to add a record into the task table with:
Status: Pending Param_1: userID
i.e. you could add this into an insert trigger of your user table.
3) built a scheduled Task that scan the task table for records with status Pending, read parameters, perform the initiaization operations you needand set Task status to OK.
I cloned this task in order to have one running every 15 min.
In your case I suggest to add a ready field into the user table with default NO. It should be set to YES by the task and could be checked to avoid logon until initialization rocess is completed. Let me know.

hi @KG360, sorry I taken for granted the Task Table. Here are its fields meaning and how I use them:
ID - is the usual unique id field  Tenant - it is a tenant identification. I use it because some apps are multitenant. You can omit this. Task - Is the task to be performed. I have different tasks that can be performed. As example one task has to scan all records in a table and sen an email if a record mach some conditions. I use the task field intoo the scheduled task logic to perform differents scan on differents tables. Status - It shows if that record (into my task table) has been perfomred or is yet to run. I set it to NO when I add a record into my task table, then into the scheduled task I scan all records with status=NO, execute the needed action and set status=OK to avoid double processing. If you want you can setup a second scheduled task that delete all records with status=OK Param_1 .... Param_n - are parameters used by the scheduled task to perform different activities. I hope it helps you. I understand that it is not the best clean solution, but I found nothing better for me,
If you like it please press the like button. If you have a best solution, please share it.
I wrote something similar to make an app log. you vìcan see it here
 
 

Even if Caspio provide some logging feature sometimes I nedd to have a more granular app log/trace. I want to share what I use in my apps.
I built a table to mantain all log records with this design:

Where.
ID - is a unique identifier tnt_id - I use it to handle multi-tenant app (it could be omitted) Timestamp - self explanatory Appl - Is the name of the app that logged an entry Source - Is the kind of element that logged the entry (i.e. Trigger/Task ...) Event - Is the eventname I logged (Update, Record scan, Insert, ecc.) Description - contains valuable data related to the event. It may be some field values, authentication fields or a concatenation of more field. Then I put some pattern like this into keypoints of my triggers or tasks filling Description with useful data.

If you consider this useful click the like at bottom-right.
If you know a better idea, please let me know.

If I remember correctly, the way you tried previously is for datapages that do not have 'Enable Responsive' checked. We use to not have that feature, so I guess this tutorial was created before that change. I don't think the solution I shared works for pages with Responsive turned off. So, it is handy to know both of them.