Accpac Views / temp = AccpacView.Exists or AccpacView.Read 1

[iwells]

Going to post a larger question, possibly, after this one ... in lot the macros I'm recording and using for my examples I see either "temp = ViewName.Exists" or "ViewName.Read".

What are the purposes of these properties/methods?

Is .Exists used for On Error Goto AccpacError testing and not a required statement? The hlpe says "reports whether the current logical record exists in the database" which tells me my statement sounds wrong, but I'm not sure I understand why you'd be testing if the logical record exists?

I see the help file says .Read "fetches the logical record indexed by the current contents of the view's key fields" but this explanation doesn't help me.

 

[ettienne]

You can delete all the "temp = " lines, they check if the records exist.
.Read fetches the indexed record, like .Seek does in DAO/ADO. With this you set the index and the index field value, then read the record. Very fast if you seek a specific record, not for searching multiple records. Say you want to find a customer, you set the customer account ID and call .Read and it pops up, simple as that.

Sage 300 Certified Consultant

 

[iwells]

Ok then as a continuation something I typically see is:

GLBCTL.RecordCreate 1
GLBCTL.Read

Where a record is created, then .Read occurs ... is this necessary to "set" the current record being written to?

 

[ettienne]

No
If you create a new record it will be the current record.

Sage 300 Certified Consultant

 

[iwells]

This is a long one and the reason it's so long is I see items that I'm not sure they're actually required in each section and I'm trying to filter through unnecessary code....

I'm trying to put context to what I'm seeing in the recorded macro and I'm trying to do find the minimum requirements to form store procedure and loops to create GL entries. In the code below I'm planning on placing section 1 (and likely section 3) in a procedure to create the record in GLBCTH, then sections 2 and 4 (I think someone will tell me section 7 is required after the GLJED logic) in a procedure with a call to a new procedure containing sections 5 and 6. I'll post what I have shortly but some answers to some questions below would help.


Declarations and etc omitted ...

GLBCTL.Compose Array(GLJEH)
GLJEH.Compose Array(GLBCTL, GLJED)
GLJED.Compose Array(GLJEH)

'section 1
'line 1 - i believe this is not required as it is just a filter for the batch list window
'line 3 - unsure if the .read is required for line 2 or lines 4/5
'line 4 seems like it makes sense to lock the batch thus making line 5 required i assume to process the lock
GLBCTL.Browse "((BATCHSTAT = ""1"" OR BATCHSTAT = ""6"" OR BATCHSTAT = ""9""))", 1
GLBCTL.RecordCreate 1
GLBCTL.Read
GLBCTLFields("PROCESSCMD").PutWithoutVerification ("1")
GLBCTL.Process

'section 2
'line 1 - i'm not sure what the purpose of this line is
'line 2-3 - not sure why a browse and fetch are required here
'line 4 - again not sure
'line 5 - i assumed this would be the actual starting point and lone line of code required for GLJEH to start creating a record
GLJEHFields("BTCHENTRY").PutWithoutVerification ("")
GLJEH.Browse "", 1
GLJEH.Fetch
GLJEHFields("BTCHENTRY").Value = "00000"
GLJEH.RecordCreate 2

'Section 3 ... I understand the purpose of what's happening here, but
'could I have done this in section 1 before section 2 (cleaner to keep it all together for GLBCTL)

GLBCTLFields("BTCHDESC").PutWithoutVerification ("test batch desc")
GLBCTL.Update

'Section 4 ... seems like this got split up due to section 3 and that's why I asked about
'rearranging the location of section 3 so section 3 would merge with section 1 and this section
'with section 2
GLJEHFields("DATEENTRY").Value = DateSerial(2017, 1, 15) 'required

'section 5
'GLJED - Loop Begin
'line 1 - do I always .RecordClear before I .RecordCreate? required?
'line 4/13 - what's the purpose of .process here? required?
'line 9 - purpose of .read? required?
GLJED.RecordClear
GLJED.RecordCreate 0
GLJEDFields("ACCTID").Value = "1000"
GLJED.Process
GLJEDFields("RATEDATE").Value = DateSerial(2017, 1, 15) ' Currency Rate Date
GLJEDFields("SCURNAMT").Value = "0.010" ' Source Currency Amount
GLJED.Insert
GLJEDFields("TRANSNBR").PutWithoutVerification ("-000000001") ' Transaction Number
GLJED.Read
'2nd half of detail entry
GLJED.RecordCreate 0
GLJEDFields("ACCTID").Value = "1000" ' Account Number
GLJED.Process
GLJEDFields("RATEDATE").Value = DateSerial(2017, 1, 15) ' Currency Rate Date
GLJEDFields("SCURNAMT").Value = "-0.010" ' Source Currency Amount
GLJED.Insert
'GLJED - Loop End

'Section 6
'line 1 - why is TRANSNBR called after the .insert?
GLJEDFields("TRANSNBR").PutWithoutVerification ("-000000002") ' Transaction Number
GLJED.Read

'purpose?
GLBCTL.Read

'section 7
'could this be merged in to section 2?
GLJEHFields("JRNLDESC").PutWithoutVerification ("test entry 1 desc") ' Description
GLJEH.Insert
GLJEH.Read

'purpose?
GLBCTL.Read

'GLJEH/GLJED - Second Loop Iteration which is the same as section 2 through 7

 

[iwells]

Chopped it down (and tested) to and it works, but wondering if there are any errors or omissions which the pros here think I should add back:


GLBCTL.Compose Array(GLJEH)
GLJEH.Compose Array(GLBCTL, GLJED)
GLJED.Compose Array(GLJEH)

'section 1
GLBCTL.RecordCreate 1
GLBCTLFields("PROCESSCMD").PutWithoutVerification ("1")
GLBCTL.Process
GLBCTLFields("BTCHDESC").PutWithoutVerification ("Modified Test")
GLBCTL.Update

'section 2
GLJEH.RecordCreate 2
GLJEHFields("DATEENTRY").Value = DateSerial(2017, 1, 15)
GLJEHFields("JRNLDESC").PutWithoutVerification ("test entry 1 desc") ' Description
'this may have to come AFTER the looping

'section 3
GLJED.RecordClear
GLJED.RecordCreate 0
GLJEDFields("ACCTID").Value = "1000"
GLJEDFields("RATEDATE").Value = DateSerial(2017, 1, 15)
GLJEDFields("SCURNAMT").Value = "0.010"
GLJED.Insert
'2nd half of detail entry
GLJED.RecordCreate 0
GLJEDFields("ACCTID").Value = "1000"
GLJEDFields("RATEDATE").Value = DateSerial(2017, 1, 15)
GLJEDFields("SCURNAMT").Value = "-0.010"
GLJED.Insert
'GLJED - Loop End
GLJEH.Insert

'GLJEH/GLJED - Second Loop Iteration End
GLJEH.RecordCreate 2
GLJEHFields("DATEENTRY").Value = DateSerial(2017, 1, 15) ' Entry Date
GLJEHFields("JRNLDESC").PutWithoutVerification ("test entry 2") ' Description

'section
GLJED.RecordClear
GLJED.RecordCreate 0
GLJEDFields("ACCTID").Value = "1001" ' Account Number
GLJEDFields("RATEDATE").Value = DateSerial(2017, 1, 15) ' Currency Rate Date
GLJEDFields("SCURNAMT").Value = "0.010" ' Source Currency Amount
GLJED.Insert

GLJED.RecordCreate 0
GLJEDFields("ACCTID").Value = "1001" ' Account Number
GLJEDFields("RATEDATE").Value = DateSerial(2017, 1, 15) ' Currency Rate Date
GLJEDFields("SCURNAMT").Value = "-0.010" ' Source Currency Amount
GLJED.Insert
'GLJED - Second Loop Iteration End

GLJEH.Insert

 

[ettienne]

I will delete what is not required and only leave what is required - that should clarify some of thee questions.
Comment: this code is not tested, the logic appears correct.
General comment about .Process lines - you need to retain the preceding xxFields("PROCESSCMD").PutWithoutVerification ("x") otherwsie the .Process may not have the desired effect. Each view has a list of PROCESSCMD values that you see the the AOM. These can be to calculate sales tax, check customer credit limit, setup optional fields etc etc.

GLBCTL.Compose Array(GLJEH)
GLJEH.Compose Array(GLBCTL, GLJED)
GLJED.Compose Array(GLJEH)

GLBCTL.RecordCreate 1
GLBCTLFields("PROCESSCMD").PutWithoutVerification ("1")
GLBCTL.Process

' Note: you can move the batch fields to the top
GLBCTLFields("BTCHDESC").PutWithoutVerification ("test batch desc")
GLBCTL.Update

GLJEHFields("BTCHENTRY").Value = "00000"
GLJEH.RecordCreate 2
GLJEHFields("DATEENTRY").Value = DateSerial(2017, 1, 15)

' First line
GLJED.RecordClear
GLJED.RecordCreate 0
GLJEDFields("ACCTID").Value = "1000"
' Missing PROCESSCMD line here >>>>
GLJED.Process
GLJEDFields("RATEDATE").Value = DateSerial(2017, 1, 15) ' Currency Rate Date
GLJEDFields("SCURNAMT").Value = "0.010" ' Source Currency Amount
GLJED.Insert

' Second line
GLJED.RecordCreate 0
GLJEDFields("ACCTID").Value = "1000" ' Account Number
' Missing PROCESSCMD line here >>>>
GLJED.Process
GLJEDFields("RATEDATE").Value = DateSerial(2017, 1, 15) ' Currency Rate Date
GLJEDFields("SCURNAMT").Value = "-0.010" ' Source Currency Amount
GLJED.Insert

GLJEHFields("JRNLDESC").PutWithoutVerification ("test entry 1 desc") ' Description
GLJEH.Insert


Sage 300 Certified Consultant

 

[iwells]

Awesome thanks, now a few follow up questions ...

1)

' Note: you can move the batch fields to the top
GLBCTLFields("BTCHDESC").PutWithoutVerification ("test batch desc")
GLBCTL.Update

I don't understand why sometimes .PutWithoutVerification is used for field updates and other times ViewFields("SomeField").Value = X is used. The only slight reason I can see if .PutWithoutVerification followed by an update looks like it's being used after freeform text fields (the batch desc and entry desc)?

2)

I omitted GLJEHFields("BTCHENTRY").Value = "00000" and it still worked, but I'll add this back so do I need to actually set an integer value starting a 0 and increment it through each loop iteration and assign the value or is the line GLJEHFields("BTCHENTRY").Value = "00000" always used?

 

[ettienne]

.PutWithoutVerification > no field validation
ViewFields("SomeField").Value = X > field validation
You can use them interchangeably, but for data validation reasons it is best to use the second option when you have customers, items, GL accounts, etc and you want to validate data entry.
Free text fields don't need to be validated, so .PutWithoutVerification is used.

You can leave GLJEHFields("BTCHENTRY").Value = "00000" out, GLJEH.RecordCreate 2 will create a new entry.... I believe if memory serves me correctly. If not then keep it in there.


Sage 300 Certified Consultant