Search the Community

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

Hello. I am trying to sort out the capabilities of the new formula data type in v9.4. I'd like to combine text fields and a date field in the formula but I get an invalid formula error when just combine the fields with a "+ Str(1)+". Do I need to convert the date to a string? Also, the formula seems to reference the stored value of a foreign key in the table. Is there a way to use the display value of a related field instead? Thank you.