Fun and Games with .GET

djswim

So... my code is skipping this line:

IF "Default Dimension".GET(27,ItemNo) THEN BEGIN

Even though there is a line in the Default Dimension table with a Table No. of 27 and an Item No. of 04AGMM (which is what is in the ItemNo variable at the time of the error)

Table ID No. Dimension Code Dimension Value Code Value Posting Multi Selection Action Table Name
27 04AGMM PROD LINE PL1020 Item

That's messy, but I just copied it out of the table to prove to myself and the world that I'm not crazy. I know it's been a while since I did Dev. work, but c'mon, how am I messing up a simple .GET?

Any help is much appreciated.

djswim

I have a feeling this has something to do with the fact that "Table ID" is a datatype of Integer, but I have no idea how the heck I'm supposed to express it except as

27

In some cases I'm getting a conversion type error of Text to Integer... when I finally figure this out it's going to be a #-o because I know it's so simple, but right now, it's more of a ](*,)

Karenh

The primary key on the Default dimension table has three fields:
Table ID, No., Dimension Code

Try adding the 3rd parameter which would be something like Dept or Project.

djswim

IF "Default Dimension".GET(27,ItemNo,'PROD LINE') THEN BEGIN

PROD LINE is the Dimension Code... also in that line as shown above.

Still skipping

Edited to add: (Edited again too)

I tried to work around it like so:

"Default Dimension".SETFILTER("Table ID",'27');
"Default Dimension".SETFILTER("No.",ItemNo);
IF "Default Dimension".FIND('-') THEN BEGIN

but that didn't work either... on this one I get the conversion type error if I don't have the single quotes around the 27. I'm thinking it has to be something to do w/ the table id since the item number is in there.[/code]

David_Singleton

djswim wrote:

That's messy, but I just copied it out of the table to prove to myself and the world that I'm not crazy. I know it's been a while since I did Dev. work, but c'mon, how am I messing up a simple .GET?

Any help is much appreciated.

The get needs all elements of the primary key.

David_Singleton

try

"Default Dimension".SETRANGE("Table ID",27);
"Default Dimension".SETRANGE("No.",ItemNo);
IF "Default Dimension".FINDSET THEN BEGIN

djswim

I think that SETRANGE worked, thank you... I'm just going to post all the code from that dataitem here because now I'm having trouble getting the debugger to tell me where the error is occuring:

"Default Dimension".SETRANGE("Table ID",27);
"Default Dimension".SETRANGE("No.",ItemNo);
IF "Default Dimension".FINDSET THEN BEGIN
    IF "Default Dimension"."Dimension Value Code" <> ItemDimValue THEN BEGIN
        "Dimension Value Code" := ItemDimValue;
        VALIDATE("Default Dimension"."Dimension Value Code");
    END;
END;

IF NOT "Default Dimension".GET(27,ItemNo) THEN BEGIN
    "Default Dimension".INIT;
    "Default Dimension"."Table ID" := 27;
    "Default Dimension"."No." := ItemNo;
    "Default Dimension"."Dimension Code" := 'PROD LINE';
    IF ItemDimValue <> '' THEN
        "Default Dimension"."Dimension Value Code" := ItemDimValue;
    "Default Dimension".INSERT;
END;

I know it's not pretty, but should be straightforward. Looking for a unique combo of Table ID and No., if it exists and the Dim. Value doesn't match the variable, it replaces it. If it doesn't exist, it creates it.

The error I'm getting now is:

"The Default Dimension already exists. Table ID='27',No.='041GMM',Dimension Code='PROD LINE'

ajhvdb

The second IF rereads the record values and overwrites the first IF..

Your first IF should be replaced in a GET with the 'PROD LINE' value. Can't a colleague help you first.

DenSter

"Default Dimension".SETRANGE("Table ID",27);
"Default Dimension".SETRANGE("No.",ItemNo);
IF "Default Dimension".FINDSET THEN BEGIN
    IF "Default Dimension"."Dimension Value Code" <> ItemDimValue THEN BEGIN
        "Dimension Value Code" := ItemDimValue;
        VALIDATE("Default Dimension"."Dimension Value Code");
    END;
END;

What this does is find the first default dimension for the Item table, and if the dimension code is not the same you change it on the fly. Why would you change the default dimension for a specific Item on the fly? What happens when the one you want to see is already in there as the second default dimension?

IF NOT "Default Dimension".GET(27,ItemNo) THEN BEGIN
    "Default Dimension".INIT;
    "Default Dimension"."Table ID" := 27;
    "Default Dimension"."No." := ItemNo;
    "Default Dimension"."Dimension Code" := 'PROD LINE';
    IF ItemDimValue <> '' THEN
        "Default Dimension"."Dimension Value Code" := ItemDimValue;
    "Default Dimension".INSERT;
END;

You are not following people's suggestion about providing all primary key fields. When using GET, you MUST specify ALL primary key fields to make sure that you retrieve the unique record from the table. The Default Dimension table has 3 primary key fields, so you MUST provide 3 fields as parameters for GET.

What puzzles me is that you are retrieving the same record twice with different methods. I'm starting to think that you are a consultant who is getting his feet wet writing C/AL code, the single most dangerous type of consultant in my opinion . You should leave writing code to a developer, or learn how to do it properly first.

Could you just tell us in functional terms what you're trying to do, and we'll help you sort out the syntax.

David_Singleton

DenSter wrote:

Could you just tell us in functional terms what you're trying to do, and we'll help you sort out the syntax.

I think this may be ahint:

djswim wrote:

http://www.mibuso.com/forum/viewtopic.php?t=28434&postdays=0&postorder=asc&start=0

So you're suggesting that I:

1. "Change Global Dimensions" function in the GL Setup
2. Delete "Prod Line"
3. Add "Prod Line" to Dim 3 or 4
4. ????
5. Delete "Prod Line from Dim 3 or 4
6. Add "Prod Line" back to my Global Dim 1 field

Could you explain the ???? part?

my guess is we are at item 4 the ???? bit

djswim

Ok... let's try this again:

1. I'm really not new at this, I just haven't written code in over 4 years... well... I guess that means you should assume I'm new
2. Here is what I'm trying to do:

a. My report has two dataitems, Item and Default Dimension, DD is nested inside Item
b. All the Item dataitem does is pass the Item No. and Product Group Code into variables... I have that part working
c. Now, because the PGC on the Item is correct, and the Default PROD LINE Dimension is not, I want to look for the Default Dimension, PROD LINE value of this Item... if it's there but not correct, I want to change it, if it's not there at all, I want to create the record and make it correct

Essentially, I want Global Dimension 2 Code to equal Product Group Code for all items.

Thanks for slogging through this with me, I don't think I'm that far off... am I?

P.S. Don't worry, I'm in a test database... I'm as paranoid as any of you are I promise

djswim

David Singleton wrote:

DenSter wrote:

Could you just tell us in functional terms what you're trying to do, and we'll help you sort out the syntax.

I think this may be ahint:

djswim wrote:

http://www.mibuso.com/forum/viewtopic.php?t=28434&postdays=0&postorder=asc&start=0

So you're suggesting that I:

1. "Change Global Dimensions" function in the GL Setup
2. Delete "Prod Line"
3. Add "Prod Line" to Dim 3 or 4
4. ????
5. Delete "Prod Line from Dim 3 or 4
6. Add "Prod Line" back to my Global Dim 1 field

Could you explain the ???? part?

my guess is we are at item 4 the ???? bit

Kind of.... I figured I could work on fixing the codes while I waited on responses to that other post... they're related, but I don't think what I'm trying to do is #4....

djswim

Alrighty, two data items:

Item
Default Dimension

Here is the Item Code

ItemNo := '';

ItemNo := "No.";
ItemDimValue := "Product Group Code";

A clear would work as well I suppose... any tips by the way on stuff I could have done better would be appreciated. I also stuck an Item.NEXT in the OnPostDataItem trigger.

Ok, now the Def. Dim. code:

CLEAR(RecChecker);
"Default Dimension".SETRANGE("Table ID",27);
"Default Dimension".SETRANGE("No.",ItemNo);

RecChecker := "Default Dimension".GET(27,ItemNo,'PROD LINE');

CASE RecChecker OF
FALSE: NoRecord;
TRUE: RecordExists;
END;

CLEAR(RecChecker);

On the OnPostDataItem here I just have some clearing of variables. As you can see, it got to the point where I wasn't sure about the nested IF statements... there was a comment above that it was a problem, so I circumvented it with the use of CASE and a few functions. Here they are:

NoRecord

    "Default Dimension".INIT;
    "Default Dimension"."Table ID" := 27;
    "Default Dimension"."No." := ItemNo;
    "Default Dimension"."Dimension Code" := 'PROD LINE';
    IF ItemDimValue <> '' THEN
        "Default Dimension"."Dimension Value Code" := ItemDimValue;
    "Default Dimension".INSERT;

RecordExists

    "Default Dimension".DELETEALL;
    NoRecord;

In the second one I realized that something I was doing to modify the record wasn't working, so I just wiped it and started from scratch. Yes, I know, the DELETEALL is an evil bastard... but in this case my filtering works. If this were for a more important table, I would NEVER use this thing... besides, none of my clients items have any other dim. assignments anyway except for one that's an old test item from conversion, so that helped give me the courage to use it.

Thanks for the help everyone... I'm glad I finally got it on my own because that means I'll retain it a little better, turns out it was just a series of n00b mistakes because I haven't coded in so long.

DenSter

Alright now we know a little more. Basically the design is flawed from the start. You have a dataitem for the Item table, and one indented for Default Dimension, presumably linked by the value of the Item's "No." field. I assume that your code is in the DD's OnAfterGetRecord trigger. The problem is, that for an Item without any default dimensions, you will never get to that code, because the report won't find any default dimensions, and therefor never executes the OnAfterGetRecord trigger.

So..... remove your default dimension dataitem, and handle the whole thing through code. Loop through the Items and program the default dimension logic in the Item's OnAfterGetRecord. Something like this:

Item - OnAfterGetRecord
IF DD.GET(27,Item."No.",'PROD LINE') THEN BEGIN
  IF NOT (DD."Dimension Value" = Item."Product Group Code") THEN BEGIN
    DD."Dimension Value" := Item."Product Group Code";
    DD.MODIFY;
  END;
END ELSE BEGIN
  DD.INIT;
  DD."Table ID" := 27;
  DD."No." := Item."No.";
  DD."Dimension Code" := 'PROD LINE';
  DD."Dimension Value" := Item."Product Group Code";
  DD.INSERT;
END;

By the way, you wouldn't have to pass any field values into any variables. From the DD dataitem, all current Item's field values are accessible just by referencing the dataitem's name.

DaveT

Hi Folks,

I know I'm coming in at the end of this discussion but standard Nav uses

DocDim.SETRANGE("Table ID",DATABASE::Item);

I think it makes a different as I had a problem with using the table number before and have use this ever since.

DenSter

djswim wrote:

Alrighty, two data items:
<snip>

Well if it works it works, can't argue with works....

I have to say though, that is NOT how I would do it at all. There is WAY too much code, and the logic is all very roundabout. I'd stick with my suggestion, that's the least amount of code. You really do not need both dataitems.

And again, you don't have to put any field values in variables, because when you indent your DD dataitem, it is at the current Item record, and you can access al its field values directly. Instead of clearing your ItemNo variable and setting it (which is redundant in itself), you can simply use Item."No." directly, same with product group code.

Anyway, you say it works, I'm sure you verified it

DaveT

Hi Daniel,

I was not recommending one method over the other but recommending using DATABASE::Item instead of the value 27. I have found inconsistencies when using the integer number (mainly in reports) so I stick to the DATABASE way now.

DenSter

I wasn't talking about your suggestion Dave, sorry if I gave that impression. Djswim went back to a previous post and edited it, so the alignment of the replies got messed up a bit.

Your suggestion of DATABASE::TableNumber is actually a much better way to do it than to hardcode the number. I was being a bit lazy when I wrote that reply.

DaveT

Hi Daniel,

I probably should be more clear on these things - As you say a GET is far more efficient and is the way I would allways go if appropriate