Jump to content


Caspio Ninja
  • Content Count

  • Joined

  • Last visited


About NickO

  • Rank
  • Birthday August 31

Recent Profile Visitors

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

  1. @Hastur thanks. It's unfortunate because this feels to me like another pretty straightforward functionality that is missing from the tool.
  2. I have a single table that has 3 different types of results. Call them Advantage, Disadvantage, Neutral. I would like to ideally display these in 3 separate columns in a Gallery datapage. I found some older conversations on the topic but nothing recently so I figured I would ask. If I have to, I will consider using 3 separate datapages and do the separation on the HTML of the page but I would prefer to be able to accomplish it with a single datapage. Anyone have any suggestions or ideas?
  3. @Ed727 I actually did find a few potential solutions. Please note that none of this is my original work per se. I have stitched together a bunch of different answers and tips I have found through the forums. I would love to give credit where it's due specifically, but I don't recall exactly where all of it came from and I have tweaked some of it to fit my needs more specifically. The first, and easiest, is to use a Pivot Table type data page and make the data the links or the lookups you want. There are several posts here about how to add those elements to Pivot Tables. The downside is that this is not efficient for large views. I found serious performance issues once I got over about 100k records. The second is a little more complicated, but is working great for me in a variety of data pages. I'll try to explain as best I can. In a nutshell, you are searching against a view and then using Javascript to hide any rows that don't match that search. Where this method really gets useful, at least for me, is when I am doing complex joins across multiple tables. I can create my view using Outer joins then whittle the results down based on whatever combination or criteria I choose to use (limited only by your creativity with SQL). Start with what I call the "Target" table. For example, if you have Orders and Products linked with a normalizing table as in the above example and you are looking for all the orders that contain a certain product, then your Orders table would be your target table. Create a view that links all of your relevant tables together. We will call it OrderProductView for this example. Create your datapage with the Target table as the source (Orders for us). In your search criteria you will add a Virtual Field that references the names and values for Product_id (again using the tables above as a point of reference) In your results, you will add whatever information you want from the Orders table and a Calculated Field The Calculated field will be used to create a query that counts the number of "hits" to your criteria. Something along the lines of the statement below. The last criteria is important because it makes sure you are counting hits for the specific row of the datapage. SELECT COUNT(DISTINCT(**FIELDNAME**)) from _v_**VIEWNAME** WHERE --For criteria fields that are in the target table OR if all inner joins in view being searched **CRITERIA FIELDNAME1**=COALESCE(NULLIF('[**CRITERIA PARAMETER1]', ''), **CRITERIA FIELDNAME1**) . . . AND --For target criteria. I.E. when searching the "Description" table, DescriptionID would be the CRITERIA TARGET FIELD **CRITERIA TARGET FIELD**=target.[@field:**TARGET FIELD NAME**] Specifically for our example, it would be something like this (assuming we use the naming convention in the view of Table_FieldName and the name of the virtual field in the search is configured to pass @ProductID on exit from the advanced tab). SELECT COUNT(DISTINCT(Order_Order_id)) from _v_ProductOrdersView WHERE --For criteria fields that are in the target view ((Product_Product_id is NULL AND '[@ProductID]'='') OR Product_Product_id=COALESCE(NULLIF('[@ProductID]', ''), Product_Product_id)) AND --For target criteria. I.E. when searching the "Product" table, Product_id would be the CRITERIA TARGET FIELD Order_Order_id=target.[@field:Order_id] Now that we have a mechanism to count the unique hits, we have to hide any rows that have 0 hits. Add the below code to a HTML block in your datapage and modify the fields as indicated in the notes on top. // CHANGE @FIELD:FIELDID FOR ROW KEY ID // CHANGE @CALCFIELD TO FIELD WITH COUNT OF QUERY HITS (OR CHANGE CRITERIA COMPLETELY TO MEET NEED) <a id="visi[@field:FieldID]"> <script> var isi = document.getElementById("visi[@field:FieldID]"); if([@calcfield:1#] == 0){ isi.parentNode.parentNode.style.display = 'none'; } </script></a> IF we've done it all correctly, the datapage should show us only those rows that meet the criteria chosen and the "hit count" column should never actually display a zero.
  4. Does anyone know if there is a way to execute actions of the Excel plugin via VBA? I am putting together a sheet to help our team with data entry where using data pages is just too inefficient (there are too many records to manually enter every item and every link). So basically I use the sheet to create the parent items, user imports that, then refresh the updated tables via the Excel plugin to get the new GUIDs, and from there all the rest of the data/links are created and then the user can import them. It works pretty effectively except for the fact that each table must be selected one by one manually to refresh the Caspio data in Excel. I would love to be able to automate that. Thanks in advance
  5. NickO

    Auto Search

    I had a chance to get back to this and did find a way to reference the search criteria with a wildcard search. <script> document.addEventListener('DataPageReady', () => { var thisDP = document.querySelector(`[action*='[@cbAppKey]']`) var field = thisDP.querySelector('select[id^="Value1_1"]'); var field1 = thisDP.querySelector('select[id^="Value2_1"]'); var field2 = thisDP.querySelector('select[id^="Value3_1"]'); var submitButton = thisDP.querySelector('.cbSearchButton'); field.onchange = function(){ submitButton.click() }; field1.onclick = function(){ submitButton.click() }; field2.onchange = function(){ submitButton.click() }; }) </script> In the above example, the value set is field1 is set from a cascading drop down. If I make it a cascading listbox, it works great. If I use onchange it spins constantly and I cannot interact with the page (looks like it is looping). When it is a dropdown, the "onclick" event triggers as soon as I hit the box to drop it down. I need to use drop downs due space on the page and user experience, but this one has me totally stonewalled. Anyone have any thoughts?
  6. Thank you @Andrew. This does the trick. As far as the ID being the same on every line, I found my issue. Your solution is actually reading the name listed there, not the ID behind it. When I switch my logic to be based on the name instead of ID, it's golden. I appreciate your help.
  7. NickO

    Auto Search

    Thanks @kristina and @DefinitelyNot31337 I am using this in several reports to great effect. However, when I have cascading fields, I run into two problems. - For @kristina's solution, the naming convention of the fields changes and it appears as if they are attached a random suffix so setting the field variable is not successful. Below is the code I am using in the footer of the page. <script> document.addEventListener('DataPageReady', () => { var thisDP = document.querySelector(`[action*='[@cbAppKey]']`) var field1 = thisDP.querySelector('#Value1_1'); var field2 = thisDP.querySelector('#Value2_1'); var field3 = thisDP.querySelector('#Value3_1'); var field4 = thisDP.querySelector('#Value4_1'); var submitButton = thisDP.querySelector('.cbSearchButton'); field1.onchange = function(){ submitButton.click() }; field2.onchange = function(){ submitButton.click() }; field3.onchange = function(){ submitButton.click() }; field4.onchange = function(){ submitButton.click() }; }) </script> - For @DefinitelyNot31337's solution in the HTML block, it constantly is recalculating when I have cascading values so I can't interact with the page. Anyone have thoughts on how to resolve this? I am obviously not a great js coder and kind of hacking this together so I really appreciate the help.
  8. @carlJS my apologies for not getting back to you on this. It must have gotten lost in the shuffle somehow. Thank you very much! This is what I was trying to put together and was failing miserably.
  9. Thank you very much for the reply @Andrew This is very close to what I want to do. I am only good enough with js to be dangerous. My actual solution is Solutions and Details (in screenshot below). The City/State was just an easy visualization. I was able to do some minor tweaking (mainly formatting, link construct, etc. and nothing functional) to get it closer to what I need but still having trouble with two bits. I'm hoping somebody can help on this part. First, for some reason I am getting the same ID for every parameter...so in my case specifically it is SolutionID=[@field:aNEW_Solution_SolutionID]. When I add a link in an HTML Block, it works great. But here for some reason, it always populates the same SolutionID (first in the database) instead of dynamically assigning it based on the row of the report. So, for illustration, in the screenshot below QA-QC should have an ID of 2000 and Reports of 1000. Instead, both links have an ID of 1000. Second, the heading I want to replace with a link is actually at the second level. Below is a snip to illustrate. Instead of the blue linked heading, I want to change those in the red boxes to links. Is this possible?
  10. I am sure this is not unique but I cannot find anything on it for some reason. I have grouped data (i.e. State expands out to a list of cities). I want to put a link to additional data that is at the State level. Is there any way to do this? I don't want the link on every City row because that implies the data is for the city and not the state.
  11. Thanks @carlJS, this is very promising! I am travelling this week and have not had a great deal of time to dig into this. I will do so next week and let you know how it goes.
  12. Thanks for the suggestion @carlJS. Either I am hosing it up or it is not fitting the bill on this one. I tried both adding your code to the footer between the solutionid and productid sections as well as to an HTML block. I have a List datapage and have enabled the ability to insert inline. If the second field is not a cascading parent, the value (productid) is populated just as I would expect. As soon as I make it a parent in a cascading relationship, the value is not populated. The cascade works fine when I select it manually, I just can't get it to select the value via the code. Basically, my use case is that I have a relationship between solution-product-feature that I need to establish. Basically I am linking a solution to a feature-product combo. Automatically selecting the product and solution for the inline insert is a usability enhancement I am trying to make.
  13. I have two tables that are linked with a relationship table. Basically a Problem table (Fields: ID, Name, Description, etc.) and Vertical table (Fields: ID, Name, Description, etc.) linked by a ProblemVertical relationship table (Fields: ProblemID, VerticalID). The ProblemVertical is a many to many table. The Vertical list is relatively static. The Problem list is very dynamic. Any time I add a new Problem, I want to create a relationship to each of the current Verticals. So, if I have the Vertical table as below and add Problem 1 to the Problem table, I want to automatically create the records in ProblemVertical as shown below. I could hard code in the Vertical IDs to a triggered action, but that would be a pain on the occasion that a vertical changes or is added/removed. I would prefer to be able to loop through the Vertical table somehow when I add a record to the Problem table. Problem Table Vertical Table ProblemVertical Table ID Name ID Name ProblemID VerticalID Prob1 Problem 1 Vert1 Vertical 1 Prob1 Vert1 Vert2 Vertical 2 Prob1 Vert2
  14. I am using the below code to populate inline insert fields to make data entry easier for users (got it from a post by @DefinitelyNot31337). I am having some problems on one page though and hoping somebody can help clear it up. My search and results are on one page. The search is using 2 fields (SolutionID and ProductID) both go to Parameters of the same name ([@SolutionID and [@ProductID]). There is a third field (FeatureID) which is a Cascading Drop Down based on the ProductID. As soon as I make FeatureID a Cascading Drop Down, it fails. If I leave it as a regular drop down, the code works perfectly. Code below <script> // Replace [@authfield:name] accordingly with the parameter or custom value you wish. var paramValue = '[@SolutionID]'; // Replace with the name of your field as described in your DataSource var field_name = 'SolutionID'; /* Edits are not necessary for this part */ var i_field = document.querySelector('form[action*="[@cbAppKey]"] #InlineAdd' + field_name); i_field.value = paramValue; /* To this part*/ //i_field.type = 'hidden'; // If you wish to hide the field as well, just remove the two forward slashes before i_field.type i_field.readOnly = true; // Replace [@authfield:name] accordingly with the parameter or custom value you wish. var paramValue = '[@ProductID]'; // Replace with the name of your field as described in your DataSource var field_name = 'ProductID'; /* Edits are not necessary for this part */ var i_field = document.querySelector('form[action*="[@cbAppKey]"] #InlineAdd' + field_name); i_field.value = paramValue; /* To this part*/ //i_field.type = 'hidden'; // If you wish to hide the field as well, just remove the two forward slashes before i_field.type i_field.readOnly = true; </script>
  15. I have a datapage that captures a variety of information about documents in a Sharepoint library. This allows me to link the documents to data we have in our Caspio app. Is there a way that I could use a datapage to facilitate uploading the documents directly to Sharepoint and retrieving the permanent link to that document and essentially make it all one process instead of multiple systems/steps?
  • Create New...