I need to make a BAQ with a rather unorthodoxed output. It needs to be CSV-like, but not exactly. It is for creating a file that will be used to upload data into another system.
The format consists of several line types: “Hdr”, “Inv”, Dtl". and the format for each line type varies. Making each of these is easy enough, but making a BAQ with all three is where I’m hitting a wall.
Imagine it’s just Invoice data, pulling from InvcHead, InvcDtl, and Customer. A normal BAQ output might look like:
And the desired output:
"Hdr",20200124,3,6000.00
"Inv",12345,2,"CUS01",1000.00,"NET30"
"Dtl","WIDGET101",3,200.00,600.00,"PG-66"
"Dtl","FUEL-ABC",1,400.00,400.00,"PG-FU"
"Inv",12346,1,"CUS99",2000.00,"NET15"
"Dtl","JETPACK",1,2000.00,2000.00,"PG-JP"
"Inv",12349,3,"CUS22",3000.00,"NET30"
"Dtl","ANVIL",3,500.00,1500.00,"PG-AV"
"Dtl","BIRDSEED",10,100.00,1000.00,"PG-BS"
"Dtl","MAGNET",1,500.00,500.00,"PG-RR"
Details of the above structure
That’s for 3 invoices:
- Inv# 12345, having 2 lines, totaling $1,000.00
- Inv# 12346, having 1 line, totaling $2,000.00
- Inv# 12349, having 3 lines, totaling $3,000.00
The first thing that should jump out at you, is the need for more rows than the data would really make in a normal BAQ. The example output is 10 rows, but there’s only 6 real rows of data.
I thought I could make a bunch of subquries with just one output column - the whole line would be in one field. The TopLevel query would generate the "Hdr",20210124, ...
line. No problem.
Next I’d make a subquery for the "Inv", 12345,...
lines. Making that subquery a Union, tacked those lines under the "Hdr",...
line. Giving me:
"Hdr",20200124,3,6000.00
"Inv",12345,2,"CUS01",1000.00,"NET30"
"Inv",12346,1,"CUS99",2000.00,"NET15"
"Inv",12349,3,"CUS22",3000.00,"NET30"
If I do the same for the `“Dtl”,…" lines, I end up with:
"Hdr",20200124,3,6000.00
"Inv",12345,2,"CUS01",1000.00,"NET30"
"Inv",12346,1,"CUS99",2000.00,"NET15"
"Inv",12349,3,"CUS22",3000.00,"NET30"
"Dtl","WIDGET101",3,200.00,600.00,"PG-66"
"Dtl","FUEL-ABC",1,400.00,400.00,"PG-FU"
"Dtl","JETPACK",1,2000.00,2000.00,"PG-JP"
"Dtl","ANVIL",3,500.00,1500.00,"PG-AV"
"Dtl","BIRDSEED",10,100.00,1000.00,"PG-BS"
"Dtl","MAGNET",1,500.00,500.00,"PG-RR"
How can I make it so that the "Dtl",...
lines follow the appropriate "Inv",...
line?
Or is there another way to structure the BAQ so that summary lines can be generated by groups?