I am confused when defining a field whether to use a regular field with an auto-enter calculation OR a calculation field.

The field does not need to be changed by the user directly. The field needs to be indexed for searches. I have found a way (via the forum) to auto-refresh an auto-enter calculation (using Left(GetAsText(modificationTime);0)).

Are there situations when I should stay away from auto-enter?

I have managed so far not really knowing but if you have opinions on this, please let me know.

Thanks!

Auto-enter calcs can be overwritten manually...calcs can't.

Auto-enter calcs can be used in place of lookups (resulting in increased performance).

The auto-enter field itself can serve as its own trigger.

Calculations can't be stored or indexed if they reference a related field, a summary field, another unstored calculation field or a field set to global storage.

Calculations can be triggered by fields in related tables, auto-enter calcs cannot (to the best of my knowledge)

There are probably more differences, but nothing comes to mind at the moment...maybe someone else can put their in.

Hope this helps!

Thanks for your 2 cents. It helps to have it spelled out in a list like this.

Edited October 19, 200520 yr by Guest

Kent,

Could you please send me an example of this. I have a similiar problem that uses lookups but I can't format it also. Here is what was answered in the post.

Auto-enter calcs can be overwritten manually...calcs can't.

[color:red]Auto-enter calcs can be used in place of lookups (resulting in increased performance).The auto-enter field itself can serve as its own trigger.

Calculations can't be stored or indexed if they reference a related field, a summary field, another unstored calculation field or a field set to global storage.

Calculations can be triggered by fields in related tables, auto-enter calcs cannot (to the best of my knowledge)

There are probably more differences, but nothing comes to mind at the moment...maybe someone else can put their in.

Hope this helps!

In the same dialog box you used to make your field do a lookup, simply check the box "Calculated Value". A calculation dialog opens up. Select the field that holds the value you'd like to be entered into what was your "lookup" field and click OK. When the calc dialog box closes be sure to uncheck "Do not replace existing ....". That's it.

You can perform additional formatting if you wish in the same calculation dialog box.

Kent,

I am uploading a small file so that maybe you could show me. I am just not getting it. I want to format the social security number with the lookup. Look the file and tell me what I am doing wrong.

Test.zip

Edited November 28, 200520 yr by Guest

Your file just opens to text as code. Use Stuffit or WinZip to compress it and try again.

Kent,

Try it now...

No file was uploaded.

Try this thread. It deals with formatting a phone number but it can be modified quite easily to format a Social Security number.

The file was there, I downloaded it.

Lee

Lee,

Ah, I didn't realize raycock changed the original post with the upload...I was looking for it in the newer post. Thanks!

raycock,

If you just remove lookup() fuction from the auto-enter calc so that all that's left is:

Left ( Social_Security ;3 ) & "-" & Middle ( Social_Security ; 4 ; 2) & "-" & Right (Social_Security; 4)

then you'll accomplish what you want.

I assume that the lookup() function was to get the data from another table? Why not just format it in that table to start with? If for some reason you can't, change all the Social_Security fields in your calc to the Social_Security fields from the related table.

Hope this makes sense.

BTW I'd suggest that somewhere in your calc for formatting the SS number that you check for errors such as length (9 numbers), the letter "O" used instead of zero, non-numeric characters, etc.

Why not just format it in that table to start with? If for some reason you can't, change all the Social_Security fields in your calc to the Social_Security fields from the related table.

Forget that...it won't work because the field won't update since it's depending on a foreign field as the trigger. I'd suggest you do your formatting in the table where it's first entered.

Kent,

I know that this is going to sound weird but the sample that I sent you is just that, a sample. I have multible files pulling information from this file. The formatting is no problem and the lookup is no problem as you can see from the tiny sample file. If I format the social security number then there is no way to bring over the formatted data into the field. The file that I am building is so far 12 meg so it would be impossible to upload it to you. I have both commands and can't get them to work in one solution (script or calculation). The combination of both is essential.

Yeah, I realized that was a sample.

If I format the social security number then there is no way to bring over the formatted data into the field.

Why not?

Like I said, I can't do it. Need some help in getting it done. "Why not" is not helping.