Connonymous

@CoopperBackpack Okay, thank you!

@FinTheHuman I am trying to avoid duplicate data in my tables as much as possible, so I am hoping I can just reference the data through the key rather than needing to input it into this table as well. Using a triggered action could be a backup plan if needed though.

@DrSimi Thank you for getting back to me, and sorry for the slow reply. I tested out your script and while Caspio confirms that it is valid SQL, it gives an "Error in Formula" message when I actually go to the application. This is the same thing that was happening when I was combining the SQL in the way I described in my initial question. Seems like it might be some sort of Caspio quirk since it does confirm that the syntax is valid.

I have a calculated field that is calculating the ID of the value I am trying to get. This calculation is working properly: SELECT Pay_Plan_ID FROM JFM_Grade_Order_List WHERE Org_ID = target.[@field:JFM_Job_Description_Org_ID] AND ( (target.[@field:JFM_Job_Description_Department] <> 'Various' AND Class_ID = target.[@field:JFM_Job_Description_Class_ID]) OR ((target.[@field:JFM_Job_Description_Department] = 'Various' OR target.[@field:JFM_Job_Description_Department] = 'Multiple' OR target.[@field:JFM_Job_Description_Department] = '') AND Recommended_Class = target.[@field:JFM_Job_Description_Class_Title]) ) When using a second calculated field to pull the corresponding Pay_Plan name listed below, it works as expected: SELECT Pay_Plan FROM JFM_Plan_Type WHERE Pay_Plan_ID = [@calcfield:1] However, in an effort to streamline things a bit, I want to combine these into one calculated field. When I do so, I am getting an error. Is there a restriction of some kind within Caspio that prevents this from working, or is there some basic SQL mistake I am making? The combined code is shown below, this is what is causing an error: SELECT Pay_Plan FROM JFM_Plan_Type WHERE Pay_Plan_ID = ( SELECT Pay_Plan_ID FROM JFM_Grade_Order_List WHERE Org_ID = target.[@field:JFM_Job_Description_Org_ID] AND ( (target.[@field:JFM_Job_Description_Department] <> 'Various' AND Class_ID = target.[@field:JFM_Job_Description_Class_ID]) OR ((target.[@field:JFM_Job_Description_Department] = 'Various' OR target.[@field:JFM_Job_Description_Department] = 'Multiple' OR target.[@field:JFM_Job_Description_Department] = '') AND Recommended_Class = target.[@field:JFM_Job_Description_Class_Title]) ))

@CoopperBackpack okay, thank you for letting me know!

I am hoping to calculate a percentile in a calculated field, but am not able to make that work. To illustrate, if I had the 5 data points below, the 60th percentile would be 2718: 1. 4364 2. 1198 3. 2937 4. 2572 5. 1682 When using percentile calculations that I see cited from other SQL platforms, I am seeing the following syntax, but this doesn't seem to work for me within Caspio. Any ideas? In the example below, "amount" would be my field name, and "sales" would be my table name. SELECT PERCENTILE_CONT(0.60) WITHIN GROUP (ORDER BY amount) FROM sales

@cheonsa Those sections don't seem to be working for me. I did figure out a temporary workaround using JavaScript, but it doesn't seem like the optimal way to handle cases. I added a field to my Authentication table that specifies the number of columns the table needs to have. Then I reference it in the footer with this code. This doesn't handle cases where I search and a smaller subset is returned, but it does at least handle cases where all columns are blank for that user all the time. Any additional ideas on how I might be able to better handle these cases? <script> document.addEventListener('DataPageReady', function (event) { let table = document.querySelector('table[data-cb-name="cbTable"]'); if (table) { while (table.querySelector('tr').children.length >= [@authfield:Hide_Ref]) { let label = table.querySelector('th:nth-of-type([@authfield:Hide_Ref])'); let values = table.querySelectorAll('td:nth-of-type([@authfield:Hide_Ref])'); if (label) label.parentElement.removeChild(label); values.forEach(el => { if (el) el.parentElement.removeChild(el); }); } } }); </script>

I have a tabular report that has 30 columns. Some users only use 10, 12, 15, etc. of the columns, whereas others use all 30. Has anyone come across a good way to dynamically hide the columns that have no data for that user? Or even better, for that particular search/filter? One additional caveat, I would like the DataPage to be downloadable, so if possible I would like to try to avoid something that uses hidden calculated fields in the columns, because then when downloaded, those columns would be downloaded as well. Thank you in advance for any help or ideas. Please let me know if any additional information is needed!

@CoopperBackpack Ah, thank you, that's a good idea! It seems to be working as expected. Slight delay after clicking one of the options that is not present when only checking the criteria through standard (non-virtual) fields though. Is there any way around this, or is checking the multiple criteria likely just going to lead to some delay?

As far as I understand, you cannot use both and AND or logic in the rules portion of a datapage to make a field required, however that is what I am trying to do. I am fairly new to JavaScript, and also don't completely understand all the particularities of how Caspio references different elements. I have written out the following script in an attempt to make a field required based on certain criteria, but this does not seem to work as expected. Any help would be appreciated! To give more background on the app and the overall goal, this is a performance evaluation application, and the attempt is to require a comment if a certain evaluation score is selected. For example, the organization can declare that any score of 1 must require a comment to go along explaining why a score of 1 was given, etc. <script> let Q1C = document.querySelector('[id*=cbd_JPT_Performance_Eval_Q1_Scr#]'); if ((Q1C.value == '1' && '[@field:a_JPT_Organizations_Comment1_Req^]' == 'TRUE') || (Q1C.value == '2' && '[@field:a_JPT_Organizations_Comment2_Req^]' == 'TRUE') || (Q1C.value == '3' && '[@field:a_JPT_Organizations_Comment3_Req^]' == 'TRUE') || (Q1C.value == '4' && '[@field:a_JPT_Organizations_Comment4_Req^]' == 'TRUE') || (Q1C.value == '5' && '[@field:a_JPT_Organizations_Comment5_Req^]' == 'TRUE')) { document.getElementById("EditRecordd_JPT_Performance_Eval_Q1_Comment").required = true; } </script>

I currently have a Pivot Table DataPage that displays individual values, then aggregates by averaging the individual values where the "Classification Title" column matches. Is it possible to create something similar to this functionality (through a Pivot Table or any other type of page) that would allow me to instead aggregate at a given percentile? For example, show the 60th or 75th percentile of the included values? I have included a picture of the current average-based page for reference.

@DrSimi Okay, thank you! I am no JavaScript expert, but I will give it a try and see if it is anything I can figure out. I appreciate you taking the time to help.

Hi @DrSimi, Thank you for sending this over. Looking at that post, it looks like that would have the same issues I am running into currently, it would just use JavaScript instead of CSS. If calculated fields are needed as helper cells, then we are hiding them, then they would still be present when downloading the table. Would there be any way to hide these columns without the helper calculated fields, or to have them not be included in the data download? Any help or ideas would be greatly appreciated!

Hi @NiceDuck, Thanks again for working to help me with this! It turns out this action was being caused by a different triggered action on another page. So this one was functioning properly, but that was triggering another action which was overwriting it with the old definition. Not great that I overlooked that, but I am glad I was able to get it resolved. Thanks again!

Is there a way to specify which columns will be included in the download of a tabular report? For context, I have a tabular report and I wanted to conditionally hide columns that were not being used depending on the filtering of the data. I did this by creating calculated fields that would specify if a column was being used, and hide it if it was not being used, sort of like what the article below walks through. That works well for viewing the table, but when I make the table downloadable all the helper columns are also downloaded. So, is there a way to specify which columns I would like to be included in the download? Or is there a better way to conditionally hide columns that does not involve creating a bunch (in my case, 30) of calculated fields?