Filtering a field

[Andycal]

Now, I'm sure this is going to be one of those "hits me in the face" things, but it's got me baffled.

Field one has a list like this:

AL
XLDLS
XMISC
XMOD
XTNGS

Field two like this:

AL-4017,,,0,-2,AL,,2007,50.85,21.36,4017 FISH TAIL BURNER,,0,1,0,0,L11,,,,,17-Jan-08,1013,DISCONTINUED,0,0,AL,1013,,,,
AL-4018,,,0,0,AL,,2007,34.4,10.43,4018 FISH TAIL BURNER,,0,0,0,0,L11/6,,,,,18-Apr-00,1013,DISCONTINUED,0,0,AL,1013,,,,
AL-4BURGAS,,,0,0,AL,0,41 1/2,1250,832,4 BURNER GAS FORGE LPG,DREVER UK,0,0,0,0,,,,,,10-Apr-00,99999,,0,0,AL,99999,,,,
XTNGS1999,,,0,0,XTNGS,MILD STEEL,,118.75,0,"42 OAL HOLLOWBIT TONGS""",ASSAY OFFICE,0,0,0,0,,,,,,,99999,,0,0,XTNGS,99999,,,,
XTNNGS-102,,,0,0,XTNGS,,,56,28,6FT LONG S16 TONGS,ARCO,0,0,0,0,,,,,,,99999,,0,0,XTNGS,99999,,,,
XTNSG-1376,,,0,0,XTNGS,,,48,0,"30 X 1/2"" BOX TONGS""",HALLADAYS DROP FORGINGS LTD,0,0,0,0,,,,,,,99999,,0,0,XTNGS,99999,,,,
XTNSG-1385,,,0,0,XTNGS,,,82.75,0,"5 FROGMOUTH TONGS TO SAMPLE""",NIAGARA LASALLE,0,0,0,0,,,,,,,99999,,0,0,XTNGS,99999,,,,
XTNTS-1166,,,0,0,XTNGS,99,99,22.15,11.07,"FORGING TNGS 27OL 41/2""X11/4""""",JOHN STOKES,0,0,0,0,,,,,,,99999,,0,0,XTNGS,99999,,,,

I'm trying to get all the lines in field two removed if they begin with any of the patterns in field one. So, for example, after processing the above, field two would have:

XTNNGS-102,,,0,0,XTNGS,,,56,28,6FT LONG S16 TONGS,ARCO,0,0,0,0,,,,,,,99999,,0,0,XTNGS,99999,,,,
XTNSG-1376,,,0,0,XTNGS,,,48,0,"30 X 1/2"" BOX TONGS""",HALLADAYS DROP FORGINGS LTD,0,0,0,0,,,,,,,99999,,0,0,XTNGS,99999,,,,
XTNSG-1385,,,0,0,XTNGS,,,82.75,0,"5 FROGMOUTH TONGS TO SAMPLE""",NIAGARA LASALLE,0,0,0,0,,,,,,,99999,,0,0,XTNGS,99999,,,,
XTNTS-1166,,,0,0,XTNGS,99,99,22.15,11.07,"FORGING TNGS 27OL 41/2""X11/4""""",JOHN STOKES,0,0,0,0,,,,,,,99999,,0,0,XTNGS,99999,,,,

"Filter without" doesn't seem to work, I'm guessing because they're not seperate words.

Help much appreciated!

[Klaus]

Hi Andy,

"filter without" DOES work, when used correctly!

I did this:

Code: Select all

on mouseUp
  put fld 1 into tFilter
  put fld 2 into tContent
  repeat for each line tF in tFilter
    filter tContent without (tF & "*")
   ## the * is necessary!
  end repeat
  put tContent into fld 3
end mouseUp

Et voila, got exactly what you want in field 3!


Best

Klaus

[Andycal]

Just tried that and it appears to have deleted a few more lines than I expected, but it could just be me!

I'll have a closer look...

[Klaus]

Hi Andy,

my script will exclude all lines that begin with one of these strings:
AL
XLDLS
XMISC
XMOD
XTNGS

Is that not what you want?
You will however have more control with a nested "repeat for each line tLine..." loop, if neccessary!


Best

Klaus

[Andycal]

Hi Klaus,

Right, I had a dig through and found some oddities. For example, the code "XTNGS" is in the list of ones to be filtered out, but it's also filtering out "XTNSG" and "XTNTS".

I'm guessing this is something to do with the wildcard at the end? Maybe?

[Andycal]

AHHH!! Found it, my bad, the string appears at the end of the line, it's working perfectly!!!