Two fields show matching relationship in object but not in script, why?

[#1]

I am trying to identify which records Match for the two fields  SourceLinkGL and SourceLinkPPV

and which ones do not match.

The fields SourceLinkGL (transactions posted in General Ledger file) and SourceLinkPPV (transactions posted in Purchase file)

have a lot of records that match. However there are some that do not match, and the ones that dont match.

I am trying to identify which records Match for the two fields and which ones do not match.

First attempt

IF(match(SourceLinkGL,SourceLinkPPV)>0,'Match','No') as [Sourcelink Test], and all records come up as 'No' which is incorrect.

Second attempt

IF(SourceLinkGL=SourceLinkPPV),'Match','No') and the result come up 'No' for all records which is incorrect.

Third attempt

then i tried giving SourceLinkGL and SourceLinkPPV the same field name SourceLink and as you can see from the attached, when a record is selected that matches both SourceLinkGL and SourceLinkPPV are lite up in which shows matching association but the above formulas still dont show up as 'Match"  I dont understand why.

any help would be greatly appreciated.

[swuehl]

Hi Joseph,

you do a join in your script, but the common field [Source] shows always differing values for both tables (GL and PPV), so I believe you will get separate records (this is also what you see in table box or table viewer, you have multiple records for same SourceLink value, so a record based comparison of SourceLinkGL and SourceLinkPPV will not match. Never.

Hope this helps,

Stefan

[Jason_Michaelides · Accepted solution]

Look up ApplyMap() in the f1 help and see if this will do what you need. Load one file into the map and then load the second file using ApplyMap() to match whatever is the common ID field.

[#4]

Hi Joseph,

I tried to create two list box for SourceLinkGL (transactions posted in General Ledger file) and SourceLinkPPV (transactions posted in Purchase file). And I have found that for a field value in SourceLinkGL there is no value for SourceLinkPPV.

Hence, the data that has been brought for SourceLinkGL and SourceLinkPPV has to be brought based on Join or some other way that business suggests.

Hope this helps,

Bikash Debnath

[#5]

I thought i could match without using applymap, however that applymap seemed to do it and help me identify what i needed.

also swuehl advise was very helpful, because there were too many combinations, therefore, eventhogh the fileds had some matting records, their associaed records caused a "No Match" return for all.