ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> Problem Reports - Repeatable >> Purchasing line numbers
http://www.istechforum.com/YaBB.pl?num=1489776995

Message started by ShawnH on 03/17/17 at 11:56:35

Title: Purchasing line numbers
Post by ShawnH on 03/17/17 at 11:56:35

Hi,

Is there a way to have the line numbers in PO-A re-calculate after you delete a line off of a p.o?

Title: Re: Purchasing line numbers
Post by Kelloggs on 03/20/17 at 17:31:59

Hi,

Nope, there's no way I'm aware of. >:(
it would be nice ....

have fun,

Kelloggs

Title: Re: Purchasing line numbers
Post by ShawnH on 03/21/17 at 07:06:23

Thank you

Title: Re: Purchasing line numbers
Post by Kelloggs on 03/22/17 at 11:04:50

With a little of imagination you can do a lot!!

here is a script, save it as dba_test.vbs on your desktop and you run it by going into Start then Run:
C:\Windows\SysWOW64\wscript.exe "C:\Users\john\Desktop\dba_test.vbs"

where "john" is your user name
It will rename your po lines : 01, 02,03, etc, etc :P

here it is :

Dim Connection
Dim Recordset
Dim cmd
Dim var_int
Dim Response
Dim the_line
Dim the_po

the_po = 39244 'THIS IS THE PO NUMBER, CHANGE IT AS NEED IT

Set Connection = CreateObject("ADODB.Connection")
Set Recordset = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")

Connection.Open "DSN=MYODBC" 'YOU NEED TO CHANGE THIS !!!
Set cmd.ActiveConnection = Connection

Recordset.Open "select bkap_pol_cntr from BKAPPOL where bkap_pol_ponm=" & the_po & " and bkap_pol_pcode <>'' order by bkap_pol_cntr;",Connection

var_int = 1

If Recordset.EOF Then
WScript.Echo("No Records Returned. ")
WScript.Quit
Else
Do While NOT Recordset.Eof
the_line = Recordset("bkap_pol_cntr")
cmd.CommandText = "update BKAPPOL set nkap_pol_um_lin_1='0" & var_int & "' where bkap_pol_ponm=" & the_po & " and bkap_pol_cntr=" & the_line & ";"
cmd.Execute

var_int = var_int + 1
Recordset.MoveNext
Loop
End If

Recordset.Close
Set Recordset=Nothing

Connection.Close
Set Connection=Nothing

'End
WScript.Echo("DONE !!!!")
WScript.Quit

Have fun

8-)

Kelloggs

This is for illustrative purposes only. This script is supplied "AS IS" without any warranties and support.

Title: Re: Purchasing line numbers
Post by Kelloggs on 03/22/17 at 11:24:38

:-[

there is a few issues with this, I just realized

if you have more than "10" Lines the script will rename them as 010, 011, 012, actually I can change it but too much work hehehe ;D
if there is more than a line (s) that have the same po and cntr number, well there is a problem, the script will rename them regardless 01,02,03, etc, it will not 01,02,02,03, etc, etc

user be aware

;D

Kelloggs

Title: Re: Purchasing line numbers
Post by ShawnH on 03/29/17 at 07:02:47

Hi,

thank you for the info. I will def try this. I don't mind the 01,02 lines numbers.

thank you for your help.

Shawn :D

Title: Re: Purchasing line numbers
Post by Kelloggs on 03/29/17 at 17:07:48

Cleaner version, this one will rename them 01,02, ....09,10,11

:D

Dim dba_conn, dba_rs, dba_cmd, var_int, var_po, line_dba, line_my
var_po = 38467 'Your PO Number

Set dba_conn = CreateObject("ADODB.Connection")
Set dba_rs = CreateObject("ADODB.Recordset")
Set dba_cmd = CreateObject("ADODB.Command")

dba_conn.Open "DSN=DBA" 'Your ODBC
Set dba_cmd.ActiveConnection = dba_conn
dba_rs.Open "select bkap_pol_cntr from bkappol where bkap_pol_ponm=" & var_po & " and bkap_pol_pcode <>'' order by bkap_pol_cntr;",dba_conn

var_int = 1
If dba_rs.EOF Then
var_po = "Unable to Find Purchase Order Number : " & var_po
WScript.Echo(var_po)
WScript.Quit
Else
Do While NOT dba_rs.Eof
if var_int >= 10 then
line_my = var_int
else
line_my = "0" & var_int
end if

line_dba = dba_rs("bkap_pol_cntr")
dba_cmd.CommandText = "update bkappol set nkap_pol_um_lin_1='" & line_my & "' where bkap_pol_ponm=" & var_po & " and bkap_pol_cntr=" & line_dba & ";"
dba_cmd.Execute
var_int = var_int + 1
dba_rs.MoveNext
Loop
End If

dba_rs.Close
Set dba_rs = Nothing
Set dba_cmd = Nothing
dba_conn.Close
Set dba_conn = Nothing

WScript.Echo("Done !!")
WScript.Quit 'END

have fun,

Kelloggs

This is for illustrative purposes only. This script is supplied "AS IS" without any warranties and support.