By Edhy Rijo - 3/13/2008
Hi All,I have a form with some fields including address fields. There is one field FormattedAddress which I have to update with some fields concatenated for an address label. What would be the best way to address this kind of situation? So far I am testing in the BO_FieldPropertyChanged event, but I then have to filter this event for the list of fields that will participate in the concatenation format. Just wondering if I am in the correct place or there is any other way to do this? Thanks!
|
By Peter Jones - 3/13/2008
Hi,Check the topic "Adding Custom Field Properties" in the SF help - the example is exactly (I think) along the lines of what you are looking to do. I presume you are not trying to concatinate these columns together and actually storing the info into another column. If you are then it is best that you don't - just create the address label data on the fly as you need it, either in a custom field like the example of in a proc that returns the data for the label. Cheers, Peter
|
By Trent L. Taylor - 3/13/2008
Pete is right on the money, create a custom property that does the work for you. You can then use this property from anywhere in your application and even bind to it. This is how we do it ourselves. Public Readonly Property FormattedAddress As String Get '-- Establish Locals Dim r As String r = AddressLine1 & COntrolChars.CrLf & City & ", " & State & " " & Zip '-- Return formatted address
Return r
End Get
End Property You can get the property attributes from another property on your bo out of the partial class. You can also copy the Imports out of the header of the partial class for the BO as well to make some of those errors go away. Once you do this, if you intend to bind to the property then you will need to override the GetCustomBindablePropertyDescriptors method (which you can find in the help and samples....or by doing a search out here on the forum...lots of posts  )
|
By Edhy Rijo - 3/13/2008
Hi Peter, Trent,Thanks for the suggestions, but I actually need to have this value stored in the database because it will be use from many forms to print receipts, labels and reports. This is what I have done in the BO: Private Sub ProducerBO_FieldPropertyChanged(ByVal sender As Object, ByVal e As ProducerBOFieldChangedEventArgs) Handles Me.FieldPropertyChanged If e.FieldChanged = ProducerBOFieldNames.ProducerName Or ProducerBOFieldNames.Street _ Or ProducerBOFieldNames.City Or ProducerBOFieldNames.State _ Or ProducerBOFieldNames.Zip Or ProducerBOFieldNames.Phone _ Or ProducerBOFieldNames.Fax Or ProducerBOFieldNames.EmailAddress Then ' Format the Full Address string Dim cFullAddress As StringBuilder = New StringBuilder cFullAddress.AppendLine( Me.ProducerName) cFullAddress.Append( Me.Street) cFullAddress.Append( " " & Me.City) cFullAddress.Append( " " & Me.State) cFullAddress.AppendLine( " " & Me.Zip) cFullAddress.AppendLine( " Phone " & Me.Phone & " Fax " & Me.Fax) cFullAddress.Append( Me.EmailAddress) Me.FullAddress = cFullAddress.ToString End IfEnd Sub The format of the address is working fine, but I want to limit this code to only execute for the fields included in the IF.. condition, but it is not working. If anybody can see what I am doing wrong please let me know. Thanks!
|
By Peter Jones - 3/13/2008
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
|
By Trent L. Taylor - 3/13/2008
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: Select Case e.FieldChanged
Case Field1, FIeld2, FIeld3, FIeld4
'-- Add your logic here
End Select
|
By Edhy Rijo - 3/13/2008
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 L. Taylor (03/13/2008)
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 SelectTrent 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!
|
By Greg McGuffey - 3/13/2008
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).
-- Field def in a table for calculated field
CREATE TABLE MyTable
(-- other fields here....
,Street varchar (50) NULL
,City varchar (50) NULL
,State varchar (25) NULL
,Zip varchar (10) NULL
,FormattedAddress AS (Street + CHAR(13) + CHAR(10) + City + ', ' + State + ', ' + Zip)
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
|
By Edhy Rijo - 3/13/2008
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.
|
By Trent L. Taylor - 3/13/2008
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 CREATE FUNCTION [dbo].[Udf_FormattedAddress] (@parentPk INT) RETURNS NVARCHAR AS BEGIN
-- Declare the variables that will be used to build the formatted address
DECLARE @returnAddress NVARCHAR(1024);
DECLARE @address NVARCHAR(150);
DECLARE @city NVARCHAR(60);
DECLARE @state NVARCHAR(128);
DECLARE @postal NVARCHAR(20); -- Select the values into the
SELECT @address = cust_Address1,
@city = cust_city,
@state = cust_state,
@postal = cust_postal FROM Customers WHERE cust_pk = 1; -- Format the return value
SET @returnAddress = @address + CHAR(13) + @city + ', ' + @state + ' ' + @postal; -- Return the formatted address
RETURN @returnAddress;
END Including the UDF in a query SELECT Customers.*, dbo.FormattedAddress(Customers.cs_pk) AS cs_FullAddress FROM Customers
|
By Greg McGuffey - 3/14/2008
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...
|
By Trent L. Taylor - 3/14/2008
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:SET STATISTICS PROFILE ON
SET STATISTICS TIME ON 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.
|
By Greg McGuffey - 3/14/2008
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
|
By Trent L. Taylor - 3/14/2008
LOL....thanks, Greg! I always truly appreciate your contributions out here!
|
|