Tough Question Regarding Macola 1

[pronet74]

I have Macola 7.5.103e running on Pervasive SQL. Previously to keep track who has entered a PO or OE, we had the user put there initals in as a line item. We would like this changed. I have flexability and good with VBA.

Before going into that big mess of doing it by hand, is there anything in Macola that will keep track of what a user has done by their login name? We would want their initials printed on the pick ticket when it's printed as well.

 

[Rob7]

You can select the Audit Trail check boxes in OE and PO respectivly and that will keep a record of who does what to the orders by sign on name. It does not appear to be able to be printed on pick tickets, however, through Forms Designer. You could use Flex to find the user name and place that in a user defined field. The user defined fields can be added to pick tickets through the Forms Designer in System Manger

Rob

Cytec Corporation
rbrown@cyteccorp.com

 

[pronet74]

I assume the only tables that get looked at when printing a Pick Ticket is OEHDR & OELIN. My best guess on how to implement this is, when the user enters an order, to have vba code take the data where the user name is and copy it into a user-defined field in the oehdr table. Would that make the best sense?

 

[dgillz]

Yes, I would write flex code to populate a user defined field with the user's name from the OEHDRAUD_SQL table, then place the user defined field on the pick ticket.

You will find 4 types of records in the OEHDRAUD_SQL table in the aud_action field. A-Add, B-Before Change, C-After Change and D-Delete. Based on this, grab the appropriate record in the user_name field and populate on the the users defined fields in the ORORDHDR_SQL table.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[pronet74]

Ok, in the save function of the OE form I have the following code:

Call OpenMacolaConnection
Set statrs = New ADODB.Recordset
statstmt = "SELECT OEORDHDR.USER_FIELD_1 FROM OEORDHDR INNER JOIN OEHDRAUD ON OEORDHDR.ORDER_NO = OEHDRAUD.[NO] WHERE (OEHDRAUD.ACTION='A' AND OEORDHDR.ORDER_NO = '" & OrdNo & "')"
statrs.Open statstmt, Macola, adOpenforward, , adCmdText
statrs!USER_FIELD_1 = statrs!USER_NAME
statrs.Update

OrdNo is a variable from the no.text field on the OE form.

However, I get an error when it tries to run the query: [Intersolv][ODBC Btrieve Driver] Extra characters at end of query:INNER

I'm totally stumped. I tried running an UPDATE query instead, but gave me an error saying I was missing a SET command, so I tried doing it this way. Any advice?

 

[ultrav]

I have an easy way (if you don't have alot of users) to get the name on the order and pick ticket - unfortunately, it does not work if the users are grouped. What I did(we wanted the name on the pick ticket & invoice)was use designer on the order header - used one of the user defined fields - then made each persons name the default value of the field on their screenset.

Thought it might help

Celeste

 

[PhilPPhillips]

The easiest and most straightforward way to do this is to just use a user defined field in the header record. Add it to your screen and add all your users as values and don't allow additional input. Make the field a required field. You can use user defined fields in any type of form or report.

If you don't want any user interaction to capture who entered the order (and you don't have too many people who enter orders) you could create separate screens for each user that defaults that particular user's name into the user defined field.

 

[dgillz]

Celeste's solution was awesome....this requires no user input at all. Very ingenious.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[pronet74]

Unfortunely I have some users in a group. This was done by a prior IT person. Any harm in pulling them away from the group? The only other problem is I don't have the screen designer add-on. Just flexibility. Too bad you couldn't modify print forms and save them to a user's directory like regular screens..

 

[dgillz]

No harm in making them individual users.

In terms of modified screens, you can have your Macola business partner modify the screens then copy in their oescrfil.btr file to your Macola76\screens directory.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[kscheeler]

Buy the screen designer!

It's short money and you can modify any screen in Macola. You'll find this useful even with Flexibility because on most screens (not Order Entry OE0101 though), you can add additional fields that would be used only for Flexibilty.

Kevin Scheeler

 

[pronet74]

That's what I need, to add a field to the OE0101 screen, along with the PO screen so the username is shown & added to the user_field_1 field. Won't the screen designer let me alter that screen?

 

[dgillz]

Yes, you can do this on the order header, but the order line screen is very limited. I can demonstrate if you wish.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[kscheeler]

Yes, you can add a field that is "hidden" in screen designer but you can't create a "new" field for use only with Flexibiliy on the OE0101 screen because their is a limit of the number of fields the program can handle on any one screen. That's the only screen where the number of fields are maxed out.

Kevin Scheeler

 

[pronet74]

Ugh.. that sucks. I got my query working, but it takes way too long. I have it searching through OEORDAUD and finds the record with the Order Number matching the Order Number field. I have the USER_NAME value going to a variable. I then search on the OEORDHDR file for that same order number and then assign that variable to USER_FIELD_1. I was then going to put that field on the printout.

I had to create two seperate queries, since I'm getting those weird errors stated above trying to use one single update query. Can you do an UPDATE query in Macola VBA? Thanks for all the reponses so far. I'd wish Macola themselves would read this forum to see how some of us are having problems with certain things. It's just frustrating on it's limitations.

 

[kscheeler]

If you query against the OEHDRAUD table on both ord_type and ord_no it will run much faster.

Why not take a different approach and use flexibility to write to the user defined field 1 the Macola login username which flex has access to. It would do this when you save the order during order entry. I would put the code on the order type field on the since that's where the cursor goes after you save an order. It still has a lock on that order until you either exit the screen or start a new order.

You could set the code to run if the user defined field 1 is null or empty or if the order entry screen is in add mode since you want to track who entered the order.

Kevin Scheeler

 

[pronet74]

Thanks for the tip. Question though. Where does Macola keep the username when they logon? Is it a variable that I can access in Flexibility?

 

[pronet74]

Ok, did what you suggested with the query and it runs alot faster. However, I have the code in the macform_save, problem is, the code is running before the data gets written to the table. When does the data get written to the OEHDRAUD table?

 

[pronet74]

I noticed there is a macform_postsave, but the code is never run if I put it in there. How does the postsave get triggered?

 

[MacolaHelp]

Here's what I do @ one site: unhide a user defined field & create a list box w/all order entry personnel. It is a required that they must answer & populates the field. If you don't own & don't want to purchase screen designer. Write a VB or MS access update query to force a value into an oeordhdr user defined field based on oehdraud "A" type records. I would avoid flex & use a straight external update as the complexity of flex w/oe0101, ESPECIALLY tied to the audit files (which are not keyed well) will slow down the order entry process more than most people would like.