Jump to content


Caspio Ninja
  • Posts

  • Joined

  • Last visited

  • Days Won


Everything posted by NickO

  1. @CoopperBackpack and @NiceDuck thanks for the replies and sorry for the delayed response. I had to dip out for a few days and am sort of back now. Long story short, no unique fields or formula fields. Nothing out of the ordinary from what I can tell. All that said, I started a new triggered action from scratch, exact same as the others (using the WHERE clause to ID which records to trigger the email alert as CoopperBackback suggested) and it works fine. IDK why the other one was choking, but it's good to go now.
  2. Hi @CoopperBackpack, thanks for the suggestion. This does clear the error in the Triggered Actions builder, but I get a much less helpful "Record cannot be saved because an error was encountered in Triggered Actions." I stripped it down to just the email (changing the message to a generic text for testing). I am not sure if it makes a difference because the #inserted should deal only with the record being inserted, but this is a pretty big table the record is being added to. I also tried to add the "Top 1" condition to the select statement at the top and got the same error. Wish it was more descript about what is failing. I also tested it with Marketing Produced set to unchecked/false to see if it would just add that record. I got the same error.
  3. I am trying to create a triggered action with notifies a group of users in the AuthUsers table when the record meets certain criteria. I use it successfully in other tables but it is choking here for some reason and saying it expects a single value. Any thoughts on why or how to get around that? I have trimmed down just the relevant code (hopefully) to save space as it has a lot of variables for populating the email message. As a reference, I use something very similar, just without the If/Then in another table and it runs without issue.
  4. If anyone else runs into this, I came up with a solution. It's a little cludgey, but it is functional. I added a calculated field to my table which concatenates the two IDs together, then did a comparison to those values before adding a new record with the triggered action.
  5. I have the action below which adds relationship records between Description and Category tables when a user selects to "Add All". What I can't figure out, or find, for the life of me is how to add logic to this that would either skip the join if the record pair already exists or delete duplicates after adding all of the associations. The problem I have is that if some of the associations already exist and a user chooses to "Add All" then it creates duplicates for those that already exists. This causes problems in views and data pages.
  6. Late thank you @Vitalikssssss. For some reason I didn't get notified of your reply. I will work with this some after I get through the current iteration and see what I can come up with. At first blush this looks very similar to what I am trying to do. I appreciate it.
  7. @Vitalikssssss Hopefully the info below clarifies a little. The first is a screenshot of the report with no criteria selected (and the JS to hide empty rows disabled). The second is a report with criteria selected. I am currently able to hide rows in the "Solution Name" column based on criteria in hidden rows in the datapage. I would like to be able to hide rows at the Category Name and Product level conditionally based on the associated aggregate value (hiding it with CSS in second screenshot so it only shows aggregate at the product level, but it's there).
  8. Apologies for the late reply @Vitalikssssss ... I am not sure how I missed this one. I have successfully hidden individual rows in the datapage where the value is zero, but the groups still show up. So, to illustrate. Instead of Group 1 Agg 1 Name 1.1 Calc 1 Calc2 Calc 3 Name 1.2 Calc 1 Calc2 Calc 3 Group 2 Agg 1 Name 2.1 Calc 1 Calc2 Calc 3 Name 2.2 Calc 1 Calc2 Calc 3 I have Group 1 Agg 1 Group 2 Agg 1 I am trying to hide those aggregate lines of the datapage as well. Even with collapsible groups, it still shows the "group" level even if the individual rows are not visible. If there were a way to hide the aggregate row based on the value of the aggregate, I would be golden.
  9. Thanks @Vitalikssssss, brain freeze on my part. I ended up doing it differently for my solution, but your suggestion was correct for that approach.
  10. I have a datapage which make about 14 different calculations to get to a list of calculated fields. I am trying to take any row in which there is a "2" and display "Primary" in this row. Each of the calc fields contains the value of blank (perhaps NULL? it is a query that is returning no results), 0, 1 or 2. Below is my case statement that is not playing nice. I appreciate any input. CASE WHEN [@calcfield:3] = 2 THEN 'PRIMARY' WHEN [@calcfield:4] = 2 THEN 'PRIMARY' WHEN [@calcfield:5] = 2 THEN 'PRIMARY' WHEN [@calcfield:6] = 2 THEN 'PRIMARY' WHEN [@calcfield:7] = 2 THEN 'PRIMARY' WHEN [@calcfield:8] = 2 THEN 'PRIMARY' WHEN [@calcfield:9] = 2 THEN 'PRIMARY' WHEN [@calcfield:10] = 2 THEN 'PRIMARY' WHEN [@calcfield:11] = 2 THEN 'PRIMARY' WHEN [@calcfield:12] = 2 THEN 'PRIMARY' WHEN [@calcfield:13] = 2 THEN 'PRIMARY' WHEN [@calcfield:14] = 2 THEN 'PRIMARY' WHEN [@calcfield:15] = 2 THEN 'PRIMARY' WHEN [@calcfield:16] = 2 THEN 'PRIMARY' WHEN [@calcfield:17] = 2 THEN 'PRIMARY' ELSE 'NOT' END
  11. Anybody have any ideas on this one? I'm open to alternative approaches as long as I can get to the same net result.
  12. I am not sure I follow @Corpcatalog. On my datapage, I have a column that does a custom calculation to count the number of users in various categories. I aggregate that number in the datapage and need to pass that aggregated value. Maybe I am just being dense, but I cannot find a solution that aggregates the value in a parameter that I can pass to another datapage.
  13. I have two data pages, one to calculate which applications are need and one to calculate the number of users per application. The output to the user needs to be a report of users per application. Due to elements involved in calculating these two values, it is extremely inefficient to create a single view that has all that info. Is there a way to pass the data from the aggregate row of a datapage as a parameter? I could then use a third page to calculate the results. I am not married to that approach, by the way, that is just where my brain is locked at the moment. I am more than willing to consider alternative approaches to make this happen.
  14. I have a tabular datapage which essentially has the columns of Group, Name, Calc 1, Calc 2, Calc 3 and an Aggregation of Agg1 . The grouping is done by Group field and Agg 1 is shown at that level. Is there a way I can hide the entire grouping if Agg 1 does not meet a certain criteria? Likewise I would like to highlight specific rows in the expanded group based on values in one of the calc fields (varies depending on situation). In the example below, I would want to hide all of Group 1 (Group 1 row w/Agg1 as well as Name 1.1 and Name 1.2 rows) if Agg 1 is less than a designated threshold value. Is there a way to do this? Group 1 Agg 1 Name 1.1 Calc 1 Calc2 Calc 3 Name 1.2 Calc 1 Calc2 Calc 3 Group 2 Agg 1 Name 2.1 Calc 1 Calc2 Calc 3 Name 2.2 Calc 1 Calc2 Calc 3
  15. I have not @kpcollier. Just powered through the task at hand and refreshed them one at a time.
  16. @Hastur thanks. It's unfortunate because this feels to me like another pretty straightforward functionality that is missing from the tool.
  17. 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?
  18. @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.
  19. 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
  20. 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?
  21. 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.
  22. 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.
  23. @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.
  24. 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?
  25. 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.
  • Create New...