GLAMF/GLAMFO 1

[iwells]

How do I create a view that's a combination of GLAMF and GLAMFO so that I can .browse on ACSEGVAL01 and OPTFIELD in the same filter OR is there a way to do a substring/mid statement in a .browse like "Mid(ACCTID, 1, 4) = ""XXXX""" so that I only need GLAMFO?

I'm trying to create a macro to loop through and create accounts and optional fields, but we use multiple segments in our GL accounts and segment 1 is our main account segment while segment 2 is our "project" segment so what I'm trying to do is look in GLAMFO and to see if ACSEGVAL01 exists, if it does, create it for the new supplied ACSEGVAL02.

 

[iwells]

Sorry, I know this is frustrating (and this is why I've offered to pay for services as I'm sure you're frustrated), but here's what I'm not understanding and goes back to a question I asked before.

If you're using CSQRY and the query can return 0 record, then you always have to test to see first IF the query you're running in CSQRY will return results because .fetch doesn't indicate that there is in fact a record it just moves it to BOF and tells you if EOF is next? For example in my case the query I'm going to run 95% of the time will only return 1 record, but there are times that it won't return any, so after .browse and .internalset I need to be able to either 1) test to see if there is a record because .fetch can't tell me the difference between 0 and 1 records (.fetch = true indicates > 1) or 2) have another method other than running a count select beforehand to determine that there is a record to act on.

Am I missing a method or still misunderstanding?

 

[DjangMan]

You probably just need a break from this for a bit.

If you think that CSQry will return zero records then change up your loop like I suggested above:

[pre]vCSQRY.browse MySqlStatement, True
vCSQRY.internalset 256
if vCSQRY.Fetch then '<- will return true if 1 or more records were in the resultset
Do
all of these
statements to process
the results of the query
Until vCSQRY.Fetch = False '<- .Fetch will attempt to go to the next record and return if it was able to go to another record or not
else
there were no records returned
end if
set vCSQRY = nothing[/pre]

 

[ettienne]

I was going to do some VBA/macros classes, but interest was a bit slow. No time for it now, neck deep in NetSuite stuff.

Sage 300 Certified Consultant

 

[iwells]

I took your advice, stepped away, but I made a document this morning (see attached) so you can see what I'm seeing as I step through the code.

Screenshot1 - haven't browsed yet and no values
Screenshot2 - Browsed and .Fetch = True now (as it should)
Screenshot3 - .Fetch = False, but the fields are indicating the correct values that I'm attempting to manipulate
Screenshot3 - .Fetch = False and the fields are now empty

According to what you're telling me shouldn't .Fetch stay True and the fields all retain their values?

 

[Jay Converse]

1. You can't check values before the .Fetch, so strTemp = vsQuery is an invalid statement.
2. If GLAMF and GLAMFO are properly composed, you don't set the value of GLAMFO.ACCTID, all you do is set the OPTFIELD and its value

 

[iwells]

Tuba ... if I remove the dim statement and strTemp I still don't see how can .Fetch = False, but the .Fields of vCSQRY contain the correct expected values of a single record from the .Browse statement in those watches as I'm seeing?

That's where I'm really struggling here. I understand conceptually what I'm being told that .Fetch will move to the first record and return true if there is a record, but in this case it's returning false when the SQL statement executed outside of VBA in SSMS return the same single record data the watches also show in the screenshot I'm showing so it's like I'm seeing all the right data, but .Fetch is still false.

 

[Jay Converse]

I can't see the whole statement, but if you're inner joining to GLAMFO for that a GLAMF, of course it's empty.

 

[iwells]

Why would inner joining GLAMF and GLAMFO come up empty? I've said multiple times I can run the statement in SSMS and it returns the correct data

SELECT GLAMF.ACSEGVAL01, GLAMFO.OPTFIELD, GLAMFO.VALUE, GLAMFO.AUDTDATE, GLAMFO.AUDTTIME FROM GLAMF INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID INNER JOIN (SELECT GLAMF.ACSEGVAL01, GLAMFO.OPTFIELD, GLAMFO.AUDTDATE, MAX(GLAMFO.AUDTTIME) AUDTTIME FROM GLAMF INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID INNER JOIN (SELECT GLAMF.ACSEGVAL01, GLAMFO.OPTFIELD, MAX(GLAMFO.AUDTDATE) AUDTDATE FROM GLAMF INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID GROUP BY GLAMF.ACSEGVAL01, GLAMFO.OPTFIELD) GLAMFO_I ON GLAMF.ACSEGVAL01 = GLAMFO_I.ACSEGVAL01 AND GLAMFO.OPTFIELD = GLAMFO_I.OPTFIELD AND GLAMFO.AUDTDATE = GLAMFO_I.AUDTDATE GROUP BY GLAMF.ACSEGVAL01, GLAMFO.OPTFIELD, GLAMFO.AUDTDATE) GLAMFO_O ON GLAMF.ACSEGVAL01 = GLAMFO_O.ACSEGVAL01 AND GLAMFO.OPTFIELD = GLAMFO_O.OPTFIELD AND GLAMFO.AUDTDATE = GLAMFO_O.AUDTDATE AND GLAMFO.AUDTTIME = GLAMFO_O.AUDTTIME WHERE GLAMFO.OPTFIELD != 'BID' AND GLAMF.ACSEGVAL01 = '" & Mid(vCSOPTFD.Fields("VALUE").Value, 1, 4) & "'", True

 

[iwells]

Also that doesn't take in to account the fact that vCSQRY does initially have the expected values as well

 

[Jay Converse]

Please tell us in 25 words or less what this code is supposed to do.

 

[iwells]

The program: create new accounts (gl segment 1 and gl segment 2) and optional fields for new user supplied gl segment 2

SQL: get most recent optional field value for gl segment 1 and 2 combo and use that to populate value for optional field for new gl segment 2

 

[iwells]

expanded explanation ... we have a set chart of account for gl segment 1 (IFRS) and we use segment 2 for jobs/branches so when the user wants a new job/branch we create a range of accounts for segment 1 and supply the new segment 2 and then want the most recent account optional fields created as well (they change over time hence the most recent value)

 

[ettienne]

You know that there is a Create GL Account icon in GL? It will create new GL accounts for a user supplied GL segment. It may even do optional fields... I can't remember.

Sage 300 Certified Consultant

 

[iwells]

I know it exists, however corporate policy is to restrict access to that functionality therefore the macro can only create new accounts in a range (revenue and expense accounts).

this cannot be changed per internal auditors and C-level management

 

[Jay Converse]

As Ettienne told you way back in this thread, recording a macro that adds an account and its optional field would show you how to do it.

1. There is no ".Post" method used with GL accounts
2. First you .Insert GLAMFO (as many times as you need), then you .Insert GLAMF

 

[ettienne]

Corporate policy BS blah blah.... what they don't know will not hurt them. Sometimes I wonder how the mind of some idiots work... I don't get. Everything you need is right there and a neat function. Give an intelligent person access to it and be done with it.
********** RANT OVER ************

Sage 300 Certified Consultant

 

[iwells]

Unfortunately that's not an acceptable or reasonable response and if I gave them access and it were discovered, I would be fired. And it would be discovered as we have annual security audits performed by internal resources as well as external consultants such as Deloitte, E&Y, KPMG and etc.

Generally I don't explain the back story, but we've struggled with consistency, standardization and other local accounting problems and restricting access to create accounts has resolved the issue. We removed the ability for users to create any accounts and provided them with a tool where we specifically hardcode the accounts to be created. I understand you don't understand the issue, but when you're dealing with 20+ branches, 5000+ employees, internal and external auditors as well as stock exchange reporting requirements it becomes necessary to manage access to mitigate issues.

In smaller companies perhaps what you're describing is a non-issue, but we're not a small company and your logic simply doesn't apply and was not considered valid by many other internal and external resources.

 

[iwells]

Again, I appreciate the help and understand the frustration as I am as well, however I again reiterate that I need to resolve this issue as it is defined and will pay for external support.

If you guys all have contact information I'd be willing to take it for this issue and in the future.

I'll also wait on Djangman to weigh in and contact me as well.

 

[DjangMan]

There's no reason why you cannot call .fetch after the .browse. You will encounter issues when you try to work with the fields in the view if you do not call .InternalSet 256. Having .Fetch in your watch list will impact the flow of your code as you step through it.

[pre]Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READONLY)
mDBLinkCmpRW.OpenView "CS0120", vcsqry
sql = "SELECT 4000 ACSEGVAL01 ,'GLGOV' OPTFIELD ,'TEST' VALUE ,'20000000' AUDTDATE ,'0000000000' AUDTTIME"
Debug.Print "a:" & vcsqry.Fetch
vcsqry.Browse sql, True
Debug.Print "b:" & vcsqry.Fetch
vcsqry.InternalSet 256
Debug.Print "c:" & vcsqry.Fetch
If vcsqry.Fetch Then
Debug.Print "d:" & vcsqry.Fetch
End If
Set vcsqry = Nothing
[/pre]

"A" returns an error from Sage
"B" returns True because Sage found the one record that the query returns
"C" returns False because there was only one record returned from Sage
"D" never happens

.Browse runs the query. That's absolutely true.
Your first call to .Fetch will let you know that Sage picked up a record from the result set.

The Sage views were not designed to run on database result sets. They were designed to find one record at a time. Normal views don't look for any data until you call .Fetch. So in order to keep the 'view' mechanics intact they made CS0120 look like it worked that same way. It doesn't when you look under hood. You shouldn't have to worry about any of that. You just need to treat CS0120 like a normal view with a small handful of odd requirements (calling .InternalSet 256, you have to set to Nothing before reusing the variable, and sometimes you get an array of char back from the view instead of a text field value).

If you don't know if your query is going to return a record then call (which is the same for normal views):

If .Fetch Then

If you know that your query is going to return a record then call (which is the same for normal views):

While .Fetch Then

If your query, when run through SSMS is different than when run through CS0120 then you'll need to change the query. I've written huge queries with CS0120 with 15-20 sub-selects but I always try to keep my joins as simple as possible.

 

[iwells]

ok, wow ... Djangman THANK YOU!

I had NO idea that adding a watch on .Fetch would impact the code. Once I removed the watch it worked. I have VB/VBA/SQL experience, but this API at times is very, very foreign to me and while I can muddle my way through many of the methods or properties, small items like that are total unknowns to me.

Again, thank you SO MUCH for your patience and assistance.