Jump to content

Roosta

Members
  • Content Count

    14
  • Joined

  • Last visited

  • Days Won

    1

Roosta last won the day on December 20 2019

Roosta had the most liked content!

About Roosta

  • Rank
    Member

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. Hi @NiceDuck Where did you get those figures from as it doesn't state this in the pricing schedule for basic plans https://www.caspio.com/pricing/basic-plans/ in fact Triggered actions doesn't state a monthly or daily limit at all only Notification Emails, SMS, Import Update, Scheduled Tasks and REST API have a stated monthly limit?
  2. Hi @hercules97 To be honest a lot of it is going to depend on your method of deployment, what your trying to accomplish, the end users skill levels (are they familiar with using databases or is this a general public facing application?) and your expectations given that Caspio is ultimately an online relational database for collecting, processing and displaying data/information. We are deploying our system via a self-built website which gives a lot more flexibility with layout and additional code JS/HTML/CSS than say using a website builder such as Weebly or Wordpress. So some questions; What are you trying to build How is it going to be deployed How familiar are you with relational databases and structure Do you have any coding skills such as JS/HTML/CSS Is the system you are building be integrated with other systems such as mobile applications using REST There are many more questions I could ask but these seem the most relevant to begin with, I am by no means a professional coder/programmer but have had extensive experience with MS Access and Visual Basic in the past and these skills helped. You need to set objectives e.g. what do I want this datapage to do and how am I going to deploy it, what user interactions will be required etc, then if you need help trawl the forums and see if anyone else has had similar problems and there are plenty of people here who are willing to help (I have only been using the forum a short time but found it invaluable as much for seeing what can't be done and looking at solving it in a different way!) You could always use Caspios "Expert Sessions" add-on; However its not cheap and I would advise if you go down that route you have a good idea of what it is your trying to accomplish before booking a session/s Failing that you can try Caspios "Professional Services" we have used them for a project that I didn't have time to learn how to implement myself, again not a cheap option but they are a good team of people and will get you where you need to be if its a business critical function that you need to implement reasonably quickly. Sorry for not having a definitive answer but you asked a pretty broad question with little to no information of what your trying to achieve, how you have tried to implement it and how far you need to go. Kind regards, Roosta.
  3. Hi @roattw If this is a submission form I assume you mean something like this; Where you can have headings over each section? If so you can split the form up into sections with columns to suit and have HTML block in-between their own sections set at 1 column to span the page like this; This is one column proceeded by the HTML block with the next section containing the submission form fields; You will have to experiment (a lot!) to get decent results and spacing for your whole form as if you start mixing section columns other then 1 and X (in my case X = 5) it will space them to overcome this I either used columns of 1 or 5 and if required less then 5 used 1 and had the elements continue on the same line. Your other option would be to build a full HTML table within the HTML block something similar to this; <style> .wrapper { background-color: lightgray; padding: 5px; } td { padding: 10px; } </style> <table border="0" cellpadding="1" cellspacing="1" style="width:100%;"> <tbody> <tr> <td><strong>Some Data 1</strong></td> <td><strong>Some Data 2</strong></td> <td><strong>Some Data 3</strong></td> </tr> <tr> <td class="wrapper">[@field:Some_Data_1]</td> <td class="wrapper">[@field:Some_Data_2]</td> <td class="wrapper">[@field:Some_Data_3]</td> </tr> <tr> <td><strong>Some Data 4</strong></td> <td><strong>Some Data 5</strong></td> <td><strong>Some Data 6</strong></td> </tr> <tr> <td class="wrapper">[@field:Some_Data_4]</td> <td class="wrapper">[@field:Some_Data_5]</td> <td class="wrapper">[@field:Some_Data_6]</td> </tr> </tbody> </table> Which would result in something like; Again you would have to experiment with layout to match the rest of your form/fields but may be a possible solution but without seeing exactly what you want as an end result its a bit hard to offer any further advise. Good luck, Roosta
  4. Hi @Vitalikssssss We have come up with a partial solution which I have posted here if you want to have a look it might be along the lines of what you were thinking already or maybe you could improve on it which would be appreciated Kind regards, Roosta
  5. Have a solution sort of... This works for the way I have the datapage deployed but probably won't work with direct URL's or deployment via Weebly or similar as it utilises the CSS code of the website page its deployed on and therefore also doesn't work if you try to download the data as a PDF but works sufficiently for my current needs; <script> $("table.cbResultSetTable tbody tr.cbResultSetOddRow, table.cbResultSetTable tbody tr.cbResultSetEvenRow").each( function (x) { $(this).children().each( function(){ if ($(this).text().indexOf("%") != -1){ if ($(this).text() == "100 %"){ $(this).css("color", "white").css("background","green") } else { $(this).css("color", "white").css("background","red") } } } ) } ) </script> Probably not the most elegant solution in the world but gives the following results in the frontend webpage when deployed in the footer of the pivot report; It would obviously be far easier if Caspio had a conditional formatting function in the "formatting" section for Values but in the absence of that this may be useful to others if all you need is to format the display of certain columns. Thanks for looking, Roosta
  6. Hello all, I have been putting together a simple pivot table view to display outcomes and percentages of an audit process that we perform which looks something like below; I would like to conditionally format the "% Correct" columns so that they are Green if = 100% or Red if < 100% I have found this a bit difficult as there do not appear to be any Element ID's associated with the cells, I got the result below by using Class Name and column No; Using this code deployed in the footer; <script> function f_color(){ if('[@calcfield:11]' == '100 %') { document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.backgroundColor="green"; document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.color="white"; }else{document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.backgroundColor="red"; document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.color="white"; } } f_color(); </script> However it doesn't switch to green when the field is = 100% (see below I assume this is because the calculated field I am referencing is either the incorrect syntax or can't be used in this way ) and as you can see doesn't apply to all cells in the respective column; Any suggestions or guidance would be gratefully received as I am probably going in completely the wrong direction and I have searched the forums and can find a number of suggestions/solutions for Tabular reports but nothing for Pivot Table reports. Kind regards, Roosta
  7. Solved it! (well sort of...) To get round the issues I created a number of formula fields in my results table similar to this creating columns of 0's and 1's depending on the results which can then be summed; CASE WHEN [@field:AS_Audit_Sat] = 'Satisfactory' THEN 1 ELSE 0 END Then in the pivot tables calculated field that I need to get a percentage I used; ISNULL(SUM([@field:DEA_Main_Body_AudOut_Sat]),1)/(SUM([@field:DEA_Main_Body_AudLive_Val])) Setting the format to percentage with the result being this; This has also saved me from having to use Select Counts in the other calculated fields as all I need is something like (yes I know I could have set the calculated field to summarise by "Sum" but it puts "Sum of" in front of the column label text which I didn't want); SUM([@field:DEA_Main_Body_AudOut_Sat]) All I need to do is get the conditional formatting of the % columns which is a bit difficult as there do not appear to be any Element ID's associated with the cells, I got the above by using Class Name and column No. using the below; <script> function f_color(){ if('[@calcfield:11]' == '100 %') { document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.backgroundColor="green"; document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.color="white"; }else{document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.backgroundColor="red"; document.getElementsByClassName('cbResultSetData cbResultSetTableCellNumberDate cbResultSetCalculatedField') [3].style.color="white"; } } f_color(); </script> However it isn't working for the whole column or changing color based on the value as can be seen below; So as always any suggestions would be gratefully received! Many thanks, Roosta
  8. Hello all, Well I am getting nowhere with this, I just don't get the syntax used in Pivot Table Formula FIelds I can do this (Count + Count); IsNull((SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot_AudSatPer Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]),1) +(SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]) Which avoids a Zero in the first Count (The second count will always have a result of >0) But I can't do this (Count / Count): IsNull((SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot_AudSatPer Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]),1) /(SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]) As it returns an "Invalid Formula" when verifying. I can't get the [@calcfield:X] use to perform in any rational manner either for example SUM([@calcfield:9]+3) seems to result in the Calc Field value added together 3 times so if its value = 1 then its 1+1+1 =3 not 1+3=4 or if you try to use [@calcfield:X]+[@calcfield:X] within a calculated field such as below; It verifies as a correct formula but returns "Error in formula" when trying to preview. There is very little information in Caspios help files for Pivot Table Calculated Field Formulas and they don't seem to behave the same way as tabular reports and the syntax definitely isn't the same as standard SQL. Any help or guidance towards some documentation for correct syntax would be gratefully received, failing that I think my only option may be to create an HTML Table in a form datapage using HTML blocks and figuring out how to display the required data. Thanks all, Roosta
  9. Hi all, After a little experimentation I am pretty sure its not recognising the Calculated Fields within the formula as an experiment I tried [@calcfield:9]+10 which just gave me a column all with the value "10" but should have been various values such as 12, 11, 13, etc when I tried ([@calcfield:9])+10 it verified as a correct formula but then displayed "Error in formula" when previewing. Maybe the Pivot Table Wizard doesn't like referencing Calculated Fields within another Calculated Fields formula? Kind regards, Roosta
  10. Hi @Vitalikssssss Thanks for the suggestion, I have tried as you suggested and when verifying the formula I get the following error; I have tried using a suggested solution from the Caspio help; Case When [@calcfield:9]= '0' Then null Else ([@calcfield:1]/[@calcfield:9])*100 End This verifies as a correct formula but when previewing shows; I will keep trying some variations on the formula to see if I can find the problem in the syntax, any suggestions more than welcome and thanks for your help so far much appreciated. Kind regards, Roosta
  11. Hello Vitalikssssss, I have only just swung back round to developing this datapage and greatly I appreciate the solution you offered but its not what I am hoping to achieve. As you can see from my orginal screen shot of an exisiting pivot table I am using as a model I have several results/fields I need to total and provide percentages for but the Caspio standard features get very confusing for users to interpret/read (the layout isn't very good even with tweaking). I have created something near what is required using calculated fields only (no columns selected and just the engineer as a row) as below; The problems that remain are that I can't figure out how to display/calculate percentages based on 2 calculated fields say Audit Total and Satisfactory (the first 2 columns) for example; I would like to carry out a calculation using the results from 2 of the calculated fields e.g. Calc Field 1 (third column) / Calc Field 9 (second column) * 100 so for example Steve Rogers would be (3/5)*100 = 60% is this possible? I have tried using ([@calcfield:1]/[@calcfield:9])*100 which verifies as a correct formula but I get an error message "Error in formula." when trying to preview it. Or I need a formula that will allow me to divide the result of this formula; IsNull((SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot Where DEA_Main_Body_AS_Audit_Sat = "Satisfactory" AND CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]),0) With the result of this formula; IsNull((SELECT COUNT([@field:DEA_Main_Body_RecordID]) from _v_DEA_Eng_Pivot Where CUS_Contrator_Engs_Full_Name = target.[@field:CUS_Contrator_Engs_Full_Name]),0) Then multiply by 100 to get the percentage? And as I am using calculated fields I would also need to be able to filter/search by date (have the date search fields in place) but currently they don't work as all values shown are drawn directly from the View. I also need to include conditional formating as in the orginal screenshot for the percentage fields e.g. 100% = Green <100% = Red. Hopefully I am explaining this correctly but I am coming to the conclusion that this may not be possible using the standard Pivot Table function within Caspio and may need to look at doing this in a different way if anyone has any suggestions? Kind regards, Roosta
  12. Hello Vitalikssssss, Thank you for the example it is very much appreciated I will have a look through later and let you know how I go Kind regards, Roosta
  13. Hello Vitalikssssss, I have attached a couple of truncated tables with the relevant data included, the CUS_Contractor_Engs has the engineers names and ID Field which is used in a one to many relationship with the DEA_Main_Body table. There is one column with Yes/No values and one with Satisfactory/Unsatisfactory as samples as mentioned in my original post, thanks for looking and I would be very grateful for any suggestions or guidance. Kind regards, Roosta CUS_Contrator_Engs.xlsx DEA_Main_Body.xlsx
  14. Hello all, Thanks for taking the time to have a look at my first post! I have been using Caspio for a few months now and generally it seems very versatile but I have come up against something that I can't seem to resolve. I would like to achieve something similar to below; This is version of a pivot table which counts instances of Yes/No or Satisfactory/Unsatisfactory in a single column and sums the totals in the appropriate fields then calculates the percentage based on the total number of audits undetaken for an individual (this is test not live data hence the lack of overall data and names) I have tried various ways to do this in the standard pivot table available in Caspio but don't seem to be getting anywhere and think I may need to create an HTML table of my own with calculated fields but thought I would post here to see if anyone has any ideas or has done something similar in the past? Cheers all, John
×
×
  • Create New...