Get data from field not working

[rcorbitt]

I'm having trouble getting data from a global field into an Applescript. Here's what I have now.

tell application "FileMaker Pro" to set theCounty to cell g_CorpCounty of database 1

The script keeps giving me an error "theCounty" is not defined. Unknown error -2753.

I've read that adding a try statement helps native Applescripts in FileMaker, but it hasn't prevented to error so far.

[Søren Dyhr]

A context message in the syntax to a chosen layout or table is required:

tell application "FileMaker Pro Advanced"

	set cell 1 of table 1 to "Hurlumhej"

end tell

The use of table over layout, avoid the need for the fields presence on the specific field. Do here not confuse TO_name with table_name, counting is pretty secure, but self documents the script badly!

The "database" statement isn't of much use here since global fields are ignoring the found set anyway!

--sd

[rcorbitt]

This isn't working. I get the same error.

The purpose of this AS statement is to get the value stored in the global field g_CorpCounty. This field is entered by the FM user when searching for records to be exported.

I want to set it to an Applescript value (object?) so that I can use it again later in the Applescript to name the file.

(set theOutputName to theCounty & " County final.txt")

[Søren Dyhr]

Well I know I made a set field instead of grapping the value, but in essence is it utterly important to get included where to look for the fields content.

This works:

tell application "FileMaker Pro Advanced"

	set theCountry to cell 1 of table 1

end tell

However does the purpose not quite require AS at all, you can build everything with $variables natively:

--sd

Untitled.zip

Edited November 20, 2007 by Guest

[rcorbitt]

Actually, I'm using the whol eApplescript for more than just this field.

tell (get (current date) - 30 * days)

	set currentMonth to (month of it)

	set currentYear to (year of it)

	set lastMonth to ("- " & currentMonth & " " & currentYear) as string

end tell





set theExportedText to (path to desktop as Unicode text) & " county final.xtg" as alias

set theExportedText to (open for access file thePath with write permission)



--Read the whole file at once because it is a small one

set FileData to (open for access file theExportedText)



set c0 to {""<@FTRCorpName>",", ""<@FTRCorpName>""}

set c1 to {""<@FTRCorpAddress>",", ""<@FTRCorpAddress>""}

set c2 to {" Dba ", " D/B/A "}

set c3 to {" D/b/a ", " D/B/A "}

set c4 to {",AL,", " AL,"}

set c5 to {",GA,", " GA,"}

set c6 to {",NC,", " NC,"}

set c7 to {",FL,", " FL,"}

set c8 to {",SC,", " SC,"}

set c9 to {",TN,", " TN,"}

set c10 to {",NC,", " NC,"}

set c11 to {""", ""}

set c12 to {", Inc.,", " Inc.,"}

set c13 to {", LLC,", " LLC,"}

set c14 to {"L.L.C.,", " LLC,"}

set c15 to {", PLLC,", " PLLC,"}

set c16 to {"L.L.C,", "LLC,"}

set c17 to {", ,", ","}

set c18 to {",", ", "}





set ThingsToCleanOut to {c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18}



repeat with aCleanUpPair in ThingsToCleanOut

	

	--grab the stuff to search and replace for

	set TextToFind to item 1 of aCleanUpPair

	set TextToReplace to item 2 of aCleanUpPair

	

end repeat



--Create The File and set the Creator Type



set QuarkTags to "@Normal=

@BusLicCompany=

@FTRCorpName=

@Normal=[s"","Normal"]<*J*h"Standard"*kn0*kt0*ra0*rb0*d0*p(0,9,0,0,0,0,g)Ps100p100t0h89.999z10k0b0cKf"StoneSerif"n0oG0>

@BusLicAddress=[s"Normal","BusLicAddress"]<*ktA*ra0*rb0*p(0,0,0,0,0,2.88,g)*t(292.5,0,"1  ")Ps100p100t0h89.999z10k0b0cKf"NewsGothic"n0oG0>

@FTRCorpAddress=[s"","FTRCorpAddress"]<*J*h"Standard"*kn0*kt0*ra0*rb0*d0*p(0,0,0,0,0,0,g)Ps100p100t0h89.999z9k0b0cKf"NewsGothic"n254oG0>

@BusLicAddress:<*L*kt0*ra0*p(0,0,0,9.7,0,8.856,g)><*t(265.5,0,"1  "445.5,0,"1  "553.5,0,"1  "657,0,"1  "729,0,"1  ")>

<*t(265.5,0,"1  "445.5,0,"1  "553.5,0,"1  "657,0,"1  "729,0,"1  ")>" & theCounty & " County - " & lastMonth & "

@FTRCorpAddress:<*t(512,2,"1  "20736.457,0,"1  "27392.344,0,"" as text



end



tell application "Finder"

	set creator type of alias FinalFile to "XPR3"

	set file type of alias FinalFile to "TEXT"

end tell

There's a little more after this but you get the idea. I need "theCounty" for use in the name, and in some of the text that's written to the "formatted" file.

[Søren Dyhr]

What makes you think applescript does it better than say a relational approach and native scripts??

--sd

[rcorbitt]

First, there's not much relational data in this db. I created it to handle, format, and manage imported text. (I'm better with FM Pro than Applescript, and this way we have an easily searchable record of all the info.)

I'm using the text that is being exported for a Quark XPress document. There are about 2,000-8,000 records each month that get exported. Formatting this large amount of data is only feasible using Quark tags or a plug-in like Xtags.

I'm using .csv format for the export, so the records have to be cleaned and formatted with Quark Style Tags. I could do some of this with a calc field that uses the Substitute() function, but the Applescript seemed faster as the most important formatting occurs after the data has been exported from FileMaker.

I suppose that I could save the Applescript code in FM globals and use a calculated script to call them. But I think fields in FM Pro are limited to 255 characters, so I'd need a bunch of globals to handle the entire script.

I'll probably just use an Applescript step that asks the user for the g_County field value ...

[Søren Dyhr]

But I think fields in FM Pro are limited to 255 characters

No! ...a textfield can hold 2GB, the number of char's in a calc'field is 800 ...

You shouldn't for your purpose ignore this:

http://www.filemaker.com/help/Script-Steps36.html

--sd

[rcorbitt]

Thanks, but as I understand it, this will only work on data in the fields.

To complete the project, I still need to manipulate the exported text, outside of FileMaker. That's why I'm using the Applescript...

[Søren Dyhr]

No I do not think so, I once saw this Quark tagging done entirely inside filemaker in an Advisor article. The only thing which probably needs to be done via applescript is the change the creatortype of the output.

I say this because, the time it takes to get the AS-syntax correct is quite daunting, the error messages tells next to nothing!

--sd

[Fenton]

I would somewhat agree with Søren; who is, by the way, a very competent AppleScripter. Unless you are also, it would be wiser to do most text manipulation in FileMaker. I think FileMaker has about the easiest to use text manipulation tools there are (unless you require grep).

The fact that "theCounty" is not defined tells us almost nothing, because:

1. It's not in the AppleScript you posted, and

2. We have no idea of the context of FileMaker

It could simply mean that the field is not on the current layout, if you did not specify layout or table occurrence. AppleScript is (too) picky about these things.

In the AppleScript you posted, you did not define thePath variable. So that would stop it cold right there.

I don't see why you need to open the file to read it. You could wait until you were done manipulating the data before writing it back to the file. From what I see of the script, you never wrote the data back to the file.

The "switch" routine for the replacements is off to a good start, but it never writes back to the data (as far as I can see). It's just running through resetting a couple of variables.

It seems all of that could be done is a single chained FileMaker Substitute() function.

Substitute ( theTextField; [" Dba ", " D/B/A "]; [" D/b/a ", " D/B/A "] )

You can see that with a decent text editor (BBEdit, TextWrangler) you could quickly turn your pairs into the right syntax for FileMaker's function. Heck, I'll do for ya (I'm assuming you want to keep the escaped quotes?)

Substitute ( theTextField;

[""<@FTRCorpName>",", ""<@FTRCorpName>""];

[""<@FTRCorpAddress>",", ""<@FTRCorpAddress>""];

[" Dba ", " D/B/A "];

[" D/b/a ", " D/B/A "];

[",AL,", " AL,"];

[",GA,", " GA,"];

[",NC,", " NC,"];

[",FL,", " FL,"];

[",SC,", " SC,"];

[",TN,", " TN,"];

[",NC,", " NC,"];

[""", ""];

[", Inc.,", " Inc.,"];

[", LLC,", " LLC,"];

["L.L.C.,", " LLC,"];

[", PLLC,", " PLLC,"];

["L.L.C,", "LLC,"];

[", ,", ","];

[",", ", "]

)

[rcorbitt]

First, let me say thank you for all the help. I'm very grateful for all the suggestions.

Second, I am not experienced with Applescript. My goal using AS with this database was to reformat text "after" it had been exported from FileMaker.

Fenton's suggestion to use the Substitute() function is a good one, and I will probably include it once we're done with some beta testing ...

The script that I posted was not complete. It was only to illustrate that I was using more than a simple AS statement or two. After fighting with FM for a few days, I have given up on running a native AS from FM and created a droplet.

I would welcome any reference to how FileMaker runs Applescript. Or why scripts that run fine in Script Editor or Script Debugger, fail in FM. I'm sure this is a matter of syntax, but I couldn't find any kind of resource ...

The droplet removes quote marks, commas, etc ... that are present in the exported FM text. I found a couple of canned solutions that use AS and a scriptable text editor to reformat text, again outside of FileMaker Pro.

I chose to use Applescript rather than depending on another application, that is not "native" to the Mac OS, for editing.

For those playing the home game on this thread, the answer to the original question is:

--[original script]

set theCounty to cell g_CorpCounty of database 1 --gets error -2753 in FM

--[corrected script]

set theData to get the cellValue of cell "fieldName" of layout "layoutName" of document "FileMaker Document"

The mistake causing the error was due to my failing to enclose the "cell" and the "database name" in quotes.

Ironically, I scrapped the AS to get the FM data, and included the info I needed in the output name of the file. The droplet, using AS delimiters, gets what it needs from the filename.

Thanks again for all your help!

[Fenton]

Yeah, cells have to have quotes. I missed that in the first post. I came in a bit later. I think FileMaker and AppleScript can work very well together, as long as you're aware of the quirks of each. I don't see why you'd use a droplet, if you're exporting from FileMaker. I think of droplets more as something you "drop things on" (duh :-).

My experience is that AppleScript runs faster when run from FileMaker rather than stand-alone AppleScripts. I think it's because of the memory.