Farnsbarnes

HI I have a large table which when displayed in a submission/report datapage takes a few seconds to process when a column is clicked to sort. I'd like to display a 'spinner' so the User knows something is going on while waiting. I can activate the spinner by placing some jQuery in the Header of the 'Configure Table Fields' page of the submission/report wizard. Problem is that when a column is clicked to sort, the spinner is not called immediately and only starts around half way through the processing time. What I don't know is where to place my spinner jQuery code so it switches on as soon as the column is clicked to sort. I tried the Header in the search page, but it doesn't get picked up from there. Is there an event listener that could be used somewhere? Many thanks

Many thanks Vitalikssssss. Great idea.

Thanks for the reply Nuke354. Screenshot attached. I'm looking for the table to be ordered in terms of values in the 2019 column, in this case with Germany at the top. Any code that does this can't reference the name "2019" specifically, as the range of years can change. I therefore want to reference the 'right-most column', whatever year that happens to be. Calculated fields are very limited in pivots, hence my problems with this and request for help. Tabular reports aren't a solution as they won't allow me to use years across the top. Thanks

Hi I've got a pivot table with countries down the left and years across the top. The values are tonnes. I'd like the table to appear with the values sorted, so that largest values start from the top. The sorting always needs to apply to whatever year is in the right-most column. So if for example years happened to go from 2000 - 2019 (left to right), I'd like values to be sorted in the 2019 column. I'm suspecting the solution is in a 'Calc Field1' but I don't know the sql to sort the table Many thanks

Many thanks guys for your feedback, sympathy and suggestions! I've indeed submitted my votes in the ideabox. Changing the zoom level occasionally helps, but often hinders with other things happening on my screen. Really don't understand why this icon feature wasn't thought through a little more.

HI everyone Perhaps it's just me but when looking at Tables I find the chat link icon on the lower RH side of the screen incredibly annoying. It covers the scrollbar and, as luck would have it, always seems to block me from the specific position I need. Clicking on the link then minimising it makes no difference. Is there a way to get rid of this thing? Thanks

Thanks for the responses. Unfortunately I don't think I have any alternative to allocating the regions at runtime. This is because the User has up to 50 different regions which can be selected and countries can overlap. For example, a User could select 'EU27' or 'Western Europe', or 'Europe', etc. France, for example, falls into all those regions. But if the User selects 'Europe', then that's the region I want to assign next to the record with France in the calculated field. If he selects 'EU27', then it's that region that I want to assign next to the record with France in the calculated field, and so on. Any other suggestions most welcome!

I think the above code might be able to help me. In my report I have fields for 'year', 'region', 'country', 'product' and 'tonnage'. The 'region' field is a calculated field. I wish to show aggregates of my data by region, but also for each year. Caspio prevents me grouping data by 'region' because 'region' is a calculated field. And I can't use 'Totals & Aggregation' either because Caspio only allows me to use 5 of those. My mix of 'region' and 'year' could end up being many aggregate totals. I could be wrong but I think the 5 year-old code from Xiang (above) allows me to add aggregate rows at the bottom of the table... or am I being too optimistic?! Anyhow, I tried to get his code to work but I think it uses old Caspio commands and all I get is error "Cannot read property 'getElementsByTagName' of undefined". Is anyone able to make adjustments to this code to make it more generic? Alternatively does anyone have any other suggestions on what I can do? Many thanks

Can't seem to find any previous references to this. In my tabular report one of my fields is a calculated field that outputs text (don't ask... it had to be done this way)! Each record in this calculated field contains a region (ie Europe, N.America, etc) My other 'normal' fields cover a range of text and numbers What I want to do is display the whole table grouped in terms of the regions in the calculated field, rather like having a 'total and aggregation' for the calculated field. I've tried adding the calculated field in the 'total and aggregation' option, but just get an error when trying to display the table Any help very welcome. Thanks!

Many thanks for this kpcollier, really useful info.

Sure, I've voted for this already in the Ideabox... although I'd have thought this feature should be an obvious and natural part of the pivot table without relying on the Ideabox to prompt it. First I've heard that Caspio is changing its infrastructure - it scares me a bit due to backwards compatibility issues, but I guess that's their job as experts to get right. Do you have any idea when this new infrastructure might come out?

Does anyone have any insight as to when Caspio is going to finally add HTML blocks and calculated fields to pivot tables? It's a really significant missing feature and means my tabular tables offer features my pivot tables don't - which just makes for a poor, inconsistent GUI experience for my users. I'm guessing that doing the code is probably difficult, but I've been around long enough to know there's always a way! Come on Caspio!

Many thanks for your reply @Vitalikssssss What a huge shame this is, and a strange omission by Caspio given that values can be looked up in HTML blocks, JS code, etc. I appreciate your advice with the View and I'll follow it, but it's going to be detrimental on performance as my records run to the low millions. How I wish Caspio would expand its sql capability in calculated fields. Best Farnsbarnes

Does anyone know how to access the display value of a field when using a formula inside a calculated field with sql? The critical line for me is below. In my table the field 'Column_values' is referenced to another table, but displays text in place of the underlying reference data. So far I can only manage to access the references, not the text display values. WHEN CHARINDEX('[@myexample]','[@field:Column_values]') > 0 THEN ... Other than the above I've tried '[@lookupfield:Column_values]', '[@lookupvalue:Column_values]', and all kinds of other combinations, but I just can't get the right syntax. Any help much appreciated.

Hi SunakoChan, thanks for you response. Perhaps I wasn't clear enough. I know that I can freeze columns in the datapage wizard. But once set they are fixed. What I want to be able to do is alter the freeze column settings when viewing the report itself, in real time. I'm fine with doing the coding for a little drop down menu in jQuery or javascript to be placed next to the download button in the report. But in this dropdown I'd like to be able to turn the column-freeze on and off, or even better, to set to 1, 2,3 columns, etc, again, in real time. Does anyone know the essential coding to be able to do this?

Does anyone know if its possible to have a freeze columns dropdown in a report - tabular or pivot? Would be great if the User could choose whether to freeze 1, 2 or 3 columns, or turn freeze off completely. Alternatively, does anyone at least know whether it's possible to simply turn the column freeze on or off, using javascript or jQuery, when the report is displayed, via a dropdown I could add in there? I'm really looking for the code that controls the freeze function. What I've found so far is the following HTML: <th class="cbResultSetLabel cbResultSetHeaderCell cbFreezeColumnsDivider_65699f5f6d3e23 cbFreezeBoxSizing_65699f5f6d3e23" scope="col" style="display: none; width: auto; visibility: hidden;"> But I'm just not sure if I'm able to have any control over this. Any help much appreciated .

Thanks for the offer HeezyMeezy. Would love to take you up on this but unfortunately I'm weighed down with client confidentiality issues - I've got to first come up with a way of unpicking the code to just show the essentials. Will let you know if I get there!

Thanks for your reply. Sure, they were going to be my next port of call. I'll talk to support and keep you posted.

Has anyone had any issues with styles following the latest Caspio release 18? I use both jquery datatables as well as Caspio results tables. Everything is built inside the Caspio environment. I've customised everything with my particular Caspio styles, Everything displayed fine until the new release went through a few days ago. Now, the row styles on my jquery datatable just don't get called. For example, I used to have row color banding (ie odd rows displayed as a different shade to even rows). But now my style code has no effect at all! Even adding '!important' in the CSS doesn't work. Very strange as I've done nothing in past month that could have changed this Anyone any comments?

Thanks douvega. I suspected it might come down to using API calls. However, while I understand the Caspio codes and explanations as presented, I just can't get my head around actually putting the explanations together to make a basic API call. If only Caspio had a step-by-step example of the simple operations required to have a GET View Rows up and running (a video would be good). This is such a big and obvious gap in Caspio's explanations that I can't help suspect it's purposely done! Do you know of any such help anywhere? I appreciate the 3rd party recommendation. Perhaps for later, as I'm on a bit of a shoestring budget at the moment. Thanks

Well, Lets say I've got a table called 'Customer_services' with 3 fields; Name, Country, Product (all text fields). 3 records in the table: 1. Sue USA Rake 2. Eric Spain Fork 3. Sue Mexico Rake I'd like to create a variable containing all countries and products associated with 'Sue'. So what I want is: myvariable = 'USA, Rake, Mexico, Rake' Hope this is possible. Thanks MayMusic

Is it possible, using javascript / jQuery, to extract data from specific fields in a Caspio table, or its results datapage, and assign that data to an array? If so, can this code be made to work by placing it in an HTML block inside the relevant datapage? Please give me an example of the code that would achieve this. Thanks

Understood. Thanks.

I'm normalising a large Table which can extend to 3-4 million rows. One of the fields is 'Year' (eg '2017'). There are up to 80 different years in the database and obviously many are repeated many times, Is it worth indexing this field? I can make a table with a primary key and the related year, such as y1 = 2000, y2 = 2001, y3 = 2002 and so on, then relate the primary key to year field in the main Table. I'm just wondering whether all this fuss for so little is worthwhile. Is it going to make a performance improvement? Or should I be doing this a completely different way. Thanks for any advice

Panic over, I've found the answer myself. Use a target tag. Using '_top' breaks outside of all the frames it is nested in and opens the link in the top document in the browser window. <a target="_top" href=" --- Enter Caspio URL here --- " id="Logout_button">Log out</a> Hope this can help someone.