Help with Custom Table Design

Dynamics 365 Business Central/NAV Developers Forum
#1

Hey guys,

So we just added a granule to our license that lets us add up to 10 custom tables.

I have already made the table, I know enough about object designer to do that. But what I need help with is auto-populating new entries. I have a “code” field, which stores two digits. Essentially, I want the shortest unique code that a given row can have.

For example, AA, AB, AC, AD and so on. Then it would become AZ, BA, BB and up to ZZ, which allows for 2^26 possibilities.

Is there a way, using C/AL code or something else, to make it automatically continue the series when you add a new line? It would be nice for our users to not have to think about it, they could just make a new row, enter in the rest of the columns and hit enter and the code would appear.

I know we do it with sales headers, for example - the auto-numbering. But that C/AL code uses “number series”, and for this we want letters.

Any help would be appreciated.

#2

What happens once you use them all?

Is that the primary key?

Perhaps something like mentioned in this post can help:

http://www.mibuso.com/forum/viewtopic.php?t=14078

#3

No this allows for 26^2 possibilities. Two characters, 26 options each, 26 times 26.

#4

Yeah I realized my error - so I’ve changed it to a 3-character and I’ll use three sets (AAA through ZZZ)

That has 26^3 possibilities.

As for the primary key - my primary key is a combination of two things, “applies to” (which is just a CODE value that has a table reference to Item Category Code: Code), and “Name”. I suppose I could make this Code the primary key, but it didn’t really seem like it mattered at the time.

I don’t anticipate that we will ever use all 26^3… right now we have about 2000 values, and we might add another 1000 in the future but that still leaves lots of them wide open.

Does it need to be the primary key for the auto-fill stuff to work?

#5

We did that in the beginning too…assuming. We made all our item numbers 5 digits. We then used autoenter & maxlength properties = 5 so order entry would be quicker.

At the beginning we never thought we ever use 99999 items since we the prior 10 years we only averaged around 5000 items.

Over the years new unforseen possibilities arose we now have over 187,000 items. which is a good thing and we learned about limiting ourselves.

#6

Also if you want to make it auto update without user intervention why not just use numbers then?

it can be a hidden column that no one sees anyway.

And if it’s not in the key then the users can use AA multiple times? is that what you want?

#7

Because letters give you more options. 3 letters equates to 26^3 or 17576, 3 numbers would be 999.

And trust me, we will NOT have more than 17,000!! LOL

Long story short, this is purely a “reference” table for another table - and we need the IDs to be as short as humanly possible. For exmaple, my new table would have

Code: AAA
Applies to: [Item category 1]
Name: Sample Attribute
Possible Values: [Bool]

Where AAA would be a boolean. Then, we’d have a form that our user would check either yes or no to “Sample Attribute”, and it will be stored in a totally different table in text: “AAA=1”

Either way. I’ll make another thread explaining my agenda and why it has to be concise codes like this, but for the time being, I just need some sort of auto-series using letters.

#8

When I mentioned number I wasn’t thinking of limiting it to 3 digits.

Anyway if the code field is not part of the key how are you preventing the use of AAA more than once?

Have you looked at the code posted in the link above about autoincrementing an alpha variable?

I haven;t tried it but I assume you would need to use findlast in some code to get the last used code and then increment from that point.

#9

I changed it so that the code field is the only key. I just hadn’t done that originally.

All I mean is that numbers take up more space in a text field than letters do. Storing 27000 is 5 characters where something like ZZZ is 3. That’s all.

#10

I was curious to see how something like this might look, so I scratched this out. It works, but doesn’t include any fault handling (strings shorter than 3 characters, characters outside the range, etc.) but I think it’s enough to get you going in the right direction.

You might put a call to this function in the OnInsert trigger of your table, passing in the value of the PK field in the last record in the table. Your call to this function should begin with an Offset value of 0, which tells the function to start incrementing at the last character in the string.

====================

IncCODE(VAR TargetCode : Code[3];Offset : Integer) : Boolean

IF (Offset < 0) OR (Offset = STRLEN(TargetCode)) THEN

EXIT(FALSE);

tmpCode := TargetCode; // tmpCode is same type and length as TargetCode, defined locally

CharPos := STRLEN(tmpCode) - Offset;

tmpCode[CharPos] += 1;

if tmpCode[CharPos] > 90 then begin

tmpCode[CharPos] := 65;

IF NOT IncCODE(tmpCode, Offset+1) THEN

EXIT(FALSE);

END;

TargetCode := tmpCode;

EXIT(TRUE);

===============

Caveats:

Assumes that the acceptable range of values for individual characters is a contiguous block A-Z.

Assumes that you are passing in a non-blank string, and each character is in the range A-Z.

Assumes you’re passing in a value that’s already the length you desire the value to be.

  • if you pass in ‘BB’, the function will return ‘BC’.

  • if you pass in ‘ZZ’, the function WILL NOT add a character and return ‘AAA’.

#11

Interesting, that seems like it would work. However, when I paste that in OnInsert, it says IncCODE is an unknown variable and that I have to define it under global variables.

I hate to sound stupid, but what is IncCODE supposed to be defined as? And when you mention passing in the value from the last record… how do I do that?

#12

On the field have you tried filling in the properties?

MinValue = AAA
MaxValue = zzz
CharAllowed = AZ

#13

Again, I hate to sound dumb, but how do I do that?

If someone could give me some step-by-step instructions, that would be very helpful. I’ve only ever modified C/AL code, never created it from scratch.

#14

For me I goto the form

I click Tools->Designer.

Click on the field that I was to see the properties for.

then View->Properties

#15

Also, what version are you on?

Do you have a copy of the application designers guide? it should be on your product cd and it’s also available by download.

#16

I’m on 2009 R2, and I’m using the object designer for the table directly, not a form.

#17

here’s a link to a list of properties you can use on your form, page, textbox…:

http://msdn.microsoft.com/en-us/library/dd301153.aspx

your users will not be entering data directly into the table.

#18

OK, but I’m just asking what I’m supposed to put where.

Also, I noticed I can’t export the table contents to Excel. A quick search mentioned something about a codeunit… do I have to set something up in order to be able to export table data? I just did “Run” on the table from object designer.

#19

Ah, the perils of publishing code after the sun goes down … I’ve tidied it up a bit (don’t understand my affinity for recursion, definitely not the best tool for the job here.) There is yet room for augmentation.

The IncCODE code I posted is a function, so you’ll need to create a function in your table object and put the code there. I’ve also demonstrated the basics of the NextCode function that retrieves and increments the last code in the table as the basis for the next code.

So, this next bit is a text representation of the test table object, and it includes everything you’ll need. Where you see PROCEDURE in the text below, you’ll need to define that function in your object.

If you’re still having issues, maybe e-mail me?

================================================

OBJECT Table 50001 Test

{

OBJECT-PROPERTIES

{

Date=07/26/13;

Time=[ 8:43:25 AM];

Modified=Yes;

Version List=;

}

PROPERTIES

{

OnInsert=BEGIN

IF Code=’’ THEN

Code := NextCode;

END;

}

FIELDS

{

{ 1 ; ;Code ;Code3 }

{ 2 ; ;Description ;Text30 }

}

KEYS

{

{ ;Code ;Clustered=Yes }

}

FIELDGROUPS

{

}

CODE

{

PROCEDURE NextCode@1000000001() NewCode : Code[3];

VAR

Test2@1000000000 : Record 50001;

BEGIN

WITH Test2 DO BEGIN

NewCode := ‘AAA’;

IF FINDLAST THEN

NewCode := IncCODE(Code);

END;

END;

PROCEDURE IncCODE@1000000002(SourceCode@1000000000 : Code[3]) TargetCode : Code[3];

VAR

CharPos@1000000003 : Integer;

i@1000000001 : Integer;

BEGIN

TargetCode := SourceCode;

FOR i := 0 TO STRLEN(TargetCode) DO BEGIN

CharPos := STRLEN(TargetCode) - i;

TargetCode[CharPos] += 1;

IF TargetCode[CharPos] > 90 THEN

TargetCode[CharPos] := 65

ELSE

i := STRLEN(TargetCode);

END;

END;

BEGIN

END.

}

}

#20

Alright, got my answer, thanks to George.

I appreciate the rest of you guys trying to help, though!