Automatically Maintain Record Order?

Philtoo

I am trying to add some shipping functionality to our system. In order to rate and ship packages our 3rd party software requires a sequential "box number" field for each detail record. This field *must* be a sequential number starting at 1 within each shipment.

So I've designed a header/table structure and a form to display and allow editing of the data. My detail table contains the box by box details for the shipment. The key structure is shipment number, line number, box number. The problem I'm having is keeping the boxes in sequential order while allowing the users to modify, insert, delete, etc. The table will automatically re-sort after each new record is inserted but I also need to renumber the "box number" field so that (a.) the box numbers remain sequential, and (b.) the user doesn't have to worry about where in the sequence this new record will fall. (I don't want the user to have to worry about the box number when updating or adding line items.)

The idea was to select all the lines in the shipment after a new box has been inserted and then simply re-number all the lines according to the primary key sort sequence. How should I go about this? I can't seem to find an after insert trigger which fires after the new detail line has been inserted. Would it be on my subform or my table? Is there an easier way to maintain the sequence?



Thanks,
Phil

kine

1) You need to use OnInsert, OnDelete of the table
2) Do not forget that when firing these triggers, the record is not inserted yet, or deleted yet. It means you need to work with this
3) After that you need only go through the records and assign numbers

(if deleting line, filter the line out when assigning the numbers, if inserting, reserve the number for your inserted line based on LineNo of your newly inserted record)

Philtoo

So, is there a way to force the system to insert the pending record before the trigger exits? Would a COMMIT or Rec.INSERT update the table with the new record?

It would be much simpler to re-sequence the table after the pending updates are complete.

Savatage

Your key must include box number because I see 3 line #10000's. :-k
Must the Box # be assigned so early in this process?
How about assigning them at the end once the entire shipment is ready to go?

Is there a reason this can't happen?

Philtoo

Yes, the Box No is part of the record key. This keeps the key unique while still referencing a specific shipment and line number for the given box.

I could in theory assign the box numbers at any point before I passed the data to the 3rd party routines.

What if I were to change the Box No field to be a FlowField. Could I somehow define a calc formula so that the field value would indicate the record position within the sort order? If that works would I still be able to use the field as part of the record key?

Savatage

If the Box # has to be unique then there is no real point of having a line number? is there.

I'm not sure how you export the data - but my thought was either a function that adds box numbers once the shipment is complete or on export (say using a dataport) if that's what you use. As each box gets exported
Box := Box +1;

Also by using the box as the key & losing the line no - your boxes should sort in order.
Another thought is if you had a total box count you could then see if the last box equals that total box # else there's a missing sequential box #... Count is an option for a flowfield.

Philtoo

Point taken on the key structure. I can work with this. Thanks!

I believe I'll try it just as you suggest. I already have the FlowField setup on the header record to give me a "box count".

Philtoo

Thanks, that worked. Now I'm having trouble with the ability to delete boxes. The code I've designed only works if the user deletes one box at a time. Otherwise I get one of a few different error messages. The error messages are indicating that the record that was going to be deleted next was modified outside the process, and then the delete operation aborts leaving me with just 1 of the records deleted.

So again I'm in need of an AFTER delete trigger, or a delete trigger which is only called one time rather than once for each record.

This is how I was going about the delete process. Selecting all the box records, renaming them to a new range, and then renaming them back to a sequential order less the record to be deleted. The below code works fine if the user is only attempting to delete 1 box at a time.

Form - OnDeleteRecord() : Boolean

ShipmentNo := Rec."Warehouse Shipment No";

Rec.DELETE;
COMMIT;

ShipmentLines.SETRANGE("Warehouse Shipment No", ShipmentNo);

BoxCnt := 10000;

IF ShipmentLines.FINDFIRST THEN REPEAT
  BoxCnt := BoxCnt + 1;
  ShipmentLines.RENAME(ShipmentNo, BoxCnt);
UNTIL ShipmentLines.NEXT = 0;

COMMIT;

BoxCnt := 0;
ShipmentLines.RESET;
ShipmentLines.SETRANGE("Warehouse Shipment No", ShipmentNo);

IF ShipmentLines.FINDLAST THEN REPEAT
  BoxCnt := BoxCnt + 1;
  ShipmentLines.RENAME(ShipmentNo, BoxCnt);
UNTIL ShipmentLines.NEXT = 0;

COMMIT;

CurrForm.UPDATE;

EXIT(FALSE);

So the questions are:

1.) How can I change the code to allow the user to delete multiple boxes at the same time?

2.) How can I suppress the "Are you sure you want to rename this record?" prompt?

Philtoo

Ok, this is strange. I've been able to resolve the issue in the previous post. I'm not sure why this works, but I changed the CurrForm.UPDATE; to CurrForm.UPDATE(FALSE); and now I can delete multiple lines without an error message.

HOWEVER, some strange issues still persist.

If I select rows 1, 2, and 3 and then delete them. The system deletes rows 1, 3, and 5.
If I select rows 1, 3, and 5 and then delete them. The system deletes rows 1, 4, and 7.

LOL, what is going on here?!

Its almost as if the selection isn't changing as the rows get removed. So as row 1 is removed and row 2 takes its place the selection doesn't change, even though the data in that row has changed.

Here is the code:

Form - OnDeleteRecord() : Boolean

ShipmentNo := Rec."Warehouse Shipment No";

Rec.DELETE;
COMMIT;

ShipmentLines.SETRANGE("Warehouse Shipment No", ShipmentNo);

BoxCnt := 10000;

IF ShipmentLines.FINDFIRST THEN REPEAT
  BoxCnt := BoxCnt + 1;
  ShipmentLines.RENAME(ShipmentNo, BoxCnt);
UNTIL ShipmentLines.NEXT = 0;

COMMIT;

BoxCnt := 0;
ShipmentLines.RESET;
ShipmentLines.SETRANGE("Warehouse Shipment No", ShipmentNo);

IF ShipmentLines.FINDLAST THEN REPEAT
  BoxCnt := BoxCnt + 1;
  ShipmentLines.RENAME(ShipmentNo, BoxCnt);
UNTIL ShipmentLines.NEXT = 0;

COMMIT;

CurrForm.UPDATE(FALSE);

EXIT(FALSE);

kine

1) I am not sure, if OnDelete on the form is the correct trigger for you (it should only return if it is possible to delete the record or not, not to delete it...
2) In the loops, you are renaming the records, but you are iterating through them. Ok, it should not change the order, but it is not good in general. At least, use the FINDSET(True,True) to do it nicely and never use the FINDFIRST when using repeat until after it!

Philtoo

If I use FINDSET the records get all out of order. When I use FINDLAST the boxes maintain their original sequences, IE if I delete box 1, then box 2 becomes box 1, etc. If I use FINDSET and delete box 1, box 2 becomes the last box in the set and then the rest maintain sequence.

kriki

[Topic moved from 'NAV 2009' forum to 'NAV/Navision' forum]