Hidden database windows and AppleScript

[xyz3]

Hi!

In FileMaker Pro 6 the following code user to work, but now in FileMaker Pro 8, it returns --> FileMaker Pro got an error: Event not handled

tell application "FileMaker Pro"

tell database "hiddenDatabase" --window must be shown?

set kRecordMatch to ID of (every record whose cell "field" = "searchValue")

end tell

end tell

If the database window is visible, the above script works as expected.

Does anyone know of a workaround for this?

[Søren Dyhr]

Well we used to use layout 0 as object to work with sans layout, today is the object "Table" !

The rewst is pretty straight forward either counting your way thru the objects by number or the actual name in quotes!

--sd

[xyz3]

Hi - Thanks for the quick response!

Are you saying I should target layout "name" or table "name" ?

Can you provide a code example of how this works?

Edited January 16, 2006 by Guest

[xyz3]

Has anyone else been able to get this type of scenario to work?

tell application "FileMaker Pro"

--go to database "HistoryDatabase" --open database, not what I really want.

tell database "HistoryDatabase"

--set kEveryRecordCount to (count of every record)--this works if the database window is hidden(?)

--log kEveryRecordCount

tell table 1 --table 0 does not work, layout 0 does not work

set kRecordMatch to ID of (every record whose cell "catalogYear" = "YM04") -- window must be visible

return count of kRecordMatch

end tell

end tell

end tell

[Søren Dyhr]

Sorry for the delay... I have a solution for you, the problem you face is that you have converted the files separately or rather because there isn't a relationship between them. What I suggest is that you make a contant relationship between them, and migrate the solution.

What happens is that you on the one-side of the relationship gets two table occurences in the define database dialog. To each of these TO's do you make a layout, and disconnect the relation already made when migrating and deletes the two constant fields since they are of no importance for the solution any more.

What you have made is a Singlefile interface where you just by flipping the rolodex in layout mode have a layout you can address with applescript despite not being the frontmost. This means you can make found sets in both as you pleases, gather the ID's etc.

When you open you SFI file does it know of the other, and opens it accordingly but the user needs not know of it's layout being placed in the SFI file, if you remove the status area from prying eyes.

By try to investigate my template, I have allowed myself to embed the AS!

Please don't hessitate to ask further!

--sd

AsSFI.zip

[xyz3]

Hi-

Thanks for the pointers. I was able to get this to work:

tell application "FileMaker Pro"

--tell database "mainDatabase"

set kRecordMatch to ID of every record in layout "HistoryDatabaseLayoutForScripting" whose cell "CatalogYear" = "YM04"

return count of kRecordMatch

--end tell

end telll

Now - strangely I cannot use a tell database target and get this to work. Do you know why this is?

Also - in your example, you are setting the AppleScript text item delimiters to return, but not setting them back to their defaults. This could cause issues for another script if the delimiters were not reset. Not sure if you were aware of this or not, but I felt I should point out.

Thank you for taking the time to create the example database - I greatly appreciate your help!

[Søren Dyhr]

It's obvious that the delimiters should be put back to default - I skipped it in pure sloppyness. But you're right it should be mentioned.

Then to the "document" vs. "database" distinction, which played a role when... well it makes me thinking - why bother with the unavoidable AS syntacticalities your run into when digesting a AS directory. When everything by and large today is provided for in native Filemaker scripting as well as bidirectional relations definition.

With FM6arrived "contrain found set" while FM8 made it posible to go to related records based on a found set in the other table - This added to the bidirectionality introduced with FM7.

All these features, have in my opinion made a lot of the applescripting we used to do redundant, since all the new methods stays clear of the clipboard ...which was the reason we dived down in AS syntax.

I by no means saying that the AS and filemaker is a bygone, only it's much more in it's place to use it dealing with interfacing stuff outside the database realm.

Perhaps you should point me in directions I'm ignoring?

--sd

[xyz3]

Perhaps an overview of what I am doing:

In the main database I have a button which does the following:

1.) Get name of frontmost InDesign document

2.) Get a list of elements from the InDesign document

3.) Delete all records from the HistoryDatabase that match (this is the code above. Some HistoryDatabase records are shown in the main database.)

2.) Repeat through the InDesign elements, and get the label name

3.) Create a new record in the HistoryDatabase, and insert the label name, and other data.

I don't understand how the inheritance works in FMP8 - shouldn't the layout be part of a database, or a document?

Beyond that - if I wanted to include above AppleScript in a Script Menu, Application, or InDesign Script Palette, I am sure I would have to do some checking of which database is open, and if the layout exists.

When I was able to target the database via a tell block in FMP6 - this was so much easier.

[xyz3]

Another question I have is :

If 2 databases are open, and they both have the same layout name - how do I know which one the script is using?

Fo instance - I added the same layout to the HistoryDatabase, but I am now seeing this layout only works with the currently found set of records, rather then finding all of them, whereas in the main database, it always seems to search the entire set - dues to how the relationships work.

is there away to add a layout that would always show the entire contents of its own database?

I am ultimately looking for a way to get the same code to work with either database.

Perhaps not allowing the user to interact with the HistoryDatabase directly is the best way.

[Søren Dyhr]

Ah! it both an Applescript issue as well as an filemaker issue!

So you have made your filemaker solution track outstanding task in inDesign, this is done by running the applescript once and awhile to optain sync. and while doing it optaining som sort of audit trail?

All tasks is created as records in filemaker from a sort of template generating them in one scripting which you have chosen to be in applescript, perhaps because you havn't considered if a better or a worse relational structure might give different results?

There is absolutely no need to delete records, since you need to create evidence of the records existence in what you think is another base, if you approach it from a relational structure should a simple change to a key be all it takes to make the very same record show up in another view.

You're by lack of structure making an extra table of data, causing all kinds of syncing issues, when all it takes is several layouts each having their own relationship upon a multicriteria relationship.

Now if you stack all objects from inDesign via textitemdelimiters could it be one of the keys for a relationship ...here comes this custom function to mind:

http://www.briandunning.com/cf/39

What it does is it strains lists of ID's or objects in this case, to make a new key ...here making it posible to see uncompleted tasks.

Perhaps it's better to see this in action. Take a look at this template:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

Now to your questions:

shouldn't the layout be part of a database, or a document?

No! it could very well live in another database, where it's referenced as a table occurence and perhaps given a couple of layouts there.

I added the same layout to the HistoryDatabase, but I am now seeing this layout only works with the currently found set of records, rather then finding all of them, whereas in the main database, it always seems to search the entire set - dues to how the relationships work.

You're not getting it, there should be no layouts at all in your HistoryDatabase all interacting with interfaces happens in the other file if they need to be in two in the first place?

History and the task itself is only two aspects of the same record with different key-values to show up in different layouts. Your problems stems from seeing it as two different!

You need to read about migration foundations: http://www.filemaker.com/downloads/pdf/techbrief_fm7_foundations.pdf

All who upgrades a solution muct know these things before embarking on the journey ...how can you navigate by the stars if it's cloudy?

--sd

[xyz3]

So you have made your filemaker solution track outstanding task in inDesign, this is done by running the applescript once and awhile to optain sync. and while doing it optaining som sort of audit trail?

I do not want an audit trail.

There is absolutely no need to delete records, since you need to create evidence of the records existence in what you think is another base, if you approach it from a relational structure should a simple change to a key be all it takes to make the very same record show up in another view.

Actually - there is a need to delete. I only want the latest page data to be stored in the database, essentially by document name.

I do realize there are better ways of doing this, but this is a migration, and since there is already much data in these databases, and the specification was given to me by the customer, I can offer suggestions, but am not at liberty to change functionality without approval.

I appreciate, the explanations, the links, and the pdf - looks like a very good read. Will surely put aside some time to digest these.

Thanks for your help.

[Søren Dyhr]

There is even more to the shifts in objects...:

http://www.fmpromigrator.com/downloads/demo_software/filemaker7_applescript_reference02.pdf

--sd

[xyz3]

Hi-

Hit a bump:

Open the SFI database, and switch to Layout #2

Show all records

Show omitted only (0 records should be found.)

Now switch back to layout #1 and run the script.

[xyz3]

is there anyway to show all records without the database coming to the front?

The thing that is REALLY ticking me off at this point it that my old FMP6 code works perfectly - unless the targeted database window is in front. (Hidden and behind another database window result in the same error.)

This combined with the odd behaviour of the above post makes me wonder if there is a solution that works.

The nice thing about the way the script worked in FMP 6 is that the records could be acted on without them being shown.

My other issue is the horrible way FMP deals with EPS image previews from a linked file. Storing obviously isn't the way to go with big files, and linking isn't much better with big files.

My solution in this instance was to create a command line tool (using coreImage) to generate preview files, then glue this all together using applescript and a filemaker script to insert the preview file. (In FMP6 I used Cumulus to generate the previews, and inserted the data directly into the record.)

Doesn't Apple own Filemaker?

[Søren Dyhr]

is there anyway to show all records without the database coming to the front?

Searches is dependant on a layout, while writing could be done straight to the table. The object "document" referes to the found set, while "database" is the every record in the table.

In it's present form needs only one file to be open, the entire system could be build in one file (usually?) What seems to be an option is to resize the window until next to nothing, somewhere where it doesn't get in the way.

Perhaps is all it takes to gather the ID's from a layout and then close it, if it's embedded won't you notice it at all:

http://www.codecomments.com/archive262-2004-8-196931.html

If i get time at hand in the next couple of days, will I give it a whirl.

This might give some hint as well:

http://bbs.applescript.net/viewtopic.php?pid=38621

BTW is Fenton far the best Applescripter around here in this forum as well.

--sd

[xyz3]

This appears to be the best workaround I have found yet (no error handling yet.)

set kFoundRecordIDs to kSearchFMP8forCellEqualTo("databaseName", "cellname", "search")

return count of kFoundRecordIDs

on kSearchFMP8forCellEqualTo(kTargetDatabase, kCell, kSearch)

tell application "FileMaker Pro"

if (name of window 1) ≠ (kTargetDatabase & ".fp7") as string then

return ID of (every record of layout (kTargetDatabase & "Scripting") whose cell kCell = kSearch) --requires layout to be added to frontmost database and set correctly

else

tell database kTargetDatabase to show every record --last find can really screw up results. also if someone did a show all records, then show omitted (in this case no records are shown) then you can pretty much so bet on an error.

return ID of (every record of database kTargetDatabase whose cell kCell = kSearch)

end if

end tell

end kSearchFMP8forCellEqualTo

I liked the old FMP behaviour much better.

[Søren Dyhr]

But by reading your AS does it strike me that you're unvilling to join all tables in one file, why is it so? - None of the usual issues for splitting seems to be here?

It seems like your tagging off completet task has nothing to do with relational database designs - You're syncking two databases instead of changing a keyvalue to change relationships.

I understand you're tied up demand-wise in a project which boldly are migrated to a tool which is an entirely new beast of objects as well as tons of added oppertunities in defining relationships, making a lot of the workarounds which were neat to do in AS redundant as well as useless.

Why can't the solution continue to live on under FM6? Mustn't the basics be to expect a pretty normalized solution if it's going to get advise in a filemaker forum?

To expect participation in damage-control is perhaps too far fetched - Deliberately Ignoring posibilities in inner mechanics of a tool, and regard it as a blackbox is an applescript question and has nothing to do with the fact that it's actually done clumsy in filemaker. To make it work is based on pure guesswork and fixing based on pure luck.

It's extremely frustrating! ...and I guess that the main reason why others shy away from your question. I carries in essence what this t-shirt is all about:

http://www.thinkgeek.com/tshirts/frustrations/388b/

--sd

[xyz3]

Yes. I suppose I deserve that.

Time is a luxury that I do not have currently. Leaving in FileMaker6 would have been the best bet. Had I known ahead of time how massive the changes to Filemaker 7/8 were, I would have suggested this. See screenshot of one layout of one database (there are 5 total) - and this is one of the easier layouts. To rework and retest this entire solution would take a few months. I assume you know the amount of scripting it takes to build something like this, and there are several attributes for each image/button that are needed for this one tab to work correctly.

I was not posting directly back to you, but rather for the next user that comes across the same issue. Thank you for your thoughts however.

Perhaps if you were in my shoes, you would not be so quick to judge.

[Søren Dyhr]

I assume you know the amount of scripting it takes to build something like this

No I don't! Portals can today be cut up in small chunks if needed. Searches in blobs (binary large objects) for pattern recognision is not something, anyone have thought into filemakers find algorithms yet (is it a database task?). So it must be in text/number fields in the childrecords. GTRR can make you go to main record, since the relations are bidirectional.

So the scripting to make what seen is next to nothing - where scripting comes in is to make it work together with inDesign, but again does it seem best to make this on each individual records level, and not in the presentation layer.

--sd

Edited January 27, 2006 by Guest

[xyz3]

Hi-

Hit a bump:

Open the SFI database, and switch to Layout #2

Show all records

Show omitted only (0 records should be found.)

Now switch back to layout #1 and run the script.

Try the quoted example above, and note the error you will get.

Do you know if there is a workaround? I tried a few things, none of which worked in all situations. I still have a few more ideas to test.

Søren, I do apologize if I am not understanding/comprehending your thoughts correctly. I am new to FMP7/8 and have not had time yet to sit down and fully read and digest the pdf manuals.

Thank you for your help.

[xyz3]

Well - I made an example from scratch:

Example

And even though all interfacing is to be done via one database (images.fp7) I am still hitting the same issue.

It boils down to this: A script can be run outside the database - which is why I added the show history button in the header of both layouts - for simulation of the problem. Anyhow - since a script can be run from outside filemaker, any layout can be active, and the user could have affected which records are shown from the related database. This is not ideal, as to search the entire related database, the layout must be showing all records, or some will not be located. The outside script has no real way of knowing of this, and therefore the only thing I can think to do is to to "show all records". Any way I look at this - If I go with one database the script will have to show all records to get an accurate search using applescript.

Now - from a user point of view - this approach sucks, because if I search for *1* in the history database, and I run a script to do something my search has been changed, and I have to go back and search again - this interrupts my workflow as a user, and I will not be happy.

So lets try this:

1.) Open images.fp7

2.) Switch to history layout and show all records. Looks like we have a total of 6 records.

3.) Switch to the images layout.

4.) Press the Show History button.

5.) Looks like image 1234 was found in 2 documents. Cool!

6.) Switch back to history layout.

7.) Do a search for documents that contain *1*. Looks like 3 records were returned.

8.) Switch to the images layout.

9.) Press the Show History button.

10.) Looks like image 1234 was found in 1 document. What? - we know that isn't right. image 1234 is in 2 documents!

So the above is not a real workflow, but an example of the problem that will happen. This problem will be compounded even further if the script is attempting to update the database to match the state of the document. Say image 1234 is removed from the document, the script is run, since only some records were shown the records will not be properly removed/updated.

Now we have a mess. History database records do not match the state of the document. The customer will not be happy.

So the burning question is this: How does one allow all records to be searched via a script - independent of what is shown in the layout interface?

[Søren Dyhr]

Alright I've played a little with you stuff, and what you seems to get from AS is the term "contain". Well I made alterations to it and got rid of everything AS, by adding a global field for keying on the primary side and a CF on foreign side that makes all combinations of the name (within reason... although) comming from here:

http://www.briandunning.com/cf/77

The script is purely native!

Set Field [ images::gName; "1234" ] 

Show Custom Dialog [ Message: "Search for record in history:"; Buttons: “OK”, “Cancel”; Input #1: images::gName ] 

Set Variable [ $howMany; Value:Count ( history::name ) ] 

If [ $howMany ] 

     Set Variable [ $i; Value:1 ]  

     Loop 

             Show Custom Dialog [ Message: "Found " & images::gName &  " in document " & GetNthRecord ( history::document ; $i );  Buttons: “OK” ] 

             Exit Loop If [ $i = $howMany ] 

             Set Variable [ $i; Value:$i+1 ] 

       End Loop 

Else 

       Show Custom Dialog [ Message: "Doesn't exist! Try another one?"; Buttons: “Yes”, “No” ] 

       If [ Get ( LastMessageChoice ) = 1 ] 

              Perform Script [ “Show_history_SD” ] 

       End If 

End If 

Which means we by the use of relations, can get rid of yet another layout!

But what has these searches to with the layout you showed yesterday - there are no need for them as far as I can guess ...actually could it also be done in previous versions without scripting, although the new tab feature as well as the ability to cut portals up in slices have made it much faster to develope.

--sd

fmp8_sample_sd.zip

[xyz3]

Your FMP script is very cool. Nice work.

Removing AS is fine, but how do I pass a list of images located on an InDesign document back to the database to be removed/updated?

The concept is to get the fmp database to accurately reflect the state of the contents of the indesign document. For a end user - this yields an important functionality: Show me every document that has this text, or image on it. The user wants this information at their fingertips, available with a quick search, they may want to print this data, etc..

The example I posted did not show the InDesign code, in an effort to show the base problem: Every record in AppleScript == every record found in the database at that time, and this is not necessarily every record that exists in the database, and may be only a small subset of the records.

Also - in your latest example you removed the history layout - which is an important part of the fore-mentioned functionality.

There needs to be a way for the user to search, and interact with other editable fields that also exist in the history database.

[Søren Dyhr]

Can InDesign be set to execute AS while a document is saved?

Saving the document in a containerfield as .pdf doesn't make us extract the objects.

Perhaps you can attach a script to the folder where the documents temporarily are saved "Folder Actions" kind of type - so when ever something happens in the folder are the changes mirrored into the filemaker base.

http://www.apple.com/applescript/folderactions/

My idea is that you can write straight to the table sans layout, it's only required for applescriped searches - which now is solved with native scripting including the *t* (front and end truncated) searches you only thought posible with "contains".

If you monitor the temp file storage folder do you stand a chance to get rid of them as soon as everything is stuffed inside the filemaker base. Getting them back into inDesign should be done by the .pdf format - methinks?

But how did you ensure timely syncronizations under fm6?

--sd

Edited January 28, 2006 by Guest

[xyz3]

But how did you ensure timely syncronizations under fm6?

There is a button in the filemaker database that was run by the user. The changes were also inserted or updated when any text was updated on a document via a script.

I have have this code, and this method has been working fine for the last 5 years.

Following is the problem:

The example I posted did not show the InDesign code, in an effort to show the base problem: Every record in AppleScript == every record found in the database at that time, and this is not necessarily every record that exists in the database, and may be only a small subset of the records.

Also - in your latest example you removed the history layout - which is an important part of the fore-mentioned functionality.

There needs to be a way for the user to search, and interact with other editable fields that also exist in the history database.