Exporting Value List Values To Excel, Not Ids

[erinreid]

I have a Person table. Associated with that Person table is an Instrument. The data for instruments are held in a separate Instrument table. There are two fields in the Instrument table: Instrument ID and Instrument Name. Of course, the Person::Instrument field is related to the Instrument::Instrument ID field.

When displaying layouts of the Person table, I associate an instrument value list with the values from Instrument::Instrument Name, so that the appropriate instrument name shows up in the Person layouts, and not the number from Instrument::Instrument ID. Basic, right?

The problem is when I export to Excel. When I export records from the Person table, I get the Instrument ID number that's stored in the Person table, and not the Instrument Name thats associated with it.

How should I arrange things so that the Instrument Name shows up upon exporting the data to Excel?

Thanks in advance for any responses.

- Erin

[bcooney]

Each person is assigned only one instrument, right? (If not, you need a join table).

Also, in People, you should have _kF_InstrumentID, and that is formatted with a popup menu using a value list from Instruments (ID and Name, showing only the second field). Is that what you have?

So, export the related peo_Instructments::Instrument Name field (this relationship is by Instrument ID).

[erinreid]

Each person is assigned only one instrument, right? (If not, you need a join table).

Also, in People, you should have _kF_InstrumentID, and that is formatted with a popup menu using a value list from Instruments (ID and Name, showing only the second field). Is that what you have?

So, export the related peo_Instructments::Instrument Name field (this relationship is by Instrument ID).

Yes, this is exactly what I have. Each person is assigned just the one instrument through the Instrument field. (I join another field in the Person table to the Instrument table; I'm wondering if that's the problem?) The Pop-Up field in the Person display layout displays a value list of all of the Instrument names, though of course the ID numbers are what is stored in the Instrument table. But when I do exactly what you say, my Instrument Name export field is blank.

Now I'm suspicious, though, that it's the other field causing the problem. I have another field in the Person table, Audition Instrument, which also links to the Instrument table. I'm betting that FileMaker Pro is confused and then doesn't know which Instrument I'm talking about when I try to export. And that I probably should have used a join table, with a third field that lists what category that instrument is. Does that sound like I'm on the right path, or am I missing something else?

Thanks for your help for a newbie. I'm a long-time SQL developer, and I'm used to being able to handle all of this on the logic side, even with a less-than-ideal database design. Now with FMPro I'm realizing how critical getting the database design right in the first place is.

Thanks,

Erin

[comment]

I have another field in the Person table, Audition Instrument, which also links to the Instrument table.

You need to have two relationships between the two tables, then:

People::InstrumentID = Instruments::InstrumentID

People::AuditionInstrumentID = Instruments 2::InstrumentID

Note that an additional occurrence of the Instruments table is required.

When you export from the People table, include the field Instruments::InstrumentName in the field order. If you want to export the audition instrument instead (or in addition), use Instruments 2::InstrumentName.

I'm betting that FileMaker Pro is confused

I would bet on another horse... :B

[erinreid]

Yep, you were right, thank you! It worked like a charm.

This is just hard changing my mental approach. I appreciate the help getting me on the right path.

- E

[dztrucktion]

Is it possible to accomplish this when you have multiple values in the value list in a checkbox format and have no join table?

I also don't want the ID's, only the definitions exported.

[comment]

Could you be more specific? This was quite a long time ago AND your own situation is not described very clearly.