CraigSZ

Thanks very much @NiceDuck

Hi all, Is anyone able to please explain how Table Variables can be used in Triggered Actions, ideally with screenshots of how they are set and used? I have a Triggered Action where I am currently creating & setting (via select) a large number of individual variables and I believe that a Table Variable would allow me to do this once. I have not been able to find any useful Caspio documents on Table Variables and have just been on a very frustrating chat with support who confirmed that there is no documentation/instructions on the functionality. The only mention I can find of them is "table variables for temporarily storing a set of records, which can be used as the data source for other Action blocks" which is in the Triggered Action "howto" page. TIA Craigsz

Caspio have confirmed via online chat that aggregates/totals in GridEdit datapages is not possible. It really is disappointing that the GridEdit form lacks basic features and seems to have a number of bugs especially when you contrast the GridEdit datapage that application users experience against the Caspio Bridge grid edit form used to maintain/design tables. The Bridge grid edit form has a slick interface and operates as expected. The application datapage however changes column widths, has missing functionality and you have to click somewhere else on the record to force the changes to update which results in a very poor overall user experience. I was asked to submit a feature request through Ideabox but there doesn't appear to be any active management of Ideabox. The development roadmap shows items from 2017 as under development so I can't see any evidence that the ideas are taken seriously.

Hi all, I need a total, based on the number of records which have the "Selected" checkbox set to true/checked. The calculated value will be displayed in the header of the form which is only shown to the user in GridEdit mode. At the moment the total is static so when the user checks/unchecks the value in the "Selected" column then the total is not changing. I have created three calculated fields to try and solve the issue but have a number of problems as follows: ChkSelected field - this is a SQL query to count the number of records which have "Selected" checked. Total value is correct but it is not changing when the user checks/unchecks records in GridEdit mode. Total Selected field - this is case statement to show if each record has "Selected" checked or not. The value is correct but when I display it in my header (via field selector) then it only shows the value for one record. Aggregate field - this sum Total Selected and is set to display above the data but it is not appearing. I'm not sure if that is because I am using a GridEdit form or due to another reason. I have attached two documents to show what is currently happening. Document 1 - this shows the datapage when it is loaded. The ChkSelected total is correct and the value for Total Selected is correct for each record. The Aggregate field is not showing Document 2 - this is after checking a record (and navigating to another record to force the update in GridEdit). The ChkSelected in the table is correct for the updated row but the total in the Header has not changed. The Total Selected value for the updated record is correct. I would like the ChkSelected total shown in the header to be recalculated & updated when the user checks/unchecks the "Selected" checkbox. I suspect I will need JavaScript fired when the checkbox state changes but I don't know how to "force" the SQL field to recalculate. This would then allow me to have additional checks/formatting based on the total of ChkSelected as I don't believe that "aggregated" fields can be used for this purpose as they do not have IDs (when you inspect the page). I would appreciate any suggestions on how to resolve the issue. TIA, Craig

@kgraham2121 One possible solution is to do the following... Create a StockCountMaster table (holds a record for each time the physical count is performed) - simple table & only needs a date field as a minimum Create a StockCountDetail table (holds a record for each item in the Materials List) - holds link to StockCountMaster + Material List item + Physical Count (will be set to 0) Create a basic Submit Form datapage to add a new record to the StockCountMaster table e.g. for a Physical Stock Count for 14th of January Create a Triggered Action on the StockCountMaster table (to run on Insert) that does the following... Loops through the latest list per the Materials List table and Inserts a StockCountDetail record (with a zero physical count as default) for each active item on the Material List The Grid Edit datapage would then use the data in the StockCountDetail table to present the Material List for the physical count. You would also then have a history of each Physical Count. Hope the above makes sense Cheers Craig

Thanks for your response @NiceDuck I have managed to resolve the issue by creating triggered actions. I was trying to see if there were any other ways to do it without triggered actions (e.g. MS-Access automatically creates the records from the query) as I needed to create a number of them depending on the circumstances. Regards, Craig

I have been able to turn off the "Grid Edit/Exit Grid Edit" link using the script below... Notes This only works because of the current grid edit behaviour where the form opens in the list view first otherwise the class name changes when you click the "Grid Edit" link. You need to use the Class Name per the initial form load before entering Grid Edit. On opening my datapage in list view, I established (using <F12> in Google Chrome) that the Class Name was 'cbResultSetActions....' (full name in script below) so check the Class Name of the item in your own form and update the script accordingly. The script does the following... Gets ALL Class Names and puts them into an array Finds the first instance (hence the [0] as that is the first item in an array) where the Class Name (check your own datapages as may not be the first instance) meets the name of the item to suppress Suppresses the display as opposed to hiding it {Style.Display = 'none' --> this ensures that no blank space is left where the item would have normally been displayed} I had to use Class Name as the ID field is dynamically generated by Caspio which tags on unique numbers to the ID each time you refresh the page. I'm sure you could do a partial ID match by taking a substring of the ID but I didn't need to get that complicated. Insert into Footer section of HTML <script> document.getElementsByClassName('cbResultSetActionsContainerCellspacing cbResultSetActionsContainer cbActionPanel')[0].style.display = 'none'; </script>

Hi @kgraham2121 I'm no Caspio expert but have you tried using a tabular report with Grid Edit enabled? I've been looking at these myself and they will allow capture in a spreadsheet type format but I have experience a number of shortcomings with the grid edit view... 1. The datapage does not automatically open in grid edit mode and the user currently has to manually click the "grid edit" hyperlink in top left corner 2. Formatting the grid edit view can be tricky as column widths reset on reopening 3. You cannot use grouping and groups will change to columns as Caspio is expecting data to be entered You can allow Grid Edit by setting up a Tabular Report datapage and then setting Grid Edit flag on the Edit settings per screenshot. I have attached two examples.. The List View is the default that opens even if grid edit is checked. The user must click the Grid Edit link You cannot edit in this mode. Checkboxes, data pickers etc will not work The Grid Edit view where you can enter in a spreadsheet view Date pickers, checkboxes operate as expected Hope you get sorted Cheers Craig

Hi, I need a tabular report datapage to open in Grid Edit mode on the initial load/rendering of the datapage as the users will be working with a lot of yes/no fields via checkboxes. I thought that only ticking "grid edit" on the datapage setup would achieve this but the list view is used by default and no edits are possible in this mode. Checkboxes also show as values yes/no instead of an actual checkbox. In-line edit means multiple clicks when the user just needs to select/deselect a number of checkboxes. I have been able to force my datapage to switch from in-line to grid edit mode (per the Grid Edit by Default post on August 15th, 2016) but the problem I have is that the datapage is first displayed in list mode mode and then the datapage refreshes (when the script kicks in) to change to grid edit mode which is a very poor user experience & doesn't look very good. You can see in the script that it is emulating a "click" of the grid edit link as opposed to opening the datapage directly into grid edit mode. I can reduce the delay from 500 milliseconds but you still see the list mode open and then change to grid edit mode. I would therefore like to find out if... Is there a way that the datapage can be immediately shown/rendered as a grid edit datapage instead of switching after the initial load? (either via datapage settings or javascript) Is there a way to disable/hide the Grid Edit/Exit Grid Edit link that shows by default? i.e. the user would only ever see the grid edit format for this datapage Would be happy with any methods that provide a clean user experience where the datapage is already set to grid edit mode when they see it. TIA, Craig

Hi @Vitalikssssss Your solution works brilliantly. Thank you very much! Regards, Craig

Hi @Vitalikssssss, Thank you very much for taking the time to provide your response. I will work through it and incorporate into my app. Will update once I've completed it. Regards, Craig

All, I have now done the following to resolve the issue: 1. When a Player is added to a Team then a triggered action now fires to create an Availability record for each existing Game in the team 2. When a Player is removed from a Team then a triggered action now fires to delete the Availability records for every Game in the team If both triggered actions are not used then you have problems if the same player rejoins the team at a later stage which may happen. I will also need to create additional triggered actions to fire when... a. a new Game is added so that an availability record is created for all Players in the Team where the Game was added b. an existing Game is deleted so that the existing availability records for the Game are deleted for all Players c. a Team is deleted so that all availability records for all Games and Players in the Team are deleted I would still like to know if there is a better way of doing this in Caspio as I will land up with 5 triggered actions, on 3 different tables, which need to be documented & maintained. TIA

Hi NiceDuck, I don't have the data yet. I have an existing table and I need to insert/create the data in the table and then display it in that view/pattern so that the user can set the checkbox to yes/no. My screenshots were from MS-Access and you don't need to (i) create the data records and (ii) display them for edit as Access automatically creates the data records as you use the checkbox in the query/view. I was Caspio would do something similar but can't find a way to do it. I think I am going to need a Triggered Action to create data entries in the table when certain events occur e.g. if a new Player joins a Team then they must get a record created in the table for each Game. I can then create a report/pattern where the user can set the checkbox to yes/no. Would appreciate any ideas/suggestions. Thanks

I have 4 tables which are Players, Teams, Games & Availability. Players can belong to more than 1 Team and a Team can have multiple Games. I also have a "link" table for Teams/Players to record which Players are part of which Teams. I've attached a diagram with basic tables and the relationships. I would like the Player (user) to see a list of ALL games for the that Teams they belong to and then be able to mark if they are available for the game by ticking a checkbox. Clicking "yes" would insert a record into the Availability table (if it doesn't already exist). I've attached a diagram of the view I'm trying to create. I've tried a few things but haven't been able to achieve what I need... I cannot use a view as there are no records in the Availability so you cannot edit the data (shows text false/true and not checkbox). A single submission form does the right thing (i.e. inserts a record into availability table) but doesn't work for the user as dropdown lists only show a single field so the user is unable to see other information about the date, time, location etc of the Game A tabular report, with detail, would not work as the Detail is in a different table. It would also mean that the user has to click on the detail button, select "yes" to update the single Availability and then return to the report view which is not a good user experience. I don't want to insert a record into the availability table in advance as that means having multiple triggered actions on each of the Players, Teams & Games tables which would cause maintenance issues and would bloat the database by having obsolete records. I could do this via a "dummy" datapage that receives a value of "yes" and creates the Availability record but that means using up a licenced datapage and also seems to be a workaround. Is there perhaps a way to do this via a Report and Virtual Fields? If the user checked "yes" for multiple games then they would all need to be updated but I don't think Caspio handles multiple submissions so perhaps each checkbox needs to "fire" independently but unsure if this would take a few seconds to run or refresh the webpage which results in a poor user experience. I may be missing something or maybe I don't understand the options/functionality in Caspio but I would appreciate any suggestions on how to achieve the above. TIA Craig

Thanks Vitalikssssss. I unfortunately cannot access the original posters site to see the suggested AJAX script in context or see how the full solution is deployed in the HTML block. Regards, Craig