Using Calculations To Do Google Searches

[OttawaBusinessContacts.com]

Filemaker calculations can be used as an Internet Scripting Language example:

"http://www.google.ca/search?q=" & "Company" & ""

This Calculation when Transmitted as a URL to Google Searches for the variable in

Company

Example: Company= Acme Widget Co

but I want to search for the phrase "Acme Widget Co" and Filemaker removes the " " or misinterprets the vatiable. Any Ideas? I tried single quotes and extra quotes neither solve the problem

[transpower]

In Version 7 we would use the Quote function. For Version 5, I seem to recall that you could use the back slash () with the ASCII code; but don't quote me.

[QuinTech]

don't quote me.

LOL

Double-quotes definitely work:

"http://www.google.ca/search?q=""" & Company & """"

Anywhere you want a quote to show in the calculated result, type a double-quote (a double-double-quote?) in the calc definition.

I've seen the back slash syntax to which transpower referred also, but i'm not sure how it's used either.

HTH,

Jerry

[Lee Smith]

I'm not clear as to what is working for you and what isn't working for you.

As Jerry pointed out, entering " quotes into a calculation is tricky. Sometimes it is best to have either a global field to use in the calculation where you have inserted a quote, and sometimes you just need to play with the number of quotes. It is usually necessary to have 4 quotes for every single quote you need.

Here is how you could approach it.

Field 1 ([color:"purple"]URL text) for the the Google site and the search key setup (i.e.):

http://www.GOOGLE.com/search?as_q=

Field 2 ([color:"purple"]Search Words) for the Search words and your quotes (i.e.):

"LaCie"

[OttawaBusinessContacts.com]

"http://www.google.ca/search?q=""" & Company & """"

I tested Jerry's "" Quote Quote Solution. He gets a half cigar. The result is in Quotes;

I wanted to send "Acme Widget Co" to google

Jerry's Solution gives "AcmeWidgetCo" it strips out Blanks

Darn it we are close!!!

[QuinTech]

OK, then... if you google "search for this phrase" and look at your address bar, you'll see:

http://www.google.com/search?hl=en&q="search+for+this+phrase"&btnG=Google+Search

So then it seems your calculation should be:

"http://www.google.ca/search?q="" & substitute ( Company , " " , "+" ) & ""&btnG=Google+Search"

J

[Lee Smith]

Here is a quick Sample of what I was trying to say

HTH

Lee

URLTestFile.fp5.zip

[OttawaBusinessContacts.com]

To Quin Tech Wow I'm Impressed! Thank you!

I end up with

"http://www.google.ca/search?q=""" & Substitute(Company , " " , "%" ) & """"

This definitely works in Filemaker 5

Perhaps I'm wrong but there are subtle differences in Google between "+" and "%"

[OttawaBusinessContacts.com]

Ok Part II If you put the above calculation in a Loop You will see a problem.

How do we capture the First page of the Google output to a notes field in Filemaker.

My 1st thought was to use the Troi Clip N Save Plug in. However it falls short because you cannot select the Internet Explorer Page automatically. Clip N Save will work if you manually select.

I tried 6 or more automated clipboard savers. None were sufficiently automated.

Filemaker will copy and paste but it will not select the Internet Explorer results page.

The idea is that the search results should be to keep results with the appropriate record. You Apple Guys are Laughing at Us Windows Guys on this one.

[Lee Smith]

Hi Barry,

If I'm understanding your problem now, it's a problem of how to move from FileMaker, to a specific page in a Browser, gather the data from the page(s), and than move back to FileMaker and insert the data into a field or fields.

IMO, this is beyond FileMaker's scripting ability (at least I have never been able to get past the Open URL). I think that most of this would have to involve external applications, such as AppleScript, Quickeys, etc., in order to do anything outside of FileMaker.

All I've ever been able to do is to use a script (much like the one I used in the sample) and open the URL. After that, I've had to physically copy the page, manually move back to FileMaker, and Paste the results into a field (you can use a script to Copy the Clipboard into the field). Anyway, that was it. One page, one Paste. Not at all as eloquent as one would hope.

BTW, this problem has been discussed a few times in the past, and I don't remember a resolution being mentioned. Duh, if there had been, I would be using it. However, you might want to do a search anyway. I would Search "[color:"blue"]All Forums" "[color:"blue"]Keywords" of [color:"blue"] +browser +web +page +FileMaker and put a "[color:"blue"]Blank" in "[color:"blue"]Weeks". and see if anything turns up.

Sorry this isn't very helpful,

Lee

[BobWeaver]

I don't think quote characters are valid in URLs, so your search string needs to have the quote characters replaced with % 22 and the spaces replaced with either + or % 20. So the seach string part of the URL would look like this:

&q=% 22Acme+Widget+Company% 22

So you could use this formula:

"http://www.google.ca/search?q=" & Substitute(Substitute, Company, """","% 22")," ","+")

<<Edit Note: I had to put a space between % and 22 in this post so that the forum code wouldn't convert it back into the actual quote characters. Please delete the extraneous space from the formula when you try it.>>

As for getting the info from the browser back into Filemaker you may be able to use a Windows scripting utility such as Winbatch. On a Mac you could use applescript of course.

[OttawaBusinessContacts.com]

Lee you describe it exactly I downloaded Quickeys & It Doesn't look promising.

This is the script that I am using Gogsearch is some variation above. Actually I found phone number to be excellent in finding info.

* Allow user Abort On

* Loop

* Open Url(NoDialog,"gogsearch")

* Pause/Resume Script("00:.05")

**************************************

This gives me 5 seconds to manually select & copy a Web Search Page

What we need is a program that we can access using DDE EXECUTE that we repeately run at this point. The program could be in C, Java or Visual Basic.

The only thing this program would do is select the Active page of IE explorer and copy it to the clipboard. For DDE Execute to work the program would have to be in the register. This is really a command line approach. I'm off to get winbatch

Does anyone know such a program, None of the clipboard programs work properly. I'll be an old man before I evaluate them all.

****************************************

* Paste(Select,"Notes")

* Goto Record/Request/page (Next)

* End Loop

Barry

[BobWeaver]

It should be possible to write a short Java program that could be called using the Send Message script step (including the Google URL in the command line). The Java program could load the web page and save it to a text file which Filemaker could then import. You wouldn't even need to run a web browser. Java has pretty good web access capabilities, but it's been a couple of years since I've done any Java programming. Since I have some spare time today, I'll have a look. If I come up with anything useful, I'll post it here.

[BobWeaver]

Okay, I wrote a java application which works. I posted the whole thing here. I was only able to test it on a Mac, but I think it should work on Windows with only minor tweaking.

[OttawaBusinessContacts.com]

I'M running into another problem on this project.

Example I want search Google using the lastname field in my database. I have about 20 versions of this google search thing now. I want to make it more general.

I created a text field called Freeform

The formula becomes

"http://www.google.ca/search?q=""" & Substitute(Freeform, " " , "%" ) & """"

w I want to figure out a way to enter any variable such as Company or Last_Name into Freeform and have it recognized as a variable

In other words if I put the variable company into freeform it would transmit

"Acme%Widgit%Co" to the Google search Line.

I know I am breaking all the rules but there must be a way to do it. I tried Quotes and Hexdecimal around Freeform but they do not work,

The fundamental question is how does filemaker differentiate string information from variable information!

[BobWeaver]

You can do the equivalent of a merge field. Define which field names you want to allow in the search field and then set up a substitute calculation that replaces the field name keyword with the field contents.

Here's an example:

Substitute(Substitute(Substitute(Substitute(

Substitute(Substitute(Substitute(SearchString,

"<firstname>",firstname),

"<lastname>",lastname),

"<company>",company),

"<streetaddress>",streetaddress),

"<city>",city),

"<state>",state),

"<country>",country)

So, if your user entered this: <company> <lastname> mousetrap

And assuming the company field contained "Acme widgets Inc."

and the lastname field contained "Smith"

it would convert to: Acme widgets Inc. Smith mousetrap

If you are using FM7, the merge formula is much simpler:

Evaluate ( """&Substitute ( SearchString ; ["<";""&"];[">";"&""] )&""" )

BTW I don't think you want to replace spaces with %; you should replace them with %20.

[OttawaBusinessContacts.com]

This thing Works!!!!!! Awesome The only changes I made was to use a global variable to enter the merge string and a second version in a loop.

Thanks I DOWNLOADED THE Java Script and it may take me a couple of days to make it work. That is because of where I am on the java learning curve. Barry

[BobWeaver]

Glad it worked. Please keep us up to date with your progress on that Java file. I think it should work okay with the Send Message script step. I'm just not sure what version of Java (if any) comes installed on WinXP. When I was learning Java a few years ago, I was on Win95 which required installation of JDK (Java Developer Kit) from the Sun Microsystems web site. It's a free download. I think all modern operating systems have this installed, but you can find out by getting an MS-DOS command line and try the following:

Type the Following:

c:> java



It will either respond with a command not found error,

or else display a list of command line options.

If you get the options displayed, that's what you want.



Next change to the directory where the Grab.class file is:



c:> cd js



Then try running Grab with a simple URL like this:



c:JS> Java Grab 1 http://www.yahoo.com



If it works, It will create a file called output.txt in the JS directory

which will contain the page source html.



If that all works, then it's simply a matter of ensuring that the

Send message script step properly assembles the correct command line.

[OttawaBusinessContacts.com]

Bob neither my WIn 98 or XP has java on it.

I went to

http://www.sun.com/download/index.jsp?cat=Java%20%26%20Technologies&tab=3&subcat=Java

And was overwhelmed by options. I'm guessing the runtime version (15 Mb) is the correct one. There are 3 or more SDK's at 100 Mb If I have problems I hope to get it at our fudgeo.ca meeting @ month end

[BobWeaver]

Yes you just need the runtime (JRE5.0 I think) not the whole developer kit. My mistake. Earlier, I had said developer kit. Although that will work, it's more than you need. If you go to the following link, you will find the JRE 5.0 download:

http://java.sun.com/j2se/1.5.0/download.jsp