Solved

Export Filemaker Container

Posted on 2011-03-09
13
1,397 Views
Last Modified: 2012-05-11
Want to export Filemaker database to a new MySQL database.  FM records include both data fields and container fields.  Problem:  Filemaker's export command for container fields is separate from the data export command, essentially separating the two.  With 60,000 records... some which have no container documents and others having up to six container documents... I need a way to "assign" a unique label or serial number to the exported container files to later match them with the appropriate records.  The container files have no consistent file labels either, so matching them to the records (once separated) need to be automated.  Is there a solution?
0
Comment
Question by:rodneyhalltalon
  • 7
  • 6
13 Comments
 
LVL 24

Expert Comment

by:Will Loving
ID: 35087356
Create a looping script that goes through each record and within each record checks each container field for contents using:

If [ not isempty( ContainerField1 ) ]
   Set Variable [ ( $FilePath ; Value: "xxxxxx" ]
   Export Field Contents [ ContainerField1 ; $FilePath ]
End If

The main challenge is going to be setting the $FilePath variable for which I entered "xxxxxx" above but which would need to be a valid filepath for FileMaker to use. Assuming that each of your 60K records has an SerialNumber or unique ID, I would probably start with that in the name and then something else to identify it like the name of the field it came from.

The variable path could be something like:

Case( abs( get( systemplatform ) ) = 1 ;  "filemac:" ; "filewin:" ) & Get ( DesktopPath ) & "/ExportedFolder/" & Table::ContactID & "_01.jpg"

The first part just checks to see what OS you're using. You would need to change the 01 to something different for each container field.
0
 

Author Comment

by:rodneyhalltalon
ID: 35096792
Thanks, willmcn.  I'll give it a try and let you know if I run into problems.  Yes, each record has a unique ID # assigned by Filemaker, which helps.  
0
 
LVL 24

Expert Comment

by:Will Loving
ID: 35097274
Here's something that might help you with naming. If the contents of the container field was placed there using the "Insert..." command rather than via drag-and-drop, you can get the name of the original file using the calculation GetAsText( ContainerField1 ). If FileMaker has a name stored, it will list it, otherwise it will show a question mark.

So, if you want to have the images retain the same name then you can alter the calculation above to use the name if it's available:

Case( abs( get( systemplatform ) ) = 1 ;  "filemac:" ; "filewin:" ) & Get ( DesktopPath ) & "/ExportedFolder/" &

Case(
not isempty( getasText( Table::ContainerField1 ) ) and getasText( Table::ContainerField1 ) ) <> "?";
getasText( Table::ContainerField1 ) ) ;
Table::ContactID & "_01.jpg" )

This checks the container field to see if a name exists and then uses the name if it's available. If not it uses the default name. Keep in mind that unless you know that all your images are JPEGs, that the default name file extension may be wrong.
0
 

Author Comment

by:rodneyhalltalon
ID: 35098837
Getting confused when I get to the variable path part.  My script exports the container contents as instructed by does not rename the file to the record ID.  Not very skilled at script writing beyond simple commands; maybe you can help translate if I provide more details.  For now, we'll leave out the loop and deal with a script for one record at a time.  Once that proves to work, I'll go back and make it a looping script to do a volume update.  Here's what I'm working with:  

Mac OS

Container field in our database is labeled (candidates_files::file)

Unique ID for each record is labeled "zk"

Want to rename all container exports with their unique ID ("zk") and then export them to a folder titled "Container Exports".  No need or desire to retain their original name from the Insert activity.  

Records with multiple container exports might need to have a subset attached.  If record 11222 has two container contents, they would be saved in the folder as 11222_01, 11222_02, etc.  

0
 
LVL 24

Expert Comment

by:Will Loving
ID: 35099156
Here's what it would look like though you can leave the loop off for initial testing. It checks each container field in turn, candidate_files::file1, candidate_files::file2, candidate_files::file3, etc. Keep in mind that since you are choosing to ignore the saved file name in FM, you must name the files including the file extension. if one of the files should have a file extension other than the one you specify (I used .jpg below) then it will have the wrong extension and may not open properly. I suggest that once you get this running on a single record that you try other records to see whether the results are as expected.


Go to Record/Request [ First ]
Loop

If [ not isempty( candidates_files::file1 ) ]
   Set Variable [ ( $FilePath ; Value: "filemac:" & Get ( DesktopPath ) & "/Container Exports/" & Table::zk & "_01.jpg" ]
   Export Field Contents [ candidates_files::file1 ; $FilePath ]
End If

If [ not isempty( candidates_files::file2 ) ]
   Set Variable [ ( $FilePath ; Value: "filemac:" & Get ( DesktopPath ) & "/Container Exports/" & Table::zk & "_02.jpg" ]
   Export Field Contents [ candidates_files::file2 ; $FilePath ]
End If

If [ not isempty( candidates_files::file3 ) ]
   Set Variable [ ( $FilePath ; Value: "filemac:" & Get ( DesktopPath ) & "/Container Exports/" & Table::zk & "_03.jpg" ]
   Export Field Contents [ candidates_files::file3 ; $FilePath ]
End If

<Add more repetitions of above if you have more container fields>

Go to Record/Request [ Next ; Exit After Last ]

End Loop
0
 

Author Comment

by:rodneyhalltalon
ID: 35099623
Getting "Table cannot be found" alert when entering the Set Variable
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 24

Expert Comment

by:Will Loving
ID: 35099927
I don't know what your tables and relationships look like so I was using what you had entered, candidate_files::file for the container field and Table::zk for the Contact ID.

Before creating the variable, make sure you are on a layout that is based on the table containing the fields you want to export. Then go to the Set Variable, click "Specify" and the table listed on the top left should be the correct one to start from. Select the first container field and use it's name in the Set Variable calculation string. You'll need to use the correct field name for each subsequent field that you want to export from.

Similarly, you'll need to select the correct field for the unique Contact ID field part of the string. If your table isn't name "Table" which is what I think you had written earlier, it needs to have the correct name. For me, that might look something like "Contacts::zk_ContactID" but yours will be different based on the table and field name.
0
 

Author Comment

by:rodneyhalltalon
ID: 35100138
Thanks for hanging in there with me.  I'll try that and let you know.  
0
 
LVL 24

Expert Comment

by:Will Loving
ID: 35100223
Getting paths correct can be tricky. You might want to create a calculation field that has just the path calculation that you are using in the variable and put in on the layout with the images. That way you can see exactly what is being calculated and whether there are extra or missing forward slashes (often a problem).

Just focus on getting one field to export properly. Once you get one the rest will be easy and then you can wrap it in a loop.
0
 

Author Comment

by:rodneyhalltalon
ID: 35100677
OK... here's where things stand.  First, the correct table for both the unique ID (zk) and the container file (file) is candidates_files.  Note:  the container fields are not designated as container 1, container 2 etc.  They're simply added as more documents are Inserted.  The candidate_files table displays only one "file" in the menu.  Using that, here's what I put together and nothing is exported to the target folder (Container Exports).  PS:  made certain I was in a record with one container content.  

Go to Record/Request/Page [First]
If [ not isEmpty (candidate_files::file)]
Set Variable [$Filepath; Value: “filemac:’ & Get (DesktopPath) & “/Container Exports/” & "candidate_files::zk"
Export Field Contents [candidate_files::file; “Container Exports”]
End If
0
 
LVL 24

Accepted Solution

by:
Will Loving earned 500 total points
ID: 35102044
Ah! I understand, the container fields are in a related table that is just for holding the files. I was imagining them as being multiple fields in fields in the same record rather than multiple records related by the same "candidate" record. So does the routine you've written work?

The only problem you will have with the above scenario is that if you have multiple files for the same "zk" ID, then each file will  have the same name and willreplace the previous one and/or produce and error when it tries to do so. You need a way to differentiate the individual candidate_file records. If these records have their own serial numbers then that could be added to the file name string.

Another way to do it would be to sort the records by the "zk" field and then include in the loop a check to see if that field has changed. It might look like this:

Sort Records [ (Sort by "zk" field ) ]
Go to Record/Request/Page [First]
Set Variable [ $ContactID ; Value: candidate_files::zk ]
Set Variable [ $Counter ; Value: "001" ]

Loop

If [ not isEmpty (candidate_files::file)]
    Set Variable [$Filepath; Value: “filemac:’ & Get (DesktopPath) & “/Container Exports/” & "candidate_files::zk" & $Counter )
    Export Field Contents [candidate_files::file; “Container Exports”]
End If

Go to Record/Request/page [ Next ; Exit after Last ]
If [ $ContactID = candidate_files::zk ]
    Set Variable [ $Counter ; Value: $Counter + 1 ]
Else
    Set Variable [ $ContactID ; Value: candidate_files::zk ]
    Set Variable [ $Counter ; Value: "001" ]
End If

End Loop

You'll notice that I've changed the file name to include the $Counter value. The steps after the Go To Record [Next] check to see if the ContactID is still the same and either increments $Counter by one or resets both variables.
0
 

Author Closing Comment

by:rodneyhalltalon
ID: 35103208
I'm a novice so the solutions took awhile to connect due to my lack of knowledge and terminology.  Really appreciated the diligence on the expert's side.
0
 
LVL 24

Expert Comment

by:Will Loving
ID: 35110680
I was looking at the code and realized that the $FilePath calculation above would contain an extra forward slash after the Desktop Path so you'll need to remove that from the calc string.

I've created a sample file that shows the export for one record and the looping export for all records. The $FilePath variable contains the code that checks Get(SystemPlatform) and uses the correct heading for Windows or Mac. It also uses the saved file's name if it's available or a generic name if not. You edit that part of the calculation if you wish to only have generic file names.

Finally, the path assumes that there is a folder on the Desktop called "Container Exports". It does not create the folder so if it doesn't exist you'll get an error.
Container-Field-Export.fp7
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now