FileMaker, Encoding, Unique Values, Oh My!

Scripting Forums AppleScript | Mac OS X
#1

I’m trying to, and currently succeding, script FileMaker so that, for a given brand name, a text file for each CD for that Brand is exported.

Before I get into the questions though here is the relative portions of the script (which as mentioned is working in its current incaranation).

-- Set local scope variables
set HeaderLineWrite to ("Brand" & tab & "CD SKU" & tab & "CD Title" & tab & "Image ID" & tab & "Caption" & tab & "Keywords" & tab & "Image Type" & tab & "Color" & tab & "Orientation") as text
set TitleList to {}
set currentCD to ""
set deskPath to path to desktop as Unicode text
set FolderPath to deskPath & "Metadata:"
set RecordCount to 0
-- Brand for which to export data
set BrandName to text returned of (display dialog "Please select one of the following brand names to ouput:" & return & BrandList default answer "")
tell application "FileMaker Pro Advanced"
	go to layout "Images"
	-- Find all records for a given brand and sort by CD Title
	tell database "Images"
		show (every record whose cell "Brand" is BrandName) of table "Images"
		sort current layout by field "CD Title" in order ascending
		-- Cycle through found records to build CD List

		repeat with i from 1 to count of every record of window "Images"
			set RecordList to record i of window "Images"
			set {ImageName, Caption, CDTitle, CDSKU, Brand, Vendor, Photographer, ImageType, ColorType, Orientation, Notes, Keywords} to RecordList
			if CDTitle is not currentCD then
				set end of TitleList to CDTitle
				set currentCD to CDTitle
			end if
		end repeat
		repeat with i from 1 to (count of items of TitleList)

			set LineWrite to ""
			show (every record whose cell "Brand" is BrandName and cell "CD Title" is (item i of TitleList)) of table "Images"
			set RecordCount to RecordCount + (count of every record of window "Images")
			-- With a CD's metadata found now generate the text stream

			repeat with c from 1 to count of every record of window "Images"
				set RecordList to record c of window "Images"
				set {ImageName, Caption, CDTitle, CDSKU, Brand, Vendor, Photographer, ImageType, ColorType, Orientation, Notes, Keywords} to RecordList
				set LineWrite to (LineWrite & return & Brand & tab & CDSKU & tab & CDTitle & tab & ImageName & tab & Caption & tab & Keywords & tab & ImageType & tab & ColorType & tab & Orientation) as text
			end repeat
			-- Create new file and write the text stream

			tell application "System Events"
				set newFilePath to FolderPath & BrandName & ":" & (item i of TitleList) & ".txt"
				set fileRef to (open for access file newFilePath with write permission)
				write HeaderLineWrite to fileRef
				write LineWrite to fileRef
				close access fileRef
			end tell
		end repeat
	end tell
end tell

Okay in regards to the Encoding portion of my problem… The ultimate destination for these text files is for import into a java application and when doing a export from Filemaker, tab delimited and Output character set as “Macintosh” the files import fine. When using this script though if I leave off the “as text” in the following lines

set HeaderLineWrite to ("Brand" & tab & "CD SKU" & tab & "CD Title" & tab & "Image ID" & tab & "Caption" & tab & "Keywords" & tab & "Image Type" & tab & "Color" & tab & "Orientation") as text
set LineWrite to (LineWrite & return & Brand & tab & CDSKU & tab & CDTitle & tab & ImageName & tab & Caption & tab & Keywords & tab & ImageType & tab & ColorType & tab & Orientation) as text

I end up with a text file that looks identical to a file created with the “as text”, but is slightly larger and will not import into the java app. I’m not sure how to exactly determine the encoding of the file or why it’s not working. If anyone could provide any info it would be greatly appreciated.

The Second Question deals with finding unique values. I know I can find unique values using built in functions of FileMaker, but for a few reasons I would rather not. So with that said I came up with the following method, but it’s terribly slow. Any ideas how to speed this up or an entirely different way to begin with?

* Sub Routine Scripts *)
on ScanBrand()
	-- Set Local Scope Variables
	set currentBrand to ""
	set BrandRecord to {}
	set BrandList to ""
	tell application "FileMaker Pro Advanced"
		go to layout "Images"
		tell database "Images"
			show every record of table "Images"
			sort current layout by field "Brand" in order ascending
			repeat with i from 1 to count of every record of window "Images"
				set BrandRecordList to record i of window "Images"
				set {ImageName, Caption, CDTitle, CDSKU, Brand, Vendor, Photographer, ImageType, ColorType, Orientation, Notes, Keywords} to BrandRecordList
				if Brand is not currentBrand then
					set end of BrandRecord to Brand
					set currentBrand to Brand
				end if
			end repeat
		end tell
	end tell
	repeat with i from 1 to (count of items of BrandRecord)
		set BrandList to BrandList & return & (item i of BrandRecord)
	end repeat
end ScanBrand

And that’s about it. Sorry for the insanely long post =)

#2

I have some ideas about your second portion of finding unique brands. I think one reason it is slow is either the multiple repeats, or the number of records, or both. Please understand that I do not have access to any version of Filemaker, so my suggestions may not work exactly. I would do the whole sorting and repeating like this, if possible:

set Brandlist to {}--Set this to an empty list instead of an empty string
set all_Recs to every record of window "Images"--This MAY make a list of all the records
repeat with a_Record in my all_Recs--The use of the term 'my' will speed it up if the list of records is huge
if Brandlist does contain a_Record's Brand then--Just loop through the list of records, and if the Brand is not in the Brandlist already...... 
set end of Brandlist to a_Record's Brand--Put it there
end if
end repeat

If you need the list of unique brands put into a text format, this simple loop will do it quickly:

set brand_String to ""
repeat with ab in Brandlist
set brand_String to brand_String & ab & return
end repeat

I hope this helps somewhat, good luck.