Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Thursday, July 9, 2020 
 
slxdeveloper.com Community Forums  
   
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
 Administration Forums - Database Administration
Forum to discuss SQL Server or Oracle database administration related to SalesLogix databases. View the code of conduct for posting guidelines.
Forums RSS Feed


 Back to Forum List | Back to Database Administration | New ThreadView:  Search:  
 Author  Thread: Recommendation for custom fields
CPB
Posts: 3
 
Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 Jul 11 2:11 PM
fiogf49gjkf0d

Can anyone tell me Sage Software's stance on adding custom fields to their core tables vs using an extension table?  What is best practice and what is common practice?


How about fields that are not 1:1 (i.e., account manager phone number)?  Is that considered acceptable?


I appreciate the feedback.

[Reply][Quote]
Mike Spragg
Posts: 1226
Top 10 forum poster: 1226 posts
 
Re: Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 26 Jul 11 2:32 PM
fiogf49gjkf0d

Since a while back (v7.2/5 I think) - the stance is now to allow the extension of core tables. However, just be careful of common column names as, in the future, Sage may use them and you will clash. I typically use cn_co. When cn = customername. That way, unlikely to clash.

[Reply][Quote]
Raul A. Chavez
Posts: 1300
Top 10 forum poster: 1300 posts
 
Re: Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 27 Jul 11 1:23 AM
fiogf49gjkf0d

Quote:
Originally posted by Mike Spragg


Since a while back (v7.2/5 I think) - the stance is now to allow the extension of core tables. However, just be careful of common column names as, in the future, Sage may use them and you will clash. I typically use cn_co. When cn = customername. That way, unlikely to clash.



The one concern with using Underscores is that sometimes certain features in SLX don't work well with them.


For instance (not sure if this has been fixed yet), there were early issues with filters not working when the Field or table names contained underscores.


Never the less, your point is valid regarding using some sort of Standard naming convention to identify custom fields.

[Reply][Quote]
Mike Spragg
Posts: 1226
Top 10 forum poster: 1226 posts
 
Re: Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 27 Jul 11 4:13 AM
fiogf49gjkf0d

Hi Raul


I'd be interested to see a defect# for this. As you know, been using this a while and have to honest I've *never* seen an issue with doing this ? The only time was in an early version of 6.x whereby I used E1_ (previous company) and SLX interpreted that as an exponent number ! But, they fixed that years ago.


Regards
Mike


 

[Reply][Quote]
Raul A. Chavez
Posts: 1300
Top 10 forum poster: 1300 posts
 
Re: Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 27 Jul 11 9:25 AM
fiogf49gjkf0d

I ran into this on 7.5 (no SP).


We had added quite a few fields using the "c_" naming convention for our Custom fields.


When we tried to use Filters, they would not work for this Custom Fields. I recall going through the code back then and the Workaround at the time was to rename the fields.


I don't recall the Defect information around it, but I do recall having a ticket open to SAGE directly by the Customer for this issue.

[Reply][Quote]
CPB
Posts: 3
 
Re: Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 27 Jul 11 10:33 AM
fiogf49gjkf0d

Thanks everyone for the feedback.  Does Sage have an official stance on the Best Practice for this topic?  Also, I can understand why it's nice to use the core tables for a 1:1 relationship, but what if it is 1:M?  Does Sage recommend to use the core tables in that situation? 

[Reply][Quote]
Mike Spragg
Posts: 1226
Top 10 forum poster: 1226 posts
 
Re: Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 28 Jul 11 4:04 AM
fiogf49gjkf0d

For every table it's fine. I'm not sure of your question (not entirely convinced you are either). If it's a 1:M on custom data then simply create a new 1:M anyway?

[Reply][Quote]
CPB
Posts: 3
 
Re: Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 28 Jul 11 10:39 AM
fiogf49gjkf0d

Hello Mike,


I am actually quite sure of my question.  I have been working hard to keep my personal view out of the post, so that may have made it a little confusing.


I have been told that it is a best practice and the standard to put all custom fields in the core tables.  The question initially arose because a developer added several fields to a core table that constituted a one-to-many relationship where each core table row would be linked to one custom field value, but each custom field value could exist many of the core table rows.  This is obviously not as bad as if it were the reverse, but was still surprising to me that it would be recommended as best practice.


For example, say we wanted to suddenly include "sub products" in our database.  Each product could have only one sub product and there are 10 sub products.  Rather than adding six or seven fields to the product table that would essentially repeat themselves, I would choose to create an extension table and link them by ID.  What I am hearing is that this is not recommended practice anymore and I am trying to see if that is truly the case.  If it is, why?  I like to stay up on the best practices for all products I work with and could not understand the reasoning behind this one.


Thanks.

[Reply][Quote]
Mike Spragg
Posts: 1226
Top 10 forum poster: 1226 posts
 
Re: Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 28 Jul 11 10:48 AM
fiogf49gjkf0d

Right, that makes more sense now! No, I wouldn't have advocated doing it this way (SLX also do the same with History.LeadID and History.TicketID etc. where the table's extended to handle several entities rather than an intermediary. It makes things very messy at the end of the day - once you start to extend it past the 1 or 2 it was probably designed for.


Your suggestion (ext table plus link) is the way to go - otherwise you'd have to recode anything that only expected 1-6 sub-fields/products.


But, at the end of the day, you are {now} free to do whatever you wish and to keep to whatever standards you prefer (Sage/SLX don't make recommendations as far as I know) and you need to weigh up best-practice -v- cost. If you are an end-user and want to do it right (do it your way). If you are a BP with a schedule/costs to keep to then you do whatever's needed to get the job done.


You can modify an standard/core table to include what you need (naming conventions recommended). You can extend to any number of tables (1:1, 1:M or none) and, if they are extension tables, then unlikely Sage will use the same name (but, of course, beware).


Hope that helps.
Mike

[Reply][Quote]
Lane
Posts: 121
 
Re: Recommendation for custom fieldsYour last visit to this thread was on 1/1/1970 12:00:00 AM
Posted: 01 Aug 11 10:04 AM
fiogf49gjkf0d

I think the reasoning behind such a stmt was that field level security only worked on core tables in some versions.


Now that was a while ago and dependent on license type as well, several options can be used today.


My standard is to always add the 1:1 fields to the base tables (watch the record size limit) and keep the names from being too generic.


A sql compare of the slx default db system tables to the custom db, will show you all custom field name/size/type differences and is 100% reliable.


I have only run into a naming conflict a few times and has always been due to an existing field name becoming a reserved word in the new version of MSSQL, ORACLE or SLX.


 

[Reply][Quote]
 Page 1 of 1 
  You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

   Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
 

 
 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2020 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 7/9/2020 11:45:33 AM