Import / Export Workflow issue with Excel

[AdamZ]

I just started working with FileMaker 11, so I'm still trying to figure out the proper technique for many things...

The DB: I'm working on a application/interview database. The main table contains applicant info and the other table contains interview data.

Problem 1: Interview data will be populated into an excel template. Having these users direct input into FileMaker is not an option. When I import an interview, the foreign key to tie the interview to an applicant does not populate. I set up the field as lookup based on an auto-entry serial number field in the applicant table.

Problem 2: To work around the issue above, I thought I could export data from filemaker and use a vba macro to populate the excel template. This way, I could export the foreign key, hide it and have it when the data comes back to import. However, FM exports the entire table, not just a single record. Is it even possible to export a single record?

Any help / advice would be greatly appreciated!

[comment]

This part is not quite clear:

I set up the field as lookup based on an auto-entry serial number field in the applicant table.

A lookup works over a relationship. If there's no foreign key in the Interviews table, what does the relationship use in order to find a matching record in Applicants?

Is it even possible to export a single record?

Filemaker exports the found set. In order to export a single record, make it the only record in the found set.

[AdamZ]

This part is not quite clear:

A lookup works over a relationship. If there's no foreign key in the Interviews table, what does the relationship use in order to find a matching record in Applicants?

Right you are ... What I want to happen is for a user to be looking at an applicant record that has a serial id. Then when they import interview information to the interview table, the foreign key in that table gets populated by the serial id from the applicant table for the currently viewed record. What FileMaker seems to be doing now is uploading the interview record with no regard for which applicant record is active.

I will play around with making the active record the found set for export...

[comment]

The "currently viewed record" is a non-entity when you are dealing with a relationship. If you want the imported interviews to auto-enter a certain ApplicantID, you must place this value in a global field or a variable, and set the foreign key to auto-enter that (or, if the import is scripted, you can replace the field contents immediately after import).

[AdamZ]

I don't know why, but I'm still having issues with this after banging my head against the wall all day.

The layout is based on candidate info. Each tab has interview information in a different table. I want to upload a record to populate the interview info, but I can't get it to associate with the correct foreign key. I can get the record to upload, but it just overwrites the first record in the interview table (which is associated with a different applicant id). Using the script to populate the foreign key only creates a new blank record - but the upload still overwrites the first existing record.

I think at least part of the problem is that I'm working with data from 2 tables on one layout.

[comment]

I don't follow your description. Does the imported file have an ApplicantID? If not, do you (or whoever is importing the file) know which applicant it belongs to?

[AdamZ]

the applicant ID is not on the incoming record - it's only used as a key in the database and has no real meaning. the idea was that the user would navigate to an applicant, click a button, and upload interview results that would map to that applicant. As a workaround, I've created an export button on the application, an import macro in excel to bring in the applicant ID, an export macro in excel to create a .csv file in the correct format for FileMaker to accept, and finally an import button in FileMaker. That's a lot of manual steps for a user to screw up, however.

[comment]

the idea was that the user would navigate to an applicant, click a button, and upload interview results that would map to that applicant.

That's possible using the method I mentioned earlier. Roughly:

Set Variable [ $applicantID ; Applicants::ApplicantID ]

Go to Layout [ Interviews ]

Import Records

Replace Field Contents [ Interviews::ApplicantID ; $applicantID ]

But you still depend on the user matching the imported file/s to the correct applicant. A better option would be to pre-create the Excel document by exporting as XML with a custom XSLT stylesheet to produce a Excel 2002/2003 XML file.

If you had the Advanced version, you could give the interviewers runtimes instead of spreadsheets - that would likely be the best way to go.