Accpac AR Invoices and Customer Optional Fields
In Alchemex ive created a container linking AR ivoice headers and detail and customer
i wrote the report to list detailed sales by AR Items but each customer has Optional fields on it for district and resturant.
now i can link the Ar Customer optional fields Table in the container but this will create the following problem because of the way the table is it will list it Custno District value custno Resturant value this will give me 2 lines for the detail and i would like to make it come through as Column not as a row
so
Custno, invno, itemno, district, resturant
1 inv1 1000 2 abc
if i just link the optional table ill get
Custno, invno, itemno, optional, value
1 inv1 1000 District 2
1 inv1 1000 Resturant abc
i hope this makes sense please give me some advice on this matter
Views: 60
Replies to This Discussion
-
Permalink Reply by Leon Colton on -
The additional complexity of this report comes from the fact there are two Optional Fields, therefore it shows two instances for each detail line. Are you building your report as a Pivot Table? If so, there are a few ways to go about it. I’m not sure how would you like to layout this report, the easy way around that complexity is to show the report in the Outline Form, to prevent duplicate Customer/ Invoice fields for the same detail line. Another simple way to handle it is to place the Opt.Field in Filter section – this way the results will show by District. Running Values across the columns (providing there are only few) would be another option. You might also want to try the Slicer in conjunction with Filter, to show only single lines.
Leon Colton
Independent Sage ERP Accpac Consultant
-
Permalink Reply by Allan Smallie on -
Hi Maree,
Based on sample Accpac Data, the below view allows you to 'flatten' optional fields for each item.
This shows 3 fields, however you can see its easy to extend to include more.
Again, linking back to sales history would prove simple enough...
This would return much neater data to work with, and hence simplify your output
SELECT ICITEM.ITEMNO, ICITEM.[DESC], ICITEMO_2.VALUE AS COLOR, ICITEMO_1.VALUE AS DANGEROUS, ICITEMO.VALUE AS ITEMTYPE, ICITEMO_3.VALUE AS PRICE
FROM ICITEM INNER JOIN
ICITEMO ON ICITEM.ITEMNO = ICITEMO.ITEMNO AND ICITEMO.OPTFIELD = 'ITEMTYPE' LEFT OUTER JOIN
ICITEMO AS ICITEMO_2 ON ICITEM.ITEMNO = ICITEMO_2.ITEMNO AND ICITEMO_2.OPTFIELD = 'COLOR' LEFT OUTER JOIN
ICITEMO AS ICITEMO_1 ON ICITEM.ITEMNO = ICITEMO_1.ITEMNO AND ICITEMO_1.OPTFIELD = 'DANGEROUS' LEFT OUTER JOIN
ICITEMO AS ICITEMO_3 ON ICITEM.ITEMNO = ICITEMO_3.ITEMNO AND ICITEMO_3.OPTFIELD = 'PRICE'
-
Permalink Reply by Maree Palmer on
-
Thanx this looks exactly like what i am looking for where would you use the Select statement under the container under a the join or graphical join
-
Permalink Reply by Allan Smallie on
-
I would build this into a VIEW or treat it as a select statement, as it would be easier to add tables/optional fields in the future!
-
Permalink Reply by Shwu Hua Gan on
-
Have you tried creating SQL expression field using subquery - sql syntax. Note that in your syntax, you need to put in Bracket.
Eg; Create an SQL expression field call OptDistrict
The syntax will be something like (Select Value from ARCUSO where ARCUSO.CustID=ARCUS.CUSTID and ARCUSO.OptField='District')
The key is the bracket and also the field syntax.
Follow Alchemex on twitter
Like Alchemex on facebook
© 2012 Created by Admin.
