Search the Community
Showing results for tags 'formula'.
Found 20 results
-
Hello, I am trying to do a function to remove and replace some special characters from a text field using formula DataType, However, how can I remove the apostrophes (') Here is the formula (Last one with apostrophe) CASE WHEN Charindex('!',[@field:TITLE],0) <> 0 THEN Replace([@field:TITLE], '!', '') WHEN Charindex('#',[@field:TITLE],0) <> 0 THEN Replace([@field:TITLE], '#', '') WHEN Charindex(':',[@field:TITLE],0) <> 0 THEN Replace([@field:TITLE], ':', '') WHEN Charindex(''',[@field:TITLE],0) <> 0 THEN Replace([@field:TITLE], ''', '') END
-
I have a formula field which calculates a number value perfectly but I'd like to add a letter to the front of the number so I assume that means I need to cast my result to a string. Here is my formula right now: SELECT MAX(Clean_Job) FROM tbl_ggl_job WHERE Job_Letter = 'CC') + 1 Which gets the max job number that is currently in use and adds 1 to that. So if the max job is CC100 this would return 101. I'd like to concatenate this with the Job Letter CC so that the result would look like CC101. How can I do this?
-
This is the formula: Datediff(year, [@field:DOB], [@field:Initial_Intake_Dt]) Something broke in my table and I had to export and import back and now trying to set up all my fields again. Can someone tell me why a perfectly valid formula that was used in the previous table would suddenly be invalid? Little bit frustrated with this... Thank you! Amanda -
I am attempting to write a formula to LookUp a value in another table. This is what I have but it did not work. LookUp(tblUnit, UnitRent, UnitID=[@field:XBSUnitID]) tblUnit: The name of the table to query. UnitRent: The field containing the rent value to retrieve. UnitID=[@field:XBSUnitID]: The condition for the lookup, where UnitID in tblUnit must match the value in the field named XBSUnitID.writing
-
I understand we are not able to use the date formula for day of the week directly in a table field. I have managed to create a calculated field in a DataPage that uses a day of the week formula (see below for the formula used) which is working nicely and gives me the desired result on the DataPage. However, this is good for display only and I cannot use the result other than viewing it on the DataPage. Is there a way to pass the result from my DataPage calculated field back to a table (same table that the DataPage is connected to)? Or, is there anyway to get a working formula directly on the table that provides a number 1 through 7 for day of the week for a given date? The formula I am currently using on the DataPage and works well is: DatePart(dw,[@field:XXXXX])
-
I have a table with a field named Total_Time with a function datatype, that calculates total time using the flowing formula.. Round(((([@field:End_Hour] - [@field:Start_Hour]) * 60 + ([@field:End_Min]-[@field:Start_Min])) - (([@field:Break_Fin_Hour] - [@field:Break_Start_Hour]) * 60 + ([@field:Break_Fin_Min]-[@field:Break_Start_Min]))) /60 ,2) This works as expected… Now I would like to add a conditional statement that checks the value of the field "Assigned_Tech" and if the value is “Summary” then leave the Total_Time field blank, else run the formula. Im thinking.. CASE IF ([@field: Assigned_Tech])!==”Summary” THEN Round(((([@field:End_Hour] - [@field:Start_Hour]) * 60 + ([@field:End_Min]-[@field:Start_Min])) - (([@field:Break_Fin_Hour] - [@field:Break_Start_Hour]) * 60 + ([@field:Break_Fin_Min]-[@field:Break_Start_Min]))) /60 ,2) END Can someone please help me with the correct case statement to use. I spent 2 days writing the total_time formula and now am in a bit over my head using conditional statements in the formula. Thanks in advance for any an all help
-
Just wanted to share a workflow on how to create a an editable formula field. This will allow you to calculate the value for a field based on other fields (Formula) or manually input the desired value. 1. Create a virtual field that will contain your formula. 2. Add a header and footer in your DataPage and disable the HTML editor on the footer. 3. In the footer put the following code: <script> document.querySelector("input[id*='VIRTUAL_FIELD_ID']").onchange = function() {myFunction()}; function myFunction() { var virtual_val = document.querySelector("input[id*='VIRTUAL_FIELD_ID']").value; document.querySelector("input[id*='FIELD_ID']").value = virtual_val; } </script> note: The field that is going to be used for the "field_id" will be the actual field that will store the calculation. -
Hello All, I am stumped. I have the following case statement in a formula field and it verifys. CASE WHEN [@field:SMNType] = 1 THEN 'l1q1.html?XBSPropertyID=' + [@field:SMNPropertyID] + '&;VRRYear' + [@field:SMNYear] WHEN [@field:SMNType] = 2 THEN 'l1q2.html?XBSPropertyID=' + [@field:SMNPropertyID] + '&;VRRYear' + [@field:SMNYear] WHEN [@field:SMNType] = 3 THEN 'l1q3.html?XBSPropertyID=' + [@field:SMNPropertyID] + '&;VRRYear' + [@field:SMNYear] WHEN [@field:SMNType] = 4 THEN 'l1q4.html?XBSPropertyID=' + [@field:SMNPropertyID] + '&;VRRYear' + [@field:SMNYear] WHEN [@field:SMNType] = 8 THEN 'b1.html' ELSE '' END However, when I try to save the table I get Unable to save table due tto incompatible values in one or more formula fields.
-
How to separate a value in a semicolon-delimited value.
Wikiwi posted a question in Calculations and aggregations
Just wanted to share this workflow that I used to get a specific data from a field that uses ';' to separate inputs. Ex input: Manager;Donald Duck;Donald@gmail.com The formula below will show the 2nd input (Donald Duck) as an output if it detects at least 2 ';' are inside the string and if it only detect 1 or less ';' it will output a blank field. CASE WHEN Charindex(';',[@field:Original_value], 0) > 0 AND Charindex(';',[@field:Original_value], (Charindex(';',[@field:Original_value])+1)) > 0 THEN Substring([@field:Original_value], (Charindex(';',[@field:Original_value], 0)+1), (Charindex(';',[@field:Original_value], (Charindex(';',[@field:Original_value])+1))-Charindex(';',[@field:Original_value], 0)-1)) WHEN Charindex(';',[@field:Original_value], 0) = 0 THEN '' END table snippet: Just a note: You can edit this formula to check for other delimiters not just ';'
-
How to create a Formula based on another formula
Woolf posted a question in Calculations and aggregations
I need to be able to calculate a date, created by a formula, which is prior to whatever the current date is. That sounds easy but I cannot figure out how to do it in my specific situation. Because I needed to determine a date that was 2 weeks after a date entered based on two different scenarios I created this formula in my data table: CASE WHEN [@field:Brand_Received] = 'Brand 1' THEN Dateadd(day, 14, [@field:Date_1]) WHEN [@field:Brand_Received] = 'Brand 2' THEN Dateadd(day, 14, [@field:Date_2]) END It works well to give me the date that is two weeks after the specific date given, based on unique requirements. However, now I need to create a field that checks to see if the new Formula Date is before whatever the current date is. I have not been able to get a result for this. Caspio does not allow me to use results from one formula field as variable in another formula field. And I have not been able to figure out just yet how to combine all the variables I need to produce a valid formula. Any thoughts? -
Hello Community, I´m new to Caspio and my try, to use a calculated field in a table ends with an error (see Screenhot here: http://prntscr.com/cxgbw8) in the formula: Invalid formular: Incorrect syntax near PERSISTED I´ve used this formular DateDiff(year, [@field:P_DOB], GetUTCDate() as it is shown in the help section (http://howto.caspio.com/faq/reports-datapages/calculated-fields-and-datediff-function/). Any ideas about my wrong usage? Thanks for your comments! -
How to SUBSTRING everything before the @ in an email address
Woolf posted a question in General Questions
How would I grab all the text in an email address BEFORE the @ sign? I am trying to create a login address that matches our other systems. But I cannot figure out how to get the SUBSTRING of just the front of the email address. For example: If the email address were MMouse@business.com, then I would want to create a USER_NAME variable that is just MMouse. Any suggestions? -
formula Multiple Consequential Formula Functions
Esraaa posted a question in Calculations and aggregations
How to perform multiple consequential functions using Formula DataType? I want to replace multiple characters from a TextField (TITLE) using Formula DataType functions. Let’s Say TITLE = DESK!TO$P AND desired outcome = DESKTOP Replace([@field:TITLE], '!', '') AND Replace([@field:TITLE], '$', '') INVALID THE "CASE, WHEN THEN" STATEMENT PERFORM THE FIRST TRUE STATEMENT ONLY -
I've created a few formulas and now require a field that uses some of the existing formula values in a calculation. Is there a way I can create a new formula similar to the following: [@field:Formula1] / [@field:Formula2] Reference: http://howto.caspio.com/tables-and-views/data-types/formula-fields/ "Other Formula Fields Not Compatible" Hoping for a way around this.... Thanks
-
Remove string value from text string in formula
DesiLogi posted a question in Calculations and aggregations
Hello, Does anyone know, in a formula field in a table, how to remove the value of a text field from another text field? There are 3 fields involved: Field1: text255 that contains comma delimited values. Field2: text255 that contains a value that may or may not be included in Field1 string value Field3: formula: removes field2 value from field1 if that value is in field1 (and does nothing if not). For example: Field 1 (text255) = 524_789B, 524_788B, 524_794B, 524_791B, 524_1047B Field 2 (text255) = 524_794B, Field 3 (formula) = 524_789B, 524_788B, 524_791B, 524_1047B What would be the formula for Filed 3 to do this? Many thanks! -
Display aggregate of total time as HH:MM
guardmetrics posted a question in Calculations and aggregations
I have a table which includes a timestamp and a type field (among others) I've created triggers and a view that combines this table into itself and displays a clock-in time, clock-out time, and a total time difference between them in HH:MM format. The formula in the calculated field is below: CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]), 0), 114) My issue is that I need to have a total/aggregate for this field, but it gives an invalid error if I just do sum, and I've been unable to figure out how to get it to add the time and display it in the HH:MM format. Any help would be great!
-
Hi, Didn't exactly know where to put this one, so sorry if this is in the wrong place. I'm new to Caspio so perhaps this is a noob question, but please help me out and thanks for reading anyway I've got a table which has a couple of date fields for the weekdays. A workday starts on monday and the user can select which mondayDate that is . I would like to add the date of monday + 1 day to the field TuesdayDate and so on for the other days. So if a user selects 01 januari 2017 as date for monday, the field TuesdayDate = 2 januari 2017 etc. I thought i could do that via a tabel/formala field where TuesdayDate = DATEADD (dd , 1, [@field:MondayDate] ) but when i use this the formula field doesn't show up any more in my Submission DataPage. So perhaps via a triggered action, where i used an insert and update action when the field MondayDate is not empty then fill the TuesdayDate as the value of Monday + 1 day. But i'm doiing something wrong cause when i validate no problem but when i try to enable this i get an error concerning nesting level 1. Do you have any idea how to approach this ? I added a screenshot of the triggered action Thanks Bart
-
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.
-
extract value from a text in table data sheet
Master posted a question in Tables, Views and Relationships
I have a table in which there is a field that a part of the value in this field needs to be extracted and saved in another column. How can I achieve this? The value always starts with: CG: and it can be in the middle where it ends with "," or the last value For instance: GG:American Value Entered, CG:Startup, MG:Advanced or GG:American Value Entered, CG:Startup -
Hello, I'm having difficulty with my time formula for a specific scenario. It is working correctly but producing too much time in some instances. I'm using a calculated field with the following formula to find the total time worked each day: cast (Datediff(hour, [@field:Start_TIme], [@field:End_Time])%24 as varchar)+' hours,'+ cast (Datediff(minute, [@field:Start_Time], [@field:End_Time])%60 as varchar)+ ' minutes' The formula is working when: The time calculated is more than an hour (i.e. 2:00 PM to 4:45 PM) = 2hrs, 45 minutes (CORRECT RESULT) or The time calculated is less than an hour but within the same hour of the day (i.e. 2:00 PM to 2:23 PM) = 0hrs, 23 minutes (CORRECT RESULT) The formula is NOT working when: The time calculated is less than an hour across DIFFERENT hours (i.e. 2:50PM - 3:05PM) = 1hrs, 15 minutes (INCORRECT RESULT) The hour should be '0' as the total time worked is '15' minutes only. I'm not sure if I can build a condition into the already existing formula(s) or if a new formula(s) is required. The standard datediff function also produces the same result. I've tried other code located online but have not been successful at removing the hour that appears in error. I've searched the CASPIO forum but was not able to locate a like issue. Any help or suggestions would be much appreciated. Thank you!
