Creating a xml string - trouble with quotation marks and ToDate() function

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered

Hello, I am trying to create a string that will look like this:

<session id="3435" url=”http://something.something.com/WC2680” date="MAY-28-2013, 1:00 pm - 2:00 pm ET" />

However, I seem to have trouble with placing quotation marks around the [LMS Session ID] field and also with the ToDate() function.

======== without the ToDate Function =========

"<"&"session id="&[LMS Session ID]&" "&
"url=http://cihieducation.adobeconnect.com...; "" &(Left([Session Code],6))&" "&
"date="&[Confirmed / Actual Launch Date] &
", "&(ToTimeOfDay([LF StartTime]))& " - "&
(ToTimeOfDay([LF EndTime]))&" "&"/>"


======== with the ToDate Function ==================

"<"&"session id="&[LMS Session ID]&" "&
"url=http://cihieducation.adobeconnect.com...; "" &(Left([Session Code],6))&" "&
"date="&(ToDate([Confirmed / Actual Launch Date])) &
", "&(ToTimeOfDay([LF StartTime]))& " - "&
(ToTimeOfDay([LF EndTime]))&" "&"/>"

======= With Quotes around the [LMS Session ID] field====================

When I put quotes around the [LMS Session ID] it comes out like this:
http://<session id=&[LMS Session ID]& url=http://something.something.com/WC2372 date=03-21-2013, 1:00 pm - 2:00 pm />

"<"&"session id="&"&[LMS Session ID]&"&" "&
"url=http://cihieducation.adobeconnect.com...; "" &(Left([Session Code],6))&" "&
"date="&[Confirmed / Actual Launch Date] &
", "&(ToTimeOfDay([LF StartTime]))& " - "&
(ToTimeOfDay([LF EndTime]))&" "&"/>"

=============================================================================================
What field type shoud I use for this? When I use a URL formula it shows with http at the beginning

http://<session id=3238 url=http://something.something.com/WC2372 date=03-21-2013, 1:00 pm - 2:00 pm />


Thanks in advance for your help

Photo of Miriam

Miriam

  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
You have to escape double quote characters using \" within a double quoted string to get them to show up inside. Like this where the two instances of \" have been placed in bold text:

"session id=\"" & [LMS Session ID] & "\" " &
Photo of Miriam

Miriam

  • 0 Points
Thanks so much for this tidbit. That worked perfectly.

Now I just need to deal with the ToDate function any suggestions on that?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
The title of your posts suggests you are trying to create an "XML string" so taken literally I would assume you need a text formula field. However, I am not sure what you user would do if you displayed a nicely formatted "XML string" other than stare at it. After all you can't click on it and have it do anything. All you might do is copy and paste it into another application. What I suspect is that you really want to create a button that when clicked on will POST the "XML string" to some adobeconnect.com endpoint to invoke a service of some sort. If in fact that is what you want to do, you have to use script to accomplish it (it is easy to do) and quite frankly a script will make the problem with your escaping your formulas properly disappear as all you will have to do is pass some fields to JavaScript which can assemble the correct "XML string" to send as the POST body to adobeconnect.com. Am I correct in this?
Photo of Miriam

Miriam

  • 0 Points
No sorry - it is a misleading title. Its an internal field name.
The string is being pulled into a script by another application to create an xml link. I just need to format it in that specific way. Everythign is done except the data format instead of displaying 03-21-2013 I would like to display MAR-21-2013
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
>The string is being pulled into a script

Okay so this field is part of a script solution - that makes sense then.

You will probably have use a case statement to map the month into the appropriate three letter
abbreviation. Also your formula is complicated enough that I would use formula variables. Here is a quick formula variable named mydate:

var Text mydate = Case([Confirmed / Actual Launch Date], 1, "JAN", 2, "FEB", ... 12, "DEC", null ) & "-" & Day([Confirmed / Actual Launch Date]) & "-" & Year([Confirmed / Actual Launch Date]);
You can read about formula variables here:

http://www.quickbase.com/help/default.html#formula_variables.html

You insert formula variables into your final result using the notation $mydate as described above. You would also need to do the same escaping around the double quotes using the \" technique.