How to set Get(CurrentDate) for a specific field during importing a .xls file

[sicSRT8]

How would I tell the script to auto enter Get(CurrentDate) in the Date field as it gets the info from an .xls file?

[*susan*]

Are you autoentering when the xls file is imported, for example generating your primary key?

*susan*

[sicSRT8]

Sorry... I mean how do I set a field in File Maker that isnt in the .xls as impoort date.

[*susan*]

I guess I didn't ask my question clearly enough.

When you import a file into Filemaker you have the option of "autoEnter" or deselecting "autoEnter." YOu can store this preference as part of the script.

If AutoEnter will be on, you can change the attribute of the date field to be [autoEnter=Get ( CurrentDate) ]. If the autoenter import option is deselected, loop through the records to set the date.

Set Variable [

$date; Get ( CurrentDate ) ]Go to Record [First]

Loop

Set Field [date: $date]

Go to Record [Next, Exit after Last]

End Loop

Is that clear? Good luck,

*susan*

[LaRetta]

How would I tell the script to auto enter Get(CurrentDate) in the Date field as it gets the info from an .xls file?

So you are importing and you want a date field to insert the current date when it imports?

It sounds like you need a simple auto-enter creation date (or I would actually suggest timestamp instead; more flexible). You import from your worksheet but skip this field in your mapping. Set the creation date in field definitions Options auto-enter tab (see auto-enter for your choices). Then just check 'Perform auto-enter' during the import itself and it'll insert the date or timestamp for you.

Edited March 26, 2009 by Guest
Changed some wording to make it more clear

[LaRetta]

Set Variable [

$date; Get ( CurrentDate ) ]Go to Record [First]

Loop

Set Field [date: $date]

Go to Record [Next, Exit after Last]

Hey susan, I'm wondering why you need to set a variable at all, even if you DID want to set it manually? If you choose to loop, it could be simply:

Go to Record/request [ First ]

Loop

Set Field [ date ; Get ( CurrentDate ) ]

Go to Record/request [ exit after last ; next ]

End Loop

... it would be faster. And using Replace Field Contents[] would be faster still.

LaRetta :wink2:

Edited March 26, 2009 by Guest

[*susan*]

LaRetta,

My tests show that the variable sets faster. I am not an engineer by any stretch of the imagination, but I always guessed that Get ( CurrentDate) actually has to be calculated by the database engine each time. Setting it once with the variable makes it much faster. At least that is my theory.

I have a client that regularly imports 10,000 records at a time. At that record count, the speed difference is actually noticeable to the end user.

Replace always makes me nervous in a multi-user setting. In real life, my loops traps for records that might be busy.

Thoughts?

*susan*

[LaRetta]

I couldn't imagine that a 10,000-record loop could show much of anything but I understand what you are saying and I'm glad I asked! You can indeed save time by setting a variable first if the calculation is complex. In using our two scripts through 100,000 records under a Freeze Window[], there was no difference when ran 10 times ... average was 7 seconds for both. So I don't see it necessary for simple calculations such as Get ( CurrentDate ).

But same test, using a complex calculation, variable method took 36 seconds and standard method took 44. Thanks for bringing this up!

As for Replace Field Contents[], one can trap error on it as well. :wink2:

[Fenton]

Another tip, which admittedly I do not know if recent versions of FileMaker do it differently. Which is that using Freeze Window, then flipping to Form view (or going to a Form view layout) during the Loop, then back to List view at the end will make a (big) Loop go much faster. It has to do with FileMaker having to update the scroll bar I believe, which happens even if you Freeze Window otherwise.

[*susan*]

LaRetta,

Of course you can trap with a replace, but you can't suppress the update bar that shows as this is occurring.

I think this moves to the realm of personal preference. As I move away from having unstored calculations whenever possible, I find that I am manipulating the data more and more, so the loop allows me to set multiple values with just one routine.

Interesting to hear how other people manage these type of circumstances.

*susan*

[*susan*]

Fenton,

I actually move the user to a TRAP layout with no fields when doing the imports to increase the perceived speed. This is particularly effective in a system with a "wide-row" where other values in the row are being generated based on the imported data.

For less complex tables, moving to a FORM will be faster, as long as you don't have a summary field on that layout. Summary fields bring the import speeds down to a crawl in a hurry.

*susan*

[LaRetta]

Of course you can trap with a replace, but you can't suppress the update bar that shows as this is occurring.

I agree. And I'm glad it shows. How many times do we wish for an update bar and have to jerry-rig a solution. The request for built-in progress bar appears repeatedly on Wants & Wishes.

For me, the verdict is still out but I favor Replace Field Contents[] a bit.