JKSGT

Concatenated into Text(64000) Field I’d like to dynamically concatenate several paragraphs into a single Text(64000) field using Triggers. I have a table called tab_Responses that a customer updates. In this table I update a field called Trigger_Run with Trigger_Run = ‘Update Para’ when I’m ready to create the concatenated field. I also have a field called Profession that I want to use to identify the paragraphs to combine, this stores a profession such as ‘Fire Eater’. I also have a field called Concate_Para that stores the final concatenated paragraph. Then I have a table called tab_Paragraphs where I store the template paragraphs. Paragraph Text 1 Lorem ipsum dolor sit amet, consectetur adipiscing elit. 2 Nunc faucibus sem purus. Suspendisse fermentum id magna nec pulvinar. 3 Donec auctor feugiat justo, sed convallis nunc malesuada ut. And I have a table called tab_Profession_Paragraphs where I store lists of paragraphs to appear for each profession. For example: Profession Paragraph Fire Eater 1,2, 3 Lion Tamer 1,2 Clown 1,3 Juggler 1 Result So I’d like the outcome to be something like this: - If Profession = Fire Eater then lookup tab_Paragraphs and concatenate Paragraph_01, Paragraph_02 and Paragraph_03 and store them in Concate_Para. - If Profession = Lion Tamer then lookup tab_Paragraphs and concatenate Paragraph_01 and Paragraph_02 and store them in Concate_Para. - If Profession = Clown then lookup tab_Paragraphs and concatenate Paragraph_01 and Paragraph_03 and store them in Concate_Para. I wonder if anyone has any thought for how I could set up a trigger to achieve this as the logic is escaping me at the moment? Many thanks Jay

Hi, I have changed the approach and decided to turn a text field (dropdown) into a multi-select with a bit of JS. It has solved all my issues but it is a little bit clumsy because the user needs to hold the CTRL key down to select multiple options. I'd prefer it to be just with a click. <!-- MULTI SELECT FIELD--> <script> var v_state = "[@field:Field_ID]" ; var o_state = document.getElementById("EditRecordField_IDs") ; o_state.multiple = true ; function f_listbox() { if ( v_state.indexOf(",") > 0 ) { for (var i=0 ; i < o_state.options.length; i++ ) { if(o_state[i].value == v_state) { o_state.remove(i); break ; } } var o_st = v_state.split(", ") ; for (var j=0 ; j < o_st.length; j++) { for (var i=0 ; i < o_state.options.length; i++ ) { if(o_st[j]== o_state.options[i].value){ o_state.options[i].selected = true ; break ; } } } } } document.addEventListener('DataPageReady', f_listbox); //You can also use the line below instead of the eventlistener // setTimeout(f_listbox, 20); </script>

Hi,I am trying to perform a simple calculation in Virtual2 (a calculated field) using a value selected in a multi select dropdown. However, the calculation is failing.Step 1 - I set up a multi select dropdown - List_StringStep 2 - the multi select dropdown field ID cannot be used in a CASE statement therefore I set up [@Virtual1] to capture the selection in the List_String using JavaScriptStep 3 - I then tried some CASE statements in [@Virtual2] to perform a simple calculation based on the List_String selection copied to [@Virtual1]. However, this part isn’t working. I have tried this formula: CASE WHEN '[@cbParamVirtual1]' LIKE '%'+'Car'+'%' THEN 300 END The above formula works with any other field (dropdown, radio buttons, etc) but it will not work with [@cbParamVirtual1]. I wonder if it is because of the data type (when copied from the multi select dropdown) and whether my formula needs to CAST [@cbParamVirtual1] as something that Caspio will recognise as text. Would anyone have any recommendation? Many thanks JayMany thanksJana

The solution (courtesy of Caspio support): REPLACE(REPLACE(REPLACE(STUFF((SELECT ' ' + CAST(Wording AS nvarchar(MAX)) FROM tab_Endorsements WHERE Quote_ID=target.[@field:P0002_Responses_Quote_ID] FOR XML PATH ('')), 1, 1, ''),'&lt;', '<'),'&gt;','>'),'&amp;nbsp;',' ')

Hi, I wonder if someone would have a bit of JS to help populate a multi select dropdown with values which are saved in a table as text field as text separated by commas? The page I am using is a Submission Form and I am effectively trying to copy a record. In the Submission Form I can populate all the fields with the previously saved values in the table but the multi select dropdown is defeating me. I have managed to set up a text field and save the user's previous selection in the multi select dropdown in this text field as text separated by commas. Now I need to get this text back into the multi select dropdown so that the user doesn't have to make a new selection. Many thanks Jay

Hi, Thank you for your response. I got distracted by another problem... Going back to this one, I did try a virtual field approach using the SELECT statement below: SELECT Wording FROM tab_Wording WHERE Record_ID = target.[@field:Responses_Record_ID] However Caspio only returns the first record in the Wording table not all matching records. Secondly where my wording contains html markup tags such as paragraphs </p> or line items </li> I get double line spacing for some reason. This does not happen when I display the content of the fields directly just as a html field. Which is why I was asking if it’s possible to combine the contents of the same field from multiple records in the same table into a single Text(64000) field? Either using a triggered action or a virtual field and display it on my PDF that way. Many thanks Jay

Hi, I am building a dynamic document that comprises of first page one followed by several pages of wording and a last page. The first page and last page get data from multiple fields in Table A. The wording pages get data from a multiple records in Table B, but only from a single field (ie. each record in the wording table represents a paragraph that is marked up in html for formatting). Ultimately I want my users to be able to download the complete document as a single PDF. I found the Details Data Page works well for to PDF the first and last page as I can add some calculated values and set page breaks where I want to be using the PDF option. I also found that a table data page works well for the wording as I can query the wording table and display all the paragraphs as a table. However I’m struggling to combine the two that results in a usable PDF. I tried using two data pages (Details and Table) with an iframe to embed the wording from the Table within the Details Data Page. This worked to a point but I struggled to dynamically size the iframe when the wording went over a page but more crucially the iframe split some of the sentences in half when I created the PDF as it treated the iframe content like an image rather than lines of text. So I stopped this approach. I found when I added all the paragraphs manually to a Text(64000) field I can get the wording to display correctly. So my question is - does anyone know if it’s possible to combine the contents of the same field from multiple records in the same table into a single Text(64000) field? Either using a triggered action or a virtual field? I think if I can do this then I can probably create PDF documents that include my fields plus the table content. Many thanks for your thoughts Jay

Hi, Thank you. In fact the solution that I found to be working is: CASE WHEN LEN('[@cbParamVirtual2]') > 0 THEN CONVERT(nvarchar, (DateAdd(year, 1,DateAdd(day, -1,CONVERT(DATETIME, '[@cbParamVirtual2]', 103)))), 106) ELSE ' ' END Thanks again for your thoughts. Jay

Hi, I have got an issue with [@cbParamVirtual3]. [@cbParamVirtual3] value is calculated with the below CASE statement. For clarity [@cbParamVirtual2] is a calendar pop up. The CASE statement below works fine. When a user picks a date in [@cbParamVirtual2], [@cbParamVirtual3] is calculated correctly. CASE WHEN [@cbParamVirtual2] IS NOT NULL THEN DateAdd(year, 1,DateAdd(day, -1,CONVERT(DATETIME, '[@cbParamVirtual2]', 103))) END However, the problem is when [@cbParamVirtual3] is hidden and blank. [@cbParamVirtual2] and [@cbParamVirtual3] are hidden under certain circumstances with a rule which hides the section that contains those two fields. When a user tried to move on to the next datapage, the 'Values in one or more fields are invalid' message appears. It is appearing because [@cbParamVirtual3] is required even though it is hidden and not made required by the field setting or any rule. I have tried to add an ELSE statement to the above CASE statement to get around the issue: CASE WHEN [@cbParamVirtual2] IS NOT NULL AND [@field:Price_Type]='New' THEN DateAdd(year, 1,DateAdd(day, -1,CONVERT(DATETIME, '[@cbParamVirtual2]', 103))) ELSE DateAdd(year, 1,DateAdd(day, -1,CONVERT(DATETIME, '[@cbParamVirtual2]', 103))) END I was expecting the result in [@cbParamVirtual3] to be 31 Dec 1900 but I'm not getting anything if [@field:Price_Type] does not equal 'New'. If you can see where I am going wrong with the above, I will be very grateful for any tips. Many thanks Jay

Many thanks, I'll give it a try.

Hi, I could do with some wisdom on how to calculate a date. I'm trying to set a date in a field using a CASE statement. I have Virtual Field 1 which is capturing today's date without timestamp (pls see image). I also have a number (in this case 30) stored in Virtual Field 4. I am trying to add the two together and have the result formatted without time stamp (e.g. 25 May 2021) but when I do this I get 31 Jan 1900. See my CASE statement below, the red part is the part that's not working. CASE WHEN DateDiff(day, SysUTCDateTime(), [@field:Date_1]) > [@cbParamVirtual4] THEN DateAdd(day, [@cbParamVirtual4],CONVERT(DATE, '[@cbParamVirtual1]', 103)) ELSE [@field:Date_1] END Many thanks Jay

I have a datapage which is used for 'new' records and 'amend' records. When record is 'new', the datapage asks the user to input a start date and the end date is a calculated field. There are contained in section 1. If the record is 'amend', I want to make section 1 hidden and display section 2 with virtual fields which only shows the previously input values of the start/end date. However, hiding section 1 is causing an error saying 'Values in one or more fields are invalid' when trying to submit the form. If I unhide section 1, I can submit the form. Any idea what's going on? Many thanks

Many thanks!

Hi, I think I'm ok on this front now. Thank you for responding.

@Vitalikssssss Hi, I have a few text fields in my data page and tried your code (below) but I'm not getting any results. Am I missing something? Many thanks <script> document.addEventListener('DataPageReady', assignEvent); function formatAsDollars(el) { el.value = el.value.replace(/[^\d]/g,'').replace(/(\d\d?)$/,'$1').replace(/^0+/,'').replace( /\d{1,3}(?=(\d{3})+(?!\d))/g , "$&,"); el.value = el.value ? '$' + el.value : ''; } function assignEvent(){ let fields = ["Stock", "Content"]; //specify your fields here fields.forEach(element => { element = "EditRecord" + element; // replace "InsertRecord" with "EditRecord" for Details/Single Record Update DP document.getElementById(element).onkeyup = function() { formatAsDollars(this); } document.getElementById(element).onchange= function() { formatAsDollars(this); } }); } </script>

Is it possible: 1. to grey out or not display dates before today 2. before a certain date in the past 3. after a certain date in the future in the popup calendar? If so, any pointers would be appreciated. Many thanks