Jump to content


Caspio Guru
  • Posts

  • Joined

  • Last visited

  • Days Won


Everything posted by NiceDuck

  1. If you are on a form type of datapage, you can try using this workflow where they converted it first on a comma seperated values on a virtual field then use that comma seperated values for your calculation. I hope this helps.
  2. I'm afraid that we cannot access list string fields on calculated fields, the same with the calculated values and formula fields.
  3. The one I have provided to you is for datapage level (specifically, form types such as submission form, SRUpdate and report details page since they are the only one that can have a virtual parameter). It can be done directly on the formula field as well but you will have to significantly change the formula.
  4. Okay, lets I'm gong suggest some step here part by part. first thing you need to have is the SQL formula for isolating the 6 digit number you have. Once you have it isolated, you can use something like this. On my case, the isolated 6 digit number is on the virtual 1. CONVERT(DATETIME, SUBSTRING('[@cbParamVirtual1]', 1, 2) + '/' + SUBSTRING('[@cbParamVirtual1]', 3, 2) + '/20' + SUBSTRING('[@cbParamVirtual1]', 5, 2), 101)
  5. Hello Im kinda bit shocked when I saw your formula. TLDR, Anyway, what I can suggest for you on that case is, try not to use all of the formula in one go. For example, Use only the first when and then, then go preview it and see if the issue persisted. Basically, try the formula part by part to trace which part of it is causing the issue.
  6. Just a short note to differentiate them: Triggers: Runs based on table activity (insert/update/delete) Is attached to a specific table as its parent Task: Runs on demand or on predefined Schedule.
  7. I really think that the formula field runs first. If you have a formula field on your table and you insert a record on it, the #inserted.Formula_field will already have value which you can use.
  8. you said that this field is unused thus you probably checked this already but you may want to check your table relationship as well. If that field is being used there, it would show you the same error.
  9. I'm not sure what you mean by creating a new field. You can use the formula field on the table design to generate values base on what you currently have on your table but we cannot use that to create a new field/column.
  10. This should be my setup without the for each loop. I know that the table variable will surely not have any records on it on initial setup but I still keep the delete action block just for the sake of my paranoia.
  11. I'm not sure if I'm correct, but as I read your trigger, you will have a conflict on the result there if you happen to have multiple inserts and updates with different Parent ID. It will end up giving the same AVG values to different parent records.
  12. @TellMeWhy The For Each? Good question. Lets just say that it is a way of making sure that the trigger will still work on the cases where there are more than 1 records inserted or updated (Bulk Insert/Update). Most of the trigger setup is design to accommodate only 1 record at a time and tend to fail or return an incorrect result during bulk inserts and updates. That is why I am using a 'for each' loop so the trigger blocks will process the contents/records of the #inserted 1 by one. Of course, there are better ways to setup your trigger to optimize it and make it adaptive to bulk inserts. Still, let just say that the use of 'For Each' loop is a 'default' or 'basic' way and works most of the times. It kind of slows your trigger though, specially when lots of records are being involved.
  13. NVM, I figured it out. Turns out that I will need to compile all the records first into a table variable then get the average values from there.
  14. Hello, I notice that when we are using the aggregate functions on the triggered action, we can easily combine the results for the SUM and COUNT to the value of the #inserted, however, if we tried to use the same AVE, we will be getting a wrong average results. This one is wrong Any Ideas?
  15. If the time values for the recess start/recess end, lunch start/lunch end or after school end is static, you can try using another calculated field to check that for you by using a case statement and datepart function. Use the datepart to get the specific time values of the time you have from your original field. You should have an integer result from that which you can directly compare to the to your other ecess start/recess end, lunch start/lunch end or after school end value.
  16. If you are going to create a case statement, make sure that the return value of all the cases will have the same data type. Otherwise, you may encounter an error during validation.
  17. You can also do the same on a report datapage (assuming that this datapage is using the parent table as its datasource) using a calculated field with an SQL statement. ex. Select count(<anyfield>) from Child where Parent_ID = [@field:Parent_ID] just replace the <anyfield> with the name of any field from the child table
  18. Case when DatePart(hour,[@field: date]) > 12 then DateAdd(hour, -12, [@field:date]) + ' PM' else [@field:date] + ' AM' end its a lot longer than what you have but oh well...
  19. If you are using a combined chart and report datapage, the chart will be included on the pdf if you download the whole report as PDF.
  20. In regards with 'IS NULL', you can actually try using ( = '' ) as well. ex. Where [@field:name] IS NULL Where [@field:name] = ''
  21. You may also try using a 'Not Exist' operator instead of 'Not In'. In my experience, it works better when a lot of records involved.
  22. Hello @Jan Thank you so much for your script, I modified it a bit to fit on my workflow where I need to make every negative numbers on my tabular report datapage results to be red and this is what I came up with: <SCRIPT LANGUAGE="JavaScript"> var elems = document.getElementsByTagName("td"); for (var i=0, m=elems.length; i<m; i++) { if (elems[i].innerHTML.charAt(0) == "-") { elems[i].style.color="red";} } </SCRIPT> I placed this at the footer and it magically works! Thank you!
  23. I would suggest that you replace this part: with Your current setup is returning blank values when I tried it on my end.
  24. NiceDuck


    If what you meant is connecting integrating your separate AWS-s3 to your Caspio bridge, then you may want to consider checking into Zapier: https://zapier.com/apps/aws-lambda/integrations/caspio-cloud-database https://zapier.com/apps/amazon-s3/integrations/caspio-cloud-database I'm not sure which is which but I hope it helps.
  • Create New...