Customise Primary Key when using Extensions

Developer101 · 2018-01-17

[NAV 2017]

Question:

I know it is not possible to custom the primary key in the standard table and include that table in the extension.
But is there a work around?

What I am trying to do:

The context is - basically when Item is same on multiple sales or purchase lines, the GL entries roll up and show one line as total, I want to separate those lines and show descriptions from document itself. I hope this make sense.

To do that I need to customise table 49 and add description field and then add it to primary key of the table and this is where the issue of extension limitation hits.

Any ideas will be appreciated!!

Ciao!

Slawek_Guzek · 2018-01-17

How abount using OnBeforeInsert event and modify some field already in PK, but not used in your solution? Tax Area Code for 'ciao' speaking customers seems to be good candidate

Developer101 · 2018-01-17

Sounds like an idea!, will give it a go. thank you!!

Developer101 · 2018-01-17

The issue is the length of the field

, Description is 50 chars long and Tax Area Code is 20.

HannesHolst · 2018-01-17

Hi,

For my current installation, I'm using the field "Line No." of the Sales Line-Table to show the Description per Sales Line in the G/L Entry-table. Maybe it is possible to use the same Field and the field "Fixed Asset Line No." of Table 49 in your solution.

Slawek_Guzek · 2018-01-17

Developer101 wrote: »

The issue is the length of the field , Description is 50 chars long and Tax Area Code is 20.

Uppercase, remove spaces and hash the description, and take the first 20 characters of the hash. I'm pretty sure you won't see duplicates

AK · 2018-01-17

Slawek_Guzek wrote: »

Uppercase, remove spaces and hash the description, and take the first 20 characters of the hash. I'm pretty sure you won't see duplicates

I wouldn't recommend that. Hashes aren't collission free, so different data can result in the same hash. Cutting the hash will increase that probability.

Counting the unique descriptions and filling one of the unused key fields of table 49 with the corresponding integer value might do the job.

Slawek_Guzek · 2018-01-17

AK wrote: »

I wouldn't recommend that. Hashes aren't collission free, so different data can result in the same hash. Cutting the hash will increase that probability.

The probability of collision of 128 bit long MD5 hash is 1/2^128. The hex string representation of 128 bit long hash is 32 characters long. If you cut it at 20 characters you will get 20 x 4bit = 80 bits. Probability of collision of that is 1/2^80, which is 1 in 1,208,925,819,614,630,000,000,000

I woudn't be much worried much about collisions...

AK · 2018-01-17

My math skills are not good enough to prove you wrong, but the calculation of probability for the cut hash just feels wrong. Anyway, if there is a solution that might fail (with whatever probability) and another one that can't fail, I know which one I chose.

David_Singleton · 2018-01-18

Developer101 wrote: »

[NAV 2017]

The context is - basically when Item is same on multiple sales or purchase lines, the GL entries roll up and show one line as total, I want to separate those lines and show descriptions from document itself. I hope this make sense.

Keep it simple.

just create a Dimension that maps one:one with the Item no. Then add this to the sales line. This will create a unique Dimension Set ID which will then group by dimension and thus do your grouping by Item automatically.

Customise Primary Key when using Extensions

Answers

Categories