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.

 

[ettienne]

.Compose

Sage 300 Certified Consultant

 

[DjangMan]

Another way to do that is to use CS0120 to write a SQL query and use that for your main control loop.

 

[iwells]

I get an error "Criteria Error. Account Optional Field. Syntax Error. Field Not Found" from the following code:

Dim GLACCOUNT1header As AccpacCOMAPI.AccpacView
Dim GLACCOUNT1detail4 As AccpacCOMAPI.AccpacView

mDBLinkCmpRO.OpenView "GL0001", GLACCOUNT1header
mDBLinkCmpRO.OpenView "GL0400", GLACCOUNT1detail4

GLACCOUNT1detail4.Compose Array(GLACCOUNT1header)

GLACCOUNT1header.Init
GLACCOUNT1detail4.Init

GLACCOUNT1detail4.Browse "ACSEGVAL01 = ""4000""", True

Sorry, I've never done this so I'm not sure what's missing?

 

[iwells]

DjangMan, the query I want to use would be extremely long and it's probably better written with a few nested loops for understandability.

 

[DjangMan]

.browse on GLACCOUNT1header for the optional field value.

 

[ettienne]

Compose the 2 views GLAMF and GLAMFO.
Loop GLAMF and then have a nested loop for GLAMFO.

Sage 300 Certified Consultant

 

[iwells]

DjangMan, ok so that fixed and shows my lack of understanding around this method. When you .Compose you're linking GLAMFO to GLAMF, not the other way around so to speak. I thought GLACCOUNT1detail4.Compose Array(GLACCOUNT1header) meant I joined GLAMF to GLAMFO and therefore I would browse on GLAMFO when in fact it's the other way around?

 

[DjangMan]

I believe that optional fields are a special case scenario so even though we would think of them as a kind of 'detail' record Sage allows us this small luxury.

However, I believe that you cannot change the .order of your GLACCOUNT1header to anything other than 0 which means that you cannot optimize your query on ACSEGVAL01 (for which there is an index). Probably isn't an issue until you're dealing with 100,000+ records but you're going to be reviewing all of the records in GLAMF in your loop. Using Ettienne's solution allows you to change the .order of the header to take advantage of the index on ACSEGVAL01 to target just those GL accounts you want to consider.

 

[ettienne]

You compose them both ways: GLAMF to GLAMFO and GLAMFO to GLAMF - this sets up the relationship or linking.
You will have an outer loop on GLAMF and an inner loop on GLAMFO.
GLAMF determines which GL account you are working with, GLAMFO determines which optional field you are working with related to the GL account in GLAMF.

Sage 300 Certified Consultant

 

[iwells]

Ok, becoming extremely frustrated ...

Dim vGLAMF As AccpacView
Dim vGLAMFO As AccpacView
Dim vGLAMFFields As AccpacViewFields
Dim vGLAMFOFields As AccpacViewFields

mDBLinkCmpRW.OpenView "GL0001", vGLAMF
mDBLinkCmpRW.OpenView "GL0400", vGLAMFO

vGLAMF.Compose Array(vGLAMFO)
vGLAMFO.Compose Array(vGLAMF)

Errors ... Field 'OPTFIELD' is not a valid field name / Field 'VALUE' is not a valid field name

What am I doing wrong here?

 

[Jay Converse]

OPTFIELD is in vGLAMFO, not vGLAMF

 

[ettienne]

Record a macro while you add an optional field to a GL account, this should give you some direction.

Sage 300 Certified Consultant

 

[DjangMan]

It's possible that this method doesn't work. The finder supports this so perhaps it is a special case for the finder.

I just tried it and it doesn't work as you're wanting.

Go with Ettienne's suggestion and .browse for the accounts in the vGLAMF view and then .Browse for the optional field value that you're looking for in vGLAMFO in an inner loop. Or use CS0120 to locate the records in one step (but you still have to vGLAMF.read to get to the record to do anything with it).

 

[iwells]

I'm taking the CS0120 route, but still having an odd issue I can't explain ... here's the query I've hard coded and tested in SQL management studio to confirm it does return a result:

vCSQRY.Browse "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 AND GLAMF.ACSEGVAL01 = '4200'", True

Seems like a lot, but I'm trying to isolate the most recent AUDTDATE/AUDTTIME value for each ACSEGVAL01 and OPTFIELD combination so that when I create the account optional field I take the most recent optional field value for each ACSEGVAL01 instance. I've hardcoded the ACSEGVAL01 value I'm testing, which I know in fact exists, but when I try to filter the data based on any account it faily, however if I take the filter off I get result and when I look through the results on of them is in fact account 4200.

So if I remove "AND GLAMF.ACSEGVAL01 = '4200'" from the end of the query I get results and one of them is 4200, but once I add that in I get no results. Can't figure it out.

 

[iwells]

I don't think the query is that complex, but I don't see another way to do it (well I could do it using exists clauses but that's just as messy) ... the thing is in my testing if I filter by anything OTHER than ACSEGVAL01 it works and I can't understand why

 

[DjangMan]

Works when I run this against sample data. Try changing the last AND to a WHERE since you're only interested in GL accounts from GLAMF with that value.

 

[iwells]

Yea I did that and still doesn't work ... I can't understand it

 

[DjangMan]

Does it work if you run it in your SQL Server Management Studio?

 

[iwells]

it does, that's what I can't understand ... what's more frustrating if I select on something like ACCTTYPE or something else it work, but ACSEGVAL01 doesn't