Jump to content

Search the Community

Showing results for tags 'trigger'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • Caspio Bridge
    • General Questions
    • Caspio JavaScript Solutions
    • Tables, Views and Relationships
    • Import/Export and DataHub
    • DataPages
    • Deployment
    • Security, Authentications, Roles, SAML
    • Styles and Localizations
    • Parameters
    • API and Integration
    • Calculations and aggregations
    • User JavaScript and CSS Discussions

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start




Website URL






  1. I have a rather complicated task that I'm trying to execute. This may take a bit of explaining. It references three lookup tables and two other tables. The point of this task is to set a number of days required to complete a specific task (Engineering, Welding, etc.) in a project. These days vary based on the unit size and type. Here is part of the table that I am using for reference so that you can understand what I'm working with. So for a job making a "Non Jacket Bin" that is under 500L, the Engineering task should take 5 days and the Weld task should take 10 days. Here is the task as I currently have it. So the table that this is updating is specifically for tracking the number of days required to complete each operation for a job line. It is connected to the job lines table via a Line Number. Right now I only have it updating the days it takes in Engineering but I will add Weld and more. That's why I have the joins in the Update statement and not the select - just to avoid redundancy. So first, it joins the days line number to the lines line number. Then it takes the unit size and joins it to a lookup table. This lookup table is used to convert the size to an integer. In the lines table it is written as "100L" so this join just allows for a value "Liters" to be used later which removes the L. The next join connects the Unit Type to another lookup table which will be used to find the general category the type is in ("Bin" rather than a code like "BINX"). Finally, this is joined to a table that is similar to the one pictured above. Here is part of that table for reference: (The days are the days before the ship date) The Unit Size refers to anything that is less than or equal to that number. Thus, why my join is on Liters >= Unit Size. I then sort the select by the unit size ASC which should connect Liters to the smallest Unit size without going over. Instead, what I get is all units, regardless of size, assigned the smallest days. What am I doing wrong? Any help would be appreciated!
  2. Hi there, I need to create a trigger that sums different column values per user. I have multiple users on a table (below) and would like to on another table insert sum the number values of each a_user per column and insert it into another table as a combined history. ie user1: 106 | 60 | 73 user3: 96 | 45 | 71
  3. So I'm trying to make a trigger so that when new data is inserted/updated in my table it checks that the address doesn't match any existing addresses for that Customer and if not, it stores the address in a lookup table for easier inputting in the future. This is the trigger I set up but it isn't working. Anyone know a better way to do this?
  4. I need some help figuring out the issue with my trigger. Currently, it inserts nothing into my tbl_ggl_job_lines_dates table. In previous iterations, it has inserted duplicates of the same record. What it does is make a line from the feeder table which is this #inserted table. Then it should use this trigger to create a matching line in the dates table with some info filled in from the feeder table. The feeder table only has the Job ID which can have many Line Numbers within it. The dates table only has the Line Number. The #inserted table has both. Thus the two joins. The where statement is supposed to ensure that I don't pick up duplicate records but I'm not picking up any records. Any help would be appreciated. Thanks!
  5. I'm working on a trigger to create a certain number of lines based on the quantity specified upon submission. Basically it's one table that feeds another table. Here's the trigger. Variable i starts at the First Serial Number specified upon submission and iterates while i is less than the serial number plus the number of lines. Each line has a unique incremental serial number in the format JOB-01 where 01 is the serial number and JOB is the job ID. That's what the concatenate statement should do. This is the error I'm getting which doesn't make any sense because this should just be inserting new lines right? It shouldn't affect any vast number of records... Please help!
  6. I need help debugging a trigger. The whole trigger is very big because it's all the actions that need to happen when any data in the table is updated. Trying to avoid too much cascading. But here's what's relevant to my issue: So I have two tables, one for current notes and one for the history of all notes ever added to a job. In my job table, I have a Notes column and a New Notes column which is perpetually blank so that new notes can be added. What this trigger is supposed to do is insert the old note into my history table along with the Job ID and who submitted it. Then, the update should put the new note into the Notes column and empty out the New Notes column. What actually happens is the second part of the trigger works perfectly and adds the new note to the notes column but something goes awry with the first part and it adds 2 new rows to my history table with the note as completely blank and it also changes any pre-existing records with the same Job ID to be blank ???? I am at a loss. Please help!
  7. I'd like to be able to timestamp when each individual column is updated. Basically, when column "x" is updated, the column "timestamp_x" is updated with the time "x" was updated and when column "y" is updated, column "timestamp_y" is updated with the time "y" was updated and so on. Is there a way to do this with a trigger? Or within the datapage with HTML or with a calculated value? Let me know if you know a solution! Thanks!
  8. I created this trigger so that when I insert a new line into this table, it automatically creates a matching line in another table (if the unit type is not "FREIGHT" or "OTHER"). For some reason, it ran perfectly the first time I added a new line but now when I enable it and try to add a new line to my table, it freezes the page and does not allow me to add a new line to the original table. The page turns unresponsive. Here is my trigger. Can anyone help me identify the issue?
  9. Hi there, I am relatively new here with my first post. I am busy figuring out trigger actions which I have the basics down but struggling with something: I have 3 tables: 1. user_info 2. trading_results 3. user_history I need a trigger action to insert newly captured data from trading_results (date, growth in %) to user_history applying it to all active users (tick box) from the user_info table.
  10. I have a table that I have a lot of information coming into using a multi step form . There are parts of it I want to go into a new table. I created the trigger, and the new row is created when the form is submitted but the actual data isn't going into the table. I don't have a clue of why its not going into the table see the photo of the trigger and the table showing blank rows. Any help would be awesome. Thanks.
  11. Hi, I'm not sure if this is possible but I need to set up a trigger to copy multiple records from one table to a) the same table and b) to another table. This is basically so a user can draw from old records for new projects without having to re-enter all the data again. The user would be doing this via Bulk Edit (selecting multiple records and updating a field for them all). There are a couple ways I thought this maybe could be done: 1) having Bulk Edit update one field ('copy_me') to 'True' and then a trigger that runs 'On Update' using Where 'copy_me'= True to select the records to copy. Then run duplicate those records (but not all fields, only some) on the same table OR into another table. After that the next step in the trigger would be to change 'copy_me' to False Where 'copy_me' was True so that the record was no longer 'triggerable'. 2) use a separate table to store, in a new record, the Id's (integers) of each record that's been chosen via Bulk Edit and then running an 'on Insert' trigger that Joins those Id's (they're in one text field, comma separated) to the main table's Id's and pulls various fields to copy them into a new table. I currently have the first half of this scenario set up (storing multiple record Id's in a separate table's text field via Bulk Edit) but don't know how to create a 'duplicate records' trigger for it. If anyone knows the specifics of getting a trigger like this to work-- or a better way to copy multiple records after they've been updated via Bulk Edit-- I'd really appreciate the help.
  12. Will trigger be executed in a table when new record is inserted via Zapier?
  13. Hello, I'm new to Caspio. I have an Update Form with a calculated field called "Total_Percentage", that takes the values from other fields. Code below: Isnull([@field:PointFactor_Duties_PercentDuty1],0)+Isnull([@field:PointFactor_Duties_PercentDuty2],0)+Isnull([@field:PointFactor_Duties_PercentDuty3],0)+Isnull([@field:PointFactor_Duties_PercentDuty4],0)+Isnull([@field:PointFactor_Duties_PercentDuty5],0)+Isnull([@field:PointFactor_Duties_PercentDuty6],0) What I would like to do is to prevent users from going forward when their total exceeds 100%. I also, would like to show them, which field(s) is/are causing this field to go over the threshold of 100%. The rules section is not conducive to this goal, as rules section is limited. Triggered action section forces me to either email or text the users if their total exceeds 100%. I don't want to do this, since the whole point is for users to correct errors immediately. Is there anyone out there who can impart me a script or two to help me? SQL or javascript that would work with Caspio. Thank you so much. Emme
  14. I am creating an on insert trigger for my table but upon testing, I got this error. Modified the trigger to a simple insert like this: However, the error still persists. Does anyone encounter the same issue? Any help would be greatly appreciated.
  15. Is there a way to preview HTML email in trigger without enabling it and sending the email?
  16. I am trying to configure SMS action in trigger. But upon testing, it does not work. Below is a screenshot. Any help would be appreciated.
  17. Do records inserted through REST API execute triggered actions?
  18. Are file data types available in trigger?
  19. Hi, Does anyone know how I can create a trigger to delete records in a table that are older than say 10 minutes? I can do this easily with SQL statements but can't get my head around how to do this in the drag-and-drop screen for triggered actions. thanks. John
  20. I'm trying to create a trigger that will add fields to a bunch of related tables based on the new CCTAproject_id that is entered on the parent table. It won't let me get rid of the ID field for some reason so I'm guessing that is a necessary field. My solution was to take the max existing value in the ID field and add a 1, so that new records increase sequentially by 1. I get an error when using this method, "Column '#inserted.CCTAproject_id' is invalid in the select list because it is not contained in either an aggregate function of the GROUP BY clause, (error code: SQL8120). But if I remove the number block containing the "MAX etc" and just leave it to equal a static number, the trigger works. I don't understand what the error means by grouping since the ID field is an integer and I'm not trying to group different field. Is there a way to manipulate the trigger so the numbers can increased based on existing values?
  21. I am new so please excuse my SQL ignorance: My trigger is working, EXCEPT for the fact that it is NOT INCLUDING the most recently inserted value in the CommentHRS field in the sum (CA_TOTALhrs). Trying to be clear here: It's properly calculating the SUM on the insert event, but just not including the newly inserted value as part of the sum. (The actions with the red X work fine. No problems there.) Why would it not include the value that was just inserted in the SUM? Thanks. Mike
  22. I am trying to craft a trigger that will sum inserted rows (multiple rows) and put the total in another table. I cannot seem to find a way to select multiple rows from the #inserted table though. To explain further, I have three tables, Master, Details, Totals. When a new row is inserted into the master table an insert trigger fires on the master table and several detail rows are copied from a template table into the detail table, where each row contains a price and quantity. The price and quantity are multiplied to create a total for each row through a formula field in the details table. On the details table there is a triggered action that on insert, update or delete will sum the cost of the total formula column for all rows and put that total into a totals table. I have to sum the #insert and existing detail rows separately and add them together to get the total. This all seem to work well when inserting one row but when inserting multiple rows only the first row from the #inserted table is totaled. That makes sense because the trigger on the detail table doing the sum specifies Select top 1 from #inserted when summing. However, I need to sum multiple rows from #inserted and the trigger is not letting me remove the top 1 clause stating that it only expects one row from the select query. I have tried putting the select query for the inserted table within a sum block and alternatively putting a sum expression on the result of the select query. In both cases the trigger will not save stating that the select must specify top 1. I have attached some screen prints of what I am doing in the trigger and the issues with each scenario. TriggerValidationError.docx
  23. I'm looking to only insert unique values within a record on update with a trigger. Right now I have two tables set up with a trigger on insert/update. When a user updates a field within the Employee's table, it copies all of the information from the current record into the Employee History table. However, my users want to be able to see what fields were modified within the Employee History table. The trigger currently just copies all fields (and values in those fields - most are text) from the current Employee's table to the Employee History table. The user then can compare the current record in the employee's table with the employee history records and manually scroll through to find what values in each field are different, but this takes quite a bit of time. I would like the trigger to look at all the current values in the fields it is going to copy, if the values have not been changed (or are equal to the current values in the Employee History table), then do not update/insert those fields in the employee history table; otherwise update only the fields that have been changed. This would ideally show only updated field values for each history record, instead of duplicating all of the field values. Thank you for any assistance!
  24. Hi to everybody. I have a problem. I need a table, and relative DataPage insert data than can insert data of more than one customer. In just one data Page i need to accept customer1 and customer2 data (name, surname, address, email, phone ecc) Now the problem is that i need to have the 2 customers, NOT in a table with unic horizontal line, but listed, with new id but with the same booking number, for a new record in the table. I think i need a new table but i can't be able to make triggers to copy data in a new table and records automatically (if that is the right way). (Ex. of the table/dataPage i have) ID_ booking | booking_number | n_of_ customer | Customer1_name | customer1_surname | customer2_name | customer2_surname| 1 56789 2 John Doe Mary Harris 2 56790 1 Micheal Lewis 3 56791 2 David Clarke Rose Smith (ex. of what i need) 1 56789 2 John Doe 2 56789 Mary Harris 3 56790 1 Micheal Lewis 4 56791 2 David Clarke 5 56791 Rose Smith How can i do? Thank you for your time in advance.
  25. I'm looking for information about the "expression" feature within triggers. I have not been able to find anything other than a reference to expression aliases in the Caspio Bridge 12.0 release notes. https://howto.caspio.com/release-notes/caspio-bridge-12-0/ " Expression names can now be renamed for easier reference in other areas. " " When saving or validating a Triggered Action, incomplete expressions in a SELECT block will be automatically removed. " I discovered the feature when working with a select statement embedded in an If statement. I assume these expression alias can be used elsewhere in the trigger code. Unfortunately, I have not found a means to use them. I created the following in an attempt to figure out how to use the "expression" feature. The IF block passes validation. I saved it along with additional code that modifies the inserted record (see below). Caspio doesn't complain about the simulated no operation (NOP) I created by using an IF 'exists' comparison for select statement return value This is a far as I have been able to reverse engineer the "expression" features. I have tried to access the alias (Var1, Var2, and Var3) elsewhere in the trigger withing many different contexts. I tried within the IF and THEN clauses where they are defined. I tried within subsequent Update and Select statements. etc..... Use Case I have a table that tracks eight different topic scores and an award for each topic by person. Awards qualifications for each topic are based on several factors (topic, score, when scored, gender, and age). The scope and qualification factors for each award are contained in a reference table. I implemented the triggers needed to identify and update or insert the correct the awards. It works as expect. A different qualification/criteria row may apply to identify the correct award for each topic for any person. Without going into all of the details you will not be able to identify whether or no the data has been properly normalized. You will have to trust me that it has been. Besides, I have other use cases that would benefit from variables. There are ten large Update statements used to the identify the award for each topic as well as updating summary awards in another table. The logic is complex enough along with the size of each update statement that maintenance of the code will be cumbersome and error prone. In fact, I haven't yet combined them into one update and insert trigger. I am concerned about perfomance due to the number of select and join statements. I would rather clean this up using something like variables before deploying them in one update and insert trigger. Ideal Solution Ideally, I could use variables to gather the criteria based on context for the person and use them within subsequent IF statements. This would significantly shorten the length and complexity of the trigger. Based on my reading of forum posts, Caspio help documentation, and viewing of YouTube videos I am nearly certain that triggers do not support variables. Except that maybe "expressions" within triggers could solve my problem. An Example I included the following to help the reader better understand the ideal solution that trigger "expressions" may solve. The following test uses dummy columns to hold values for use in the trigger. I think this is a dangerous kludge. I am not using this method in my production triggers. For example, three dummy columns called "ShortCountryCode", "NumbersTests", and "TextTests" were added to the table. TextTests is assigned the value from the CountryCode field in the first Update statement. It is used in the next Update statement as part of a concatenated string to update the ShortCountryCode field. TextTests is assigned the string "SumeGamaa" in the last Update statement. The NumbersTests column is used to execute the trigger from the datapage. Ideally, variables would be used instead of dummy columns. Here is the entire trigger with the test "expression" code. The IF statement that uses three expressions is not used in the last three Update statements. Note that Caspio doesn't complain about this trigger. I have attached the table and a datapage I used for my testing. The data is public domain that I downloaded from https://www.kaggle.com/ sb_dp_internet_country_codes_2018-May-13_1706.zip sb_t_internet_country_codes_2018-May-13_1705.zip
  • Create New...