I have a real mind bender for me...and hopefully someone can help. This question might be more appropriate in the "scripting" forum but I thought I would start here.

I have a DB I am developing where I have to import thousands of records from an Excel spread sheet. The data looks (kind of!)looks like this from excel: Top row = header info.

mach ejid loca NEED#

123A 9876 MACH 00001

123A 9876 MACH 00015

123A 9876 CARO 00013

122B 1234 MACH NOCHG

123A 5678 CARO NOCHG

122B 7654 CARO 00003

Looking at the data the first three lines all have the same EJID. This is as it should be. However, when imported into file maker each line will become its own record.

What I am tryin to extract from the data though is the number of occurences that EJID XXXX actually appears in the data base.

I knkow the "why's" are not important, so I will try an be brief...But, NEED = a specific needle on an embroidery machine. IN my database I have it set so when imported if need=13, then a 1 will be put in the field for needle 13. But in the event where more than one needle is changed, it give the appearence that there are "x" number of changes, where there is really on one "change cycle" with 2 or more needles being change.

In the occurence of "no change" there is only one potential cycle.

So, when I am trying to compare a ratio of thread change cycles to no change cycles I need to be able to take the duplicate EJID #'s pull the NEED # out, put it in the firtst occurence of the EJID and then delete the record that is no longer used.

Sequentially, a duplicate EJID would never be out of sequence from a matching EJID.

Is it possible to in the example above to essentially copy the NEED information from the second, third, etc. occurence from an EJID, paste it into the appropriate occurence of the EJID and then delete the no longer needed record? The above data would go from 6 records to 4 in FMP.

With EJID 9876 having a field (that exists)populated at

Thanks in advance for anyone who has any suggestion.

Steve

Hello Baylah,

There are several ways you might approach this problem, but most of them will entail some form of scripted adjunct to the import procedure.

Perhaps the most direct approach would be to build a script which first imports the data, then loops through the records checnking each to see if it is a duplicate (EJID value) of the preceding record and, if it is, adding its NEED# value to the NEED# field of the preceding record and deleting it - or, if not, beginning over to look for duplicates of the current record.

Less direct approaches (eg using an intermediary table for the import) might produce some marginal benefits in either speed or auditability of the process, but may not be warranted, based on the requirements as you've described them. ;)

Attached is an example of the kind of script you might use in FileMaker 7 to arrange the imported data as you've outlined. ;)

Needles.zip

Major Thanks to Cobalt Sky. He gave me an amzing solution that made me shine at work.

Thanks Again, Ray!

I will never cease to be amazed at the power and simplicity of FileMaker. I can't believe people still use Access.

Steve