We are using Baan4c4 database tbase6.1


We would like to extract item master outside the baan and change the value of field purchase price i.e tiitm001.prip and again we want to replace the data into Baan.

We will not allow any user to work during this period.


This we want to do this for item stock valuation.

OR if any body can have any other solution for the same.

I want to change purchase price using some formula.
that is purchase price * 1.1 something like that.

Shrikant

If you have a single formula where all purchase prices for all items is purp * 1.1 (or whatever), I would generate a quick update session on the item master and code the update program script to do the necessary calculation and update using db.update().

If on the other hand, each item may be different, the best option would be to use exchange and extract the data into a flat file (delimted or whatever works for you), edit the flat file according to your pricing and pump it back into Baan using exchange.

You could bring the first file into Excel if desired, or any editor, and save it back as delimited and then reimport back using exchange.

Regards
Eric

I had tried using exchange scheme delimter as "|". Then write a vb program to update the prices with formula when had created text file using same vb program.

When it exporte data of some field as 0000035 in excel it is taking as 35 excel. So while putting back data in Baan it is giving error.

Attaching here with a text file first 8 lines are from baan data and next 8 lines which are created thru vb.


Can you explain how to do it with db.update()

Shrikant

Hi shrikantc,

you are working on a unix system.
So why bother to use excel and or vb?
Dump the data with bdbpre6.1 (-t"|" -Ntiitm001 -pPACKCOMB
-oOUTPUTDIR -CCOMPYNYNUMBER )
Then write an awk "program" like the following
-----afile----
BEGIN{FS="|";OFS="|"}
{if ($3=="A") $70=$70*1.25}
{if ($3=="B") $70=$70*1.1 + 0.25}
{print}
----end of afile-----
(This example assumes that field no 70 contains price and field no 3 some ABC code)
then call
awk -f afile ttiitm001xxx.S > ttiitm001xxx.S.new
rename ttiitm001xxx.S.new to ttiitm001xxx.S
(replace xxx with companynumber)
Now do a bdbpre6.1 to reread created file into baan.
(bdbpost6.1 -f -k -n -m - -t"|" -pPACKCOMB -DDIRECTORYNAME -cCOMPNO)
This
a) is a 10.000 times faster than anything else (ok, a baan-program won`t be much slower, I have to admit that)
b) doesn`t give you any confusion with ascii/ansi conversion
c) is very easy to maintain/change
d) is perfect for automation in a nightly job
and cool anyway (*g*)

Feel free to ask if you encounter problems

regards

Norbert

Easier way is to do an update using the SQL statement of database. I am not quite sure of tbase6.1 but we have oracle and it is very easy to do an mass update with SQL statement like
update ttiitm001(companynr) set t$pric = t$pric * 1.1
followed by commit.

Other way is to EXPORT the data from tiitm001 with only 2 fields

1. item number
2. purchase price.

using BaaN exchange , a PIPE "|" delimeter file. e.g. tiitm001.txt

Open the text file in excel making sure that the Item number column is imported as type "TEXT" other wise leading zero will be removed. Change the price based on the calulcations required.

Once done save the file as csv (Comma separated) excel does'nt allow "|".

In Baan write an IMPORT exchange scheme with above 2 fields and make sure that on the table relation session the flag "add record = NO" and also in the field relation for "item" field "overwite and update" is set to "NO"

This works very well and is ver easy.

Hope this help

All methods that the previous posters mentioned work. I would use the method you are most comfortable with.

If you are using excel to import and create a delimited file, when you import it in set that column as text instead of numeric so the leading 0's don't get removed.

Either way, you would need to use exchange or bdbpost to put the data back in.

For the update method using db.update(), simply generate an update session on the table. Edit the program script that is created and you will see the syntax. FOr the field(s) you want to change, put in the appropriate code. I can not detail BaanIV programming for you here.

Either way, as someone may have mentioned, changing prices affects standard costs, so be careful. Make sure you take a good backup of your target table first.

norwim,

Thanks,

I had worked on Hp ux in 1995 -96, that time I had used awk shells very few time. Now from last 6 -7 years we are working on Power builder and VB so I was not in touch with awk shells.

AND MOST Importat Thing I had a very very small knowledge about the Baan. thanks a lot for information about the same now I will start using more and more unix related programs.

I had already written shell scripts for coping daily dump files to current dates and crontab to copy same to dat drive.

Thanks once again,

Shrikant

Hope this helps,

Ram.