Jump to content


  • Content count

  • Joined

  • Last visited

About vanderLeest

  • Rank
  • Birthday March 4
  1. I am trying to make a simple "Submit a Request" form, which will have fields like first name, Last name, email address, phone number, request, etc. Field names are like C_FirstName, C_Surname, C_Email, C_Phone, C_Request I want to validate text entries in the following fields as follows: First / Last name: No numerical characters are allowed Email Address : authenticate email according to https://www.w3resource.com/javascript/form/email-validation.php mobile no.: formatting and checking as per So far I have the phone script working, but my adaptation of the email script (or the combination of the two in the footer of the form) doesn't check the entry in the Email Address field. Any help sincerely appreciated Floris W3's ValidateEmail Script below: function ValidateEmail(mail) { if (/^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$/.test(myForm.emailAddr.value)) { return (true) } alert("You have entered an invalid email address!") return (false) } document.getElementById("caspioform").onsubmit=validateEmail;
  2. I am trying to add via an Insert trigger a second row of data, based on the first row of data which is inserted by a trigger associated with another table. The Awards table has an start date and end date of the project, and on insert creates the first milestone in a Milestone table, based on the entered start and end dates for the Award. On insert of the first milestone, I would like to add (if at all possible) a second milestone if the remaining period for the period is bigger than 12 months (which is calculated in a formula field in the first record). I get a "Triggered Action cannot be enabled because it exceeds maximum nested level of 1" Error message. Any help appreciated
  3. Thanks for your quick answer, MayMusic I sadly could not implement your suggestion as I need to calculate the End Date of Financial Year on a submission form, and a Caspio submission form does not have a calculated field option. I cobbled together (after much trial and error) a bit of javascript and added it to the Footer of the form: <SCRIPT> function setEndDateValue() { var today = new Date(); var entered = document.getElementById("InsertRecordStartDate").value; if (entered.length > 0) { today = new Date(Date.parse(entered)); var mm = today.getMonth()+1; var yyyy = today.getFullYear(); } if (mm > 6) { var yyyy = today.getFullYear()+1; } else { var yyyy = today.getFullYear(); } document.getElementById("InsertRecordMonths").value = mm; document.getElementById("InsertRecordFinDate").value = '06' + '/' + '30' + '/' + yyyy; } document.getElementById("caspioform").onsubmit=setEndDateValue; </SCRIPT>
  4. I want to insert a the next 30th of June as a field value in a online form, based on the entered start date of a contract e.g. is the start date is earlier than 30 June of the current year, the value I need is 30 June this year, if the start date falls in July or later, is should be 30 June next year. (I need this date to do further calculations for milestone periods and budgets) I have tried to modify a few scripts dealing with related issues, but I cannot seem to make it work for this problem, I get either the wrong date format or no calculation/result at all Any help sincerely appreciated.
  5. Hi lmooring What was the solution you came up with?
  6. What I hope to do, is autofill a table based on submitted start and end date of a project. The routine to be developed should create a record in the Milestone table for each 6 months period the projects lasts. i, mx, dur as integer dur = Round((DateDiff("m",[SA_StartDt],[SA_EndDt])+1)/6+0.3,0) = number of records to be created For i = 0 to dur mx = i * 6 Proj_ID // Period_No // Period_StartDate // Period_EndDate // Budget [Proj_Recno] // i + 1 // DateAdd(month, mx, [SA_StartDt]) // DateAdd(month, mx+6, [SA_StartDt]) // [SA-Awarded]/dur) Can I set this up as a trigger action on the table? or do I need to write a bit of Javascript and add this to a button? In both cases, I would appreciate some pointers. Floris
  7. T_EA_Entities stores for each Activity the Entities involved and a Contact Person for each Entity. Some Entities will have more than one Contact Person, and they will be entered as a separate record. The unique composite key for the T_EA_Entities table would be a combination of Activity_ID, Entity_ID and Contact Person. Data is entered in T_EA_Entities via a child form on the Activity Data Page. To run a combined chart and report that shows which Entities are involved with how many Activities (and links to details of the each Activity), I need a table for which the combination of Activity_ID and Entity_ID are unique, hence the name T_EA_Entities_Unique. On any record changes in T_EA_Entities, I want to update T_EA_Entities_Unique via a Trigger Action. To make this possible T_EA_Entities needs an Autonumber RecordNo field and a Formula Concat field combining Activity_ID and Entity_ID in a string, e.g. Right('000'+Str([@field:EAE_ID],Len([@field:EAE_ID])),6)+Right('00000'+Str([@field:Entity_ID],Len([@field:Entity_ID])),6) The table T_EA_Entities_Unique needs the RecordNo as a Number Field and the same Formula Concat field combining Activity_ID and Entity_ID. The following Trigger Action will add, delete and update the Activity Entity records in T_EA_Entities_Unique, when the records in T_EA_Entities are changed.
  8. I have changed the Trigger Action, and I do get the unique values for Activity_ID and Entity_ID I want, but sadly for the old data, not the (to be) inserted values for the Activity_ID. Any help sincerely appreciated.
  9. Thanks so much for your quick reply with a nice example. I have tried to implement it, first by selecting top 1 based on two fields, Activity_ID and Entity_ID, however that did not work. Next I created a formula field in the tables T_Entities and T_Entities_Unique, which would result in the same concatenated ID in both tables for Activity_ID and Entity_ID combined, concatenated ID, which can have multiple values in T_Entities, but only a unique value in T_Entities_Unique. Sadly, the following Trigger Action does not result in inserting unique values in the T_Entities_Unique table, i.e. no values are added when updating an Entity in T_Entities. Any assistance will be sincerely appreciated as this logic (when working) will be appropriate for many use cases.
  10. I aim to add unique values in a support table on which I hope to build a graphical report On Insert into the T_EA_Entities table (which is child table linked to the main Activities table, and data is entered via a child form on the Activity Data Page) I want to insert unique Entity IDs associated with the Activity stored in a separate table called T_EA_Entities_Unique. NB: T-EA_Entities stores nect to Entities also sometimes multiple contact persons for an entity (= multiple records) I have attached the Trigger Action. This Action only adds one Entity ID to the T_EA_Entities_Unique table, instead of the multiple who are entered via the DataPage in the T_EA_Entities table. Does anyone have an idea how to modify the Trigger Action so it will add multiple values to the table?
  11. I want to create a view that will give me unique values for an activity with an entity, while the table stores activity, entity and sometimes multiple contact persons per entity. I want a chart and a report that shows the number of activities with each entity (and do not want those entities with multiple contact persons to count / shown multiple times). Is there a way to create so-called Group By views, or is there a way to structure a report in such way that The multiple contacts are not shown?
  12. This cumbersome syntax is a Valid Formula: DatePart(mm,[@field:EA_StartDate]) + '-' + DatePart(dd,[@field:EA_StartDate]) + '-' + DatePart(yy,[@field:EA_StartDate]) + CASE WHEN LEN([@field:EA_EndDate]) > 1 THEN ' - ' + DatePart(mm,[@field:EA_EndDate]) + '-' + DatePart(dd,[@field:EA_EndDate]) + '-' + DatePart(yy,[@field:EA_EndDate]) END NB Substituting '-' with '/' (for a more standard date display) results in an Invalid Formula
  13. Following syntax worked as script for a calculated field in a previous report: label: Time Period (Start Date) [@field:EA_StartDate] + CASE WHEN LEN([@field:EA_EndDate]) > 1 THEN ' - ' + [@field:EA_EndDate] END Currently [@field:EA_StartDate] + ' - ' already generates "Invalid Formula" when verifying the script.
  14. Automated sequence number is child form

    Thank you Mathilda, for your quick reply and detailed answer Now I see there is in principle a way to do this kind of thing. My scenario is a two child forms embedded in a main form that aims to - in one case - associate Files to an Activity and in an other case Organisations to the same activity. I want to be able to add a (hidden) sequence number to each associated file, and each associated organisation in both a submission form and in a report detail form.
  15. I wonder if someone found or came across a way to add an automated sequence number (for example, in the form of the current record count) to a child form based on a one to many relationship, for example each interested person for a specific house gets a sequence number from 1 for the first entry to n for the most recent entry. For another house the sequence start again at 1.