9 Replies Latest reply on Apr 25, 2014 12:51 PM by philmodjunk

    Calculation troubles

    SkipGilleland

      Title

      Calculation troubles

      Post

           I am trying to parse a URL out of some content on one field to have it entered into another field.  The URLs are for videos..some are hosted on you tube and some are hosted on Wistia.  I would like to use the following...start from [src="] and end at ["]

      Example of calculation I am trying to use:

           Middle ( 
           post_content ;
           Position ( post_content ; "src" ; 1 ; 1 ) ; 
           Position ( post_content ;  ' " '  ; 1 ; 1 )-Position ( post_content ; "src" ; 1 ; 1 )
           )
            
           I am getting an error stating "The specified field cannot be found." and it is highlighting the ' " "
            
      Example of content I am trying to parse from: (example from youtube and wistia)

                <iframe src="http://www.youtube.com/embed/_EXwruyLHIs" height="360" width="640" frameborder="0"></iframe>

                <iframe name="wistia_embed" src="//fast.wistia.net/embed/iframe/h9lu36abmu" height="360" width="640" allowfullscreen="" frameborder="0" scrolling="no"></iframe>

            

        • 1. Re: Calculation troubles
          philmodjunk

               Position ( post_content ;  ' " '  ; 1 ; 1 )

               Has incorrect syntax and the double quotes in the middle of the single quotes is being treated as the start of a new quoted string with the next double quote then interpreted as the end of a quoted string instead of the beginning of a quoted string.

               Trying to find the position of the double quote from a starting position of 1 won't work right anyway as that will fin the position of the first double quote character instead of the second double quote after "src".

               Try this:

               Let ( [ start = Position ( post_content ; "src" ; 1 ; 1 ) ;
                          end = Position ( post_content ; "\"" ; start ; 2 )
                        ] ;
                        If ( Start and End ; Middle ( post_content ; start ; end - start + 1 ) ; "parsing error" )
                       )

               This will extract:

               src="http://www.youtube.com/embed/_EXwruyLHIs"

               and

               src="//fast.wistia.net/embed/iframe/h9lu36abmu"

               from your two examples. (tested this in FileMaker Advanced's Data Viewer.)

          • 2. Re: Calculation troubles
            SkipGilleland

                 Thank you!

            I am now getting this in the field I used the calculation in:

                 src="http://fast.wistia.net/embed/iframe/w0n2d6mrqn?controlsVisibleOnLoad=true&version=v1&videoHeight=450&videoWidth=600&volumeControl=true"

            I need it to be:

                 http://fast.wistia.net/embed/iframe/w0n2d6mrqn

            Below is the content it is pulling from...I thought the video urls always would end with a quote, but apparently some end with a ?.  Is there a way to do either or?  Also, I need to have the quotes and src= removed as I am using this field in a webviewer.  (sorry, I forgot about that part in my post)

                 <iframe name="wistia_embed" src="http://fast.wistia.net/embed/iframe/w0n2d6mrqn?controlsVisibleOnLoad=true&version=v1&videoHeight=450&videoWidth=600&volumeControl=true" height="450" width="600" frameborder="0" scrolling="no"></iframe>

            • 3. Re: Calculation troubles
              philmodjunk

                   Your source data is missing a double quote to mark the end of the text that you want to parse from this field. This would appear to be an error in the source data, not the calculation.

                   <iframe name="wistia_embed" src="http://fast.wistia.net/embed/iframe/w0n2d6mrqn?controlsVisibleOnLoad=true&version=v1&videoHeight=450&amp;videoWidth=600&amp;volumeControl=true" height="450" width="600" frameborder="0" scrolling="no"></iframe>

                   Text in red appears incorrect. Looks like that text should be: video" Height

              • 4. Re: Calculation troubles
                SkipGilleland

                     But what I need is a url that is only this....

                http://fast.wistia.net/embed/iframe/w0n2d6mrqn

                      

                     Some of the video embeds end in a " and some end in a ?.

                • 5. Re: Calculation troubles
                  philmodjunk

                       Are you sure that the result shouldn't be

                       src="http://fast.wistia.net/embed/iframe/w0n2d6mrqn?controlsVisibleOnLoad=true&version=v1&videoHeight=450&videoWidth=600&volumeControl=true"

                       ?

                       I'm not an expert on URL encoding, but starting a string with a " and not including a closing quote looks like an error to me.

                       That said, your calculation can be adjusted to handle either a question mark or a " character as the terminating character of the substring you are trying to extract.

                       Let ( [ start = Position ( post_content ; "src" ; 1 ; 1 ) ;
                                  end1 = Position ( post_content ; "\"" ; start ; 2 );
                                  end2 = Position ( post_content ; "?" ; start ; 1 );
                                  end = Case ( end1 and end2 ; min ( end1 ; end2 ) ; //select leftmost position if both characters exist to the right of start
                                                           end1; end1;
                                                           end2 );
                                ss = If ( Start and End ; Middle ( post_content ; start ; end - start + 1 ) ; "parsing error" )
                                ] ;
                                If ( Right ( ss ; Length ( ss ) ) = "?" ; Left ( ss ; Length ( ss ) - 1 ) ; ss )
                               )

                        

                  • 6. Re: Calculation troubles
                    SkipGilleland

                         So close...the issue is I can't have the src=" or the ending ".  I need only the url returned.... http://fast.wistia.net/embed/iframe/w0n2d6mrqn with no quotes or the src=

                         Thanks for all your help on this!

                    • 7. Re: Calculation troubles
                      philmodjunk

                           Which is not something that you specified in your original request.

                           Let ( [ start = Position ( post_content ; "src" ; 1 ; 1 ) + 5 ;
                                      end1 = Position ( post_content ; "\"" ; start ; 2 );
                                      end2 = Position ( post_content ; "?" ; start ; 1 );
                                      end = Case ( end1 and end2 ; min ( end1 ; end2 ) ; //select leftmost position if both characters exist to the right of start
                                                               end1; end1;
                                                               end2 );
                                    ss = If ( Start and End ; Middle ( post_content ; start ; end - start + 1 ) ; "parsing error" )
                                    ] ;
                                    Left ( ss ; Length ( ss ) - 1 )
                                   )

                      • 8. Re: Calculation troubles
                        SkipGilleland

                             Sorry, I know I forgot to mention that. :-(  Ok, I am REALLY close.....now the urls are looking like this 

                             http://www.youtube.com/embed/HFO3JFaXqQk" height=

                             I need to remove the " height= on all of them.

                              

                             Again, thank you for all your help!

                        • 9. Re: Calculation troubles
                          philmodjunk

                               That last one had an error in it. Fixed one issue and caused a new one.

                               Let ( [start = Position ( post_content ; "src" ; 1 ; 1 ) ;
                                          end1 = Position ( post_content ; "\"" ; start ; 2 );
                                          end2 = Position ( post_content ; "?" ; start ; 1 );
                                          end = Case ( end1 and end2 ; min ( end1 ; end2 ) ; //select leftmost position if both characters exist to the right of start
                                                                   end1; end1;
                                                                   end2 );
                                        ss = If ( Start and End ; Middle ( post_content ; start +5 ; end - start - 4 ) ; "parsing error" )
                                        ] ;
                                        Left ( ss ; Length ( ss ) - 1 )
                                       )