Solved

Backorder Report - Linking inventory ID to Purchase order "expected Date"

  • 24 April 2024
  • 7 replies
  • 70 views

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.

 

 

icon

Best answer by CorbinHurst 6 May 2024, 04:28

View original

7 replies

Badge +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.

Badge +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:

Filter for POLines that are not completed and not cancelled.

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.

Userlevel 7
Badge

Thank you for sharing your solution with the community @CorbinHurst!

Thank you for sharing your solution with the community @CorbinHurst!

Thanks Chris, Unfortunately im getting an error trying to upload the XML.

Here is the GI for anyone that may want to use it.

 

Corbin

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved