importing related tables

[Barbapapa24]

Dear all,

I have a question about duplicating records.

I have two tables: ‘Sentences’ and ‘Translations’. The table Sentences consists of a set of records that have ‘original’ sentences taken from books, the table Translations consists of a set of records that have translations of those original sentences into a set of different languages.

What I actually often need to extract from the database is a data from both the original sentences and the translations. Now, I need to do that once for the Sentences table and once for the Translations table, so that is not very handy. This is a design mistake that I made when I build the database. I would like to resolve this by placing all the original sentences from Sentences into Translations, so I can suffice with one search and one extraction, etc.

I think I should be able to do this by simply importing the records from Sentences to Translations. However, I have an additional problem. There is related data from several different tables that needs to be 'imported' along with the records from Sentences into Translations. The most important of these is called Morphemes (but the same idea accounts for all of the other tables as well). I have two instances of this table in my relationship structure - one attached to the Sentences table, and one to the Translations table, both using the primary keys of the respective tables. I want the records in Morphemes that are related to the Sentences table to keep their relationship as I import them to Translations. However, I don't know how I can do this. My problem is related to the problem here:

http://filemakertoday.com/com/showthread.php/25093-Importing-with-Related-tables

And I understand it might have something to do with the Auto-enter command, but I need something a bit more specific to figure this out... can anyone help me with this?

Thank you!

[bcooney]

If you have a parent sentence for each translation record, then I believe your structure is correct.

You can easily export a child record and its related parent record. If you explain a bit more of what you need exported, I believe we can help without the need to flatten your data structure.

The worst case scenario would be creating a reporting table that is populated from several tables and then exported.

[Barbapapa24]

Hi,

yes, I believe this should be possible too, but I simple do not know how !

So, Sentences and Translations are related through the primary key of Sentences and the foreign key of translations. Each sentence record is related to several translation records in this way. I think that if I would import the sentences records, making the foreign key of the newly created Translations records correspond to the primary key of the Sentences records that are imported, each 'new' Translation record will be associated with the right parent record in Sentences (i.e., for each individual record it will related the same record that was copied - I'm sorry if this is not making sense!).

However, I don't know what to do with the child records from the Morphemes table. Each Sentence record is related to multiple records in the Morphemes table, using the primary key in the Sentences table and a foreign key in the Morphemes table. I need all the records associated with each Sentence record to still be associated with the new record when I import each Sentence record to the Translations table. Just so you know - the Translations table is also related to another instance of the Morphemes table, but via another foreign key in the Morphemes table. I don't remember why I did that, but this might be a potential problem.

So, right now I'm just stuck, I don't know how to import these child records from Morphemes - they do not show up in the Import dialog as field that I can import, and I don't know what other ways would be to import records (although I guess I could write a script).

Thanks so much!

[Barbapapa24]

Hi,

I'm sorry, I've tried several different things now and I really cannot figure out how to do this by myself... I really need some help!

Immense thanks to anyone who might be able to help!

[comment]

Why don't you provide a small example of the source data.

[Barbapapa24]

Hi,

I've attached three screen-shots that hopefully make things more clear (please don't look at my terrible lay out and cluttered relationship design...). One is the relationship structure, and the other two are of a record in 'Sentences' and the same record now imported in 'Translations'.

As you can see, with the record in 'Sentences' there is related information in a portal with the table Morphemes, it's the third portal reading 'ja', '3SG', 'wel', etc. (This is all just fantasy words made up for the purpose of this post.) In the imported record in Translations, we see that the sentence 'ja ja ja' is there, but the morphemes portal (headed by 'type of m. ori_morpheme morpheme gloss etc.') is empty. So, the relationship between the Sentence record and the Morphemes records related to it were broken in the process of importing.

I hope this is a bit clearer! Thanks!

[comment]

No, actually I am still confused regarding your purpose. It seems you want to duplicate a parent sentence, along with its children translations (and morphemes?). Or perhaps you want the new sentence to be related to the same child records as the original sentence? Not quite clear - either way, importing is not likely the best method to use here.

[Barbapapa24]

Hi!

I want the second option you mention: I want to import all my sentence records to the translations table, and the imported records should still be related to the same child records (from morphemes) as the original sentence.

I really don't know what would be the best option, so if you have some ideas I'd be happy to hear them!

[comment]

I want to import all my sentence records to the translations table

Do I understand correctly that you want to eventually eliminate the Sentences table altogether?

[bcooney]

You said, "What I actually often need to extract from the database is a data from both the original sentences and the translations."

I said, "You can easily export a child record and its related parent record. If you explain a bit more of what you need exported, I believe we can help without the need to flatten your data structure."

You haven't provided the "why."

[comment]

I am guessing here that a "sentence" is also one of its own translations - so moving it to the Translations table is probably a good idea.

The question then arises whether to keep the parent record or replace it with a self-join.

[Barbapapa24]

Hi comment and bcooney,

yes, comment is right - the sentence can be considered as one of it's own translations, so that is why I want it to be in the Translations table. I have to export all the records into a tab separated format and run scripts on them quite often, and it's just a hassle to export from two tables instead of one. Another reason for the import is that I want to change some things to the database, add some fields etc., which need to be added for both the sentences and the translations. It doesn't make sense to do things twice, so I'd like to make those changes in the translation table and have the new fields with all the records I need right there in that table.

However, I do not want to eliminate the Sentences table altogether - it has several purposes, first of all creating the relationships between the different translations records through a unique ID field in that table, and second because I use it as a starting point for data entry.

I hope this helps!

[comment]

We need some names here. Let me assume these relationships:

Sentences::SentenceID = Morphemes::SentenceID

Sentences::SentenceID = Translations::SentenceID

Translations::TranslationID = Morphemes 2::TranslationID

Now, instead of importing, try something like:

Go to Layout [ Sentences ]

Show All Records

Go to Record [ First ]

Loop



# CREATE A NEW TRANSLATION

Set Variable [ $sentenceID ; Sentences::SentenceID ]

Go to Layout [ Translations ]

New Record

Set Field [ Translations::SentenceID ; $sentenceID ]

Commit Records



# TRANSFER THE SENTENCE CONTENTS TO THIS TRANSLATION:

Set Field [ Translations::Sentence ; Sentences::Sentence ]

...



# ASSIGN THE SENTENCE MORPHEMES TO THIS TRANSLATION:

Set Variable [ $translationID ; Translations::TranslationID ]

Go to Layout [ Sentences ]

Replace Field Contents [ Morphemes::TranslationID ; $translationID ]



Go to Record [ Next; Exit after last ]

End Loop

Make sure you have a backup before trying this.

[bcooney]

I understand the need to collapse Sentences and Translations ( as you say, a Sentence is actually just one of the translations). However, "I have to export all the records into a tab separated format and run scripts on them quite often" ...for what purpose?

[Barbapapa24]

Hi comment and bcooney!

Thanks for that script outline, I really think something like that should work! It makes a lot of sense. However, for some reason it doesn't... I've attached a print-screen of one version of your outline. For some reason, even copying the content of sentence fields into the newly created Translation record doesn't work, and I don't understand why. The morphemes don't get transferred either... The relationships that you assumed are the ones that are present in the database.... so I'm unsure why it is not working!

As for bcooney's comment, I work on an phylogenetic analysis of encoding systems in the various languages included in the database. Most of the time, I export the data to a tab separated format to aggregate it and do analysis on it in R. Stuff you cannot really do inside Filemaker !

I really hope I've made some stupid mistake that will be easy to point out...

Thanks so much...!

[comment]

Well, the Commit Records[] step is a bit misplaced, but I am not sure that's the real issue; are you sure you are using the correct TOs?

[Barbapapa24]

Yes, the table occurences are fine - I've just taken out the bit about the Morphemes to see more clearly what it is doing, and this version makes one correct copy from Sentences to Translations (the first record), and then stops...?

[comment]

You don't come back to the Sentences layout.

[Barbapapa24]

Aha, you were right of course! Now it works so far in that correctly imports the sentences, but the morpheme part is not working.

I'm wondering whether it would be a good idea to make this work with a specific loop again, going through all the different morphemes associated with each record from sentences and then creating new records from them (duplicating them in the same table I guess, but I wouldn't mind!)?

Or is there something wrong with the script in this respect?

[comment]

We don't see your file and you are using a naming scheme different from the suggested one.

If "Morphemes" is the name of the TO related directly to Sequences, then after the Replace Field Contents[] step all the morphemes of the current sequence should have the ID of the new translation record in their fk2_morpheme_ID field.

And if the fk2_morpheme_ID is used as the matchfield to Translations, then these morphemes should now be related to the new translation record.

[Barbapapa24]

You are right, of course: I had the wrong table occurence for the Morphemes table. Sorry!

We seem to be on the right track: the current version (see attachment) correctly imports the sentence records, and copies all of the morphemes related to them into the last record that is copied. So I think we miss a step somewhere...?

[comment]

Why? What else is there to do?

[Barbapapa24]

Sorry for not being clearer: the script now relates ALL the morpheme records (all the Morphemes records present) to the LAST record that is copied from Sentences to Translations; instead of relating each Morpheme record that was associated with each copied Sentence record to each of the newly copied record in Translations. So I think we need another step somewhere to make sure the latter and not the former happens?

[comment]

Well, that only shows you are still not following my original outline (in post #13). The Replace Field Contents[] steps needs to address the TO of Morphemes that is related to Sequences by SequenceID (and therefore exposes only the morphemes related to the current sentence).

[Barbapapa24]

You are right and I understand what you are saying, but I am unsure how to implement this in the script...

Your step:

Replace Field Contents [ Morphemes::TranslationID ; $translationID ]

Does sort of conform with

Replace Field Contents [ OriSentMorphemes::fk2_morpheme_ID; $translationID ]

because OriSentMorphemes is the table which is connected to the Sentence table, but the ID used (fk2_morpheme_ID) is used for the connection between the Morphemes and the Translation table. So I understand why I got that result, but I'm not sure what to do about it:

Replace Field Contents [ OriSentMorphemes::fk1_morpheme_ID; $translationID ]

doesn't result in a viable relation between the Translations table and the Morphemes table.

So, sorry, I understand the problem but I don't know what to do about it... Maybe go to the morphemes table and then perform the Replace Field Contents...?

[comment]

OriSentMorphemes is the table which is connected to the Sentence table

Is connected by SentenceID?

the ID used (fk2_morpheme_ID) is used for the connection between the Morphemes and the Translation table.

This part makes no sense. The relationship between Morphemes and Translations should be based on matching the TranslationID. And that is the field you should be populating by the Replace Field Contents[] step.

[Barbapapa24]

Sorry about all the confusion...thanks for not giving up on me!

Yes, OriSentMorphemes is the table which is connected to the Sentence table connected by SentenceID (see below).

Yes, it is also true that the relationship between the second instance of the Morphemes table (let's call it TransMorphemes) is connected to the Translations table via the Translation ID, but through a different foreign key:

Sentences - pk_sentence_ID - fk1_morpheme_ID - Morphemes

Translations - pk_translation_ID - fk2_morpheme_ID - Morphemes

I think my problems arise from this unlucky construction somehow... and I think somehow I need to fill the fk2_morpheme_ID field to make the relationship with the newly created Translation records work...

I'm not sure whether I've explained the confusion, please do let me know...

[comment]

I am sorry - there is a mistake in my script - here is the corrected version of the post:

Assuming the following relationships:

Sentences::SentenceID = Translations::SentenceID

Translations::TranslationID = Morphemes::TranslationID

Sentences::SentenceID = Morphemes 2::SentenceID

Try this script:

Go to Layout [ Sentences ]

Show All Records

Go to Record [ First ]

Loop



# CREATE A NEW TRANSLATION

Set Variable [ $sentenceID ; Sentences::SentenceID ]

Go to Layout [ Translations ]

New Record

Set Field [ Translations::SentenceID ; $sentenceID ]

Commit Records



# TRANSFER THE SENTENCE CONTENTS TO THIS TRANSLATION

Set Field [ Translations::Translation ; Sentences::Sentence ]

...



# ASSIGN THE SENTENCE MORPHEMES TO THIS TRANSLATION

Set Variable [ $translationID ; Translations::TranslationID ]

Go to Related Record [ From: Morphemes 2 ; Show only related records ]

Replace Field Contents [ Morphemes::TranslationID ; $translationID ]

 

Go to Layout [ Sentences ]

Go to Record [ Next; Exit after last ]

End Loop

Make sure you have a backup before trying this.

[comment]

Here's a demo file with proof of the concept.

ConvertMorphemes.zip

[Barbapapa24]

Hi comment - thanks so much for creating the database and the script, it works really well - I have the same script, the same relationships, and it just doesn't seem to work! I really don't understand why... I'm posting the relationships and my version of your script again - hopefully it's just some glaring stupid thing I am just not seeing - (really sorry to be so tedious..).

[LaRetta]

You should include a warning label before showing an image of a graph that looks like that. I almost fainted!

It is helpful to color the few table occurrences you are discussing and/or close all the other table occurrences and move the 'working ones' away so we can clearly see the connections. There are so many lines that identifying the keys is nearly impossible.

But even so, why does your Translations table have a pk_translationID AND an fk_translationID? Why is not called fk_sentenceID? So you have Sentences::pk_SentenceID = Translations::fk_translationID.

Also, we can not even see the connections to Morphemes but … you have two Morpheme IDs in your Morpheme table. And it appears that you have Morphemes::fk2_morphemeID connected to Translations::pk_TranslationID but I am not sure.

I suggest that you clean up the graph, pull apart and compare item by item to Comment's file. IDs should be matched to same-name IDs in other tables so you can guarantee you have them matched properly. It is possible this is your problem but I am afraid I cannot tell.

If you wish to post your file, I will be happy to try to help you figure it out. :laugh2:

[Barbapapa24]

Attached are a cleaned up relationship graph and the final script : it works!

There is one interesting quirk though: if there is a set of sentence records which has no morpheme records associated with it, the last of those records gets associated all the morphemes associated with the translations. This is not a problem in real life, as all sentence records have morpheme records associated with them (there are no 'missing' translations in the Sentence table), but it was a problem while testing as I kept wondering what was going on with that!

I'm going to try it on my real database now, thanks very very very much, comment and the others!!! You're the best!

[comment]

This is not a problem in real life, as all sentence records have morpheme records associated with them

If there is the slightest chance of a "widowed" sentence, you should test for this before doing Go to Related Record[]; otherwise you'll be staying in the Translations layout and Replace Field Contents[] will be addressing the wrong TO.

...

# ASSIGN THE SENTENCE MORPHEMES TO THIS TRANSLATION

If [ Morphemes 2::SentenceID ]

Set Variable [ $translationID ; Translations::TranslationID ]

Go to Related Record [ From: Morphemes 2 ; Show only related records ]

Replace Field Contents [ Morphemes::TranslationID ; $translationID ]

End If

...