Find an efficient script getting 5 last Purchase Prices.
Sub Bedrag(ikp,aant,l,b,Hl,Hb,Ehkode) SWITCH Ehkode Case 'PLAAT' If Not('$(ikp)'=0) And Not('$(aant)'=0) And Not('$(l)'=0) And Not('$(b)'=0) And Not('$(Hl)'=0) And Not('$(Hb)'=0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= ikp/(aant*(l/Hl*b/Hb)); ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= ikp/(aant*(l/Hl*b/Hb)); ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= ikp/(aant*(l/Hl*b/Hb)); ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= ikp/(aant*(l/Hl*b/Hb)); ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= ikp/(aant*(l/Hl*b/Hb)); ENDIF end if Case 'ZFPLAT' If Not('$(ikp)'=0) And Not('$(aant)'=0) And Not('$(l)'=0) And Not('$(b)'=0) And Not('$(Hl)'=0) And Not('$(Hb)'=0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= ikp/(aant*(l/Hl*b/Hb)); ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= ikp/(aant*(l/Hl*b/Hb)); ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= ikp/(aant*(l/Hl*b/Hb)); ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= ikp/(aant*(l/Hl*b/Hb)); ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= ikp/(aant*(l/Hl*b/Hb)); ENDIF end if Case 'LITER' If Not('$(ikp)' = 0) And Not('$(aant)' = 0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= ikp/aant; ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= ikp/aant; ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= ikp/aant; ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= ikp/aant; ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= ikp/aant; ENDIF End If Case 'KILO' If Not('$(ikp)' = 0) And Not('$(aant)' = 0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= ikp/aant; ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= ikp/aant; ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= ikp/aant; ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= ikp/aant; ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= ikp/aant; ENDIF End If Case 'METER' If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= ikp/(aant*(l/Hl)); ENDIF End If Case 'BUIS' If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= ikp/(aant*(l/Hl)); ENDIF End If Case 'ZFBUIS' If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= ikp/(aant*(l/Hl)); ENDIF End If Case 'STAF' If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= ikp/(aant*(l/Hl)); ENDIF End If Case 'ZFSTAF' If Not('$(ikp)' = 0) And Not('$(aant)' = 0) And Not('$(l)'=0) And Not('$(Hl)'=0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= ikp/(aant*(l/Hl)); ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= ikp/(aant*(l/Hl)); ENDIF End If DEFAULT If Not ($(ikp) = 0) And Not ($(aant) = 0) Then IF LTSTNIKP1 = 0 THEN LET LTSTNIKP1= $(ikp) / $(aant); ELSEIF LTSTNIKP2 = 0 THEN LET LTSTNIKP2= $(ikp) / $(aant); ELSEIF LTSTNIKP3 = 0 THEN LET LTSTNIKP3= $(ikp) / $(aant); ELSEIF LTSTNIKP4 = 0 THEN LET LTSTNIKP4= $(ikp) / $(aant); ELSEIF LTSTNIKP5 = 0 THEN LET LTSTNIKP5= $(ikp) / $(aant); ENDIF End If ENDSWITCH End Sub; Sub Inkoop(Art,Hl,Hb,Ehkode) Let LTSTNIKP1=0; Let LTSTNIKP2=0; Let LTSTNIKP3=0; Let LTSTNIKP4=0; Let LTSTNIKP5=0; Let LTSTFDAT = Makedate(1900,1,1); ZoekInkoop: LOAD *, 1 AS TEST RESIDENT Inkreg where INKART='$(Art)' ORDER BY FAKTUURDATUM DESC; LET NumRowsZoekInkoop=NoOfRows('ZoekInkoop'); for i=0 to $(NumRowsZoekInkoop)-1 if NoOfRows(Total)>0 then LET artikel = peek('ZKINKART',$(i),'ZoekInkoop'); LET factdat = date#(peek('FAKTUURDATUM',$(i),'ZoekInkoop'),1); If '$(factdat)' <> '$(LTSTFDAT)' Then LET LTSTFDAT = DATE('$(factdat)','YYYY-MM-DD'); CALL Bedrag(peek('INKNETTOIKP',$(i),'ZoekInkoop'),peek('INKONTV',$(i),'ZoekInkoop'),peek('INKLENGTE',$(i),'ZoekInkoop'),peek'INKBREEDTE',$(i),'ZoekInkoop'),'$(Hl)','$(Hb)','$(Ehkode)') ENDIF ENDIF next i End Sub; LET NumRows=NoOfRows('VoorraadWaarde1'); for i=0 to $(NumRows)-1 Let Art = peek('ARTIKELNR',$(i),'VoorraadWaarde1'); Let Hl = peek('HANDELSLENGTE',$(i),'VoorraadWaarde1'); Let Hb = peek('HANDELSBREEDTE',$(i),'VoorraadWaarde1'); Let Ehkode = peek('EENHEIDSKODE',$(i),'VoorraadWaarde1'); CALL Inkoop('$(Art)','$(Hl)','$(Hb)','$(Ehkode)'); //CALL Inkoop('0901C400'); Voorraadwaarde2: LOAD *, ARTIKELNR AS VRDARTIKELNR, If('$(LTSTNIKP1)'>0,'$(LTSTNIKP1)',0) AS NIKP1, If('$(LTSTNIKP2)'>0,'$(LTSTNIKP2)',0) AS NIKP2, If('$(LTSTNIKP3)'>0,'$(LTSTNIKP3)',0) AS NIKP3, If('$(LTSTNIKP4)'>0,'$(LTSTNIKP4)',0) AS NIKP4, If('$(LTSTNIKP5)'>0,'$(LTSTNIKP5)',0) AS NIKP5 RESIDENT VoorraadWaarde1; NEXT i; The above used tables are allready filled in a different sheet. I only show you the script where I work with the data. The above script loops through an table with Articels (Materials) ==> Voorraadwaarde1. With the 'CALL Inkoop' get a selection of Purchaserows (Inkreg) to retrieve the Purchase prices based on Article and Invoicedate DESC. Found data is stuffed in Zoekinkoop. Next is the CALL Bedrag which calculates a netto price. While running the script QV takes a long time (every second a record) to retrieve Purchase prices, Calculations and update in a new table Voorraadwaarde2. TO do this for 5000 articles is difficult. VB.NET is faster. Question is: can this procedure be done faster, more efficient?
