Hey Guys,
I need a little help, i have a back order report im pretty sure i found on here. ive made some changes, but i’d really like to show the expected dates for the items. Im having trouble relating tables todo so. Is it as simple as adding PO.POrder to the table then adding a relation? the relations confuse me.
Best answer by CorbinHurst
View original
+18
Hello,
Are your Sales Order lines directly linked to the Purchase Order lines? If they are, the most accurate join will be from SOLine to POLine using both the Inventory ID and the Purchase Order type (drop-ship) and Purchase Order Number.
If the back order lines are not directly linked to purchase order lines, you can use Conditions on your GI to limit results to only Open POs, POType = Normal, POLine not marked Complete, Purchase order status = Open, etc. to narrow the selection to not-yet-received goods.
When joining (Relations) to obtain the promised date, link SOLine to POLine, on Inventory ID. Your results may show multiple promise dates, if there are multiple open POs for the same item.
I hope this helps you; please post your progress and we can help further as needed.
Laura
Hey Laura,
Thanks for your reply… Im dealing with the last scenario where SO and PO’s are not linked.
Ive tried a few different scenarios, but can’t quite get it to work.
My Tables are as follows.
I'm assuming where Im going wrong is relations. I don't quite understand how they interconnect or the way you’re asking me to link them.
Based off that im having no luck with conditions, for what im thinking is me not having the relations correctly configured?
I’ve attached the XML - ignore the results grid for now, im just adding things to see how it all works.
+18
Hello,
I think your join looks fine. There isn’t much more you can do to correctly connect SOLine to POLine when the Sales Order Line isn’t directly connected to the Purchase Order Line.
You have used Conditions to filter Sales orders for only open/not fulfilled lines. I recommend trying the same on the Purchase Order side:
See how this works out. You may have duplicate lines when the same Inventory Item is on multiple open Purchase Order Lines.
Laura
Thanks Laura,
Ive managed to get there. I still don't have my head fully around relations, but I’ve got what i needed for now. Also got the conditions right to where its displaying correctly.
My main issue here was relating both PO and SO with Inventory ID. This got me infinite results for all ID’s that were on BO.
Just in case it would be useful for anyone else, here it is. I duplicate the XML and get rid of some of the results columns so i can send this off the specific customers.
Thank you for sharing your solution with the community
Thank you for sharing your solution with the community
Thanks Chris, Unfortunately im getting an error trying to upload the XML.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
