Importing excel to related tables

[equinavas]

Hello Fm experts,

 

I've been trying to import data from an excel file to a related table on my data base for days now. All of the imported data seems to work ok but I cant populate my foreign key field to match the related field on another table.

 

I have a parent table where I keep Horse's general info like age, gender, color etc... The child table keeps reproduccion info for each horse. Every horse has a daily update of the reproductive status where I have info on ovaries, uterus etc. I'm trying to import 6,800 records where I have the daily info on each horse. I have related the Horse ID with a primary key to a Horse ID foreign key, whith a dropdown list that shows the horses name. When I import the excel sheet all data is imported correctly including the horses names, but no of the info from the parent table seems to come up in the child table. I have to manualy take each record and click on the field to get the dropdown list and select the matching name... ther must be a way to make this happen automaticly please help i've been stuck un these issue for quite a while now... Thanks to anyone who helps me.

 

 

[Wim Decorte]

Your approach looks ok so it's difficult to say what is going wrong, clearly something is.

 

After your import, do you see the proper horseID in the related table?

 

If so, double-check that the relationship is set up correctly.

 

If not, make sure that the horse ID field in the related table is not a serial # and that you don't have the "auto-enter" option toggled on for the import.

[equinavas]

The relationship is set up with a primary key field using a serial number.

 

the field is set up to display data from Fk Horse ID which is Indexed,Autoenter serial, Cant modiy Auto

Control style: Pop Up Menu

Values from: Horses ID_Name (this is a value list that uses values from table Horses _pk Horses ID and displays only second field which holds the horses name)

 

I'm not sure if the explanation is very clear please let me know if you undertand what I mean.

 

Thank you very much for your reply!!

[equinavas]

The following attachments will probably make it easier to undertand.

 

Thanks again

[Wim Decorte]

The relationship is set up with a primary key field using a serial number.

 

the field is set up to display data from Fk Horse ID which is Indexed,Autoenter serial, Cant modiy Auto

 

The auto-enter serial on the primary key (in the horses table) is ok.  But the kf horse ID in the related table should not be an auto-enter serial.  That field should be populated with valid horse IDs

[equinavas]

I missed typed. The kf on the horse ID related table is a simple text index field with no auto-enter serial. It does work when I click on the field, displaying all the values on the velue list for me to select. but as I said before, every time I import my excel info the field is indeed populated with the correct Horse name but it wont bring the  info from the parent table. 

 

thanks again for your reply

[Wim Decorte]

 but it wont bring the  info from the parent table. 

 

Can you expand on this?  Are you showing the horse's details directly through related fields on the imported layout?  Or are you actually bringing in horse daa through lookups or calculated fields?

 

Also you haven't answered an earlier question: after the import, does the kf field contain exactly the same data as what you get when you manually select something from the value list?

[equinavas]

Unanswered question: The import Items contain horse names which match the names on the parent horse parent table. When imported, the name appears on the field but it won't bring the data that corresponds to the name on the HORSE table. 

example: 

Horse Parent Table Fields

Text Field  NAME: Atomica (Indexed)

Text Field __ Pk HORSE ID: 1 (Indexed,Auto-enter Seria, Can't modify Auto) 

Text Field  Repro Status: Doner (Indexed On Parent )

Date Field Date Birth: MArch, 12 2001

 

Reproduction Child Table Fields

Text Field fK HorsesID: Atomica  (Indexed) Imported data from excel

Text Field Repro Status: NO DATA BROUGHT FROM PARENT TABLE until I click on the field and choose "atomica" fron the porp up value list

Text Field Left Ovarie: Multi Folicular (Imported From excel)

Text Field Uterus: Edema (Imported from excel)....

 

 

As you can see, alll the data imported from excel fits quite well, but I can't mannage to get the name of the horse to bring the correspond data from the horses table. 

 

I hope i made it a little bit clearer this time.

[Wim Decorte]

The relationship between Horse Parent and the Child table is on __pk HORSE ID to fk HorseID, right?

Then that's the problem.  If you don't have the numeric horse ID in the excel data then don't import the horse name into the fk field.  Import that horse name into a horse name field in the child table.

You can then create a relationship between the horse name in the child table and the horse name in the parent table and run a replace in the child table to populate the fk field in the child table with the matching pk from the parent table (match based on the name relationship).

 

This will fall flat if there are horses in the parent table that have the same name.

[equinavas]

Will try... let you know when I do it.

 

Thank you for your time and atention

[equinavas]

Already created a horse name field on the child table and created a relationship with the horse name on the parent table...how do I run a replace in the child table to populate the fk field in the child table with the matching pk from the parent table (match based on the name relationship).

[Wim Decorte]

how do I run a replace in the child table to populate the fk field in the child table with the matching pk from the parent table (match based on the name relationship).

 

Since you're going to do this on each import, best to write a script for it and use the "Replace" script step.  Make sure you are on a TO for the child table, Point it to the fk field and set it to  HorseParent::__pk

(assuming that the related TO for the parent - relationship based on the name fields - is named "HorseParent"

 

 

Remember that the Replace happens on all records in your found set so make sure you have the correct found set. There is no undo for a replace.

[equinavas]

Looks like i'm to much of a begginer, been trying for hour and can't manage to work it out! AGGGGG!!

I'll tell you what I did to see if you can help me figure out my mistakes

1. I duplicated the child table and linked my child's TO new field "horse name" directly to the horse name on the PARENT table

2. I imported my records filling the child's TO Name field with the names i had for horses in the excel file

3. I created a script to replace the value for the fkHorseID in the new child TO where i had linked the name fields

 

No replacement takes place....

[Wim Decorte]

can you post a screenshot of your graph and a screenshot of the script that does the replace?

[equinavas]

I send you the screen shots for the relationship tables and the script... I colored the tables we're actually dealing with to make it a little easier for you since the information in them is in spanish.

 

 

[Wim Decorte]

You should be running the replace in the red TOs context (so on a layout based on the red TO) and the calc would be Caballos::_pkCaballosID

[equinavas]

Still doesn't work.... There must be an easier way to match the imported excel names to the names contained in: "CaballosIDs_Nombre value list" which uses values from first field _pk caballosID and displays values from seconf field: Nombre in table Caballos.

 

If you get tired of my lack of experience or anything feel free to tell me... Sorry

[Wim Decorte]

Can you confirm that if you are on a layout based on the red TO, that you can see the related info from the green Caballos TO?

 

If you can, then there is something simple wrong with the replace.

If you can not then something is not right with the relationship.

[equinavas]

I tried the import replace thing on a Layout from the green "Reproduccion" Table... the import part goes Ok but the replace script just doesn't work.

 

 

Then I tried creating a new layout based on the red TO which is a duplicate of the "reproduccion" green one. When I import the records to the red one, all the imported data appears on the layout based on the green "reproduccion" one and the info coming from the green Caballos appears on the Red One...

With this mix up I don't even get the chance to run the replace script.

[Wim Decorte]

When I import the records to the red one, all the imported data appears on the layout based on the green "reproduccion" one and the info coming from the green Caballos appears on the Red One...

 

That's expected.   Remember that the red Table Occurrence (TO) and the green repro one point to the same underlying base table (real table).  So you can import when you are on a layout based on either the red or green repro TO.  The data goes to the same place.

 

The replace to bring in the fk of the horses though can only be run from a layout based on the red TO:  only from that context do you have the name-to-name relationship to the green caballos TO.