Search the Community

I am posting this so it can help someone else if it comes up. I'm using the SOAP protocol in Excel 2003 (VBA) and I want to update a record. The field DataType I'm updating is the Text(64,000) (64, character text box). The basic Visual Basic code looks like: ---------- dim ValueList as String ValueList = "'This is the text that will be inserted into the specified field in FieldList.'" lAffected = aobjWS.UpdateData(strAccountID, strProfileName, strPassword, ObjectName, IsView, FieldList, ValueList, Criteria) ---------- ValueList is a string, and it must be inside double-quotes for VB sake to identify what ValueList will be, and the single quotes or apostrophes are the delimiters that Caspio uses to identify the string that will go into a field. The problem occurs if you want the following to be put in a field: ValueList = "'I can't put this sentence in!'" The middle single quote/apostrophe (') in the word CAN'T is being treated as a delimiter or something such that Caspio doesn't know how to put the data in the field. The solution is to use two single quotes/apostrophes wherever one would ordinarily be: ValueList = "'It''s now possible to put this sentence in correctly!'" This puts the following into the desired database field: It's now possible to put this sentence in correctly! In VB, here is the code I ultimately used to replace all single quotes with two single quotes: (you may need to paste into Word or VBA or something to see the double vs. single quotes). Also worth noting, Before using the "Replace" function I chose to remove the first and last single quotes since they need to remain as single quotes. I put them back on in the final line of code: ---------------- dim NewString as String NewString = "'I'd love contractions, but I don't always use 'em properly'" 'the next two lines take out the first and last single quotes NewString = Right(NewString, Len(NewString) - 1) NewString= Left(NewString, Len(NewString) - 1) ValueList = "'" & Replace(NewString, "'", "''") & "'" ---------------- Now "ValueList" is ready to be used in: lAffected = aobjWS.UpdateData(strAccountID, strProfileName, strPassword, ObjectName, IsView, FieldList, ValueList, Criteria) Caspio reference: http://static.caspio.com/pdfs/Caspio-Bridge-Web-Service-API.pdf%C2'> Happy coding!

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?

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!

Hello! This might be an unusual request, but I am looking for a way to create and stamp a new submission ID on multiple records each time a bulk edit is submitted for a particular DataPage. The ID needs to be unique to other IDs, but it will be stamped on multiple records in the table at the point of submission. My thought was to create a hidden field with a default value, which would be a combination of a prefix (i.e., WO-) and then a system parameter (i.e., [@cbTimestamp]). The problem I'm running into is that I would like the date to be broken down into a numeric string instead of having spaces and punctuation, such as dashes and colons. This is what I'm currently getting: WO-08/30/2017 11:34:46. I would like to get this instead: WO-1504110886. Any ideas on this? I very well may be overthinking things, so I welcome suggestions. We don't have to use the timestamp at all, but it was just an idea for how to create a unique ID at the point of submission.