?
Solved

Sales and Warehouse expiry dates

Posted on 2003-03-17
10
Medium Priority
?
340 Views
Last Modified: 2010-04-04
I have 2 tables Warehouse (WH) and Sales (SL)
WH has fields: Prod_Code, Expiry_Date & Quantity
SL has fields: Prod_Code, Quantity.

I am trying to achieve the following: For every sale that I make, I want the quantity sold to be subtracted from the WH from the same Prod_Code which will expire sooner. This way, I follow FIFO.

Can someone PLEASE help me with this??
I am a roockie and I am trying to make this work!

Thank you in advnace
Chris.
0
Comment
Question by:cnroubek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 10

Assisted Solution

by:kacor
kacor earned 100 total points
ID: 8157758
Hi Chris,

what kind of database you use? My answer depends on.

wbr

Janos
0
 
LVL 10

Expert Comment

by:kacor
ID: 8157911
In general:
1. You have tomake a view of WH indexed by date (ascending)
2. You subtract the needed quantity from the first item.  If it won't be sufficient you subtrract the rest from the second etc

Janos
0
 

Author Comment

by:cnroubek
ID: 8158410
The DataBase is access. What I need is the code itself. The general principle is clear to me.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Accepted Solution

by:
bogiboy earned 100 total points
ID: 8161165
Using ADO:

cmdWH, cmdSL:TADODataSet;
cmdWH.CommandText:='SELECT TOP 1 * FROM WH WHERE Proc_code='+IntToStr(yourProdID)+' ORDER BY Expiry_Date DESC';
cmdSL:='SELECT Quantity FROM SL WHERE Prod_code='+IntToStr(yourProdID)
cmdWH.Open;
cmdSL.Open;
cmdWH.Edit;
cmdWH.FieldByName('Quantity').AsCurrency:=cmd.FieldByName('Quantity').AsCurrency -cmdSL.FieldByName('Quantity').AsCurrency;
cmdWH.Post;
cmdSL.Close;
cmdWH.Close;
0
 
LVL 10

Expert Comment

by:kacor
ID: 8164520
Hi cnroubek,

to do are the followings:

1. Create ODBC connection to your file (If you need I'll tell you step by step)
2. Database structure is modified as follows.
added to WH
   WH_ID Counter keyed
   QuantitySold (similar as Quantity)
added to Sales
   Checked Boolean
3. WH indexed by Expiry_Date, so the data appear according to Expiry_Date.
4. I placed the following components on the form (you can place them on a separate DataModule)
   tblWH (TTable) srcWH (TDataSource)
   tblSales       srcSales
   two DataGrids  TButton
5. Create Master/Detail connection between the tables. Master is Sales. For tblWH select: MasterSource=srcSales, MasterFields=Prod_Code, so in the WH table will appear only the posts which have the prod_code as in the selected Sales record.
6. The OnClick procedure of the Button is as follows:

procedure TForm1.Button1Click(Sender: TObject);
var
   Rest, QtySold: Integer;
begin
  //if the selected item was processed Checked is true
  if not(tblSales.FieldByName('Checked').AsBoolean) then
  begin
    tblWH.Edit;
    //if the needed quantity is greater then the last item
    if tblSales.FieldByName('Quantity').AsInteger >
         tblWH.FieldByName('Quantity').AsInteger -
         tblWH.FieldByName('QuantitySold').AsInteger
    then
    begin
      Rest := tblSales.FieldByName('Quantity').AsInteger -
              tblWH.FieldByName('Quantity').AsInteger +
              tblWH.FieldByName('QuantitySold').AsInteger;
      tblWH.FieldByName('QuantitySold').AsInteger :=
              tblWH.FieldByName('Quantity').AsInteger;
    end
    else
    begin
      Rest := 0;
      tblWH.FieldByName('QuantitySold').AsInteger :=
              tblWH.FieldByName('QuantitySold').AsInteger +
              tblSales.FieldByName('Quantity').AsInteger;
    end;
    tblWH.Post;
    tblSales.Edit;
    tblSales.FieldByName('Checked').AsBoolean := True;
    tblSales.Post;
  end;
  //then continue with the Rest
end;

I hope this helps

wbr

Janos
   
0
 
LVL 10

Expert Comment

by:kacor
ID: 8164565
Remarks:
1. Create ODBC connection to your DataBase
3. WH indexed by Expiry_Date, so the data appear according to Expiry_Date ascending (in Access).

To be able to edit a dataset (table in this case) it must be set in edit mode then the changes must be posted

wbr
Janos
0
 
LVL 10

Expert Comment

by:kacor
ID: 8208840
Hi cnroubek,

if you feel so that you've got the needed question from somebody please accept it

wbr

Janos
0
 

Expert Comment

by:CleanupPing
ID: 9316731
cnroubek:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question