Loop within a Loop (within a loop??)

[LaRetta]

Hi everyone,

I'm posting this pesky cross-tab conversion script because I can't seem to accomplish what I need and I hope you Script Magicians will help me identify what I have wrong in my script thinking.

This runs through the fields and creates records out of every field/record combination. I decided this should be a new post because it involves only the script-loop problem I can't solve ...

If (after landing on a field) the contents (text) is empty, I want that field skipped and the script to jump to the next. I thought I needed another loop. Script 1 works (without the IsEmpty() test. Script 2 shows my 'logic' of where I would have thought the new loop should go but it doesn't work; neither does any of the other (20) variations I've tried. It seems a small thing but this is a major project. And half the fields in half the records will be empty. Speed-wise, skipping any empty fields should decrease processing time by 50% - which will mean HOURS saved, so I would appreciate help very much.

As always, I've done everything I can to solve this on my own and only appeal for help out of desperation. My logic is incorrect, obviously but I can't spot it.

Thank you Fenton, for the 'Commit - Go To Next Field' idea! That is truly clever!!

LaRetta

Book1Converted.zip

[LaRetta]

Ooops! Apologies that the button is broken. I've re-written this script so many times.

L

[bruceR]

You could do a series of find/import actions also. Will this be on served file or standalone, you know the deal with served files and imports.

Find all STEVNO_3 and import. Find all SOLTAB_250 and import. Etc.

And of course you could do the auto-create method using a muti-field relation. Though you have a compication because you are dealing with 2 differenrt fields. See attached.

Book1Converted.zip

[LaRetta]

Hi Bruce!

I didn't think it would work until I saw the graph!! Absolutely brilliant! I can't thank you enough for saving me many, many hours of migration work!!

I have one additional question if I may? I just noticed some fields are clear-cut on whether they are text or date text. I'm using / to determine if they are a date because I thought that easiest. But now I'm finding fields with inconsistent data (like I should be surprised?).

I believe I'll have to tighten that test on the date. Those that are 'date' always are listed properly as shown. How can I determine if the field contains a 'proper' date only? I'm not sure I could use PatternCount(field; "/") = 2 because some fields have a lot of garbage in them (which should then be comments) instead of dates. If the field contains "9/4/2004 They don't want it" my current test will dump that into a date field and it'll break. Any thoughts on this?

Oh, I'm tickled. You are wonderful!

Update: Oh so sorry ... I was being greedy with your time. I'm on a major crunch and I was trying to save time by not doing the work myself. I think I can figure it out although text calcs aren't a strong point of mine. In fact, I'm unsure what my strong points are. I'll get to work on it - the text calc, I mean.

LaRetta

[Fenton]

I just put in a test for empty contents around the setting steps. It seemed to work fine, and was quite a bit faster. It may be that you were trying to put the test in the wrong place, not around the rest. Or else mine just doesn't work, also possible. I haven't looked at Bruce's. I'm not feeling smart enough at the moment. Maybe tomorrow :-|

Book1FJ.zip

[LaRetta]

Oh no, Fenton! Yours works! Doh. I didn't need another loop at all. Wow two great solutions almost simultaneously. Instead of testing for If [isEmpty() ... I should have reversed it. I tried wrapping with an If[] test but the wrong one and that's why I kept thinking I needed to exit the settings loop if the test passed.

Well, you both have saved me so much time, I'll be able to spend a bit trying to figure out how to test if a pattern within text (or by itself) is a qualified date field. In fact (if I can isolate the dates) I might just split them properly as I go. I'm not that good however, so we'll see...

Much obliged!!

LaRetta

[bruceR]

Slightly updated version

Book1Converted.zip