Forum Discussion
FOZZY
2 years agoFrequent Visitor
Connect Work orders to Invoices based on date proximity
Hello. I need to connect work orders in table 1 to their corresponding Invoices in table 2 based on the date proximity from Work order submission and Invoice creation. The only thing they cave in co...
sayaliredij
Solution Sage
2 years agoHi FOZZY
You can create seperate table for Customer and connect invoices and work orders to that following way
Thanks,
Sayali
FOZZY
2 years agoFrequent Visitor
Thanks for the response. I might have used the wrong terminology in my request. Once the Work Order is submitted, it takes a few days for an Invoice to be generated (so the WO and INV do not show the same date). What I'd like to do is create a visual that shows the Customer ID, their Work Orders and the corresponding Invoice for each WO. but each customer has multiple WO's and Invoices - all on varius dates. I need a way to determine which Invoice goes with which WO based on the proximity of the dates submitted.
Examples...
Work Order Table
| CustID | WO_Date | WO_No | PartNo | UOM |
| G | 1/10/2024 | 2007 | REPAIR | HR |
| C | 11/15/2023 | 2001 | REPAIR | EA |
| A | 1/9/2024 | 2005 | NOCHG | HR |
| D | 1/9/2024 | 2006 | LABOR | HR |
| A | 11/30/2023 | 2002 | LABOR | EA |
| C | 1/5/2024 | 2003 | REPAIR | EA |
| B | 1/9/2024 | 2004 | REPAIR | EA |
Invoice Table
| CustID | Inv_Date | Inv_No | Price | Item | Qty |
| A | 12/5/2023 | 1001 | $0 | NOCHG | 1 |
| A | 1/13/2024 | 1005 | $0 | LABOR | 1 |
| B | 1/10/2024 | 1003 | $0 | LABOR | 2 |
| C | 11/20/2023 | 1004 | $0 | REPAIR | 1 |
| G | 1/13/2024 | 1006 | $0 | REPAIR | 1 |
| C | 1/10/2024 | 1002 | $77 | REPAIR | 2 |
| D | 1/13/2024 | 1007 | $0 | REPAIR | 2 |
Combined Data
| CustID | Inv_Date | WO_No | Inv_No | Price | Item | Qty |
| A | 12/5/2023 | 2002 | 1001 | $0 | NOCHG | 1 |
| A | 1/13/2024 | 2005 | 1005 | $0 | LABOR | 1 |
| B | 1/10/2024 | 2004 | 1003 | $0 | LABOR | 2 |
| C | 11/20/2023 | 2001 | 1004 | $0 | REPAIR | 1 |
| G | 1/13/2024 | 2007 | 1006 | $0 | REPAIR | 1 |
| C | 1/10/2024 | 2003 | 1002 | $77 | REPAIR | 2 |
| D | 1/13/2024 | 2006 | 1007 | $0 | REPAIR | 2 |