InaPlex Forums

Hello! I am trying to import data from a DBF file (with only DONOR_NUMB and various data fields) that is tied to a field in CONTACT2 (UWESSID) and where I need to have custom Detail records created/updated. After unsuccessfully trying different two-table combinations, I found a prior Inaport post from Cerf on 02-13-2008 dealing with _Calendar items, so I am now trying three tables:

1. _Contact2 as the top table, Match single existing record, #ACCOUNTNO as Primary Key, and match DONOR_NUMB from the DBF file to UWESSID in Contact2

2. _Contact1 as the second table, Match single, #ACCOUNTNO as Primary and Foreign key

3. CONTSUPP as third table -- child of _Contact1, Update single or create new record - single map, no match specified (since my DBF has only DONOR_NUMB and there is no appropriate matching field listed in the target) (I tried using _Details as the third table, but our custom detail data uses these fields CONTACT and PHONE, which aren't available fields in _Details...)

The script processed my 10 test rows the first time - but the data never appeared under the right ACCOUNTNO. Now the script complains of Multiple Matches, but I have queried the data via SQL and can't find any multiple matches...

(Another thing that I wonder about is that when I run it, it takes a long time before it starts processing rows: it says 'Adding Index Values to Index #ACCOUNTNO" for about 8 minutes while it counts up. How can I get it to use an existing Index?)

Thanks for your guidance!

Hi Sue

To match on a child table and then create update a parent table, you need to use the "Match for Child..." operations. This is not necessarily simple.

I think both your issues would be made easier if you used SQL matching instead of Standard matching. The SQL matching would let you query contact2 from contact1. Also, because you use SQL instead of building the index first, you do not have the upfront cost.

There is a tutorial on SQL matching in the resources section of the web site.

Regards

Admin

Thank you very much. SQL Matching is what I will use. Thanks again.

Hi Kim

SQL Matching is only available in the Professional Edition.

Standard matching will let you match on the parent _contact1 table, then match and create or update on the details table. Then reason we were talking about using SQL Matching earlier is that they needed to match on the child table (Details) then create/update the parent table. It is possible to do this using Standard matching, but it is quite a bit more complicated.

You might like to have a look at the tutorials on matchign in the Resources section on the web site.

HTH

Regards

Admin

Thanks Admin; so glad that I will be able to update contsupp under the standard version.

I reviewed the slides in the tutorial for a standard match but unfortunately can't quite figure it out.

Hi

Instead of choosing the CONTSUPP table, choose one of the logical tables that wrap the CONTSUPP: _Details, _AdConts, etc

These tables overlay the CONTSUPP table with *lots* of business logic to make the table much easier to work with. As a bonus, inaport will also fill in the relationship tab... :-)

Regards

Admin

Brilliant! Thank you so much, a great end to my week.

Kim

I do, however, feel a little daft that I didn't notice that table before. 

No problem - it is a big application, with a lot of stuff in it.

Yes, it is; and then there's GoldMine itself, and MasterMine, and Crystal Reports and SQL queries, and SQL server management and reporting services, and, and, and  ...sometimes I think I may go mad; but you've helped to stave it off for a few more nano-seconds (I'm pretty far along already).