Jump to content


Caspio Ninja
  • Content Count

  • Joined

  • Last visited


About NickO

  • Rank
    Advanced Member
  • Birthday August 31

Recent Profile Visitors

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

  1. 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.
  2. 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.
  3. 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.
  4. @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).
  5. 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.
  6. Thanks @Vitalikssssss, brain freeze on my part. I ended up doing it differently for my solution, but your suggestion was correct for that approach.
  7. 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
  8. Anybody have any ideas on this one? I'm open to alternative approaches as long as I can get to the same net result.
  9. 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.
  10. 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.
  11. 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
  12. I have not @kpcollier. Just powered through the task at hand and refreshed them one at a time.
  13. @Hastur thanks. It's unfortunate because this feels to me like another pretty straightforward functionality that is missing from the tool.
  14. 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?
  15. @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.
  • Create New...