Looping Logic Escaping me!

[LaRetta]

Hi everyone,

I'm attempting to correct a set of (160,000) records. The table consists only of two text fields (an import). The data looks like this:

Each Company Name may have 0-'unknown' date/time entries belonging to (under) it. I used the "M" in AM/PM (F3) to identify a date/time record because it was unique. But the logic of the loop is escaping me because the following script is setting only some date/time entries correctly through the set (well actually, it's skipping a BUNCH of records altogether and I can not establish a pattern on why it doesn't work!). I LOVE looping scripts and really tried to figure this out myself but I'm stuck.

I have new fields: CompanyName (text), Phone (text), ActivityDate (Date) and ActivityTime (Time) ready to hold the results and a global gScriptTemp in which to work. The FIRST record begins with the CompanyName and it is in the required sequence in the Show All Records and Unsorted state. I need to end up with only the date/time records, each including the CompanyName and Phone. The loop portion script:

Go To Record/Request/Page [ First ]

Loop

_ If [ not PatternCount(f3; "M") ]

__ Set Field [gScriptTemp; f2 & " " & f3 ]

__ Delete Record/Request [ no dialog ]

_ Else

__ Set Field [ CompanyName; LeftWords(gScriptTemp; RightWords(gScriptTemp; -1)) ]

__ Set Field [ Phone; RightWords(gScriptTemp; 1) ]

__ Set Field [ ActivityDate; GetAsDate ( f2) ]

__ Set Field [ ActivityTime; GetAsTime ( f3) ]

_ End If

_ Go To Record/Request/Page [ Next; Exit after last ]

End Loop

Oh, and I understand the CompanyName and Phone creates redundant data. These two fields will then be concantenated to (attempt) match to the 'real' unique Contact. Pulling data from other programs (without unique identifiers) is SUCH FUN!!

Can someone point out where my thinking is loopy? I would appreciate it!

LaRetta

[RalphL]

For starters the delete record will put you into the next record so when you go to the next record you will skip one. I don't see a need for the deletion.

[LaRetta]

Hi Ralph, I see the (now clearly obvious) logic error there (doh) ... not necessarily that I should remove the Delete Record step (because I want to delete these incomplete records once I capture their data) but rather that I have a Go To Record/Request/Page [ Next, exit after last ] step also, which would result in skipped records. Thank you so much for the catch!

If I just remove the Delete Record Request, the result is much better (smile) but it still misses setting some records!! 300-400 are still skipped!? There are so many records that it is difficult to determine the pattern. I want to delete the record with only CompanyName & Phone, so was attempting to be sanitary.

I adjusted the script as follows:

I replaced the Go To Record Request at the bottom with Exit Loop If [ not Get(FoundCount) and I added an Omit Record within the Else (after all Set Fields). In this way, the If[] would just process on each record it comes to and I'd end up with 0 found set. Seemed clean. However ... It improved the results a bit ... but there are still Date/Time records that are being skipped.

I have tried many combinations and each time the results are incorrect, ie, periodic Date/Time records will not transfer the CompanyName .....

Oh. Wait. It may not be the loop that is now the problem ... With the adjustments in the script - those records aren't being skipped but rather the CompanyName isn't being set. Something is wrong in that first Set Field because Phone (on those skipped lines) is transferring but not the CompanyName!!!

Geez, I think that first Set Field gScriptTemp calc is breaking. I'll adjust it and get back to you if that doesn't solve it. Oh. This will be a conversion migration of approx. 30 separate imports so if there are ways to improve upon this process, please let me know. Oh and I have a Freeze Window and all that stuff in place ... I just presented the loop portion.

LaRetta

[LaRetta]

Okay, in case anyone runs into this post with a similar need, I'll supply the corrected script. The split of gScriptTemp was indeed breaking for a few reasons - extra spaces and blank phone fields. By turning CompanyName and Phone into multiline, it was easier to split them.

Go To Layout [ form layout ]

Freeze Window

Go To Record/Request/Page [ First ]

Go To Field [ F1 ]

Loop

_ If [ not PatternCount(F3; "M") ]

__ Set Field [ gScriptTemp; If(not IsEmpty(F3); F2 &

[Fenton]

Oops. You didn't write the Omit Record step in your post. Personally I think I'd just use 2 global fields, or 1 with 2 repetitions, rather than the splitting. I can't say which would be faster though.

[LaRetta]

Hi Fenton!

Oops. You didn't write the Omit Record step in your post

Apologies everyone. I've fixed it above.

Hmmm, yes splitting would have been easier I think. I'll have to play with that. Sometimes when I get my mind set on solving a problem one way ... I tend to stick to it - as in concatenating the global (CompanyName/Phone). I need to learn to 'back out' of the whole scenario and take another look from the beginning; rather than just backing to the point it breaks.

These types of lessons can sure be time-consuming but, thank God, it's a labor of Love. I appreciate you taking the time to respond!

LaRetta

[RalphL]

It looks to me that you are still going to loose some records. Those companies that have 0 date/time entries will be deleted.

[LaRetta]

You are exactly correct, Ralph! But this data is for appointments. If the Company has no Date/Time data lines, I don't want them anyway. I only want the Date/Time records themselves (containing all the data). And that's also why I want to delete the CompanyName/Phone records as I progressed.

A Find on the 'M' (original set) matches the finished set, so it's working. YAY!

[Ugo DI LUCA]

Hi LaRetta,

Just in case, you could also get the result from calcs and then import it back, without any needed script.

cPreviousRecordID = Record-1

Build a new occurrences of your table, "PreviousMatch" with cPreviousRecordID matching RecordID.

Then, using "Previous" as your context for the 4 calcs

cCompanyName = Case(IsEmpty(Table::RecordID);Field2;

Case(PatternCount(Table::Field2;"M");

Case( not IsValid ( GetAsDate ( Field2 ) );Field2;

GetField ( "Table::cCompanyName" ));

GetField ( "Table::cCompanyName" )))

cPhoneNumber = Case(IsEmpty(Table::RecordID);Field3;

Case(PatternCount(Table::Field3;"M");

Case( not IsValid ( GetAsDate ( Field2 ) );Field3;

GetField ( "Table::cPhoneNumber" ));

GetField ( "Table::cPhoneNumber" )))

cAppointMentDate =

Case(IsEmpty(Table::RecordID);"";

Case( Not IsValid ( GetAsDate ( Field2 ) );"";Field2))

cAppointMentHour =

Case(IsEmpty(Table::RecordID);"";

Case( Not IsValid ( GetAsDate ( Field2 ) );"";Field3))

Hope this makes sense.

[Ugo DI LUCA]

You are exactly correct, Ralph! But this data is for appointments. If the Company has no Date/Time data lines, I don't want them anyway. I only want the Date/Time records themselves (containing all the data). And that's also why I want to delete the CompanyName/Phone records as I progressed.

A Find on the 'M' (original set) matches the finished set, so it's working. YAY!

Well, didn't seen that, but if you want to try the suggestion above, then just omit all records with empty date fields, and run your import into the other file.

Of course, you can't use these calculations in a loop process as all data is tied.

I only run this quick test with the sample you posted, and it seems to be working.

ATTACHMENT EDITED

LoopOrCalc.zip

[LaRetta]

Hi Ugo, thanks for the ideas. The 'M' is in field 3 not 2. And "omit all records with empty date fields, and run your import into the other file." There are no empty date fields - there are only records with CompanyName. There is no 'other' file; this will be the Activity table. The other file is the Contacts table in which the concatenated CompanyName & Phone calc will be used for key to pulling in the REAL ContactIDs. And I can not simply omit all records with CompanyName because that is what is needed to add to the Date/Time records - otherwise they have no connection to the Company. I don't quite see your logic - but that's not unusual.

That doesn't mean it doesn't exist ... but we sometimes 'miss' each other on the connection level. I will create what you've suggested however and try it. This current method does indeed work fine. I end up with the original F2 and F3 unnecessary fields but after conversion is complete, I have no sentiments about deleting them. While it is true for CompanyName and Phone, I could have just used the original fields, I would have had to reverse to insert Date/Time into the one record and delete one of them anyway. Made no sense. I have found from (painful) experience that it is worth having a few extra fields when migrating - just in case ...

This data will be coming in from several sources - all structured as I have indicated and be imported directly into the final Activity table. And I need to keep the Import separate from the loop script because, after import, the data is reviewed by our sales staff. Only after all imports are complete can I then match it to Contacts and set the IDs through the converted set.

Thanks for helping me on this, Ugo!!

LaRetta

[Ugo DI LUCA]

...Answering my own answer here

Hopefully, I can't try this, but I have the feeling a loop on 160,000 records would run quite slow compared to an Import.

If the method above works, then I'd suggest you add a new calculation

cAlternateKey (text result)= Choose(IsEmpty(cDateField);RecordID;"")

1) Add this field on an empty layout,

2) Add a field gIDs

3) Perform a CopyAllRecords to grab the content of the Alternate Key and paste it in the gIDs

4) Create a new relation from gIDs to RecordID

5) GTRR

6) [show Omitted and Delete]

7) Run your import into your text, date and hour fields.

With FM7 2GB limit, it may be interresting to compare both method with these 160,000 records. I'd actually be interrested to know which is faster, for pure "Curiosity".

[Ugo DI LUCA]

I don't quite see your logic - but that's not unusual.

LOL

1) The "M" is in field 2 in my test which is why the formula above was mismatched. The attachment, as you've seen, does behave like you wishes with no scripts. (sorry #1 )

There is no 'other' file; this will be the Activity table. The other file is the Contacts table in which the concatenated CompanyName & Phone calc will be used for key to pulling in the REAL ContactIDs.

With such procedures, I'd definitely use another file for the first Import, into which I'd use the calculations above.

This would prevent for eventual mismatch and errors.

When imported into the "Transitory file", you'd omit those records with the cDateField *not* being empty (sorry#2 ), probably with the trick above (copy all records from an Alternate key).

Then import it into the Activity Table where your compound key is for the rest of the job

I have found from (painful) experience that it is worth have a few extra fields when migrating - just in case ...

Same pain here, but rather than unuseful fields, I'll prefer a Transfer File.

This data will be coming in from several sources - all structured as I have indicated and be imported directly into the final Activity table.

As long as each import is run sequentially, your Ids would match the previous, so you can build a relationship to the other.

For sure, as these 4 fields are unstored calculations, you may have some slow reactions just after the import is made.

But I keep thinking that the loop will take ages compared to an import, and I strongly prefer not looping into my solution's file this way...

Only after all imports are complete can I then match it to Contacts and set the IDs through the converted set.

Sure thing.

And I need to keep the Import separate from the loop script because, after import, the data is reviewed by our sales staff.

It will be then. You'd have the original files from which you run each Import and one Master Import where all your Imported data will be, separated from the rest of the solution.

Hope this make sense.

[LaRetta]

Shock! It makes sense. Well, then we agree. Because this new Activity table IS the transition table and will only become the Activity table once the ContactIDs are added after all data is in. Since they have changed some phone numbers and some have no phone number, I won't get 100% match to Contacts. Those Date/Time records ending up without IDs will be presented to sales.

I think I will go ahead and include setting the ContactID in this loop - it will save a second loop-through. The imports will be necessary whichever way I go. My hit is that two fields and a loop will be easier. And two fields and one script will be easier to clean up after myself also. Which is faster? I'll let you know. Because I ADORE these kinds of tests and plan to run them both. Speed is a major turnon.

LaRetta

[Ugo DI LUCA]

Hi,

Attached the Import procedure.

The RawDataCalculated is a file that holds your field 1 and 2 in which you import your data at first from all sources you wish.

The MainAppointments file is the one which hold the final result of your appointments.

Open MainAppointment first, then try an Import.

LoopOrCalcsImports.zip

[Ugo DI LUCA]

Hi again,

Latest update. I included the Imported serial in the Appointment Table so that a record can't be imported twice.

I would be glad to know about the results you had.

Just for the reminder, you'd have your Raw Data file, the RawDataCalculated and the MainAppointment files.

So the RawDataCalculated is only a "Join" file, where, with the advantage of fm7, you'd keep your foundset thanks to the new TO, in the Foundset Layout.

Note : You could prefer to run a loop when imported, so that you don't even need to match the data in Appointment.

HTH

LoopOrCalcsImports.zip

[LaRetta]

Hey Ugo! This is very cool thank you so much!

However, I remain unconvinced that an extra table with 3 TOs and 7 additional fields (of which 5 are unstored calcs) will be easier or quicker. The RecordID I'll have also and both processes will need to import each set anyway. Is it worth setting all these calcs and relationships up - particularly for something which is a one-time thing (after the initial 30 original imports).

"You could prefer to run a loop when imported, so that you don't even need to match the data in Appointment."

Now we're coming closer together! But I reserve judgement until I've tested both through the entire set. Test runs through 4,000 records - using the loop script took 5 seconds. We'll see what happens when it's fully loaded and I'll see how your process handles it also; particularly speed-wise. You are much appreciated.

LaRetta