Community
Showing results for 
Search instead for 
Do you mean 
Reply

Your input requested for field ALIASNAME proposal...

Copper Super Contributor
Posts: 138
Country: United States

Your input requested for field ALIASNAME proposal...

We’re soliciting your input and comments around an issue for the field ALIASNAME that was introduced with Custom Tables in v10.02.  Please take a moment to read and respond if you write plug-ins that use this new attribute…

 

Issue:

We are considering using the field ALIASNAME to construct a new/alternate set of OLEDB Reporting Views.  This would be instead-of the current use of field Display Names.  However, the characters which could have been used when setting the field's ALIASNAME could cause problems with some third-party reporting products (Crystal, Excel, etc.) – characters such as “~!@#$%^&*()+-`=,./\|{}?;Smiley Embarassed<“ - and even blank spaces.

 

Currently, ALIASNAME is set for stock/default fields (except for key/GUID columns).  However, for all user-added (custom) fields the ALIASNAME is optional.  In the case of fields added via Define Fields, the value is always NULL.

 

Proposal:

We would disallow – and remove - the potentially problem characters (listed above) upon Create New Field.  For new fields whose ALIASNAME is not supplied, ACT! will establish that value and set the field accordingly.  The algorithm is to take the initial Display Name and remove any problem characters.

For existing user-added fields whose ALIASNAME has not been set, we would set them during Schema Update.  ACT! would again take the current Display Name and remove any problem characters.  

 

Additional Background:

Prior to 10.02, Entity fields were only accessible by either their real/physical name or their friendly/display name.  For example, the display name of field “Contact” on the Contact record has a physical name of “FULLNAME”.  When a custom field is added, whether on a stock Entity such as Contact, Group or Company, or a new custom table (Sub-Entity) , the physical name is set internally and there is no way to know what that field name will be.  And although you can set the display name, an Administrator can rename the field in Define Fields later on.  This makes in-advance field referencing difficult to impossible.  Upon field creation, or later update if never initially set, you can specify a value for the field’s ALIASNAME (v10.02 and greater).  The ALIASNAME can then be referenced by the plug-in code and by Layouts since the name is known in-advance.  ALIASNAME must be unique within the collection of all fields for a given Entity (or Sub-Entity).

 

 

Bill Blakey
ACT! Development Team
Sage Software

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Your input requested for field ALIASNAME proposal...

[ Edited ]

Bill,

Why remove the empty spaces when its supported in SQL?
It would create a lot of issues with our UI. 

 

Would you standardize all system fields?
Off the top of the head, I remember we had an issue with "Create Date". Something like CREATEDATE is also CREATE_DATE.

 

Would the existing view naming conventions continue to be supported?

http://community.act.com/sage/board/message?board.id=Pub_Dev&thread.id=1126

 

How if any would the new/alternative view naming conventions differ from the existing one?

 

 

Our custom table add-on currently already populates AliasName with the DisplayName stripped of bad characters but not blank spaces when the user adds a new field. That's because our layout designer import/export layouts files (*.DLY "Durkin Layout file" ) and we love using AliasName in DataBinding and FieldDesscriptor practices.  BUT we also write additional code to check for AliasName = null since we may be using a DB with tables from another custom table tool.

 

I would adopt your methodology in a heart beat but since we support back to 10.2 that's just not possible and sometimes not practical to support multiple version.  If the new views are less problematic than the current views then I would consider some rewriting in future version.

 

thanks

-- jim durkin

Message Edited by jimdurkin on 12-30-2008 12:30 AM
Copper Super Contributor
Posts: 138
Country: United States

Re: Your input requested for field ALIASNAME proposal...

Jim - thanks for the first feedback!

 

Yes, SQL Server supports spaces in column names absolutely, but not all third-party reporting/query tools do, nor do all database products.  If they can run with quoted identifiers mode then they're ok, otherwise you get parsing errors.  Crystal and Excel are ok, others....?  The removal of spaces is not an absolute proposal, just one that is safer - we may not take this approach based upon feedback.

 

The system and stock fields were set in 10.02 - there was an issue in either 10.03 or 11.0 (can't remember) where not all were being populated - that's been fixed for 11.1.  The Primary and Foreign Key fields would be populated.  The general rule is that we take our stock english Display Name and replace spaces with underscores, so that is why "Create Date" (physical CREATEDATE) become Aliased CREATE_DATE.  That will continue.

 

The URL reference you provided was around View names.  We're thinking the pattern will be different, not Entity-Subentity based.  Rather you would see Views that follow the Entity Names:

CONTACT

GROUP

HISTORY

NOTE

POLICY <<<<< whatever you set for the Custom Subentity NAME value!

 

This would facilitate non-Entity reporting, specifically the requests for User or Team-based reporting.  Right now if you want to create a User History Report (any/all Histories whether on a Contact, Group or Company) you have to UNION three queries together.  In this new model, the HISTORY View is secured and can be OUTER/INNER JOINed as you want to run a User-centric report. 

 

Other non-content subject areas would also have Views, like:

TEAM

USER

TEAM_USER

 

As with field names (ALIASNAME), we would remove the invalid characters (listed in my post) from the Entity Names.

 

So in this scenario, you would no longer find NULL value ALIASNAME columns - again, those would be set during Schema Update to be the Display Name less the invalid characters (maybe leave the spaces, we'll see).

 

Does this help?

 

 

 

Bill Blakey
ACT! Development Team
Sage Software

Copper Super Contributor
Posts: 138
Country: United States

Re: Your input requested for field ALIASNAME proposal...

I also wanted to mention that any spillover tables (for an Entity or Subentity) would automatically be included in the Entity View.  This would be in addition to the Virtual Fields (Address/Email/Phone) that currently get included in the Entity VRP Views today.  Feedback has been that users don't understand these tables, and where to find the fields they're wanting.

 

We are also looking at the feasibility of including Activities, and Group/Company Contact Membership in the new model, as well.

 

Besides that, what other subject areas would you have interest in reporting on?

Sync?

Preferences?

Security?

 

Thanks.

Bill Blakey
ACT! Development Team
Sage Software

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Your input requested for field ALIASNAME proposal...

I thought that virtual fields were not supported when creating a custom field?


Would you strip or replace the invalid characters? I vote strip.

 

My current invalid list is less because I based it off of file naming conventions and did not pick up the SQL character issues.  If you publish your list I will sync with it.

 

Is this the complete list of your  invalid characters?  ~!@#$%^&*()+-`=,./\|{}?;Smiley Embarassed<“    What about “[“ and “]  of the top of my head….  

 

 

Why not flip the logic to only allow A-Z and 1-9 characters and spaces?

 

Regex.Replace(strDisplayName, "[^A-Za-z0-9]+", "")   // Needs to also handle spaces

 

http://msdn.microsoft.com/en-us/library/system.text.regularexpressions.regex.replace.aspx


thanks!

-- jim durkin

Nickel Contributor
Posts: 286
Country: United States

Re: Your input requested for field ALIASNAME proposal...

Bill,

 

Some thoughts from here:

 

- Make these new views Alternate views, at least for a couple of releases.  There is quite a bit of investment in many areas in using/consuming the old views.  Don't force these users to change overnight.

- As far as forceing/providing a default ALIASNAME - well, I was kind of wondering why this was not being done already.   With lots of fields having a null ALIASNAME it was kind of hard to build code that used ALIASNAME unless you also controlled the field creation and could ensure that ALIASNAME got set.  Does this imply that Define Fields needs to allow you to set ALIASNAME (pre-populated/suggested with the "cleaned-up" DISPLAY name?)?

- I'll let those with the more expert grasp of the SQL language suggest the best typographic transformation for the default/forced ALIASNAME.  I kind of side with Jim - keep A-Z, 0-9, change blank to '_', kill all other characters - would definitely be a character set allowed by all tools we care about.

- I like the idea of having views for the sub-entities (Notes, History, custom) that do not depend on a parent.  This gives us back functionality we had in the old ACT! 6.0 SDK, and would allow for the "Report all Notes and Histories from last week" type reports to be done easier than they are now.  Please make sure there is enough identifying information to be able to somehow join back to the related USER, Contact, Company, Group that may be the parent.

 

Don Egen

Patricia Egen Consulting, LLC

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Your input requested for field ALIASNAME proposal...

Don,

 

The FieldDescriptor() has parameter for alias now.

New Act.Framework.Database.FieldDescriptor(DisplayName, AliasName, EntityDescriptor, FieldDataType)

 

I am actually against removing spaces with "_".  It would cause a lot of our code, preferences tracking, layout designer files and report designer to break.   We use the SQL views to get intelligence on the custom table for purposes of datarow/entity syncing when our lists our in virtual modes. Some of this intelligence/naming bubbles into the UI. If you remove spaces: Firstname, Lastname, zipcode yuk!

 

-- jim durkin

Copper Super Contributor
Posts: 138
Country: United States

Re: Your input requested for field ALIASNAME proposal...

Don, great feedback.

 

Yes, these would be a second set of Views in additional to the current VRP_ set.  In fact, this could be based upon a second new OLEDB Provider - so for a while there could be two.  This could allow legacy continuity and the new implementation...

 

When we introduced ALIASNAME in 10.02, we didn't see a need to behind-the-scenes set the value when fields are created via Define Fields (or not specified in the SDK).  We did set stock Entity field ALIASNAMEs (as I mentioned, there was a glitch in 10.03(?) where some fields were left un-named) less the Key fields.

 

Yes, the a-Z and 0-9 convention is a good one for us English speaking and typing folks, but we do localize.  Today users can add new fields in any language.  So if we take the approach of setting a non-specified value by removing the invalid characters of the Display Name, we need to support other languages.

 

On the "joining back to related" records (User, Contact, etc.), yes I agree.  What if each View also offered a delimited list of those related Entity names?  Like for Histories, a composed field called "CONTACTS" that would list any/all accessible Contacts alphabetically (Lastname, Firstname) delimited by a semi-colon, like:   "Blakey, Bill; Jones, Howard; Smith, Sally".  Would this be of any value?  It could be a potentially wide content field depending upon how many Contacts (or whatever other Entity) the Subentity record was with...

 

Thanks!

Bill Blakey
ACT! Development Team
Sage Software

Copper Super Contributor
Posts: 138
Country: United States

Re: Your input requested for field ALIASNAME proposal...

Jim - examples you gave of First Name and Last Name...their ALIASNAME values today are FIRSTNAME and LASTNAME respectively.  So you already have this.  With your add-ons, are you saying that the ALIASNAME surfaces to the user in your UI and custom reports?  Wouldn't you just bind to the ALIASNAME but show the Display Name...?

 

Regarding Virtual Columns (of Address/Email/Phone), you are correct that custom Subentities do not support them.  I was referring to new fields added on existing Entities.

 

Thanks!

 

Bill Blakey
ACT! Development Team
Sage Software

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Your input requested for field ALIASNAME proposal...

Bill,

I was referring to columns in the custom tables were UI and/or our add-on templates create fields called First Name and Last Name. I could use DisplayName in tmy UI if I was bound to the entityobject but in some cases I am not. So I use the column.name from the view. If you are going to design Views using AliasName then those names would be seen by the users hence my rational for using spaces.

 

Hope this makes sense. If you would like more detail contact me directly.

 

-- jim