Hi Adeel,
We'll get this for you - you've come to the right place. From what you said, it seemed to me that Tristan's tables were spot on. What information is missing? Moreover, what do you want it to look like. My concern about using something other than a view is the need to have the Account Index Master and Account Master included twice in the query. I do not think even SmartList Builder can do that. I think you would have better results if you could create a view, as Tristan mentioned, that could alias the tables. Then you could use the view as your data source. If you could give us the column names of the information you are imagining it might help us get you to what you want. I roughed out a quick and dirty SQL statement that will get you started:
SELECT IV10000.IVDOCNBR AS Doc_Number, IV10000.DOCDATE AS Doc_Date, IV10001.ITEMNMBR AS Item_Number, IV10001.TRXQTY AS Qty, IV10001.UOFM AS Unit_of_Measure, IV10001.UNITCOST AS Unit_Cost,
IV10001.EXTDCOST AS Extended_Cost, IV10001.TRXLOCTN AS From_Site, IV10001.TRNSTLOC AS To_Site, IV10001.TRFQTYTY AS Qty_Transferred, GL00105.ACTNUMST AS Inventory_Account,
GL00100.ACTDESCR AS IV_Acct_Description, GL00105_1.ACTNUMST AS IV_Offset_Account, GL00100_1.ACTDESCR AS IV_Offset_Acct_Description, IV10001.USAGETYPE, IV10001.TRTQTYTY, IV10001.IVDOCTYP
FROM GL00100 INNER JOIN
IV10000 INNER JOIN
IV10001 AS IV10001 ON IV10000.IVDOCNBR = IV10001.IVDOCNBR ON GL00100.ACTINDX = IV10001.IVIVINDX INNER JOIN
GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX INNER JOIN
GL00100 AS GL00100_1 INNER JOIN
GL00105 AS GL00105_1 ON GL00100_1.ACTINDX = GL00105_1.ACTINDX ON IV10001.IVIVOFIX = GL00100_1.ACTINDX
WHERE (IV10000.IVDOCTYP = 3)
I imagined that you wanted something similar to the following.
Doc Number
Doc Date
Item Number
Quantity
Unit Cost
Extended Cost
From Inventory Distribution Account
From Inventory Dist Acct Desc.
From Inventory Amount
To Inventory Distribution Account
To Inventory Dist Acct Desc
To Inventory Amount
What does what you want look like?
Kind regards,
Leslie
Share
Report
485
All responses (
Answers (
