Jump to content

Becca37

Caspio Ninja
  • Posts

    36
  • Joined

  • Last visited

  • Days Won

    2

Everything posted by Becca37

  1. Thanks @Glitch It makes some sense yes! I tried adding IsNull([@field:StatusOverride], 0) = 0 AND to the other conditions and it still produces the same error regarding the trigger. I then pruned the formula to: CASE WHEN IsNull([@field:StatusOverride], 0) != 0 THEN [@field:StatusOverride] ELSE 'Error' END And, the same error. I then turned the trigger OFF and tried again with the stripped down formula and got: Record cannot be saved due to incompatible values in one or more formula fields. Hmm, so maybe that seems to be the underlying issue. Then accidentally tested the status override field with an integer instead of a string, and it worked. But it will be a string so I updated the stripped down formula to: CASE WHEN IsNull([@field:StatusOverride], 'n/a') != 'n/a' THEN [@field:StatusOverride] ELSE 'Error' END And that worked. Yay! So I added back in the other cases, and the status override works, but now the others do not. Boo! They are dates so the comparison to check for Null has to be with 0 as far as I've been able to find. So, then I nested another CASE statement to try to eliminate the possibility of two values being returned: CASE WHEN IsNull([@field:StatusOverride], 'n/a') = 'n/a' THEN CASE WHEN IsNull([@field:DateOpened], 0) = 0 AND IsNull([@field:DateClosed], 0) = 0 THEN 'Pending' WHEN IsNull([@field:DateOpened], 0) != 0 AND IsNull([@field:DateClosed], 0) = 0 THEN 'Open' WHEN IsNull([@field:DateOpened], 0) != 0 AND IsNull([@field:DateClosed], 0) != 0 THEN 'Closed' ELSE 'Error' END ELSE [@field:StatusOverride] END But still only the status override option works. Boo! {pulling out my hair} SO now, this works: CASE WHEN IsNull([@field:StatusOverride], 'n/a') != 'n/a' THEN [@field:StatusOverride] ELSE 'Error' END AND this works: CASE WHEN IsNull([@field:DateOpened], 0) = 0 AND IsNull([@field:DateClosed], 0) = 0 THEN 'Pending' WHEN IsNull([@field:DateOpened], 0) != 0 AND IsNull([@field:DateClosed], 0) = 0 THEN 'Open' WHEN IsNull([@field:DateOpened], 0) != 0 AND IsNull([@field:DateClosed], 0) != 0 THEN 'Closed' ELSE 'Error' END BUT I need to be able to check BOTH in the same formula because we have to be able to override the normal status value that is otherwise calculated based on open and close dates.
  2. I have a need to calculate a status value based on (a) Open and Closed date combinations and (b) an override value, if present. My formula validates, my trigger validates, and all works well for creating status based on the data fields, but I get an error when basing it on the override field. StatusOverride is a Text(255) data type. Status is a formula in Admissions and a Text(255) in Admissions_AuditLog. Formula: CASE WHEN IsNull([@field:StatusOverride], 0) != 0 THEN [@field:StatusOverride] WHEN IsNull([@field:DateOpened], 0) = 0 AND IsNull([@field:DateClosed], 0) = 0 THEN 'Pending' WHEN IsNull([@field:DateOpened], 0) != 0 AND IsNull([@field:DateClosed], 0) = 0 THEN 'Open' WHEN IsNull([@field:DateOpened], 0) != 0 AND IsNull([@field:DateClosed], 0) != 0 THEN 'Closed' ELSE 'Error' END GOOD: If I add an admission record with no date, I successfully get a status of Pending as expected, and record is successfully written to audit log. GOOD: If I add an admission record with an opening date but not closing date, I successfully get a status of Open as expected, and record is successfully written to audit log. GOOD: If I add an admission record with an opening date and a closing date, I successfully get a status of Closed as expected, and record is successfully written to audit log. GOOD: If I add an admission record with a closing date but not opening date, I successfully get a status of Error as expected, and record is successfully written to audit log. ERROR: If I add an admission record with a status override value, I get ERROR: Record cannot be saved because an error was encountered in Triggered Actions. Can anyone tell me what I'm doing wrong? :0) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ TEST RECORDS: DATA TABLES DESIGN: TRIGGER:
  3. @kristina The 12 records to be inserted are all different and I have them in a lookup table. The Client will be managing them and we don't want to have to modify the backend everytime they change a checklist item. Your method worked great on a test table, with modification to pull the values from the lookup table. You're awesome! So that's one thing resolved. :0) But it seems I misinterpreted the error message that I was getting. Looking at it with fresh eyes this morning, along with the fact that I'm still getting the error when I move from my test tables to my real tables, I'm thinking "exceeds limit of 2 cascading triggers" means I can't do: (1) Insert into Clients triggers insert into audit and Admissions (2) Insert into Admission triggers insert into audit and AdmissionChecklistItems (3) Insert into AdmissionChecklistItems triggers insert into audit So I'll have to have the user do two steps, which is fine as that's what they're doing now I was just hoping to automate the second step. Still, it's a shame CASPIO limits cascading triggers to 2. SQL Server allows 30+ and Oracle allows 60+. 2 is extremely limiting in a relational database, IMO.
  4. On Insert into a Client table I need to (a) insert a record into an audit log and (b) add 1 child record to an Admissions table. Both parts (a) and (b) are working fine. On the insert to the Admissions table I need to (a) insert a record into an audit log and (b) add 12 child records into an AdmissionChecklistItems table. Part (a) is working fine, but for part (b) I'm limited to ONE (1) child record due to the limitations on "cascading triggers". It all worked fine with the audit log insert and a test with 1 checklist item insert. Then I went whole hog and added the other 11 items and {bam!} error message. It was all for naught, I already had reached the limit (sure wish it wouldn't let us add more than it will let us run!). On the insert to the AdmissionChecklistItems table I need to insert a record into an audit log. This is working fine. ~~~~~~~~~~~ I do have the checklist items in a table. Effectively I need the second INSERT of the trigger to be ... INSERT into AdmissionChecklistItems SELECT ([RID from #inserted], SelectValue FROM Lists WHERE Category = 'AdmissionChecklistItems') ... which could insert all 12 checklist items in one insert statement? I know this can be done in Transact-SQL but I'm feeling hampered by the Caspio structure. IS there a way I can do this in Caspio? If not what's the suggested workaround to get this done in an automated manner?
  5. I'm migrating an application from ACCESS to CASPIO. In ACCESS the formatting being used for record ID could potentially result in duplicate ID values, though it is currently unique. This record ID is used to link the multiple child tables to the parent Client table so we're moving to have a unique ID created for each table via auto-number. After I import the data for each table, I have to go back and link child tables by inserting the NEW auto-numbered Client Record ID into the child tables. I will do this via TASKs that I will run on demand after import of the Client table data and each applicable child table data. However, I'm having difficulty crafting the TASK to update the child tables properly. It successfully matches some child records and writes the correct new Client Record ID to the child table, but ignores other child records that have matches. So, instead of saying all the things I've tried, etc., etc., let me just ask: what's the best way to write the TASK to do the equivalent of this: UPDATE mn SET mn.ClientRID = mc.RID FROM [Notes] mn INNER JOIN Clients mc ON mn.OLD_Record_ID_for_Migration = mc.OLD_Record_ID_for_Migration EDIT: {doh!} As it turns out, the users were allowed to DELETE Client records, so not all child records have parent Client records anymore. {thunks self on head} So, if anyone's curious, this is what I ended with as my task after adding a count field to my export and wanting to only update if (a) not already updated and (b) has a matching record:
  6. This example is exactly what I was needing today. So glad to find it! It would be great, fabulous, wonderful if this example could be added to the referenced How To page for Tasks as, in my opinion at least, time-based pruning of records is as common a need as sending automated emails. :0)
  7. Ahhh, thanks! I didn't even pay attention to that page I quoted being for reports and not tables. Off I go to add Age to a report instead. Thanks!
  8. Hello, I've taken the age calculation from https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/ and am trying to put it into the formula for a field in a table. Datediff(year,[@field:DOB],GetUTCDate()) My field name used in the calculation is DOB just like in the example. The field is a date/time data type. What might I be doing wrong? Thanks!
×
×
  • Create New...