Importing Into a sub table, need to match index id

[advancedtekniqs]

I'm importing an excel file in FileMaker and am having a tough time trying to figure out how to get two fields to relate.

I have a Policy Records Table and a notes table related by index id and policy id.

What I'd like to know is, how do I auto enter the index ID by looking up the policy ID?

The excel file I have has two columns:

Policy ID and Notes

If I import the records into the notes table, they don't display in the portal since it doesn't have the corresponding index id.

Maybe its easier to see what I'm trying to do so I've attached an example file and also an example .xls file

Any help would be greatly appreciated, thanks!

You can login using

username - admin

pw - g0ne

example.zip

Edited October 1, 2007 by Guest

[aldipalo]

Your relationship should be:

PolicyID = fk_PolicyID

Not

IndexID = fk_IndexID

PolicyID = fk_PolicyID

I imported the list that way and all policy numbers matched up. You also want to show the Policy Holder::PolicyID and the page will be correct, I believe.

HTH

The Note is related to the policy, not the Customer in your example. You have a one to many relationship with customer to policy. So you can have one customer with many policies. You can also have one customer to many notes not related to policies so you can have another TO for notes related to customer. i.e. Called to sell new policy, etc.

[advancedtekniqs]

Thats how I had my relationships before and it was working fine in production until I started noticing a glitch with that type of relationship.

The problem is, we don't issue policy IDs right away so a policy holder can have notes entered before his policy ID is issued...and if you then later enter in a Policy ID, the notes disappear with it. Or say, someone enters in a typo for a policy id and it doesn't get noticed until 2-3 months later when there are a stack of notes, when they change the policy id...the notes don't get related.