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.

 

[pronet74]

That's what I am doing, but using flex. I check against the Order Number, Action, and Order type. The query runs fast, but I have this in the macform_save subroutine. However when a user hits enter on the first line item, this sub routine fires off. However, this routine fires off before the actual data is written to the table. I guess the macform_save subroutine is for code 'before' the actual save takes place.

There is a macform_postsave subroutine, but putting the code in there it never gets triggered. I have the code done, just don't know where to put it so it gets fired off at the right time.

 

[pronet74]

This is the code I have so far. OrdNo gets the OrderNumber before this code takes place:

Dim rConnStr As Variant
Dim rstUserName As Recordset
Dim username_sql As Variant
Dim UserNameTemp As Variant
Dim Access As New ADODB.Connection

rConnStr = "dsn=Keystone"
If Access.State = adStateOpen Then Access.Close

Access.Open rConnStr

UserNameTemp = macForm.UserName

username_sql = "SELECT DISTINCTROW OEORDHDR.USER_FIELD_1 FROM OEORDHDR WHERE (OEHDRAUD.[NO]=" & OrdNo & ")"
rstUserName.Open username_sql, Access, adOpenforward, adReadWrite, adCmdText
rstUserName.MoveFirst
rstUserName!USER_FIELD_1 = UserNameTemp
rstUserName.Update
rstUserName.Close

 

[pronet74]

Nice.. just found out that postsave is a non-functional function. It'll never get fired if you put code in there. What a bunch of BS! Do a keyword search on 'postsave' to find the thread.

Now I'm basically screwed. I can't put it in the close form, since users here have their OE opened all day. Anyone have any advice?

 

[abaldwin]

My suggestioin is this.

Unhide User_Def_Fld_whatever (1 - 5)

Using flex put the following code behind the got focus event of the new field.

Dim stName As String
stName = macForm.ConnInfo.User
stName = Left(stName, InStr(stName, "_") - 1)
Yourfieldname.text = stname

This will populate the field with the currently signed in user. I broke this down a little more than necessary for readability. Basically it retrieves the user name from macola, takes all the characters left of the underscore (If in SQL) If not remove it. Then sets the field to the value just calculated.

Andy

Andy Baldwin

 

[pronet74]

But I read I can't do this to the OE0101 form, since it's already maxed out on controls. Is this true?

I got the code to fire off, but the update query fails because the record is locked. Here is the newest code:

Dim rConnStr As Variant

Dim username_sql As Variant
Dim UserNameTemp As Variant
Dim Access As New ADODB.Connection
Dim rstUserName As ADODB.Recordset


rConnStr = "dsn=Keystone"
If Access.State = adStateOpen Then Access.Close

Access.Open rConnStr

UserNameTemp = macForm.UserName
username_sql = "UPDATE OEORDHDR SET OEORDHDR.USER_FIELD_1 = '" & UserNameTemp & "' WHERE ((OEORDHDR.ORDER_NO)= " & OrdNo & ")"
Access.Execute username_sql
rstUserName.Close

 

[pronet74]

I found the answer. I ended up triggering the code in the ordertype.gotfocus event. Since when an order is completed, the order_type will always have the focus before the user closes the form (at least I hope).

 

[kscheeler]

The user defined field he's referencing are "hidden" and are already part of the form. It goes back to needing the screen designer to unhide them or change their properties (which you can do on user defined fields) or make something a required field (as MacolaHelp stated)

The Type1_GotFocus event is the last field the cursor goes to after you exit the line item screen. At this point, the order information is already saved to the database as well as the audit information. Since this is also the field that the cursor starts on when you open the form, you would have to trap for that or when the screen is in the add mode vs. change mode but it's a place that you could run your code to update one of the user defined fields with the user's name that added the order.

You might be able to return the following: macform.ConnInfo.User as the user back into the user defined field.

Kevin Scheeler

 

[pronet74]

I did that, here is the code for it all.

Private Sub Type1_GotFocus()
If macForm.Tag = "Saved" Then
Enter_UserName
macForm.Tag = ""
End If
End Sub

----

Private Sub Enter_UserName()
Dim rConnStr As Variant

Dim username_sql As Variant
Dim UserNameTemp As Variant
Dim Access As New ADODB.Connection
Dim rstUserName As ADODB.Recordset

rConnStr = "dsn=Keystone"
If Access.State = adStateOpen Then Access.Close

Access.Open rConnStr

UserNameTemp = macForm.UserName
username_sql = "UPDATE OEORDHDR SET OEORDHDR.USER_FIELD_1 = '" & UserNameTemp & "' WHERE ((OEORDHDR.ORDER_NO)= " & OrdNo & ")"
Access.Execute username_sql

End Sub

----

Thanks for all your suggetions.. Sometimes it's harder to actually come up with workarounds because of flexibility's limitations than the actual programming itself.

 

[pronet74]

Oops.. forgot a part:

Private Sub macForm_Save(AllowSave As Boolean)
macForm.Tag = "Saved"
End Sub

 

[pronet74]

One more thing.. I assume that Macola will automatically transfer user_field_1 into OEHDRHST, right?

 

[abaldwin]

Yes, The field will "transfer". The history table gets the contents of all the OE hdr fiels whether they have values or not.

Andy

Andy Baldwin

 

[pronet74]

Just curious.. When does that transfer take place?

 

[dgillz]

When the invoice is posted, the records move out of the OEORDHDR, ORORDLIN and OELINCMT and to the OEHDRHST, OELINHST and OECMTHST tables.

If there is a backorder, and the customer and item are both marked backorderable, then records also remain in the OEORDHDR, ORORDLIN and OELINCMT tables, for the backordered items. The OEORDHDR at that point should have a status of 1.

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

http://www.trianglepartners.com

 

[crystalreporting]

...and this makes it extremely difficult to produce traditional 'booking' reports, as an order taken on a given day, may or may not be in the open order tables depending on when it was invoiced i.e. to accurately list the number of orders placed on a given date, you must look at both sets of tables as orders placed on a given date may have been invoiced and consequently moved to the history tables. OEINQORD is the only table I know of that holds ALL orders, open, invoiced, or both (for partial shipments).

Peter Shirley

http://www.linkcorp.com

 

[kscheeler]

Bookings probably should be another thread but I set up two SQL views that combine data from the header (open and history) and line (open and history) using the UNION command. I then write the crystal report against thosw two views along with the other standard tables like IMITMIDX and ARCUSFIL.

Kevin Scheeler

 

[crystalreporting]

Kevin - yep, it's probably a whole other topic, but you're right - you pretty much have to come up with some way to 'flatten' the two sets of files so that you can report on a single dataset. I've seen this done a number of different ways - one of my customers has an MSAccess database with multiple queries that creates a single count of orders taken and shipped per day by looking at both sets of files. Back in the pre-SQL days, I used to develop reports off of the OEINQORD file, with subreports to get the open order and invoiced order information, but these were clumsy at best. I also remember there was a 'third-party' utility floating around at one point that would create a single table from the open and invoiced orders.

Peter Shirley

http://www.linkcorp.com

 

[PhilPPhillips]

Kevin, I also created a view to use on order reports. But I created just one view using a union statement for this purpose. Here is the view I use:

CREATE VIEW dbo.orders
AS
SELECT OEORDLIN_SQL.[ord_no] ORDERNO,
OEORDLIN_SQL.[item_no] ITEM_NO,
OEORDLIN_SQL.[item_desc_1] DESCRIPTION_1,
OEORDLIN_SQL.[qty_ordered] QTY_ORDERED,
OEORDLIN_SQL.[unit_price] UNIT_PRICE,
OEORDLIN_SQL.[unit_cost] UNIT_COST,
OEORDHDR_SQL.[bill_to_name] BILL_TO_NAME,
OEORDLIN_SQL.[prod_cat] ITEM_PROD_CAT,
OEORDHDR_SQL.[ord_dt] ORDER_DATE,
OEORDHDR_SQL.[slspsn_no] SALESMAN_NO_1,
OEORDLIN_SQL.[request_dt] REQUEST_DATE,
OEORDHDR_SQL.[cus_no] ORDER_CUSTOMER_NO,
OEORDHDR_SQL.[ord_type] ORDER_TYPE,
OEORDLIN_SQL.[line_seq_no] SEQUENCE_NO,
ARCUSFIL_SQL.[cus_type_cd] CUS_TP,
OEORDLIN_SQL.[ord_no] ORDER_NO,
OEORDLIN_SQL.[discount_pct] DISCOUNT_PCT,
ARCUSFIL_SQL.[state] STATE,
OEORDLIN_SQL.[vend_no] VEND_NO
FROM { oj ([data_42].[dbo].[OEORDHDR_SQL] OEORDHDR_SQL INNER
JOIN
[data_42].[dbo].[OEORDLIN_SQL] OEORDLIN_SQL ON
OEORDHDR_SQL.[ord_type] = OEORDLIN_SQL.[ord_type] AND
OEORDHDR_SQL.[ord_no] = OEORDLIN_SQL.[ord_no])
INNER JOIN
[data_42].[dbo].[ARCUSFIL_SQL] ARCUSFIL_SQL ON
OEORDHDR_SQL.[cus_no] = ARCUSFIL_SQL.[cus_no] }
UNION
SELECT OELINHST_SQL.[ord_no], OELINHST_SQL.[item_no],
OELINHST_SQL.[item_desc_1],
OELINHST_SQL.[qty_ordered] - OELINHST_SQL.[qty_bkord],
OELINHST_SQL.[unit_price], OELINHST_SQL.[unit_cost],
OEHDRHST_SQL.[bill_to_name], OELINHST_SQL.[prod_cat],
OEHDRHST_SQL.[ord_dt], OEHDRHST_SQL.[slspsn_no],
OELINHST_SQL.[request_dt], OEHDRHST_SQL.[cus_no],
OEHDRHST_SQL.[orig_ord_type],
OELINHST_SQL.[line_seq_no], ARCUSFIL_SQL.[cus_type_cd],
OEHDRHST_SQL.[inv_no], OELINHST_SQL.[discount_pct],
ARCUSFIL_SQL.[state],
OELINHST_SQL.[vend_no]
FROM { oj ([data_42].[dbo].[OEHDRHST_SQL] OEHDRHST_SQL INNER
JOIN
[data_42].[dbo].[OELINHST_SQL] OELINHST_SQL ON
OEHDRHST_SQL.[ord_type] = OELINHST_SQL.[ord_type] AND
OEHDRHST_SQL.[ord_no] = OELINHST_SQL.[ord_no] AND
OEHDRHST_SQL.[inv_no] = OELINHST_SQL.[inv_no])
INNER JOIN
[data_42].[dbo].[ARCUSFIL_SQL] ARCUSFIL_SQL ON
OEHDRHST_SQL.[cus_no] = ARCUSFIL_SQL.[cus_no] }

 

[dgillz]

I use the OEHDRAUD_SQL and OELINAUD_SQL tables to write booking reports. Add the A records, subtract the B records, add the C records and subtract the D records.

The only downside is that if you do not have the audit trail turned on in OE Setup, there will be no data here.

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

http://www.trianglepartners.com

 

[MacolaHelp]

I use iminvtrx for booking reports at one site because we also group by product category. We couldn't get that level of detail from oeinqord. They were a bit tricky, but are great now that they are done. They take about 5 minutes to run on a pervasive server against history files with about 200k line items & 15k in active order files.

 

[stravis]

A very consistent way of capturing the finallization of an order in Order Entry is to use the macForm_ModeChange event. Within this event you can check the following information to check to make sure that the order is being save/finalized.

Private Sub macForm_ModeChange(ByVal mode As mode)
    If mode = 11 And macForm.CurTab = 1 Then
        ' Your Code to run after save/finalizing
        ' an order in Order Entry.  Please note you
        ' you will need to save the order type and 
        ' order number to a variable before reaching
        ' procedure if you wish to use it in this
        ' operation.
    End If
End Sub

Scott Travis
infoSpring, LLC.