LOL....thanks, Greg!  I always truly appreciate your contributions out here!

Hmmmm...good info Trent. The tables I'm using this on are small and likely to remain small....no wonder I've had no problems. I'll keep this in mind though, going forward. This is thread is another excellent example of why SF rocks

All good advice, Greg.  I will warn you, though, that the larger your data set becomes, the slower a calculated field becomes...we have just gone through this very thing.  So we are bit more leary of calculated fields (in this manner), especially when dealing with character data, than we once were.  But we will still do this under certain conditions.  If you are expecting this particular table to get very large, then you should probably go with a UDF or sproc, otherwise the calculated field is great.  Also, you can test this against your database and see where you get the best performance.  Use the SET STATISTICS commands to see what query performance difference you get:

That would be the TRUE test on your data.  Also, it can be a really good idea to add some indexes on those fields as well.

Edhy,



Just offering some options. You never know when another trick will be needed!



Trent,

I'd agree if it was anything complicated or needed data outside of the current row or in some other table or needed parameters, etc. However, if it's just a simple manipulation of fields in the current row, then I'd lean toward the calculated field. Like I said, I'm using a few of these and they are real easy to deal with, as they just become strongly typed fields in the BO and are always available. Most of the time, things are more complicated and I use the other methods suggested, UDF, sproc, custom BO property...but sometimes...

Hey Greg,

That is a good point on the DB side, but I would probably just create a UDF or stored proc to return the formatted value versus creating a table.  This way you don't have any additional overhead hitting the database each time you call the method.  If you create a UDF, the you can include it as an AS column in a SELECT query.

Example UDF for SQL Server to format the address DB side

Including the UDF in a query

Hi Greg,

Thanks a lot for your input. 

So far this information will not be used outside the database.  Basically this is a VFP project I am converting to .NET and of course some logic will need to be changed and this is one of those

The idea is to be able to use this information in some custom printing like an Invoice/Receipt and a label, so I don't have to be recalculating this data and yes, I agree with all of you that a Custom Field is the winner here.

Edhy Rijo

I would also agree to avoid storing this in the DB, unless:



- You need it outside the application

- The logic to create the value is complex and handled by the application



This doesn't appear to the the case.



Another option, besides storing in the db or using a custom property in the BO would be to use a calculated field in the database. This is good when you need it outside of the application, but the db can handle the logic fine (as appears in the case of building an address).







As you can see, this is not nearly as nice as working in .NET, but if you needed outside of the application a lot (or really like T-SQL) this is good. It also puts the data right in the BO. Just mark the field as readonly, so you don't try to update it (which will break). I've done it both ways (using a custom property and using a calculated field in SQL) and would tend to go with the custom property in cases like this (manipulating string from exiting fields in a row). Just my $.02

Peter, Trent,

I understand what you guys are saying about using a Custom Field, and I will do it that way, but this is a learning experience for me.  I am in the process of learning and getting comfortable with VB.NET in order to move on into the .NET league .

I tested it with a Select Case and it is working fine, thanks again for the push

Trent and all, I am an experienced VFP developer (20+ years), but I can not say the same for .NET and my choice language VB.NET, so please feel free to correct me, suggest, encourage and anything else necessary to help me out in this learning process at any time.

Once again, thank you both!

Edhy Rijo

I tend to agree with Peters assessment of not storing it in the database.  I am sure that you have your reasons but creating a property to do the work for you when you need it would be more efficient and would normalize your data. 

I am sure that you already know this, but an Or in VB.NET is BitWise and an OrElse is an explicit test.  Same is true for And...you would use AndAlso in its place.  Your code will work in this scenario since this is an enum, but in other scenarios this may fail.  Another option is using a Select:

Hi Edhy,

You need "e.FieldChanged = " before every OR

That being said let me assure you that creating the address info "on-the-fly" as and when you need it is the way to go - storing it in your database is highly redundant and inefficient.

Cheers, Peter