How to do the following calculation

DarkHorse · 2008-09-29

Hello, I must realise a comparison of amounts and I do not know very clearly like doing it; I suppose that more than nothing it's a formula concept.
I 've created a table with the fields "Client", "Product" and "Amount to reserve" and what I want is that when realising a sale order, before registering, if the client of the order is not the same from the table that I've created, let's register but don't if the product is reserved for another client.
I've got it more or less. But now I want that if there are Inventory it allows to register the order although is not for the indicated client.
I put an example:
Amount to reserve: 200
Inventory: 100
Requested amount in sale line: 50
In this case it would not have to let register because the amount of the order and Inventory they give 150 and I have reserved 200 for another client at the moment.
On the following example would have to leave:
Reserved amount: 200
Inventory: 500
Requested amount in sale line: 250
Because they would continue being 250 and I only need to reserve 200. I'll like to know the formulate or expression (translated to c/AL) have I to use to do this comparison.
Thank you very much.

Stardust77 · 2008-09-29

First of all, why are you not using the Reservation feature from Navision?

Anyhow, I would create a function in the "Sales Line" table, let's say CheckReservation.

In this function, supposing your new created table is referenced as MyTable, and is having one of the keys: Client,Product, I would write the following code:

Item.GET("No.");
Item.CALCFIELDS(Inventory);

ReservedQty := 0;
MyTable.RESET;
MyTable.SETCURRENTKEY(Client,Product);
MyTable.SETRANGE(Product,"No.");
IF MyTable.FIND('-') THEN
  REPEAT
    ReservedQty := ReservedQty + MyTable."Amount to Reserve";
  UNTIL MyTable.NEXT = 0;
IF ReservedQty > Item.Inventory THEN
  ERROR('Your reservation for this item is greater than the available Inventory.')
ELSE
  IF "Quantity (Base)" +  ReservedQty > Item. Inventory THEN
    ERROR('You cannot register more than %1', Item.Inventory - ReservedQty);

kine · 2008-09-29

DarkHorse wrote:

Hello, I must realise a comparison of amounts and I do not know very clearly like doing it; I suppose that more than nothing it's a formula concept.
I 've created a table with the fields "Client", "Product" and "Amount to reserve" and what I want is that when realising a sale order, before registering, if the client of the order is not the same from the table that I've created, let's register but don't if the product is reserved for another client.
I've got it more or less. But now I want that if there are Inventory it allows to register the order although is not for the indicated client.
I put an example:
Amount to reserve: 200
Inventory: 100
Requested amount in sale line: 50
In this case it would not have to let register because the amount of the order and Inventory they give 150 and I have reserved 200 for another client at the moment.
On the following example would have to leave:
Reserved amount: 200
Inventory: 500
Requested amount in sale line: 250
Because they would continue being 250 and I only need to reserve 200. I'll like to know the formulate or expression (translated to c/AL) have I to use to do this comparison.
Thank you very much.

For me it seems like standard calculation of Item availability which will display you the Inventory warning.

All is done on the Warning Form (Form 342) - See function "Calculate".

AvailToPromise.QtyAvailabletoPromise(
  Rec,GrossReq,SchedRcpt,GETRANGEMAX("Date Filter"),
  CompanyInfo."Check-Avail. Time Bucket",CompanyInfo."Check-Avail. Period Calc.");

EarliestAvailDate :=
  AvailToPromise.EarliestAvailabilityDate(
    Rec,-NewItemNetChange,GETRANGEMAX("Date Filter"),-OldItemNetChange,OldItemShipmentDate,AvailableQty,
    CompanyInfo."Check-Avail. Time Bucket",CompanyInfo."Check-Avail. Period Calc.");

IF NOT UseOrderPromise THEN
  SchedRcpt := 0;

CALCFIELDS(Inventory,"Reserved Qty. on Inventory");
InventoryQty := ConvertQty(Inventory - "Reserved Qty. on Inventory");
GrossReq :=  ConvertQty(GrossReq);
SchedRcpt := ConvertQty(SchedRcpt);

ItemNetChange := 0;
IF "No." = ItemNo THEN BEGIN
  ItemNetChange := NewItemNetChange;
  GrossReq := GrossReq + OldItemNetChange;
END;

InitialQtyAvailable := InventoryQty + SchedRcpt - GrossReq;

DarkHorse · 2008-09-30

Thanks both for reply. I know that there is the Reservation feature from Navision, precisely we're doing this table because the reservation feature form Navision it's a little confused and we need somethig easier and simple; thanks of all ways.
I've tried your code but it seems don't work well, but I it's fault from mine; I've forgotten to comment that when the customer it's the same from my reserve table it let's to register. Also, in my reserve table, I have the option field Reserve, marking it would have to allow all the explained one, but not if it is not marked. How could to include this two conditions in the code?.
Many thanks for help.

Stardust77 · 2008-10-01

To include these two conditions, I think you should include in your code the following two lines that have comments above them.

Item.GET("No.");
Item.CALCFIELDS(Inventory);

ReservedQty := 0;
MyTable.RESET;
MyTable.SETCURRENTKEY(Client, Product);
MyTable.SETRANGE(Product, "No.");
//To calculate all the reservations except these made for the specific customer
MyTable.SETFILTER(Client, '<>%1', "Sell-to Customer No.");
//To choose only the Items that have Reserve marked
MyTable.SETRANGE(Reserve, TRUE);
IF MyTable.FIND('-') THEN
  REPEAT
    ReservedQty := ReservedQty + MyTable."Amount to Reserve";
  UNTIL MyTable.NEXT = 0;
IF ReservedQty > Item.Inventory THEN
  ERROR('Your reservation for this item is greater than the available Inventory.')
ELSE
  IF "Quantity (Base)" +  ReservedQty > Item. Inventory THEN
    ERROR('You cannot register more than %1', Item.Inventory - ReservedQty);

DarkHorse · 2008-10-01

Thanks for reply. When I attempt to register the sales order it shows me the message: "Item Nº PV15246 don't exist", I think is comparig the Nº of item with the sales order Nº, not the sales line item Nº with Mytable item Nº.. Can't you help me please?.
Thanks.

Stardust77 · 2008-10-03

You should ensure that the function is in the Table 37 - Sales Line, and not in the Table 36. The function should be trigerred from the field "Qty. to Ship", or "Qty. to Ship (Base)" by inserting a line of code like

CheckReservation;

and it should work.

Sorry, I forgotten something very important #-o:

IF Type = Type::Item THEN BEGIN  //To add this line
  Item.GET("No.");
  Item.CALCFIELDS(Inventory);

  ReservedQty := 0;
  MyTable.RESET;
  MyTable.SETCURRENTKEY(Client, Product);
  MyTable.SETRANGE(Product, "No.");
  //To calculate all the reservations except these made for the specific customer
  MyTable.SETFILTER(Client, '<>%1', "Sell-to Customer No.");
  //To choose only the Items that have Reserve marked
  MyTable.SETRANGE(Reserve, TRUE);
  IF MyTable.FIND('-') THEN
    REPEAT
      ReservedQty := ReservedQty + MyTable."Amount to Reserve";
    UNTIL MyTable.NEXT = 0;
  IF ReservedQty > Item.Inventory THEN
    ERROR('Your reservation for this item is greater than the available Inventory.')
  ELSE
    IF "Quantity (Base)" +  ReservedQty > Item. Inventory THEN
      ERROR('You cannot register more than %1', Item.Inventory - ReservedQty); 
END;     //To add this line

DarkHorse · 2008-10-03

Ok; thanks for your help and time, I'll try it.
Thank you very much.

Stardust77 · 2008-10-03

You're welcome.

In case it will work, please modify the title of the topic by adding [SOLVED].

DarkHorse · 2008-10-06

Thank your very much, it works!!, only one thing. There cases when there are more than a line on the sales line; in case where the reserved product is on the second (or henceforth) line, when shows the line "ERROR('You cannot register more than %1', Item.Inventory - ReservedQty);" because find the reserved product on register, it shows the "Item.Inventory - ReservedQty" from the first line, I mean that don't shows the real quantity, it seems like don't look for the rest of the lines of sales line. Pardon if I don't explain myself well. You know what I mean?, I have tried to put a FIND('-') but it doesn't work to me.
Thanks in advance.

Stardust77 · 2008-10-07

Please check below if I did understand what you ment.

So, let's consider the following scenario. We have the Item No. 123, Inventory = 15 pcs, Reserved Qty. = 2 pcs. We have this Item with the Quantity (Base) = 10 pcs. on the second line of the Sales Order. The CheckReservation function will find that 10 < 15 - 2, therefore you can go further. On the sixth line of our Sales Order, we have again the Item 123, with a Quantity (Base) of 6. The CheckReservation function will again check the availability of these 6 pieces, and will find that everything is ok, because 6 < 15 -2. So, in total, we will be allowed to register (BTW, register = post?) 10 pcs. + 6 pcs. = 16 pcs., which is more than we have in the Inventory, and even more than we have available not reserved.

Is this correct?

DarkHorse · 2008-10-07

Thanks for reply, it doesn't correct but near; sorry if I have not explained myself well. If there is a Reserve quantity and the function is fulfilled, gives the error message and it's correct, it does not concern the lines that there is, the problems is the message. For example, there are two lines:

Product Quantity Inventory Reserved
001 20 50 10
002 30 20 10

Imgine that the reserved product it's the product 002 and my code line from error message says:
"ERROR('You cannot register more than %1', Item.Inventory - ReservedQty);"

On register it's stop because find a reserved line, but the message says:
ERROR: "You cannot register more than 40"
And it would have of being "You cannot register more than 10", because is the product 002. The problem is the message, no the function.
Sorry if I don't explain well.
Thanks.

How to do the following calculation

Comments

Categories