x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 3 replies
  • Subscribers 138 subscribers
  • Views 2969 views
  • Users 0 members are here
Browse Forums
Announcements
236
topics
29
replies
General Discussion
5k
topics
13k
replies
Sage Data & Analytics
56
topics
95
replies
Sage Enterprise Intelligence
210
topics
416
replies
Sage Intelligence Reporting
121
topics
322
replies
TritoneSub

Graphical Query Incorrect Link

Posted By

I am attempting to create a simple query showing invoices that have been posted for the day. I'm using SINVOICE, SINVOICEV, SINVOICED, and GACCENTRY all joined via NUM_0, plus a join from SINVOICE.GTE_0 to GACCENTRY.TYP_0. When I attempt to save/validate I'm getting this error:

Graphical query ZPOSTINV -> Table SINVOICED
SID0 : Incorrect link

I assume this is because there is a second key field SIDLIN_0 on SINVOICED that needs to be linked to something.
Only thing is there are no other detail tables in the query so I don't know what this should be linked to if anything.

I'm using data from SINVOICED in expressions that sum cost and gross profit totals: sum([F:SID]CPRPRI) and sum([F:SID]PFM) 
This is because as far as I know there are no fields in the invoice header tables that contain total invoice cost and GP. 

What am I doing wrong here?

  • 0

    Graphical query, from my point of view, is not so easy tool. I prefer to use a Query tool. 

    Try to add tables in the following order: SINVOICE -> SINVOICEV -> SINVOICED and as last GACCENTRY. All links, except GACCENTRY, will be assigned automatically from the system. After link SINVOICE to GACCENTRY. 

  • 0

    UPDATE: I tried fussing more with the graphical query using info from a reply, but the way it constantly scrambles the order/placement of fields and expressions led me to abandon that. Plus the fact that Flash is being abandoned by Chrome means this tool is a dead man walking...

    Next I tried using the regular query as suggested, but was getting a similar error saying I had an invalid link.

    Finally I decided to put on my big boy coding pants and do it directly in SQL. Slight smile

    I still need to make some adjustments to the expression adding up all the invoicing elements, (my org uses one of them as a discount percentage instead of a dollar amount) but other than that the code works. Here it is for anyone interested:

    SELECT SINVOICE.FCY_0, SINVOICE.NUM_0, SINVOICE.BPR_0, SINVOICE.BPYNAM_0, SINVOICE.INVTYP_0, GACCENTRY.CREDAT_0, SINVOICE.AMTATI_0, SINVOICE.AMTNOT_0,
    SINVOICEV.INVDTAAMT_0+SINVOICEV.INVDTAAMT_1+SINVOICEV.INVDTAAMT_2+SINVOICEV.INVDTAAMT_3+SINVOICEV.INVDTAAMT_4 As MiscDiscCharges,
    Sum(SINVOICED.CPRPRI_0) As CostofSale, Sum(SINVOICED.PFM_0) As GrossProfit
    FROM (((x3v11.PROD.SINVOICE SINVOICE
    LEFT OUTER JOIN x3v11.PROD.SINVOICEV SINVOICEV ON SINVOICE.NUM_0 = SINVOICEV.NUM_0)
    LEFT OUTER JOIN x3v11.PROD.GACCENTRY GACCENTRY ON SINVOICE.NUM_0 = GACCENTRY.NUM_0 AND SINVOICE.GTE_0 = GACCENTRY.TYP_0)
    LEFT OUTER JOIN x3v11.PROD.SINVOICED SINVOICED ON SINVOICE.NUM_0 = SINVOICED.NUM_0)
    WHERE SINVOICE.STA_0 = 3 AND GACCENTRY.CREDAT_0 = CAST(GETDATE() AS DATE)
    GROUP BY SINVOICE.FCY_0, SINVOICE.NUM_0, SINVOICE.BPR_0, SINVOICE.BPYNAM_0, SINVOICE.INVTYP_0, GACCENTRY.CREDAT_0, SINVOICE.AMTATI_0, SINVOICE.AMTNOT_0, SINVOICEV.INVDTAAMT_0, SINVOICEV.INVDTAAMT_1, SINVOICEV.INVDTAAMT_2, SINVOICEV.INVDTAAMT_3, SINVOICEV.INVDTAAMT_4
    ORDER BY SINVOICE.FCY_0, SINVOICE.NUM_0 DESC

  • 0 in reply to TritoneSub
    TritoneSub said:

    I tried fussing more with the graphical query using info from a reply, but the way it constantly scrambles the order/placement of fields and expressions led me to abandon that. Plus the fact that Flash is being abandoned by Chrome means this tool is a dead man walking...

    That is a reason why I do not use Graphical query - on Chrome it gets disordered and bugged. Built in Query tool works much better. For more complex requests - SELECT can be also registered in Sage and after any of Query tools can be run to collect/request data from it.   

*Community Hub is the new name for Sage City