Update only one sales line per Trip

Gary_J · 2009-10-02

Hi,

"Trip" is a custom field linked to a new table, it basically identifies the lorry a sales line is being shipped on. Each sales order can have different Trips against each line, eg:

10000 / Item 1 / Trip 5000A
20000 / Item 2 / Trip 5000A
30000 / Item 3 / Trip 6030B

etc.

I need to find a way to loop through the sales lines and pull a value from the Trips table and assign it to the first line using that trip. So:

10000 / Item 1 / Trip 5000A - assign the value
20000 / Item 2 / Trip 5000A - don't assign the value
30000 / Item 3 / Trip 6030B - assign the value

I've created a processing-only report which does the loop and assigns the value, but at the moment it assigns the value to all the sales lines. This is the code in OnAfterGetRecord:

IF trip.GET("Sales Line".TripNo) THEN
"Haulage Cost" := trip."Haulage Cost";

Can anyone help?

kapamarou · 2009-10-02

Gary_J wrote:

IF trip.GET("Sales Line".TripNo) THEN
"Haulage Cost" := trip."Haulage Cost";

Since you are in a report you could do the following:

IF BoolLineModified THEN 
  CurrReport.SKIP;
IF trip.GET("Sales Line".TripNo) THEN BEGIN
"Haulage Cost" := trip."Haulage Cost";
  BoolLineModified := TRUE;
END;

You will need a condition to set BoolLineModified to false maybe in a Sales Header DataItem
Practically there are tons of ways to achieve what you want.

Gary_J · 2009-10-02

Hi, thank you for the advice.

I have tried what you suggested and while it works fine if there is only one trip no on the sales lines, it does not work if there are multiple trip numbers (like my example). I put BoolLineModified := false in the OnPreDataItem section, is that the right place? Also, the report only runs on the Sales Lines.

kapamarou · 2009-10-02

Can you post your code here along with the dataitems setup?

Gary_J · 2009-10-02

Documentation()

OnPreDataItem()

    "Sales Line".SETRANGE("Document Type", "Document Type"::Order);
    "Sales Line".SETRANGE(Type, Type::Item);

BoolLineModified := FALSE;

    iCount := 0;
    iTotal := "Sales Line".COUNT;
    dlgProgress.OPEN(CurrReport.OBJECTID(FALSE) + ' Updating Haulage Costs on Sales Lines:\' +
        '@1@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@');

OnAfterGetRecord()

IF BoolLineModified THEN
CurrReport.SKIP;

IF trip.GET("Sales Line".TripNo) THEN BEGIN
 "Haulage Cost" := trip."Haulage Cost";
  BoolLineModified := TRUE;
modify(true);
END;

    iCount := iCount + 1;
    dlgProgress.UPDATE(1, ROUND(iCount * (10000 / iTotal), 1));
    YIELD;

OnPostDataItem()
dlgProgress.CLOSE;

kapamarou · 2009-10-02

What sorting have you used for the sales line? Have you tried to debug the report to see were it exits?

Gary_J · 2009-10-02

Sorting on the sales line is "SORTING(Document Type,Document No.,Line No.) ORDER(Ascending)".

If I use the debugger (with breakpoint on triggers), the report appears to run OK for the first trip but exits before it gets to the next one. I think it keeps looping through the same section (for one trip) because BoolLineModified is always TRUE, and because it can't detect the change in Trip number the report finishes.

kapamarou · 2009-10-02

So you need to Rechange the value if the trip code changes?

In such a scenario you could:

IF BoolLineModified AND( processedTripNo = TripNo) THEN 
  CurrReport.SKIP;
IF (trip.GET("Sales Line".TripNo) AND ( processedTripNo <> TripNo) THEN BEGIN
"Haulage Cost" := trip."Haulage Cost";
  BoolLineModified := TRUE;
  processedTripNo := TripNo;
END;

Something like that. You could also create a temporary table variable and store in it the processed trips changing your code to something like:

IF NOT tempTripTable.GET(TripNo) THEN BEGIN
END;

Does this help?

Gary_J · 2009-10-02

Yes, that's right about the value changing if the trip code changes.

Thank you for the help, will give your suggestion a go!

Gary_J · 2009-10-02

Great, I think it works!

There was one small problem I found when I noticed the same trip number being used further down the sales lines, so the report was copying the value again (because the trip number had changed in between). However, I was able to change the sort sequence on the sales lines to TripNo and it appears to be working.

Thank you very much for your help!

kapamarou · 2009-10-02

You're welcome. But you should test it extensively. Maybe changing your sorting to TripNo changes also the First Line in the recordset... Just an idea...

Update only one sales line per Trip

Comments

Categories