Field manipulations

S

Steve Kuchta

New Member
Hello, my name is Steve Kuchta. I live in South Dakota and am putting up with a generous dosage of snow we've had over the past few days. I am fresh out of school with a degree in Networking. I am currently getting a self-taught crash course in Progress and Vantage. I have all of two weeks experience with the two software packages. I have been reading your threads and it sounds like you are a great group of people.

We use Progress 9.1D with Vantage 6.1. Many of the approximate 7,600 entries in our database are inaccurate. The quotes for the field which contain outside lead times are wrong.

Is there a way to mass-update an entire field? I have been trying to write a procedure to accomplish this, but on test databases I have not even been able to write to the database fields. Is this even possible, or am I doing something wrong? Some help would be greatly appreciated.

Thank you,

Steve Kuchta
 
There are a few ways to complete a mass update.

Are you looking for help with the Progress logic to achieve this goal?
Have you investigated why these values were innacurate?

If you are modifying the schema (modifying the format of a field), in v9, you would need to have single user access to do this (from what I remember). However, if this is just a manipulation of data you can do this will csv files, tons of ways.

I guess I don't have a clear understanding of what you are looking to do. Setting up a procedure to update values shouldn't be that difficult, but first look at the overall impact this could have. Do these values represent information yours sales people use, do tables key off this information already in place.
 
comatt1 said:
There are a few ways to complete a mass update.

Are you looking for help with the Progress logic to achieve this goal?
Have you investigated why these values were innacurate?

If you are modifying the schema (modifying the format of a field), in v9, you would need to have single user access to do this (from what I remember). However, if this is just a manipulation of data you can do this will csv files, tons of ways.

I guess I don't have a clear understanding of what you are looking to do. Setting up a procedure to update values shouldn't be that difficult, but first look at the overall impact this could have. Do these values represent information yours sales people use, do tables key off this information already in place.
Click to expand...

I've had a fair amount of programming experience. The language I've seen in Progress is very similar to C. But it has been a long time since my programming days. They do know how the data was entered incorrectly - it wasn't me - but I have been tasked to figure out a fast and accurate way to fix it.

The schema will not be changed. This is just a manipulation of the data. For instance, chrome plating for a particular part, by a particular vendor, will be changed from 9 days to 4 days. How can I change it to single-user access? I did try to convert a backup of the database to comma delimited format (csv) but did not have any luck. How would I convert to csv? That would be a great way to do it but how would I be able to get the data back into Vantage?

These values are determined by a couple of people that have been here for 30+ years. They can look at the part and process and immediately come up with the correct lead-time. There are tables that key off the information. Since the values are wrong, other processes we go through are affected. I think this has been going on for awhile. Please tell me if I left anything out or you need more information.

Thank you,

Steve Kuchta
 
You can dump data within the database dictionary with just a space seperated file. If the db is multi user,

mpro <dbname>

Tools->Admin->Dump Data and Definitions -> Table Contents

just hit <space bar> on the table and dump the information.

If you can work with space delimited files, you can just dump this file, make modifications, and then load. Remember when you load to allow a 100% error rate.

However, I am working from the db admin situation, I would mostly likely have the developers write program that will search for data and update on certain criteria.

Matt
 
Rather than any global mass update, I would suggest that you write yourself one or more little programs to do the change. Exactly what those programs look like depends on the groupings of the data. E.g., if a particular range of product numbers is all 4 days, then write a little program that loops through a range of product numbers and sets a value. If it is all like that, then make it prompt for the range and the new value. If it is some other characteristic ... product group or vendor or something, then key on that. Worst case, just a little repeat loop to present number, description and update of value and sit someone down to do it who knows the rules. 7,600 is a fair number, so you might have it take a starting number and just keep running until someone gets tired and then they can start at a new number in the next session.
 
I will suggest updating the fields through an input file rather than doing a mass update on the table(at least you will be very sure that which all records will be touched). Dump the key fields in a csv file.

Eg, dump all the cust-num in a csv file that needs to be modified and use this csv file to update the effected records. A code snippet will be in the below form

input from "path of csv file".
def var wkcust like cust.cust-num.
repeat:
import delimiter "," wkcust.
for first cust where cust-num = wkcust exclusive-lock:
assign
cust.cust-serial = "value that needs to be updated for this field".
end.
end. / * repeat */

right a procedure like this and update this through procedure editor after connecting through database mpro<dbname>

Arshad
 
I have been trying to write a procedure to accomplish this, but on test databases I have not even been able to write to the database fields. Is this even possible, or am I doing something wrong?
Click to expand...

Is there an error message associated with "not even able to write to the database fields"?

My initial thought is that code something like this is what you want:

Code: 
for each badTable exclusive-lock where badTable.part = "partNum":
  badTable.leadTime = newValue.
end.

If there are a reasonably small number of WHERE clauses this would be simple enough to code as above. Which would seem much more sensible to me than dumping the data out to CSV files and reading it back in.

If there are a large number of possible WHERE clauses then it may be better to do something like:

Code: 
for each badTable exclusive-lock:

  display
    badTable.vendor 
    badTable.partNum
    badTable.description
    badTable.leadTime
  .

  update badTable.leadTime.

end.

That will display every record and provide an opportunity to correct the leadTime field.
 
It will be a painful task to update 7600 entries one by one through display screen.Using CSV was just to be double sure that which all records will be updated rather than mass update using For each.....

"Many of the approximate 7,600 entries in our database are inaccurate."
 
If 7,600 entries are inaccurate in 3 ways it's not such a big deal ;-)

The point was really to show an approach to coding the fix. Making it more effective from a user effort POV depends greatly on the circumstances at your location. Are you planning on using a CSV file because there is already a spreadsheet with the fixes made? If so then that makes sense. If OTOH the idea is to dump the data to CSV, import it to Excel, fix it in Excel and then reload it I don't see how that is any more effective than what I showed...

And of course there are all sorts of solutions in between.

Assuming that you already have a CSV with the corrected data something like this might be what you need:

Code: 
define variable badVendor      as character no-undo.
define variable badPartNum    as character no-undo.
define variable fixedLeadTime as integer    no-undo.

input from value( "fixes.csv" ).

repeat:

  import delimiter "," badVendor badPartNum fixedLeadTime.

  find partTable exclusive-lock
    where vendor = badVendor and partNum = badPartNum no-error.

  if available( partTable ) then
    partTable.leadTime = fixedLeadTime.
   else
    message "Oops!".

end.
 
Unfortunately, I seem to be wearing a lot of hats these days. I am the network administrator and the database administrator and the development team and... But that's OK. There is not an already corrected csv file, that would be great but I have not had time to try that one. What I have been doing is trying to create a program like Tom suggested so that one of our interns can punch the data in. The code looks like this:

CODE:

FOR EACH PartOpr WHERE PartOpr.OpCode = "HTOP" EXCLUSIVE-LOCK:

DISPLAY PartOpr.VendorNum.
DISPLAY PartOpr.PurPoint.
DISPLAY PartOpr.OpCode.
DISPLAY PartOpr.PartNum.
DISPLAY PartOpr.DaysOut.

UPDATE PartOpr.DaysOut.

END.


In the final code there will be "and" statements but for now, this gets me to an input screen where I can enter the corrected number (on the database copy I made). When I enter the number and hit enter the following error comes up.

db\trg\write.p Database vantage not connected. (1006)

Thank you everybody for all your help -- I greatly appreciate it.
 
It's a quibble but you really only need one DISPLAY statement:

Code: 
FOR EACH PartOpr WHERE PartOpr.OpCode = "HTOP" EXCLUSIVE-LOCK:

  DISPLAY
    PartOpr.VendorNum
    PartOpr.PurPoint
    PartOpr.OpCode
    PartOpr.PartNum
    PartOpr.DaysOut
  .

UPDATE PartOpr.DaysOut.

END.

The error is telling you that, in real life, your system expects to have a database named "vantage" connected. A trigger is firing when you update PartOpr records. I don't know the ins and outs of your application but there might be important logic in that trigger that updates other records when this one gets updated.

Or it could just be that your copied db needs to be named "vantage".

If you are very sure that the trigger is not needed you could add:
Code: 
disable triggers for load of PartOpr.

To the top of your code.
 
I tried the line of code below to disable the triggers - it seems to work. I will enter some value, hit enter, it hangs for awhile, and then it prompts me in the status bar area that the procedure completed and to hit the space bar to continue. I then open the database and the value has not been changed. I do think I am getting closer though.

Thank you,

Steve Kuchta

disable triggers for load of PartOpr.
 
Note that disabling the triggers could be a very bad thing since it could lead to a loss of data integrity. I would disable them without knowing why.

A better route is that it sounds like one of two things is true in your normal production environment. Either you there is a logical database name being assigned which is expected by the trigger code or you are connecting to more than one database. Whichever it is, you should be sure to replicate the state in the session you are using to make the change. Then you won't have any problem with the triggers.
 
You must log in or register to reply here.
Back
Top