Hi Darin,
Any time that I am creating a SmartList that requires more than 1 join I feel that SQL Views the best and most flexible way, and then you can bring that SQL View into SmartList Designer or Builder in your case. I wouldn't be able to put the amount of time required into creating these custom scripts for you on a forum post, and at this point is not super clear exactly what the front-end user is looking for as there are a ton of factors that could play into this. In general, where would be the tables involved that you can use as a starting point.
Open POs would just be any PO in the POP10100 with a status that is not 5 or 6 (closed or cancelled) so that can really just be a select statement on its own using the POP10100 or POP10110.
POSTATUS
1=new
2=released (printed)
3=change order
4=received
5=closed
6=canceled
As for Invoices, if you are always doing shipment/invoices or are always doing shipments and then matching invoices it might be more difficult.
select * from POP10110 where PONUMBER = 'PO2080' --PO Lines
select * from POP10500 where PONUMBER = 'PO2080' --PO Receipt Lines (tracks what has been shipped and invoiced so far and links the receipt back to specific PO Lines)
select * from POP30310 where PONUMBER = 'PO2080' --Posted Receipt Lines (tracks what has been shipped and invoiced so far)
If you always posted enter/match invoices separate from shipments then you could use the POP10600 table, but that is not populated if you use shipment/invoices.
Another factor that will come into play is whether you consider Invoices that are created but not posted yet, and you would have to join the POP10310 for work records besides the POP30310 table for posted receipts too if you are going to break it down further by date with unposted items.
Ultimately, I believe what they are asking for would be possible but it's not going to just be a matter of joining a couple tables. You will have to add logic to the queries and really narrow down the rules of what they want to query.
I put this together quick to get you started, but again we can't provide custom scripting and fine tuning through a forums post as there is a lot of time and effort that would be involved in this.
select a.PONUMBER, a.EXTDCOST as 'POLINECOST',b.EXTDCOST as 'INVOICED',b.POPRCTNM, c.receiptdate from POP10110 a left join POP10500 d on a.PONUMBER = d.PONUMBER and a.ORD = d.POLNENUM left join POP30310 b on d.POPRCTNM = b.POPRCTNM and d.RCPTLNNM = b.RCPTLNNM left join POP30300 c on c.POPRCTNM = b.POPRCTNM order by a.PONUMBER
This would list out PO Lines, their line's original cost, the cost that has been invoiced on them so far, and would return null for PO Lines that have not been invoiced at all.
I hope this helps!
Isaac Olson
Microsoft Support
Share
Report
55
All responses (
Answers (
