InaPlex Forums

I have a source CSV with only one field - an email address. This list contains email addresses that do not want to receive emails from us. I want to lookup all Contact records with that email address and change the value in the DoNotEmail field in the Contacts table to "T".

I've created a Map that I think should work, but I receive an error that says "Must have a parent ACCOUNT table when creating CONTACT records". I recive this error when the operation is set to Update existing records - all matches or Update existing records - unique match.

What am I doing wrong?

Hi

You are not doing anything wrong, but Inaport insists on having the Account as a parent of Contact even for an update because of some quite complex business logic that needs to be implemented.

Two possible approaches. First uses dbexecsql()

1. add a new field in tab 3 called accountid, and initialise it with:
        dbexecsql("T", "SELECT accountid FROM contact WHERE email = '" & #myEmailField & "'")
2. use this accountid to match on account as top table, then go down to contact

Second approach uses a match on contact as top table, then account, then finally update contact:

Contact  (Operation Match single, Primary Key contactid, CHILD KEY accountid (NOTE THIS), match on email)
      Account (Operation Match single, Primary and foreign key accountid, no match criteria
          Contact(Operation Update, Primary key contactid, foreign key Accountid, match on email)

You do not need matching on the account table because there is only one account for a contact.

HTH

Regards

David

Thanks, David, but I'm getting an error.

Option #1 can only be used with the Pro version which I do not own.

Option #2 rings a bell with me from maps I created back in 2005. I've quadruple checked that my settings match what you say, but I get "Row error: No matches on table Account. To confirm:

Contact (Match a single existing record, Primary key #CONTACTID, Foreign key #ACCOUNTID, Match #email to #EMAIL)
   ACCOUNT (Match a single existing record, Primary key and Foreign key #ACCOUNTID, No Match criteria)
      Contact (Update existing record - unique match, Primary key #CONTACTID, Foreign key #ACCOUNTID, Match #email to #EMAIL)

Hi Doug

On the top contact table, you need **CHILD KEY** accountid, not Foriegn Key.

The Child key is used when you have a child table whose foreign key is not teh primary key of the parent table.

In this case, we are using Account as a child of Contact (the opposite way round from normal). The Account primary key has to accountid, and it does not have a foreign key that relates it to the Contactid of the parent Contact. We tell the Contact table to use its accountid to match to the accountid of the child Account table.

Regards

David

Child key is a new concept to me. My version of Inaport does not support Child Keys. The help file regarding Primary and Foriegn refers to Parent & Child tables. That's my confustion.

It appears I must upgrade to accomplish this task.

Thanks for the assistance.

Hi Doug

Think so. The child key stuff was introduced about 18 months ago I think

You will find V7 quite an upgrade. See here for some of teh new stuff:
http://www.inaplex.com/Products/InaportTour.aspx

Regards

David

V7 adds Child Key support and it works fine. Thanks for your help on this. I'll now have to find the time to explore the new features.

Enjoy!