Filemaker not getting every record

There’s something I’m not seeing in my attempt to get certain fields out of a certain window called “Layout #2”. Certain clients (master_area) have specified bullet points, noted in a record that has “Bullet Point 1” in a field called VARIABLE_MASTER__R.NAME with its value showed in a field called “VALUE_C”. If they do not, then the default bullet point is used (bullet_ValueDefault) as noted. If a client has its own bullet points, then the below should pick them up and replace the (bullet_array) with its own.

What’s happening is that it seems to skip the code for “Bullet Point 1” and returns: {“bullet 1 default”, “bullet 2 default”, “string that belongs to cobv2’s Bullet Point 2”}. I’m struggling to determine why it’s not copying over Bullet Point 1? As you can see below, there always should be a Bullet Point 1 if a client has any bullet points entered. It does here. And if it finds that value, it should clear out the old default list. So while it returns the default and bullet point 2, it seems to be skipping the Bullet Point 1.

Looking through the Event Log History, it’s finding that record for Bullet Point 1. So I’m not seeing something that may be obvious to a more eagle-eyed contributor here.

Here’s a subset of the trouble area:


set master_area to "cobv2"
set bullet_ValueDefault to {"bullet 1 default", "bullet 2 default"}
tell application "FileMaker Pro"
	try
		delete every request
	end try
	
	tell layout "layout #2"
		create request
		set cell "AREA_ID__C" of request 1 to ("=" & master_area)
		find window 1
		set bullet_Value to {}
		set totalVar to count of records -->13
		if totalVar > 0 then
			repeat with thisVar from 1 to totalVar
				tell record thisVar
					set bullet_Value to bullet_ValueDefault
					if cell "VARIABLE_MASTER__R.NAME" is "Bullet point 1" then
						if cell "VALUE__C" is not "" then
							set bullet_Value to {}
							copy cell "VALUE__C" as string to end of bullet_Value
						end if --bull 1
					end if -- bullits 1
					
					if cell "VARIABLE_MASTER__R.NAME" is "Bullet point 2" then
						if cell "VALUE__C" is not "" then
							--set cell "VALUE__C" to end of bullet_Value as string
							copy cell "VALUE__C" as string to end of bullet_Value
						end if --bull 2
					end if -- bullits 2
					
				end tell --record thisvar		
			end repeat --thisVar
			
		end if --totalVar
	end tell --layout
end tell --app fm
return bullet_Value

I mightily appreciate any time you may share on my issue, thanx, sam

Model: mac pro
Browser: Safari 525.27.1
Operating System: Mac OS X (10.5)

I’m a little fuzzy on exactly what’s doing and happening, however it looks like you perform a find and then act upon the records found. Yes?

	create request
	set cell "AREA_ID__C" of request 1 to ("=" & master_area)
	find window 1
	set bullet_Value to {}
	set totalVar to count of records -->13

From there you repeat thru those records found, in this case 13 records.

	repeat with thisVar from 1 to totalVar
		tell record thisVar

Perhaps this is where the reference to a record is mismatch since record 1 of the find results, for example, is not record 1 of the database. If you have 20 records total, and the find results returns 13 then there are 7 records before, after, or in-between them.

I know it’s suppose to work when referencing records of the layout instead of the database; ie., record 1 of the database is not necessarily record 1 of a layout depending on how the records are sorted and which are omitted from the layout. However I found that FileMaker could easily become confused with references to record number more often than references to record ID (at least it did in version 4; I don’t have the latest version yet). I’d be more assured of results and accuracy if I worked within a ‘tell database X’ instead of ‘tell layout X’ when acting upon records.

After the find command, it might help if you repeat thru record IDs instead of repeating with x from 1 to (record count).

	tell layout "layout #2"
		create request
		set cell "AREA_ID__C" of request 1 to ("=" & master_area)
		find window 1
		set bullet_Value to {}
		--set totalVar to count of records -->13
		set recordIDlist to ID of every record
	end tell
	tell database "[the-database-name]"
		if class of recordIDlist is not list then ¬
			set recordIDlist to {recordIDlist}
		repeat with aRecordID in recordIDlist
			tell record ID aRecordID
				--do stuff
			end tell
		end repeat
	end tell

However, as I mention, I’m not working with a current version of FileMaker so I’m just guessing and it may not apply.

You should use the following structure to talk to your database

Tell application “FileMaker Pro”
Tell Database “My database”
end tell
end tell

Now, when you talk to your database this way, referencing a record # will be on the WHOLE database, not on the found set. If you perform a find, and you then want to cycle thru only the found records, you must switch to this structure

Tell application “FileMaker Pro”
Tell document “My database”
end tell
end tell

When referencing to your file with the “tell document” structure, record 1 will be the first record of the found set instead of the first record of the database.

Manuel

Thank you very much. The entire tell structure was left out of this snippet for readabillity, but the snippet behaved exactly as it was in the much bigger structure.

But it appears that my problem was a little more pedestrian. I was pulling in the bullet_valueDefault on each iteration of each record and it was overwriting each bullet except for the ultimate one.

However, your efforts are appreciated because the tell structure of Filemaker is not very straightforward as I’ve read it. These explanations should help me to clean up my code. You’re kind to have helped me, thanx, sam

Scripting FileMaker works best when avoiding code that mimics user interaction and acting upon the object model accordingly. There’s no need to display records in a layout to find certain ones and get or set values of cells. For example, if your database name is “THEDATABASE.fm”

set master_area to "cobv2"
set bullet_ValueDefault to {"bullet 1 default", "bullet 2 default"}

tell application "FileMaker Pro"
	tell database "THEDATABASE.fm"
		--find ID of records you want to change
		set records_to_change to ID of every record ¬
			whose cell "AREA_ID__C" is equal to master_area
		--if one record matches, result is a number (a record ID)
		--if more than one record matches, result is a list of numbers (record IDs)
		
		--normalize result to work within a repeat loop
		if class of records_to_change is not list then ¬
			set records_to_change to {records_to_change}
		
		--change records
		repeat with an_ID in records_to_change
			--that form of repeat works best using "contents of [variable]"
			-- instead of working directly with the variable
			set thisRecordID to (contents of an_ID)
			
			--act upon each record by referencing each record ID
			tell record ID thisRecordID
				set bullet_Value to bullet_ValueDefault
				if cell "VARIABLE_MASTER__R.NAME" is "Bullet point 1" then
					if cell "VALUE__C" is not "" then
						set bullet_Value to {}
						copy cell "VALUE__C" as string to end of bullet_Value
					end if --bull 1
				end if -- bullits 1
				
				if cell "VARIABLE_MASTER__R.NAME" is "Bullet point 2" then
					if cell "VALUE__C" is not "" then
						--set cell "VALUE__C" to end of bullet_Value as string
						copy cell "VALUE__C" as string to end of bullet_Value
					end if --bull 2
				end if -- bullits 2
			end tell
			
		end repeat
	end tell
end tell

I’m to understand that after version 4 FileMaker adds options for layouts to display like a spreadsheet, and then later(?) versions also allow several documents to be contained within a single database. This makes it easier, tho less intuitive, to work with FileMaker’s AppleScript objects in a one-to-many relationship (one database contains several documents which contain several records which contain several fields). The only time the layout objects come into play is when the script needs to work with different layout formats (with number and/or text styles, for example), or if the UI and/or user is part of the action. Otherwise, it can all happen to the data without needing to bring FileMaker to the front or showing layouts on the screen.