import XML and lookup relational values

[Flagio]

Hello,

I have this (part of a) xml file of which I want to import the artist name and join.

  

    

       ABC

       Featuring

    

    

        Contessa Lady V

    

  

  The Night You Murdered Love

The difficult part is that I want to import integers as because in Filemaker I have related table Artists (ID and Name field). I haven't got a clue how I can do that.

So while importing via a xslt, is it possible to lookup the ID value of 'ABC' and 'Contessa Lady V' in the Filemaker Artist table and import the ID values only?

Any help appreciated!

Thx, Paul

[TheTominator]

If you set up the id to be a Lookup using the artist name that is imported, yes, you can do it while you import. If you only have the artist_id in the table, you would need to create a new field artist_name_import to be used solely as a tool to lookup the appropriate value of artist_id from your FileMaker table of artists.

Configure your current artist_id to auto-enter the artist_id from the other table by lookup using a new relationship.

Create a new Table Occurrence of your Artists table, e.g. Artists_Import. Match artist_name_import to Artists_Import::Artist_Name (or whatever your current field is called).

Note that the artist name in the XML file will need to exactly and uniquely match the artist name in your existing FileMaker table. (But I expect you knew that already.)

Edited March 19, 2010 by Guest

[TheTominator]

Another option is to edit the XML file to add the FileMaker value for the artist_id into the element.

If you only have a handful of artists you could do a search and replace on each artist.

Find:

Contessa Lady V

Replace with:

4

Contessa Lady V

Ideally the replacement would result in something like

Contessa Lady V

but that is harder to do with any random search and replace.

With an exported list of artist_id, artist_name from FileMaker and some script programming in a language of your choice, you could replace all of them pretty handily.

The easiest solution depends on what tools you are most handy with.

Edited March 19, 2010 by Guest

[Flagio]

Thanks for your quick answer!

I think I'm gonna use your first solution. Artist names are unique indeed, so that shouldn't be a problem. Maybe I'll think about a solution to delete the 'artist_name_import' value after import, to keep the database clean.