Parse field and compare results to another field

[wendykdt]

OK, I've batted this around for a couple of days, with the grand result of a lot of bits-and-pieces ideas, but no idea how to put it all together. Too much of a newbie, I guess. Here's the problem.

In File 1, I have a field called "EIN". It contains data such as GKL00361-6, GBV358.101, GDN2345-67. In File 2 there is a field called "ECO", and it has data such as 00361-6, 358-47, and 2344-67.

An import from File 2 to File 1 can proceed if the "root" of the number matches in both records. The root consists of the numbers to the left of the hyphen (or dot, in a few cases), excluding any letters.

I already have an extensive script that handles the import. It does not/cannot use a match field, so the user manually chooses the correct record to import to/from. I just want a way to double-check their choices by comparing the "root" of both numbers.

I dinked around with a lot of functions and don't really know how to combine the correct ones or how to incorporate them into the script. I did figure out a calculation of

Middle (EIN; 4; Length (EIN) -3)

which gets rid of the initial letters. I plopped that result into a global. I think I could find the position of the hyphen or dot and then remove whatever is to the right of that. (I wasn't too happy to discover that LeftWords didn't work because of the presence of numbers in the data!) But how/where do I create a calculation that "on the side" parses two fields in two different files and compares the results? Within a script run from File 1?

Would someone get me started with a few clues?

[Fenton]

Let ( [

pos_dash = Position ( EIN ; "-" ; 1 ; 1 );

pos_dec = Position ( EIN ; "." ; 1 ; 1 );

Left_EIN =

Left ( EIN; Case ( pos_dash; pos_dash; pos_dec; pos_dec; Length (EIN) ))

];

Filter ( Left_EIN; "0123456789" )

)

This will also handle an EIN without a dash or decimal, if that ever happens. It "favors" the dashes; hopefully you never have a dash AFTER a dot. If so, you could write it so it uses the lesser position.

Edited April 25, 2007 by Guest
dashes AND dots

[wendykdt]

Man oh man, the things I don't know... Wow. I had to stare at this and pull it apart and review the syntax for every function used, but I think I get it. In particular I've never seen "Case" used this way before. Neat.

So this calculation will get me the "root" of the EIN field, and I can do the same for the ECO field. And it sounds like I just do this all "on the fly" within the script by plopping these calculations into an "IF" script step:

If

(Calculation for EIN field = Calculation for ECO field)

Proceed with import

Else

Show user error dialog and halt import

Do I really not need any globals or actual calculation fields? I gotta go try this. What fun. Thank you sir!

(Note: An EIN field without a dash OR decimal is unlikely to happen, but given human capacity for error, you never know. I AM pretty sure that an instance with both a dot AND a dash in the same number would not occur.)

[wendykdt]

Worked beautifully!!! One more question please...?

My group of scripts to accomplish this import first makes sure that the found set in File 2 contains only the record to be imported. It also makes sure that the found set in File 1 contains only the record over which the imported record will be written.

Then the import script does the import, including a prior IF step that incorporates FentonJones's calculation. (Basically it compares the results of a calculation to isolate the root of the EIN field in File 1... to... the results of a calculation to isolate the root of the ECO field in File 2.) However, I found I had to first set the contents of the ECO field in File 2 to a GLOBAL (then compare gECO to EIN) in order to get the comparison to work properly.

It's not a big deal to use the global, as long as it works, but there is apparently something missing in my understanding of Filemaker, because I didn't realize I would need to do this. Why couldn't Filemaker do the one-to-one comparison, without a global ...as long as both files only had one record in their found sets?

If anyone wants to take on the task of enlightening me, I would appreciate it... thanks.