Relative Paths for Exports and Imports

[Steve Joiner]

It's amazing to me that I'm having so much trouble with this, but I am. So, any help is really appreciated.

 

I need to move records from one table to another. The tables are virtually identical with the same field names. In fact, I just duplicated the one table assuming it would be easy to just move them into the second table from the first. Guess I was wrong.

 

I am currently running FM 12 Advanced and sharing it with an online hosting agency that specializes in hosting FM solutions.  Right now we will have about 5 people sharing the FM solution, but eventually 20 or so.

 

I've created a script that exports the found set of records in the first table to an excel file. Then the script switches to the second table with the intent of importing the excel file.  Here is the script:

 

 

Set Variable [ $$pool_name; Value:New Media Contacts::Pool Name ]

Set Variable [ $$editor_id; Value:New Media Contacts::Editor ID ]
Set Variable [ $filepath; Value:"filewin:" & Get(DocumentsPath) & "FilemakerAttachments/Exported_Records.xlsx" ]
Show Custom Dialog [ Title: "Test"; Message: $filepath; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ] Enter Find Mode [ ]
Set Field [ New Media Contacts::Editor ID; $$editor_id ]
Perform Find [ ]

Export Records [ File Name: “$filepath”; Use field names as column names; Character Set: “Unicode (UTF-16)”; Field Order: New Media Contacts::Editor ID

New Media Contacts::Outlet

New Media Contacts::Salutation

(many more fields)

Go to Layout [ “PMC - All Fields” (Pitched Contacts) ]

Import Records [ "$filepath"; Worksheet: ""; Target: “Pitched Contacts”; Method: Update matching; Add remaining; Character Set: “Mac Roman”; Field Mapping: Source field 1 match with Pitched Contacts::Editor ID

Source field 2 import to Pitched Contacts::Outlet

Source field 3 import to Pitched Contacts::Salutation

Source field 4 import to Pitched Contacts::First Name 

(many more fields)

Go to Layout [ “New Contact Edit Screen” (New Media Contacts) ]

Set Field [ Pitched Contacts::Remove from Import; "" ]

Show All Records

Show Custom Dialog [ Title: "Remove Contact?"; Message: "This media contact has been added to the pitched contact list. Do you also want to delete it from this pool? (Are you done with it?)"; Default Button: “No”, Commit: “Yes”; Button 2: “Yes”, Commit: “No” ]

If [ Get ( LastMessageChoice ) = 2 ] Delete Record/Request

[ No dialog ]

Else
End If
Enter Find Mode [ ]
Set Field [ New Media Contacts::Pool Name; $$pool_name ]
Set Field [ New Media Contacts::Remove from Import; "Remove" ] Perform Find [ ] 

 

When I run the script I do show what $filepath is being set as and it's file win:/Macintosh HD/Users/Steve/Documents/FilemakerAttachments/Exported_Records.xlsx

 

“Exported_Records.xlsx” could not be created on this disk.  Use a different name, make more room on the disk, unlock it or use a different disk."

 

Again, I know I'm not the first to get this message and have read quite a few threads about, but I don't understand why this is not working.

 

I have to run this across multiple Macs.  My Mac account is at the admin level.

 

I would be open to trying another process to get the records from one table to another, but this seems the best and most common way. That is, unless you can't get it to work!

 

If I've forgotten anything, please let me know and I'll respond quickly.

 

Thanks!

 

 

[eos]

I really hate these complicated path syntaxes, and every time I need such a solution I have to consult my references – but even so I'd say “filewin” on a Mac doesn't sound right!

 

That being said: Is there a reason why you don't find the desired records in tableA, then switch to the target layout in tableB and import them directly, without the intermediate Excel export?

[MartieH]

I use a simpler version of this in a couple of solutions - but, I have set some global fields that the user fills out - which takes care of the differences between windows machines and macs. The first field is a drop-down - choose either filewin or filemac - the second shows up depending on which choice was made -  either volume name (for macs) or drive letter (for windows), the third is the directory name. the file path is then calculated with this information and the name of the file itself. Mind you, this does require the user to KNOW these things - which your average user typically does not. I think there are limited scenarios for such a solution. But, you do need to get the file path correct if you want to do a scripted export/import.

 

hth,
Martie

[IdealData]

 

 

Set Variable [ $filepath; Value:"filewin:" & Get(DocumentsPath) & "FilemakerAttachments/Exported_Records.xlsx" ]

 

That's definitely defining a WINDOWS path

 

 

 

When I run the script I do show what $filepath is being set as and it's file win:/Macintosh HD/Users/Steve/Documents/FilemakerAttachments/Exported_Records.xlsx

 

That's definitely a MAc filepath

 

If your solution is to be run dual platform then you will need conditional filepaths based on the platform in use.

 

See also Get (SytemPlatform)