Overwriting Existing Record

elTorito

Hi, ....

I Have a Table with Primary Key, ItemNo., Priority

An ItemNo. can have Priority from 0 to 9. (Priority is an Integer).

Now i Have 3 ItemNo.

ItemNo. Priority

---

NR001 0
NR001 1
NR001 2


Now i get the record With ItemNo.= NR001 and Priority= 1
I would like change the 1 in 2 ...
Now Navision asked if i would like change record.
If i say yes it cames an error that the record already exists, if i say none it resets my input to 1 again.

What must i do for ommit this error?

In the Trigger OnValidate i check if exists already a record with the ItemNo. and Priority what i would Input, when if exists i get out
MESSAGE('STOP. Priority 2 already Exists');

IF the ItemNo. has no Priority, e.g. 3. then i would change directly the data, so that the error mencioned (Would you like change Data) don't appears.

Understand your what i mean?

Thanks.


---
EDIT:
Other Way to solution my wish is follow idea:
In the OnValidate Trigger:

IF GET("ItemNo.", Priority) THEN BEGIN
     // IF ItemNo. WIth new inputed Priority exists
     MESSAGE('Item: ' + "ItemNo." + ' with Priorety ' + FROMAT(Priority) + ' already exists');
    
    // Check what prioretys are free
    // Foreach Record ItemNo. must check if used or not
    
    FOR IDX := = TO 9 DO
       IF IDX <>Priority THEN
          FreePriority := FreePriority + ', ' + FORMAT(IDX);

END ELSE BEGIN
     // MUST SAVE THE NEW VALUE
END;

But this give me out all the Prioritys except the new what i would insert.
Perhaps can help me one to complete this because i don't know what i must di for ask each Record.

If i think right, at the moment when the Trigger OnValidate runs i have only one record (the actual).

Thanks.
.

i4tost

Use xRec.Priority instead of Priority.

elTorito

i4tost wrote:

Use xRec.Priority instead of Priority.

So i make it:

// Filter for ItemNo. 
xRec.SETFILTER("ItemNo.", xRec."ItemNo.");

IF xRec.GET("ItemNo.", Priority) THEN BEGIN
  MESSAGE('Item with Priority ' + FORMAT(Priority) + ' exists') ;
  FOR idx := 0 TO 9 DO
    IF xRec.GET("ItemNo.", idx) THEN
      FreePriority := FreePriority
    ELSE
      IF idx = 9 THEN
        FreePriority := FreePriority + FORMAT(idx)
      ELSE
        FreePriority := FreePriority + FORMAT(idx) + ', ';
  MESSAGE('Propertys who are free for use: ' +   FreePriority);
  FreePriority := ''
END;

I Intend make it with other way, but i don't find the appropiates functions in the C/Al - C/Side Help

I would make this so:


FOREACH ROW in xRec DO Something
But now i search again for make it better.

i4tost

IF GET("ItemNo.", Priority) THEN BEGIN
// IF ItemNo. WIth new inputed Priority exists
MESSAGE('Item: ' + "ItemNo." + ' with Priorety ' + FROMAT(Priority) + ' already exists');

// Check what prioretys are free
// Foreach Record ItemNo. must check if used or not

FOR IDX := = TO 9 DO
IF IDX <>xrec.Priority THEN
FreePriority := FreePriority + ', ' + FORMAT(IDX);

END ELSE BEGIN
// MUST SAVE THE NEW VALUE
END;

DenSter

ElTorito,

There are two ways that you can change a record. The first one is obviously the MODIFY method, which you can use when you need to change any non-primary key field.

When you need to change the primary key fields however, like you want to do here, you are trying to change the identity of the record. In this case, you have to use the RENAME method instead.

You said you had the following records:

ItemNo. Priority

---

NR001 0
NR001 1
NR001 2

and you want to change NR001/1 into NR001/2. You would do this by using the RENAME command.

MyRec.GET('NR001','1');
MyRec.Priority := '2';
MyRec.RENAME;

Obviously you can't do that because the record NR001/2 already exists. Now, you can program your system to first look for record NR001/2 before you try to rename it, which is perfectly fine, but locky for you, RENAME has a return value that you can use to not let your system error out. So I'd do something like this:

MyRec.GET('NR001','1');
MyRec.Priority := '2';
IF MyRec.RENAME THEN BEGIN
  //woohoo! it worked!
  //code for when it renamed the record
END ELSE BEGIN
  //too bad, it failed
  //code for when it did not rename the record
END;

Now for your question to get a recordset and something for each row, an example:

MyRec.RESET; //clears all filters
MyRec.SETFILTER(ItemNo,'=%1','NR001');
IF MyRec.FIND('-') THEN REPEAT //this gets the filtered recordset
  //Code to change the priority, this may be a separate function
  IF MyRec.RENAME THEN BEGIN
    //woohoo! it worked!
    //code for when it renamed the record  
  END ELSE BEGIN
    //too bad, it failed
    //code for when it did not rename the record  
  END;
UNTIL MyRec.NEXT = 0; //this loops until the last record

Let me know if you have any more questions.

elTorito

Hello Daniel,

first thanks for you detailed answer, thanks too 'it4tost'.

With Rename this works Fine
but if a try this:

myRec.GET(xRec."ItemNo.", xRec.Priority);
IF myRec.RENAME(xRec."ItemNo.", Priority) THEN BEGIN
  MESSAGE('Renamed was fine');
END ELSE BEGIN
  MESSAGE('Renamed failed');
END;

This gives me out a Message 'Renamed was fine', but then i would go to the next record, it ask me "Do You want to rename the record'

If I say 'No', the Message disapperas (Record was yet renamed), if i say 'yes' it appears a message and says me that record does not exist.

e.G.
Key is "ItemNo.", Priority

Records for ItemNo. NR001

ItemNo. Priority
NR001 1
NR001 2
NR001 3

I try the Record with Priority 2, and will them rename to 4 .
Give message out: Renamed was fine.
I Press OK.
Now in my form i would go to the next/previous record.
It asks me "Do you want to rename the record"
I say No.
I look in my Table and the Record was renamed to 4.
So i have now:

ItemNo. Priority
NR001 1
NR001 3
NR001 4

Now i get the Form with the Record with Priority 4.
I would it renamed to 3.
It Says me that Record exists.
(Identification Fields and values: ItemNo.='NR001', Priority=3)

Because the Record exists the Renamed was failed, but why did
not came my defined Error Message ('Renamed failed')?

hmm. :roll:

If i leaved
myRec.RENAME;
Blank without Values, i obtain an Error that Rename Function must have One Value. So I Think my Error must be in this Values.

When i Run the Form i Get a Record and my textboxes are filled with this values. This Values are "xRec" or not ?
e.g.
xRec."ItemNo." // for the actual ItemNo in Record

I think there is a little bite that i'm don't see.

I thanks again for our help. It helps me a lot for understand this.

Peter

PS: EDIT:
Can it be that i put my C/AL Code in a bad trigger ?
Momentanly i have the code in the Trigger OnValidate on the Priority Option Box. I will go to check this now.


.

i4tost

In OnAfterValidate triger on form write

COMMIT;
CURRFORM.UPDATE(FALSE)

The form is not updated and has old values. That's the problem.

elTorito

i4tost wrote:

In OnAfterValidate triger on form write
COMMIT;
CURRFORM.UPDATE(FALSE)
The form is not updated and has old values. That's the problem.

ahh. :idea:

Thanks :!: :!: