Custom fields on Item table - include in what other tables?

Johannes_Nielsen

Hi all

I've been adding a few custom fields to our Item table.

But what other tables should include these fields too?
Are there any "best pratice" to follow, regarding Items?

I'm planning to add the fields to these tables:

• 37 Sales Line x
• 39 Purchase Line
• 111 Sales Shipment Line x
• 113 Sales invoice Line x
• 115 Sales Cr. Memo Line
• 121 Purch. Rcpt. Line
• 123 Purch. Inv. Line
• 125 Purch. Cr. Memo Line

x = I'm sure of these - Since I need the fields printed on Sales Invoices, Shipment, Quotes etc.

So I'm wondering, it is advisable to add the fields on the rest of the tables or is this overkill? :-k

DenSter

You need to purchase a book on database design, and learn about data normalization. Just because you need to print certain fields on an invoice is not a reason to add them to those table. The Item's primary key field is already on the line, and using that you can retrieve the Item record and get the field values from there.

Savatage

One thing to remember is when you add this field to other tables is that you should use the same # & name for the field.
ex/

50055 - MyNewField
Tables like sales line will pass the data to Posted Invoice Line automatically if they are named & numbered the same.

As Daniel was saying if you just need it for reports you could just keep it in the item table and just a "get" to retrive the needed data into your report.

if item.GET(SalesLine."No.") then..

if item.get(purchline."No.") then..

etc...

bbrown

A reason for putting it in the other tables would be if the values in the item table may change over time and you need to retain the historical values related to the specific documents.

sendoh

yes, the best answer would be what are the use of that field, can you tell us what is that field, all suggestion is right since it's already in the Item Table just get it in their and show it in the report. but if this is can be change per transaction then thats the time add it in the document tables.

Johannes_Nielsen

Hi all, thank you for the quick replies :thumbsup:

sendoh wrote:

yes, the best answer would be what are the use of that field, can you tell us what is that field, all suggestion is right since it's already in the Item Table just get it in their and show it in the report. but if this is can be change per transaction then thats the time add it in the document tables.

The fields holds useful/nice-to-know information about a certain type of product, it's useful for the customer to have these on the documents we provide them. (Dimensions, way of packaging, extra product options, product certifications)

bbrown wrote:

A reason for putting it in the other tables would be if the values in the item table may change over time and you need to retain the historical values related to the specific documents.

Yes, this is exactly my worry, if these documents are used as documentation of what, exact product, the customer got, the values should be included on the relevant tables.

Savatage wrote:

One thing to remember is when you add this field to other tables is that you should use the same # & name for the field.

Yes, so the values will get transfered

DenSter wrote:

You need to purchase a book on database design, and learn about data normalization. Just because you need to print certain fields on an invoice is not a reason to add them to those table.

You, along with Savage are right, that I could read the values from the Item tables as well as have the copied onto some/all the tables, depending on what historic record I wish to keep of the documents using these tables.

-
What I'm unsure of is whether or not, there is a "best practice" regarding custom Item values/fields...

But I'll take your advice and determine this, based on the need for historic records of reports.

Sog

If the field(s) is(are) more information of the item then you shouldn't have to add them to the other tables.
If the field(s) is(are) more information of the transaction and can change, you should add the fields on the other tables and use the field on the item table as an initial/standard value.
Or something like this...